:mod:`mysqldumper.sh` - Backup mysql databases ############################################## .. module:: mysqldumper.sh :synopsis: Backup mysql databases. .. moduleauthor:: Jim Carroll .. image:: ../appicons/pull.png 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 :mod:`cncopagent` as a pre-backup command. The basic sequence is for :mod:`cncopagent` to run this program to create a dump file and then run the :mod:`backupagentnq` agent to transfer the backup offsite to the cncop network. :mod:`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 ================================ :mod:`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 :file:`*.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 :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 :mod:`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 ======================================= :mod:`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 :file:`dbname.sql.0.gz`, :file:`dbname.sql.1.gz`, :file:`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 :file:`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 :mod:`mysqldumper.sh` on a host without savelog(8) in the path, it will automatically switch to date-log. Backup retention ================ :mod:`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 ======= :mod:`mysqldumper.sh` creates a log record of actions it took. Backup-logs are stored in the ``DUMPS`` directory, and named :file:`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 :mod:`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: ============================== .. option:: dbname One or more named databases to backup. If not specified, the default is to backup all databases. Optional arguments: =================== .. option:: ?, -h, --help Display help and exit .. option:: -d, --debug Generate diagnotic logging. .. option:: -t, --dateroll Date-roll backups dumps, which names backup files with the date, eg, :file:`dbname-YYYY-MM-DD-HH-MM.sql.gz`. Default is cycle-roll which numbers the backups based on their generation, eg, :file:`dbname.0.sql.gz`, :file:`dbname.1.sql.gz`, :file:`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. .. option:: -n, --dryrun Run in reporting mode. It does not perform an actual backup, but instead reports on what it would do. .. option:: -p DUMPS, --dumps DUMPS Path to store backups. Path will be created if it doesn't already exist. Defaults to :file:`/var/lib/mysql/backups`. .. option:: -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.