Use of Mysql Hot Standby xtrabackup

Use of Mysql Hot Standby xtrabackup

InnoDB has a commercial InnoDB Hotbackup that can implement online hot backup for InnoDB Engine tables. Percona's Xtrabackup is an open-source alternative to InnoDB Hotbackup. It can physically back up InnoDB/XtraDB engine tables online. Mysqldump supports online backup, but it is a logical backup with poor efficiency. When the data volume is small, mysqldump is still competent. When the data volume is large, the recovery time is intolerable. Therefore, the open-source tool xtrabackup came into being. xtrabackup is a physical backup, very efficient.

 

Xtrabackup provides two command line tools:

Xtrabackup: used to back up InnoDB engine data (does not back up myisam, such as mysql permission related tables, or automatically copy frm files); innobackupex: a perl script, during execution, the xtrabackup command is called to back up InnoDB or MyISAM/copy frm files, but a read lock is added when the myisam table is backed up.

 

Lab environment: CentOS release 6.5 (Final), mysql Ver 14.14 Distrib 5.6.14

Install xtrabackup

#wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.2.13/binary/redhat/6/x86_64/percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm#yum-yinstalllibaiolibaio-develperl-Time-HiRescurlcurl-develzlib-developenssl-develperlcpioexpat-develgettext-develperl-ExtUtils-MakeMakerperl-DBD-MySQL.*packagebzrbisonncurses-develzlib-devel#rpm-ivhpercona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm

Some parameter descriptions

-- User specifies the user who executes the backup. -- Password specifies the password of the backup user. -- Defaults-file: Specifies the path of the mysql option file. -- No-timestamp: do not display the timestamp. -- Incremental tells xtrabackup to create an incremental backup this time. -- Incremental-basedir specifies a full backup path as the basis for incremental backup. -- Redo-only if there are other Incremental Backup sets to be processed after the preparation is complete, you need to specify this parameter. -- Apply-log reads configuration information from the specified option file and applies logs, which means the backup set is prepared for recovery. -- Copy-back: restores the specified backup set to the specified path.

 

Full backup

# Back up full backup data to/data/backup/base # innobackupex -- user = root -- password = redhat -- defaults-file =/usr/local/webserver/mysql5.6/my. cnf -- no-timestamp/data/backup/base ...................... innobackupex: alipay'/data/backup/base 'innobackupex: MySQLbinlogposition: filename 'mysql-bin.000001 ', rule: 26: 07 innobackupex: rule: 26: 07 innobackupex: completedOK!

Modify the database first.

mysql>createdatabasesharelinux;QueryOK,1rowaffected(0.00sec)mysql>usesharelinux;Databasechangedmysql>createtablet1(idint,namevarchar(10));QueryOK,0rowsaffected(0.12sec)mysql>insertintot1values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');QueryOK,3rowsaffected(0.08sec)Records:3Duplicates:0Warnings:0mysql>select*fromt1;+------+----------+|id|name|+------+----------+|1|zhangsan||2|lisi||3|wangwu|+------+----------+3rowsinset(0.00sec)

First Incremental Backup

# First Incremental backup Directory/data/backup/incremental_one # innobackupex -- user = root -- password = redhat -- defaults-file =/usr/local/webserver/mysql5.6/my. cnf -- no-timestamp -- incremental/data/backup/incremental_one -- incremental-basedir =/data/backup/base /............... ....................................... xtrabackup: Creatingsuspendfile '/data/backup/incremental_one/xtrabackup_log_copied 'withpid '000000' xtrabackup: Transactionlog Oflsn (22333659) to (22333659) Priority: 39: 26 innobackupex: Priority '/data/backup/incremental_one 'innobackupex: MySQLbinlogposition: filename 'mysql-bin.000001', position123816020115: 39: 26 innobackupex: Connectiontodatabaseserverclosed16020115: 39: 26 innobackupex: completedOK!

Modify the database.

mysql>createdatabasedb01;QueryOK,1rowaffected(0.00sec)mysql>usedb01;Databasechangedmysql>createtablet2(idint,namevarchar(10));QueryOK,0rowsaffected(0.04sec)mysql>insertintot2values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');QueryOK,3rowsaffected(0.02sec)Records:3Duplicates:0Warnings:0mysql>select*fromt2;+------+----------+|id|name|+------+----------+|1|zhangsan||2|lisi||3|wangwu|+------+----------+3rowsinset(0.00sec)

Second Incremental Backup

