Software >

(on Wikipedia)
https://github.com/mysql/mysql-server
https://www.mysql.com/

Ubiquitous database software.

• PhpMyAdmin is an important tool.

–

• I’ve used this for more years than I’d care to think about; I hate it.

Usage ∞

• Don’t back up information_schema. It is not needed.

Dumping a database to a local file ∞

• 2024-12-29 – mysqldump Ver 10.19 Distrib 10.6.20-MariaDB, for Linux (x86_64)

# This prompts for a password

database_user='DATABASE USER NAME'
database_name='DATABASE NAME'
filename="${database_name}--$( date --utc +'%Y-%m-%d_%H꞉%M' ).sql.bz2"

mysqldump --password --add-drop-database --user="$database_user" "$database_name" | bzip2 > "$filename"

• 2024-12-28 – mysqldump Ver 10.19 Distrib 10.6.20-MariaDB, for Linux (x86_64)

file='dump.sql.gzip'
\mysqldump  --all-databases  --password  |  \gzip > "$file"

• 2024-12-28 – mysqldump Ver 10.19 Distrib 10.6.20-MariaDB, for Linux (x86_64)

file='dump.sql.bz2'
\mysqldump  --all-databases  --password  |  \bzip2 > "$file"

• 2024-07-22: Used a long time ago; versions not recorded.

file='/path/to/dump.sql'

# TODO - Can these commands be combined?  Maybe stream it?
\mysqldump  soh_wp  -u soh_wp_user  -p > "$file"
\gzip  "$file"

Importing a database from a local file ∞

• 2024-12-28 – mysql Ver 15.1 Distrib 10.6.20-MariaDB, for Linux (x86_64) using readline 5.1

file='dump.sql.bz2'
database_user='USERNAME'
database_name='DATABASENAME'
# It uses the database name and tables specified in the file; be careful!
bzip2 -dc "$file" | mysql --password --user="$database_user" "$database_name"

UNTESTED

file='dump.sql.gzip'
gunzip -c "$file" | mysql -u soh_wp_user -p soh_wp

Search and replace ∞

First, confirm your search would actually find something. In PhpMyAdmin you would select the database on the left, then at the top click the Search tab.

A simple version:

UPDATE TABLE_NAME
SET column_name = REPLACE(column_name, 'search_string', 'replace_string')
WHERE column_name LIKE '%search_string%';

A complex version which lets you run a whole lot of stuff all in one go:

DROP PROCEDURE IF EXISTS SearchReplace;
DELIMITER //
CREATE PROCEDURE SearchReplace(
    IN TABLE_NAME VARCHAR(255),
    IN column_name VARCHAR(255),
    IN old_value VARCHAR(255),
    IN new_value VARCHAR(255)
)
BEGIN
    SET @SQL = CONCAT('UPDATE ', TABLE_NAME, 
                      ' SET ', column_name, ' = REPLACE(', column_name, ', "', old_value, '", "', new_value, '")', 
                      ' WHERE ', column_name, ' LIKE "%', old_value, '%";');
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

--

SET @old_value = 'search_string';
SET @new_value = 'replace_string';

CALL SearchReplace('table_name_1', 'column_name_1', @old_value, @new_value);
CALL SearchReplace('table_name_2', 'column_name_2', @old_value, @new_value);