MySQL hot-standby xtrabackup use

InnoDB has a commercial InnoDB hotbackup, which enables online hot-provisioning of the InnoDB engine's tables. and Percona produced Xtrabackup, is a innodb hotbackup an open source alternative, can be online to the INNODB/XTRADB engine of the table for physical backup. Mysqldump supports online backup, but is a logical backup with poor efficiency. When the amount of data is smaller, mysqldump can also be competent, when the amount of data is large, the recovery time is unbearable, so the open source tool Xtrabackup came into being, Xtrabackup is a physical backup, the efficiency is very good.

Xtrabackup provides two command-line tools:

xtrabackup: Used to back up the data of the InnoDB engine (does not back up MyISAM such as MySQL permissions related tables, and does not automatically copy the frm file);
Innobackupex: A Perl script that invokes the Xtrabackup command during execution, which enables you to back up InnoDB, or to back up myisam/copy frm files. Only a read lock is added when backing up the MyISAM table.

Experimental environment: CentOS release 6.5 (Final), MySQL Ver 14.14 distrib 5.6.14

Xtrabackup Installation

#wget https://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-y Install Libaio libaio-devel perl-time-hires Curl Curl-devel Zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-extutils-makemaker perl-dbd-mysql.* package BZR Bison n Curses-devel ZLIB-DEVEL#RPM-IVH percona-xtrabackup-22-2.2.13-1.el6.x86_64.rpm

Partial parameter description

--USER Specifies the user who performed the backup.  --PASSWORD Specifies the password to perform the backup user.  --DEFAULTS-FILE Specifies the option file path for MySQL.  --no-timestamp do not display timestamps.    --incremental tells Xtrabackup to create an incremental backup this time.  --INCREMENTAL-BASEDIR specifies the path to a full-scale backup as the basis for an incremental backup.  --redo-only if there are additional incremental backup sets to be processed after the preparation is complete, you will need to specify this parameter.  --apply-log reads the configuration information from the specified options file and applies the log, which means that the backup set is ready for recovery. --copy-back restores the specified backup set to the specified path.

Fully prepared

#将全备的数据备份到/data/backup/base# Innobackupex--user=root--password=redhat--defaults-file=/usr/local/webserver/ Mysql5.6/my.cnf--no-timestamp/data/backup/base ..... innobackupex:backup created in directory '/dat ', ..... .....  A/backup/base ' innobackupex:mysql binlog position:filename ' mysql-bin.000001 ', position 733160201 15:26:07 Innobackupex:connection to database server closed160201 15:26:07 innobackupex:completed ok!

Make some changes to the database first.

mysql> create database sharelinux; query ok, 1 row affected  (0.00 sec) mysql> use sharelinux;database  changedmysql> create table t1 (Id int,name varchar (10)); query ok, 0 rows affected  (0.12 sec) mysql> insert into t1  values (1, ' Zhangsan '), (2, ' Lisi '), (3, ' Wangwu '); query ok, 3 rows affected  (0.08 sec) records: 3  duplicates:  0  warnings: 0mysql> select * from t1;+------+----------+| id    | name     |+------+----------+|    1 |  zhangsan | |     2 | lisi     | |     3 | wangwu   |+------+----------+3 rows in set   (0.00 SEC)

first incremental backup

#第一次增量备份目录/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: creating suspend file  '/ Data/backup/incremental_one/xtrabackup_log_copied '  with pid  ' 19979 ' xtrabackup: transaction  log of lsn  (22333659)  to  (22333659)  was copied.160201 15:39:26   innobackupex: all tables unlockedinnobackupex: backup created in  directory  '/data/backup/incremental_one ' innobackupex: mysql binlog position:  filename  ' mysql-bin.000001 ', position 1238160201 15:39:26  innobackupex:  Connection to database server closed160201 15:39:26  innobackupex: completed ok! 

To modify the database again

mysql> create database db01; query ok, 1 row affected  (0.00 sec) mysql> use db01;database  Changedmysql> create table t2 (Id int,name varchar (10)); query ok, 0 rows affected  (0.04 sec) mysql> insert into t2  values (1, ' Zhangsan '), (2, ' Lisi '), (3, ' Wangwu '); query ok, 3 rows affected  (0.02 sec) records: 3  duplicates:  0  warnings: 0mysql> select * from t2;+------+----------+| id    | name     |+------+----------+|    1 |  zhangsan | |     2 | lisi     | |     3 | wangwu   |+------+----------+3 rows in set   (0.00 SEC) 

