MySQL open-source backup tool Xtrabackup backup deployment

Xtrabackup is a tool used to back up InnoDB data. It supports online Hot Backup (data read/write is not affected during Backup) and is a good commercial backup tool InnoDBHotbackup.

Xtrabackup is a tool used to back up InnoDB data. It supports online Hot Backup (data read/write is not affected during Backup) and is a good commercial backup tool for InnoDB Hotbackup.

Xtrabackup is a tool used to back up InnoDB data. It supports online Hot Backup (data read/write is not affected during Backup) and is a good alternative to InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex.

1. xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM Data Tables

2. innobackupex is modified based on the innoback script of InnoDB Hotbackup. innobackupex is a perl script encapsulation and 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:

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 11: 43 innobackupex
-Rwxr-xr-x 1 root 110738 Jun 7 innobackupex-1.5.1
-Rwxr-xr-x 1 root 2211237 Jun 7 11: 43 xbcrypt
-Rwxr-xr-x 1 root 2285672 Jun 7 11: 43 xbstream
-Rwxr-xr-x 1 root 13033745 Jun 7 11: 43 xtrabackup
-Rwxr-xr-x 1 root 16333506 Jun 7 11: 43 xtrabackup_55
-Rwxr-xr-x 1 root 80988093 Jun 7 11: 43 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 a 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 compressed ):

#! /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/xtrabackup_time.txt

If [-f "$backdir.lastlastlastweek.gz"]; then
Rm-rf restart backdir.lastlastweek.gz
Mv export backdir.lastweek.gz export backdir.lastlastweek.gz
Fi

If [-d "$ backdir/rec5"]; then
Gzip-cr $ backdir> pull 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 "'
Begin_data = 'date-d "$ BEGINTIME" + % s'
End_data = 'date-d "$ ENDTIME" + % s'
Spendtime = 'expr $ end_data-$ begin_data'
Echo "it takes $ spendtime sec for packing the data directory"> $ time_cost

Deploy the 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

# Incremental Backup recovery, modify the Incremental Backup Directory, apply-log one by one, and the incremental rec4 on the fifth day will all display innobackupex: completed OK!