Mysql open-source backup tool Xtrabackup backup deployment

Mysql open-source backup tool Xtrabackup backup deployment Xtrabackup is a data backup tool for InnoDB that supports online Hot Backup (data read/write is not affected during Backup ), it is a good alternative to InnoDB Hotbackup. Xtrabackup has two main tools: xtrabackup, innobackupex 1, and xtrabackup can only back up InnoDB and XtraDB data tables, the MyISAM data table cannot be backed up. 2. innobackupex is modified based on the innoback script of InnoDB Hotbackup. innobackupex is a perl script encapsulation that encapsulates xtrabackup. It is mainly used to conveniently back up InnoDB and MyISAM engine tables at the same time, but a read lock is required when processing myisam. Some options are added. For example, slave-info can record the information required by slave after the backup is restored. Based on this information, you can easily use the backup to redo slave. Xtrabackup: http://www.percona.com/downloads/XtraBackup/LATEST/binary/ Select the appropriate version. Here, select binary. Extract To the/usr/src/directory, unzip the directory name percona-xtrabackup-2.1.3, enter the bin directory, you can directly use the innobackupex command # pwd/usr/src/percona-xtrabackup-2.1.3/bin # ls-l total 112396-rwxr-xr-x 1 root 110738 Jun 7 innobackupex-rwxr-xr- x 1 root 110738 Jun 7 innobackupex-1.5.1-rwxr-xr-x 1 root 2211237 Jun 7 xbcrypt-rwxr-xr-x 1 root 2285672 Jun 7 xbstream-rwxr- xr-x 1 root 13033745 Jun 7 11: 43 xtrabackup-rwxr-xr-x 1 root 16333506 Jun 7 xtrabackup_55-rwxr-xr-x 1 root 80988093 Jun 7 xtrabackup_56 the following error may be reported at the beginning of use: sh: xtrabackup_55: command not found innobackupex: fatal error: no 'mysqld' group in MySQL options solution copy xtrabackup_55 to/usr/bin. # Cp/usr/src/percona-xtrabackup-2.1.3/bin/xtrabackup_55/usr/bin/create mysql backup User: mysql> grant reload, lock tables, replication client on *. * TO 'backupuser' @ 'localhost' identified by 'skebfef5e2'; mysql> flush privileges; backup script content (full backup once a week, six incremental backups, two weeks of backup retained, and compress): [plain] #! /Bin/bash BEGINTIME = 'date + "% Y-% m-% d % H: % M: % S "'format_time = 'date +" % Y-% m-% d _ % H: % M: % S "'Week = 'date + % W' backupbin =/usr/src/percona-xtrabackup-2.1.3/bin backdir =/data/databasebak/bak file_cnf =/etc/my. cnf user_name = backupuser password = "skEBfef5E2" db = "db1 db2 db3 db4" out_log = $ backdir/xtrabackup_log _ $ format_time time_cost = $ backdir/empty if [-f "kernel"]; then rm- Rf audio mv audio backdir.lastweek.gz audio fi if [-d "$ backdir/rec5"]; then gzip-cr $ backdir> audio backdir.lastweek.gz rm-rf $ backdir mkdir $ backdir fi # full if [! -D "$ backdir/full"]; then echo "##### start full backup at $ BEGINTIME to directory full"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" $ backdir/full 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec0"]; then echo "##### start 0 incremental backup at $ BEGINTIME to directory rec0"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/full $ backdir/rec0 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec1"]; then echo "##### start 1 incremental backup at $ BEGINTIME to directory rec1"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/rec0 $ backdir/rec1 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec2"]; then echo "##### start 2 incremental backup at $ BEGINTIME to directory rec2"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/rec1 $ backdir/rec2 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec3"]; then echo "##### start 3 incremental backup at $ BEGINTIME to directory rec3"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/rec2 $ backdir/rec3 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec4"]; then echo "##### start 4 incremental backup at $ BEGINTIME to directory rec4"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/rec3 $ backdir/rec4 1> $ out_log 2> & 1 break; elif [! -D "$ backdir/rec5"]; then echo "##### start 5 incremental backup at $ BEGINTIME to directory rec5"> $ time_cost $ backupbin/innobackupex -- defaults-file = $ file_cnf -- no-timestamp -- user = $ user_name -- password = $ password -- slave-info -- databases = "$ db" -- incremental-basedir = $ backdir/rec4 $ backdir/rec5 1> $ out_log 2> & 1 break; fi ENDTIME = 'date + "% Y-% m-% d % H: % M: % S" 'inin_data = 'date-d "$ BEGINT IME "+ % s 'end_data = 'date-d" $ ENDTIME "+ % s' spendtime = 'expr $ end_data-$ begin_data 'echo" it takes $ spendtime sec for packing data directory ">>$ time_cost deployment backup script; # crontab-l 10 2 ***/opt/cron/mysqldbbakup. sh recovery: # Full backup recovery: innobackupex -- apply-log -- redo-only -- defaults-file = $ file_cnf -- user = $ user_name -- password = $ password $ backdir/full # Add backup recovery, modify the Incremental Backup Directory, apply-log one by one, and the incremental rec4 on the fifth day all show innobackup Ex: completed OK! Innobackupex -- apply-log -- redo-only -- defaults-file = $ file_cnf -- user = $ user_name -- password = $ password $ backdir/full -- incremental-dir = $ backdir/rec5