Second incremental backup

#第二次增量备份目录/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:backup created in directory '/data/, ... .... ....., ....., .....????????.  Backup/incremental_two ' innobackupex:mysql binlog position:filename ' mysql-bin.000001 ', position 1689160201 15:59:10 Innobackupex:connection to database server closed160201 15:59:10 innobackupex:completed ok!

Simulate failure, delete data files from database

# ls/usr/local/webserver/mysql5.6/data/auto.cnf Ib_logfile0 mysql-bin.000001 node1.pid testdb01 IB_LOGF Ile1 mysql-bin.index performance_schema zabbixibdata1 mysql node1.err sharelinux# rm-rf/usr/local/w ebserver/mysql5.6/data/*

Recovery readiness

# innobackupex --user=root --password=redhat -- defaults-file=/usr/local/webserver/mysql5.6/my.cnf --apply-log --redo-only /data/backup/base/    ........................................xtrabackup: starting shutdown with  innodb_fast_shutdown = 1innodb: starting shutdown ... Innodb: shutdown completed; log sequence number 22327338160201 16:09:44   innobackupex: completed ok! 

# 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/ &NBSP: ..... ..... ..... ...................... 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 ' 160201 16:13:15  innobackupex: completed ok! 

# 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 ' 160201 16:18:27 Innobackupex: Completed ok!

Data recovery

# innobackupex --user=root --password=redhat --defaults-file=/usr/local/webserver/mysql5.6 /my.cnf --copy-back  /data/backup/base/     ...........................................innobackupex: starting to copy innodb  system tablespaceinnobackupex: in  '/data/backup/base ' innobackupex: back to  original innodb data directory  '/usr/local/webserver/mysql5.6/data ' innobackupex:  copying  '/data/backup/base/ibdata1 '  to  '/usr/local/webserver/mysql5.6/data/ibdata1 ' Innobackupex : starting to copy innodb undo tablespacesinnobackupex: in  '/data/ Backup/base ' innobackupex: back to  '/usr/local/webserver/mysql5.6/data ' innobackupex:  starting to copy innodb log filesinnobackupex: in  '/data/backup/base ' innobackupex: back to original innodb log d irectory  '/usr/local/webserver/mysql5.6/data ' innobackupex: finished copying back  files.160201 16:23:08  innobackupex: completed ok!

View Directory

# ll /usr/local/webserver/mysql5.6/data/   #这个目录下的数据已经恢复回来total  79916drwxr-x---.   2 root root     4096 feb  1 16:18 db01-rw-r-----.  1 ROOT ROOT 79691776 FEB  1 16:18 IBDATA1DRWX------.  2  root root     4096 feb  1 15:26 mysqldrwxr-xr-x.  2 root root     4096 Feb  1 15:26  Performance_schemadrwxr-x---.  2 root root     4096 Feb   1 16:13 sharelinuxdrwxr-xr-x. 2 root root     4096  feb  1 15:26 test-rw-r--r--.  1 root root        22 feb  1 16:18 xtrabackup_binlog_info-rw-r-----.  1 root  root     &nBsp; 91 feb  1 16:18 xtrabackup_checkpoints-rw-r--r--.  1 root  Root      722 feb  1 16:18 xtrabackup_info-rw-r-----.  1 root root  2097152 feb  1 16:09 xtrabackup_ LOGFILEDRWX------.  2 root root    12288 feb  1 15:26  zabbix#chown mysql:mysql /usr/local/webserver/mysql5.6/data/ -R   #将目录更改为mysql用户 # pkill mysql                                                 #杀死原来的mysql进程, and then restart the database #  service mysqld startstarting mysql.                                             [  OK  ]

The

Data has been restored back

mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  db01               | |  mysql              | |  performance_schema | |  sharelinux         | |  test               | |  zabbix             |+------------------ --+7 rows in set  (0.14 sec) mysql> select * from sharelinux.t1;     #第一次增量备份的数据 +------+----------+| id   | name      |+------+----------+|    1 | zhangsan | |     2 | lisi     | |     3 | wangwu   |+------+----------+3 rows in set   (0.03 SEC) mysql> select * from db01.t2;            #第二次增量备份的数据 +------+----------+| id   | name      |+------+----------+|    1 | zhangsan | |     2 | lisi     | |     3 | wangwu   |+------+----------+3 rows in set   (0.08 SEC)

This article is from the "Share Linux" blog, so be sure to keep this source http://sharelinux.blog.51cto.com/11163444/1740513

MySQL hot-standby xtrabackup use