# The second Incremental backup Directory/data/backup/incremental_two # innobackupex -- user = root -- password = redhat -- defaults-file =/usr/local/webserver/mysql5.6/my. cnf -- no-timestamp -- incremental/data/backup/incremental_two -- incremental-basedir =/data/backup/incremental_one /............... ......................... innobackupex: Backupcreatedindirectory '/data/backup/incremental_two' innobackupex: MySQLbinlogposition: filename 'mysql-bin.00000 1', position168916020115: 59: 10 innobackupex: Connectiontodatabaseserverclosed16020115: 59: 10 innobackupex: completedOK!

Simulate faults and delete database data files

#ls/usr/local/webserver/mysql5.6/data/auto.cnfib_logfile0mysql-bin.000001node1.pidtestdb01ib_logfile1mysql-bin.indexperformance_schemazabbixibdata1mysqlnode1.errsharelinux#rm-rf/usr/local/webserver/mysql5.6/data/*

Recovery preparation

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/........................................xtrabackup:startingshutdownwithinnodb_fast_shutdown=1InnoDB:Startingshutdown...InnoDB:Shutdowncompleted;logsequencenumber2232733816020116:09:44innobackupex:completedOK!

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log--redo-only/data/backup/base/--incremental-dir=/data/backup/incremental_one/...........................................innobackupex:Copying'/data/backup/incremental_one/performance_schema/events_stages_history.frm'to'/data/backup/base/performance_schema/events_stages_history.frm'innobackupex:Copying'/data/backup/incremental_one/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm'16020116:13:15innobackupex:completedOK!

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--apply-log/data/backup/base/--incremental-dir=/data/backup/incremental_two/innobackupex:Copying'/data/backup/incremental_two/performance_schema/setup_instruments.frm'to'/data/backup/base/performance_schema/setup_instruments.frm'innobackupex:Copying'/data/backup/incremental_two/db01/db.opt'to'/data/backup/base/db01/db.opt'innobackupex:Copying'/data/backup/incremental_two/db01/t2.frm'to'/data/backup/base/db01/t2.frm'16020116:18:27innobackupex:completedOK!

Data Recovery

#innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/mysql5.6/my.cnf--copy-back/data/backup/base/...........................................innobackupex:StartingtocopyInnoDBsystemtablespaceinnobackupex:in'/data/backup/base'innobackupex:backtooriginalInnoDBdatadirectory'/usr/local/webserver/mysql5.6/data'innobackupex:Copying'/data/backup/base/ibdata1'to'/usr/local/webserver/mysql5.6/data/ibdata1'innobackupex:StartingtocopyInnoDBundotablespacesinnobackupex:in'/data/backup/base'innobackupex:backto'/usr/local/webserver/mysql5.6/data'innobackupex:StartingtocopyInnoDBlogfilesinnobackupex:in'/data/backup/base'innobackupex:backtooriginalInnoDBlogdirectory'/usr/local/webserver/mysql5.6/data'innobackupex:Finishedcopyingbackfiles.16020116:23:08innobackupex:completedOK!

View directory

# Ll/usr/local/webserver/mysql5.6/data/# The data in this directory has been restored back to the total79916drwxr-x ---. 2rootroot4096Feb116: 18db01-rw-r -----. 1rootroot79691776Feb116: 18ibdata1drwx ------. 2rootroot4096Feb115: 26mysqldrwxr-xr-x.2rootroot4096Feb115: 26performance_schemadrwxr-x ---. 2rootroot4096Feb116: 13sharelinuxdrwxr-xr-x.2rootroot4096Feb115: 26test-rw-r -- r --. 1rootroot22Feb116: 18xtrabackup_binlog_info-rw-r -----. 1rootroot91Feb116: 18xtrabackup_checkpoints-rw-r -- r --. 1rootroot722Feb116: 18xtrabackup_info-rw-r -----. 1rootroot2097152Feb116: 09xtrabackup_logfiledrwx ------. 2rootroot12288Feb115: 26 zabbix # chownmysql: mysql/usr/local/webserver/mysql5.6/data/-R # change the directory to mysql user # pkillmysql # Kill the original mysql process, then restart the database # servicemysqldstartStartingMySQL .. [OK]

Data recovered

Mysql> showdatabases; + rule + | Database | + ------------------ + | information_schema | db01 | mysql | performance_schema | sharelinux | test | zabbix | + -------------------- + 7 rowsinset (0.14sec) mysql> select * fromsharelinux. t1; # data in the first Incremental backup + ------ + ---------- + | id | name | + ------ + ---------- + | 1 | zhangsan | 2 | lisi | 3 | wangwu | + ------ + ---------- + 3 rowsinset (0.03sec) mysql> select * fromdb01.t2; # data in the second Incremental backup + ------ + ---------- + | id | name | + ------ + ---------- + | 1 | zhangsan | 2 | lisi | 3 | wangwu | + ------ + ---------- + 3 rowsinset (0.08sec)