How to set up MySQL database backups quickly, safely and reliably
All owners of MySQL databases are familiar with the problem of daily backups. It is important not only to have a consistent copy of all the necessary databases, but also to backup at a given time, without creating an increased load on the working architecture, without locks and errors. To achieve the desired results, a utility called MyDumper is suitable.
Mydumper is suitable for backup of both large databases and very small ones. The main feature of this utility is that it executes backup in parallel in several threads, which is several times faster and more efficient than standard tools like mysqldump.
1) Install Mydumper
There are many ways to install mydumper, but it so happened that most of the ones on the Internet are either outdated or never worked. Currently, for Centos 7, the installation of mydumper is as follows:
yum install ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/LenzGr/CentOS_7/x86_64/mydumper-0.9.1-1.4.x86_64.rpm
After the installation is complete, we can request the output of the mydumper command:
mydumper --help Usage: mydumper [OPTION?] multi-threaded MySQL dumping Help Options: -?, --help Show help options Application Options: -B, --database Database to dump -T, --tables-list Comma delimited table list to dump (does not exclude regex option) -o, --outputdir Directory to output files to -s, --statement-size Attempted size of INSERT statement in bytes, default 1000000 -r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize -F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB -c, --compress Compress output files -e, --build-empty-files Build dump files even if no data available from table -x, --regex Regular expression for 'db.table' matching -i, --ignore-engines Comma delimited list of storage engines to ignore -m, --no-schemas Do not dump table schemas with the data -d, --no-data Do not dump table data -G, --triggers Dump triggers -E, --events Dump events -R, --routines Dump stored procedures and functions -k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups --less-locking Minimize locking time on InnoDB tables. -l, --long-query-guard Set long query timer in seconds, default 60 -K, --kill-long-queries Kill long running queries (instead of aborting) -D, --daemon Enable daemon mode -I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60 -L, --logfile Log file name to use, by default stdout is used --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable. --skip-tz-utc --use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege --success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist --lock-all-tables Use LOCK TABLE for all, instead of FTWRL -U, --updated-since Use Update_time to dump only tables updated in the last U days --trx-consistency-only Transactional consistency only -h, --host The host to connect to -u, --user Username with privileges to run the dump -p, --password User password -P, --port TCP/IP port to connect to -S, --socket UNIX domain socket file to use for connection -t, --threads Number of threads to use, default 4 -C, --compress-protocol Use compression on the MySQL connection -V, --version Show the program version and exit -v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
2) Backup execution script
### create sbin directory in /root/ ### mkdir /root/sbin ### create backup directory ### mkdir /backup ### create mysql-backup file in r/root/sbin/ ### nano /root/sbin/mysql-backup
Paste the contents into a file:
ionice -c3 mydumper --user=your-mysql-user --password=your-mysql-user-password -o /backup/`date +%Y-%m-%d_%H-%M` --compress --triggers --events --routines --build-empty-files --less-locking --use-savepoints --success-on-1146 --compress-protocol --threads=2 --verbose=3 /usr/bin/ionice -c3 find /backup/ -type f -mtime +14 -exec rm -f {} \; >/dev/null 2>&1 /usr/bin/ionice -c3 find /backup/ -type d -empty -exec rmdir {} \;
В этом скрипте вам необходимо поправить следующие данные:
--user=(paste here your mysql user, who has rights to databases) --password=(paste here your mysql user password)
and
--threads=(here number of cores on your server / 2, for example your server have 8 cores, so you can set 2-4 threads)
and number of stored backups
-mtime +14 (here indicate how many days to backup, in the current example 14 days are indicated, you can specify your own value)
Save script and exit. Now use chmod to set 775 value by using command:
chmod 775 /root/sbin/mysql-backup
3) Cron-task for backup
Now we need to create a cron task that will run backup script at the specified time, so run command:
nano /etc/cron.d/mysql-backup
and paste
0 2 * * * root bash -lc "/root/sbin/mysql-backup" > /dev/null
Save end exit.