Configure MySQLdump

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";