Configure MySQLdump
Updated on 28 Dec 2018
mysqldump
is a mysql application for dumping a database to a text file. It is typically used for backups. Here is my configuration file.
/etc/mysql/conf.d/mysqldump.cnf
[mysqldump]
quick
max_allowed_packet = 16M
user = backup_user
password = myPassword
add-drop-database
comments
dump-date
flush-logs
Creating a user account for mysqldump
We need to create a user account for the backup purposes. The user account name is the same one that we used in mysqldump.cnf
file above. Below is the command to create the account, and by default also has the requirements necessary:
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'myPassword'
GRANT select, reload, file, process, super, lock tables, show view ON *.* TO 'backup_user'@'localhost';
Creating a user that already has a hashed password
Sometimes we only have the hash of the password, we can still create a user account. We just need to use the keyword password
in our SQL.
CREATE USER 'dummy'@'localhost' IDENTIFIED BY PASSWORD '*AD3ADAF45AA29B98635023064D912D20A4012606';
We use this technique with our back-up scripts.
Running mysqldump
There are many configurations available for mysqldump
, and hopefully most of them have already been set in the configuration file. I run it like this:
mysqldump --databases demoapp > demoapp.sql
The file that is produced can be used directly from the command line to re-create the database.
Reloading a database
You can reload a database from backup with the following command:
mysql -u root -p < demoapp.sql
Script to backup MySQL user accounts
Backing up databases is fairly straight-forward, however backing up the user accounts requires a little more finesse. The following script does the following:
- gets the users from the
user
table - iterates over the users feeding the user into
show grants for ...
- output is SQL that can be re-run to create and load user accounts
$db = new PDO('mysql:host=localhost;dbname=mysql;charset=utf8',
'backup_user',
'myPassword');
$query = "SELECT CONCAT('\'',user,'\'@\'',host,'\''), authentication_string
FROM mysql.user
WHERE user != 'root'";
$result = $db->query($query);
while($row = $result->fetch(PDO::FETCH_NUM))
{
$query = "show grants for $row[0]";
$stmt = $db->query($query);
echo "\n\n-- Create User Account for $row[0]";
echo "\nCREATE USER $row[0] IDENTIFIED BY PASSWORD '$row[1]';";
echo "\n\n-- Privileges for $row[0]";
while($grant = $stmt->fetch(PDO::FETCH_NUM)) {
echo "\n$grant[0];";
}
echo "\n\n-- ---------------------------------------------";
}
echo "\n\nflush privileges;\n";