mysql-backup.1

.\" Automatically generated by Pandoc 2.9.2.1
.\"
.ad b
.TH "mysql-backup" "1" "2022-10-20 02:15:56" "mysql-backup" "User manual"
.hy
.SH NAME
.PP
mysql-backup - Backup MySQL databases.
.SH SYNOPSIS
.PP
mysql-backup options [ --dump | --snapshot | --check ]
.SH DESCRIPTION
.PP
\f[I]mysql-backup\f[R] performs daily, weekly and monthly backup of
MySQL databases.
Features:
.IP \[bu] 2
Backup multiple databases
.IP \[bu] 2
Compress backup files
.IP \[bu] 2
Backup remote servers
.IP \[bu] 2
Rotate backups
.IP \[bu] 2
more...
.SH ARGUMENTS
.SS Options
.TP
-c CONFIG, --config CONFIG
A configuration file to use instead of default one.
This option allows different configuration for different MySQL servers.
.TP
-v, verbose
Run in verbose mode.
.TP
-h, --help
Show a short help screen.
.TP
--dry-run
Run in \f[I]dry-run\f[R] mode, do not perform any action.
.TP
--dump, -d
Do a \f[I]mysqldump\f[R] backup.
.TP
--snapshot, -s
Perform a binary backup using \f[I]lvm\f[R] or \f[I]zfs\f[R] snapshot.
.TP
--check, -C
Do a nagios comptatible check (See MONITORING below).
.PP
NOTE: To restore a \f[I]LVM\f[R] or \f[I]ZFS\f[R] snapshot you just need
to untar the archive.
.SH CONFIGURATION FILE
.SS Default configuration
.PP
\f[I]mysql-backup\f[R] looks for a default configuration file in that
order:
.IP \[bu] 2
\f[C]/etc/mysql-backup/default.cnf\f[R]
.IP \[bu] 2
\f[C]\[ti]/.mysql-backup.cnf\f[R]
.IP \[bu] 2
\f[C]./.mysql-backup.cnf\f[R]
.PP
If no default configuration file is found, default hard-coded values
would be used.
.SS Specific configuration file
.PP
In addition of the default configuration, a specific configuration file
can be used (with the \f[C]--conf\f[R] option).
.SS General options
.TP
BACKUP_DIR
Where the backups files would be stored.
A generic backup file schema is:
.RS
.PP
<BACKUP_DIR>/<host>/<binary|dump>/<daily|weekly|monthly>/<base>_YYYY-MM-DD.sql[.<COMPRESSION_EXTENSION>]
.RE
.SS MySQL options
.TP
DATABASES
Databases to backup.
If this value is set to \f[I]ALL\f[R] then all databases would be
backuped.
\f[I]mysql-backup\f[R] would determine the database using the \[dq]SHOW
DATABASES\[dq] query.
.TP
IGNORE_DATABASES
A list of database to ignore during backup.
By default, \f[I]information_schema\f[R] and
\f[I]performance_schema\f[R] are ignored.
.PP
In addition to that option all mysql(1) and mysqldump(1) options are
also recognized as long as dash (\f[C]-\f[R]) are changed to underscore
(\f[C]_\f[R]).
.PP
NOTE: However some options are not recognized: \f[I]help\f[R],
\f[I]pipe\f[R], \f[I]table\f[R], \f[I]version\f[R], \f[I]databases\f[R],
\f[I]ignore-table\f[R], \f[I]ssl\f[R], \f[I]execute\f[R].
.PP
EXAMPLE: This is the default MySQL configuration:
.IP
.nf
\f[C]
batch=1
skip_column_names=1
quote_names=1
opt=1
add_drop_database=1
\f[R]
.fi
.PP
EXAMPLE: If you use \f[I]mysqld_multi\f[R] you can defined a common
complex configuration file (ie.
\f[I]/etc/mysql-backup/__multi-defaults.inc\f[R]) such as:
.IP
.nf
\f[C]
BACKUP_DIR=/var/backups/mysql/
 HOST=articles${_instance}
