MySQL xtrabackup backup and incremental backup

Xtrabackup is a MySQL database backup tool provided by Percona, an open source tool capable of hot and incremental backups of InnoDB and XTRADB databases, for MyISAM, only for Win Bei, and for MyISAM when using incremental backups is actually a full backup. Xtrabackup is easy to use and powerful.

official address: http://www.percona.com/software/percona-xtrabackup/
install:

Percona-toolkit-2.2.17-1.noarch.rpmpercona-xtrabackup-2.2.9-5067.el6.x86_64.rpm[[email protected] tools]# Yum Localinstall percona-*

Innobackupex operating conditions: Requires MySQL service to be in a running state

If you want to back up with a user with minimal permissions, you can create such a user based on the following command:

mysql> create user ' bkpuser ' @ ' localhost ' identified by ' 222222 ';mysql> grant Reload,lock tables,replication Client On * * to ' bkpuser ' @localhost;mysql> flush privileges;

A simple full backup and restore implementation process:

1. Full backup:

[Email protected] mydata]# Innobackupex--user=bkpuser--password=222222--socket=/tmp/mysql.sock/mydata/mybackups/ [Email protected] mybackups]# ls2016-05-24_17-12-47

2. Prepare a full restore backup:

[Email protected] mybackups]# Innobackupex--apply-log/mydata/mybackups/2016-05-24_17-12-47/......xtrabackup: Starting shutdown with Innodb_fast_shutdown = 1innodb:fts optimize thread exiting. Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 1653270160524 17:35:57 innobackupex:completed ok!

Simulation Data crashes:

[Email protected] mybackups]# cd/mydata/mysql/data/[[email protected] data]# lsauto.cnf ib_logfile0 ib_logfile1 ibdat A1 Martin Martin.err MySQL Performance_schema test[[email protected] data]# RM-FR *

3. Restore :

[Email protected] data]# Innobackupex--copy-back/mydata/mybackups/2016-05-24_17-12-47/

/mydata/mysql/data[[email protected] data]# chown -r  mysql.mysql *[[email protected] data]# service mysqld startStarting  Mysql. success! [[email protected] data]# lsauto.cnf     ib_ logfile1  martin      martin.pid  performance_schema   xtrabackup_binlog_pos_innodbib_logfile0  ibdata1      martin.err   mysql       test                 xtrabackup_info 

The following implementation of the incremental backup process:

One full backup, 2 incremental backups

[[email protected] mydata]# innobackupex --user= bkpuser --password=222222 --socket=/tmp/mysql.sock   /mydata/mybackups/[[email  protected] mybackups]# innobackupex --user=bkpuser --password=222222 --socket=/tmp/ Mysql.sock   --incremental  /mydata/mybackups/ --incremental-basedir=2016-05-24_ 17-46-48/     #上一次 # ... Insert data [[email protected] mybackups]# innobackupex --user=bkpuser --password=222222  --socket=/tmp/mysql.sock   --incremental  /mydata/mybackups/ -- incremental-basedir=2016-05-24_17-53-02/

Restores:

full backup for the first time :

[Email protected] mybackups]# Innobackupex--user=bkpuser--password=222222--socket=/tmp/mysql.sock--apply-log-- redo-only/mydata/mybackups/2016-05-24_17-46-48/

Merge First increment:

[Email protected] mybackups]# Innobackupex--user=bkpuser--password=222222--socket=/tmp/mysql.sock--apply-log-- redo-only/mydata/mybackups/2016-05-24_17-46-48/--incremental-dir=/mydata/mybackups/2016-05-24_17-53-02/

Merge second increment:

[Email protected] mybackups]# Innobackupex--user=bkpuser--password=222222--socket=/tmp/mysql.sock--apply-log-- redo-only/mydata/mybackups/2016-05-24_17-46-48/--incremental-dir=/mydata/mybackups/2016-05-24_18-04-24/

Perform the final restore:

[[email protected] data]# innobackupex --copy-back  /mydata/mybackups/2016-05-24_17-46-48/

[[email protected] data]# chown -r mysql.mysql  *[[email protected] data]# lltotal 12308-rw-r--r-- 1 mysql mysql  12582912 May 24 18:14 ibdata1drwxr-xr-x 2 mysql mysql      4096 May 24 18:14 martindrwxr-xr-x 2 mysql mysql      4096 may 24 18:14 mysqldrwxr-xr-x 2 mysql mysql      4096 May 24 18:14 performance_schemadrwxr-xr-x 2  Mysql mysql     4096 may 24 18:14 test-rw-r--r-- 1  mysql mysql      653 may 24 18:14 xtrabackup_info [[Email protected] data]# /etc/init.d/mysqld startstarting mysql. success! 

[[email protected] data]# cat /mydata/mysql/data/xtrabackup_info uuid =  e65af8bc-2196-11e6-add0-000c294b9b35name = tool_name = innobackupextool_command =  --user=bkpuser --password=... --socket=/tmp/mysql.sock --incremental /mydata/ Mybackups/ --incremental-basedir=2016-05-24_17-53-02/tool_version = 1.5.1-xtrabackupibbackup_ version = xtrabackup version 2.2.9 based on mysql server 5.6.22  Linux  (x86_64)   (revision id: ) server_version = 5.6.30-logstart_time =  2016-05-24 18:04:24end_time = 2016-05-24 18:04:27lock_time = 1binlog_pos  = filename  ' master-bin.000010 ', position 796innodb_from_lsn =  1653280innodb_to_lsn = 1653280partial = nincremental = yformat =  filecompact = ncompressed =  Nencrypted = n

Update complete If there is a file in the binary to continue to update up

[Email protected] log-bin]# Mysqlbinlog--start-position=796 master-bin.000010>~/a.bin.sqlmysql> Set Session sq L_log_bin=0;mysql> source ~/a.sqlmysql> Source ~/a.bin.sqlmysql> set session sql_log_bin=1;

from the full data can be seen merging to the last update time :

[[email protected] 2016-05-24_17-46-48]# cat xtrabackup_info uuid =  e65af8bc-2196-11e6-add0-000c294b9b35name = tool_name = innobackupextool_command =  --user=bkpuser --password=... --socket=/tmp/mysql.sock --incremental /mydata/ Mybackups/ --incremental-basedir=2016-05-24_17-53-02/tool_version = 1.5.1-xtrabackupibbackup_ version = xtrabackup version 2.2.9 based on mysql server 5.6.22  Linux  (x86_64)   (revision id: ) server_version = 5.6.30-logstart_time =  2016-05-24 18:04:24end_time = 2016-05-24 18:04:27         #可以看到最后的增量备份时间lock_time  = 1binlog_pos = filename  ' master-bin.000010 ',  position 796innodb_from_lsn = 1653280innodb_to_lsn = 1653280partial =  nincremental = yformat =&Nbsp;filecompact = ncompressed = n 

MySQL xtrabackup backup and incremental backup