- Backup mysql databases#



Backup mysql databases. The backups are simple dump files suitable for restoration using the mysql command-line tool. The dumps are compressed to conserve space, however you can uncompress and inspect and even alter them with a simple text editor.

This program is provided as part of the Carroll-Net Healthy Workstation backup system. It is designed to be invoked by cncopagent as a pre-backup command. The basic sequence is for cncopagent to run this program to create a dump file and then run the backupagentnq agent to transfer the backup offsite to the cncop network. cncopagent will capture any errors in the pre-backup step and report them to the cncop daily job reports.

How to restore a backup#

Backups are restored using the mysql command-line tool. Backups are compressed to save space, so you decompress them prior to restoration. The backups include the meta-commands needed to completely recreate the environment before loading the data.

For safety, the backup does not include DROP/CREATE DATABASE statements. You must perform this step manually before restoring the backup. For example, to restore the database DB1:

$ echo "CREATE DATABASE DB1" | mysql
$ gunzip -c DB1.sql.0.gz | mysql DB1

Configure the backup environment# uses the client mysqldump to create mysql backups. You can control the client command-line parameters using mysql options files. mysqldump will search for option files in a pre-arranged order that is dependant on host-installation. To see the search order, run mysqldump --help and look for the option file order:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Option files syntax is similar to *.ini syntax, with sections separated by bracketed section headers, followed by key=value assignments. Here’s a simple example option file to control how mysqldump connects to mysqld for backups:


Rather than store the credentials in clear text, you encrypt the credentials using mysql_config_editor. An encrypted version of the above setup could be done with the command:

$ mysql_config_editor set --login-path=mysqldump \
    --host=localhost --user=root --password
Enter password: *******

The editor will store the encrypted credentials in your home directory, in the file .mylogin.cnf. Note: Run mysql_config_editor from the user-account used to create backups to ensure the encrypted file is stored in the correct home directory.

You can use both encrypted credentials files and plain-text options files. This gives you the ability to use secure credentials, and still provide additional control over other mysqldump command-line options.

See mysqldump option summary for the complete list of mysqldump options.

Select which databases to backup#

The default is for to backup all databases. It can optionally be used to limit backups to specific databases by secifying the optional dbname parameters on the command-line.

When used to backup all databases, the program will use the SHOW DATABASES command to generate a list of databases. The user who runs this program must have permission to query and backup the installed databases. The system will automatically exclude the three mysql system views; namely information_schema, performance_schema and sys. They are not real databases and cannot be restored.

Cycle-roll backups vs Date-roll backups# defaults to cycle-roll backups. Cycle-roll uses a simple numbering system that identifies the generation of the backup. Each successively older generation is given a larger-numbered suffix, similar to the technique used by unix logrotate(8) which archives syslog messages.

Cycle-roll backups are named dbname.sql.0.gz, dbname.sql.1.gz, dbname.2.gz etc… Each time the backup is run, older backups are re-numbered 1 number larger.

Date-roll backups are named using the date they were created. The format is dbname-YYYY-MM-DD-HH-MM.sql.gz.

Both styles of rolling continue to accumulate backups and backup-logs in the path specified by --dumps DUMPS until they reach their expiration date (see Backup retention).

Its recommended to use the default cycle-roll system. Cycle-roll provides a previous generation file for the cncop backup agents to pack-update which will dramatically reduce the time to transfer backups offsite. Date-roll backups on the other hand always result in a pack-add, which takes more time backup offsite.


Cycle-roll requires the savelog(8) utility. If you run on a host without savelog(8) in the path, it will automatically switch to date-roll.

Backup retention# accepts an --oldest OLDEST command-line option to control how many days of backups and backup-logs to preserve. The parameter passed is the number of days to keep, counting from today. It defaults to preserving 10-days. Any backups and backup-logs older than this option will be deleted.

Logging# creates a log record of actions it took. Backup-logs are stored in the DUMPS directory, and named LOG-YYYY-MM-DD.log. All runs of the program from the same day are appended to this daily log file. This one-log-per-day system makes it easy to review backups that may have taken place at different times in the same day.

Some log-messages are considered phase-messages. These represent a change in the phase of the backup process, eg, initializing, creating-backup, compressing, etc… Phase-messages are printed to stdout to provide feedback while the backup is running. Phase-messages contain the three character chevron-string >>> between the timestamp and the message.


One caveat to the one-log-per-day design is the limitation that only a single instance of can run at a time. If more than one instance runs, the two overwrite the log file, likely creating a big mess.

Command line options#

usage: [-?] [-d] [-t] [-n] [-p DUMPS] [-o OLDEST] [dbname [dbname ...]]

Optional positional arguments:#


One or more named databases to backup. If not specified, the default is to backup all databases.

Optional arguments:#

?, -h, --help#

Display help and exit

-d, --debug#

Generate diagnotic logging.

-t, --dateroll#

Date-roll backups dumps, which names backup files with the date, eg, dbname-YYYY-MM-DD-HH-MM.sql.gz. Default is cycle-roll which numbers the backups based on their generation, eg, dbname.0.sql.gz, dbname.1.sql.gz, dbname.2.sql.gz, etc… Cycle-roll is better for cncop which can use the previous backups to reduce the time to transfer the backups offsite.

Note: If savelog(8) is not installed, the default is changed to date-roll.

-n, --dryrun#

Run in reporting mode. It does not perform an actual backup, but instead reports on what it would do.

-p DUMPS, --dumps DUMPS#

Path to store backups. Path will be created if it doesn’t already exist. Defaults to /var/lib/mysql/backups.

-o OLDEST, --oldest OLDEST#

Age of oldest backup in days from today. Default is to keep 10-days and delete any older backups and backup-logs.