TARGET_MOUNT=/tmp/$HOST-bck
LVM_SNAPSHOT_SIZE=\[dq]100G\[dq]
mysql_instance=mysqld${_instance}
proto=SOCKET
socket=/var/run/mysqld/mysqld_${_instance}.sock
tar_opts=\[dq]\[dq]
mysqladmin_extra_file=/etc/mysql/debian_${_instance}.cnf
user=$(${my_print_defaults} -c ${mysqladmin_extra_file} client | awk -F= \[aq]{if($1\[ti]/\[ha]--user$/){print $2}}\[aq])
password=$(${my_print_defaults} -c ${mysqladmin_extra_file} client | awk -F= \[aq]{if($1\[ti]/\[ha]--password$/){print $2}}\[aq])
\f[R]
.fi
.PP
and an instance specific configuration in
(\f[I]/etc/mysql-backup/instance00.cnf\f[R]):
.IP
.nf
\f[C]
instance=00
source /etc/mysql-backup/__multi-defaults.inc
\f[R]
.fi
.PP
And a cron such as:
.IP
.nf
\f[C]
01 00 * * * root mysql-backup -c /etc/mysql-backup/instance00.cnf --snapshot
\f[R]
.fi
.TP
SUFFIX
Suffix to \f[I]datadir_path\f[R] when creating a snapshot backup.
You can safely leave it empty if you are using default mysql
configuration.
If you are using your own layout you should use this option.
.PP
EXAMPLE: If your layout is something like that:
.IP
.nf
\f[C]
 /var/lib/mysql/db_00
 |-- binlog
 |-- config
 |   |-- conf.d
 |   |   \[ga]-- mysql-multi.cnf
 |   |-- debian.cnf
 |   |-- my.cnf
 |   \[ga]-- mysql-backup.cnf
 |-- data
 |   |-- aria_log.00000001
 |   |-- aria_log_control
 |   |-- mysql
 |   |-- relay-log.info
 |   |-- show-master-status
 |   \[ga]-- show-slave-status
 |-- log
 |-- mysql-multi.txt
 \[ga]-- tmp
