mysqldumper.sh - Backup mysql databases

Overview

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

mysqldumper.sh 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:

[mysqldump]
host=localhost
user=root
password=secret-password

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 mysqldumper.sh 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

mysqldumper.sh 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.

Note

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

Backup retention

mysqldumper.sh 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

mysqldumper.sh 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.

Note

One caveat to the one-log-per-day design is the limitation that only a single instance of mysqldumper.sh 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: mysqldumper.sh [-?] [-d] [-t] [-n] [-p DUMPS] [-o OLDEST] [dbname [dbname ...]]

Optional positional arguments:

dbname

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.