mysqldumper.sh - Backup mysql databases
Contents
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.