\f[R]
.fi
.PP
\f[I]datadir_path\f[R] is pointing to
\f[I]/var/lib/mysql/db_00/data\f[R] but you also want to backup other
files so you have to set \f[I]SUFFIX\f[R] to \f[I]..\f[R].
.TP
MYSQL_PING_TIMEOUT
Startup time given to mysql before declaring it non-functional (default
600s).
This is used when starting mysql in the temporary directory before
creating the archive.
.SS Archive options
.TP
COMPRESSION
The tool to use for compression.
Currently \f[I]gzip\f[R], \f[I]pigz\f[R], \f[I]bzip2\f[R] and
\f[I]xz\f[R] are recognized.
If compression if not known then no compression would be used.
.PP
NOTE: \f[I]gzip\f[R] generates bigger files than the others but needs
less CPU time.
.TP
COMPRESSION_OPTS
Options to pass to the compression tool.
.TP
DAILY_RETENTION
How many days a daily backup should be kept.
By default daily archives are kept 7 days.
.TP
WEEKLY_RETENTION
How many days a weekly backup should be kept.
By default weekly archives are kept 35 days (5 weeks).
.TP
MONTHLY_RETENTION
How many days a monthly backup should be kept.
By default monthly archives are kept 365 days (12 months).
.TP
WEEKLY_DAY
Which day weekly backup are done (0..6, 0 is Sunday).
.TP
MONTHLY_DAY
Which day monthly backup are done (00..31).
.TP
HOST
Name of the host to backup for logging purposes.
.PP
NOTE: This is not the mysql host to backup (use \[dq]host\[dq] in
lowercase for that).
.SS LVM Options
.TP
LVM_EXT
Extension for the LVM snapshot (Default: \[dq]_bkp\[dq]) that would be
added to the current LVM volume name.
.TP
LVCREATE_OPTS
Options to pass to lvcreate(1) when doing LVM snapshot (Default:
\[dq]--chunksize=256\[dq]).
.TP
LVREMOVE_OPTS
Options to pass to lvremove(1) when purging a snapshot (Default:
\[dq]-f\[dq]).
.TP
TARGET_MOUNT
Where to mount the LVM snapshot before archiving the data (Default:
\[dq]/tmp/mysql-snapshot\[dq]).
.SS ZFS Options
.PP
There are no \f[I]ZFS\f[R] option.
mysql-backup use ZFS by default if it detects mysql is running on a
\f[I]ZFS\f[R] volume.
.SS Hooks
.PP
Hooks are scripts that can be run via run-parts(8).
Each hook parameter consists of a directory path suitable for
run-parts(8).
.PP
See run-parts(8) for further information on how hooks are run.
.PP
See \f[I]HOOK DETAILS\f[R] section for details.
.SH ARCHIVE PROCEDURE
.PP
Every day backups are done in the \f[I]daily\f[R] directory.
On \f[I]WEEKLY_DAY\f[R] the daily backup is hard linked to the
\f[I]weekly\f[R] directory (the same is done for monthly backups on
\f[I]MONTHLY_DAY\f[R] and \f[I]monthly\f[R] directory).
.PP
After that archives older that \f[I]DAILY_RETENTION\f[R],
\f[I]WEEKLY_RETENTION\f[R] and \f[I]MONTHLY_RETENTION\f[R] are removed
from their specific directories.
.PP
This system keeps space on the backup server by the use of hard links.
.PP
NOTE: This only works if all backups are in a single partition.
.SS How is this done?
.PP
First \f[I]mysql-backup\f[R] generate a \f[I]LVM\f[R] snapshot of the
\f[I]mysql\f[R] you want to backup.
During that snapshot creation the replication is stopped, the tables are
locked (\[dq]FLUSH TABLES WITH READ LOCK\[dq]).
Then the current replication status (for both master and slave) are
dumped into mysql \f[I]datadir\f[R] in files
\f[I]show-master-status\f[R] and \f[I]show-slave-status\f[R].
.PP
For each kind of backup (snapshot or dump) an other \f[I]mysqld\f[R]
instance is started using the new \f[I]lvm\f[R] snapshot as
\f[I]datadir\f[R].
This will ensure the rebuild of innodb journal and indexes.
Then the archive process is run (\f[I]mysqldump\f[R] for dump and
\f[I]tar\f[R] for snapshot).
.PP
NOTE: For big databases you\[aq]d better want to use a snapshot backup
since the archive process would be faster and the restoration either.
.PP
Once every backup are done, the \f[I]lvm\f[R]/\f[I]zfs\f[R] snapshot is
removed.
.SS Performances
.PP
For better performances, it is advised to run mysql-backup on a
dedicated backup server instead of production, especially if you are
using pigs(1).
.PP
\f[I]ZFS\f[R] offers better performances than \f[I]LVM\f[R].
You might also want to use the \f[I]ZFS\f[R] compression features to
drastically reduce the IOs.
.PP
As an example backing up 10 mysql database representing about 760Gb took
almost one day using \f[I]LVM\f[R].
The server had a lot of harddrive IOwait.
The very same hardware was used to perform backups on \f[I]ZFS\f[R]
volumes using compression allowed to raise the mysql instances to 24.
The hard drive usage drops to 250Gb thanks to \f[I]ZFS\f[R] compression.
The whole backup process took less than 2 hours.
.PP
On the mysql-backup instances you also want to totally disable binary
logs.
Use \f[C]skip_log_bin=1\f[R] and \f[C]log_slave_update=0\f[R] for that
purpose.
.PP
If you are using pigz(1) avoid running several backups in parallel.
.SH Restoration procedure
.PP
For binary snapshot you only need to untar the archive on a new server
to create a clone.
.PP
For dump backups, you need to replay every database files, such as:
.IP
.nf
\f[C]
zcat base_YYY-MM-DD.sql.gz | mysql -
\f[R]
.fi
.SH HOOKS DETAILS
.SS Dump hooks
.TP
pre_dump_backup_hook
Hook to be run before the dump backup process really starts.
.TP
post_dump_backup_hook
Hook to be run after the dump backup process is done.
.TP
pre_dump_restore_hook
Hook to be run before the dump restore process really starts.
.TP
post_dump_restore_hook
Hook to be run after the dump restore process is done.
.PP
NOTE: In addition hook names could be postfixed with a database name.
This means a hook could be defined for a specific database.
.PP
EXAMPLE: \f[I]post_dump_backup_hook_a_database\f[R] is ran before
\f[I]a_database\f[R] would be backuped.
.SS Snapshot hooks
.TP
pre_snapshot_backup_hook
Hook to be run before a snapshot really stats.
.TP
post_snapshot_backup_hook
Hook to be run when a snapshot is done.
.TP
pre_snapshot_backup_lvm_snaphost_hook
Hook to be run before the LVM snapshot is started.
.TP
post_snapshot_backup_lvm_snaphost_hook
Hook to be run after the LVM snapshot is done.
.TP
pre_snapshot_backup_zfs_snaphost_hook
Hook to be run before the ZFS snapshot is started.
.TP
post_snapshot_backup_zfs_snaphost_hook
Hook to be run after the ZFS snapshot is done.
.TP
pre_snapshot_backup_archive_hook
Hook to be run before the archive process is started.
.TP
post_snapshot_backup_archive_hook
Hook to be run after the archive process is done.
.PP
NOTE: There is no database postfix for snapshot hooks since there would
be nonsense.
.SH MONITORING
.PP
Performing a nagios-like check allow to make sure that backups are done
regulary.
For this purpose it will test if the lock file is neither present nor
older than 24h and check if the last backup is not older than 24h.
Those values are hardcoded because in most of cases it makes no sense to
do eithe more or less than one backup per day.
.PP
If you are using NRPE (allowing arguments) you can add this in you
configuration file:
.IP
.nf
\f[C]
command[check_mysql_backup] = sudo /usr/local/bin/mysql-backup --check -c $ARG1$
\f[R]
.fi
.PP
If you don\[aq]t allow arguments you need to define one check per backup
instance.
.PP
Do not forget the sudo line:
.IP
.nf
\f[C]
nagios ALL=(mysql) NOPASSWD:/usr/local/bin/mysql-backup --check -c *
\f[R]
.fi
.SH SEE ALSO
.IP \[bu] 2
mysql(1)
.IP \[bu] 2
mysqldump(1)
.IP \[bu] 2
gzip(1), bzip2(1), xz(1), pigz(1)
.IP \[bu] 2
run-parts(8)
.SH HISTORY
.SS Version 2.4.1
.PP
2022-10-20:
.IP \[bu] 2
Migrate help as comment
.IP \[bu] 2
Do not run atexit is help is displayed
.SS Version 2.4
.PP
2019-01-04:
.IP \[bu] 2
Add ZFS support
.SS Version 2.3
.IP \[bu] 2
Add PID to log entries.
.IP \[bu] 2
Enhance log messages.
.IP \[bu] 2
Enhance launch of temporary mysql instance.
.IP \[bu] 2
Add support for pigz (http://zlib.net/pigz/).
.IP \[bu] 2
Add SUFFIX option for multi-instance mysql backup.
.SS Version 2.2
.PP
2015-02-02:
.IP \[bu] 2
Wait if blocked queries are longer than 10s.
.SS Version 2.0
.PP
2014-03-06:
.IP \[bu] 2
rewrite the core application.
.IP \[bu] 2
bump to version 2.0
.SS Version 1.9
.PP
2012-06-04:
.IP \[bu] 2
Add replication information for dumps
.IP \[bu] 2
Add snapshot option
.IP \[bu] 2
Add \f[I]LVCREATE_OPTS\f[R]
.SS Version 1.0
.PP
2010-09-06:
.PP
First release.
.SH BUGS
.PP
No time to include bugs, command actions might seldom lead astray
user\[aq]s assumption.
.SH COPYRIGHT
.PP
Copyright \[co] 2010-2017 S\['e]bastien Gross
<seb\[u2022]\[u0251]\[u01AC]\[u2022]chezwam\[u2022]\[u0256]\[u0275]\[u0288]\[u2022]org>.
.PP
Released under GNU GPL version 3 or
higher (http://www.gnu.org/licenses/gpl.html).
.SH AUTHORS
S\['e]bastien Gross
<seb\[u2022]\[u0251]\[u01AC]\[u2022]chezwam\[u2022]\[u0256]\[u0275]\[u0288]\[u2022]org>
(\f[B]\[at]renard_0\f[R]).