mysqldumper.sh - Backup mysql databases
mysqldumper.sh - 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
$ 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
mysqldump will search for option files in a pre-arranged order that is
dependant on host-installation. To see the search order, run
--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
[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
.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
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
dbname parameters on the command-line.
When used to backup all databases, the program will use the
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.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
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
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
mysqldumper.sh on a host without savelog(8) in
the path, it will automatically switch to date-roll.
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
mysqldumper.sh creates a log record of actions it took. Backup-logs are
stored in the
DUMPS directory, and named
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
>>> between the timestamp and the message.
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#
mysqldumper.sh [-?] [-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.
Display help and exit
Generate diagnotic logging.
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.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.
Run in reporting mode. It does not perform an actual backup, but instead reports on what it would do.
Path to store backups. Path will be created if it doesn’t already exist. Defaults to
Age of oldest backup in days from today. Default is to keep 10-days and delete any older backups and backup-logs.