MySQL 利用xtrabackup开展增量备份详细过程汇总_mysql

mysql 利用xtrabackup进行增量备份详细过程汇总

Xtrabackup下载、安装以及全量备份请参考：http://blog.itpub.net/26230597/viewspace-1465772/

1，创建mysql备份用户

mysql -uroot –password="" -e"CREATE USER ‘backup’@’192.168.%’ IDENTIFIED BY ‘123456’";

mysql -uroot –password="" -e"GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, SUPERON *.* TO ‘backup’@’192.168.%’";

2，xtrabackup进行全量备份

备份命令：

xtrabackup –defaults-file=/etc/my.cnf–user=backup –password="123456" –port=3306 –backup–target-dir=/data/backups/mysql/full_incre_$(date +%Y%m%d)

PS: 2>/tmp/fullbackup.log 将备份信息放入文件中。

 备份过程信息如下：

[root@data01 ~]# xtrabackup--defaults-file=/etc/my.cnf --user=backup --password="123456"--port=3306 --backup --target-dir=/data/backups/mysql/full_incre_$(date+%Y%m%d) xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0,set to 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423920168) xtrabackup: Generating a list of tablespaces [01] Copying ./IBdata1 to/data/backups/mysql/full_incre_20150319/IBdata1 >> log scanned up to (423920168) >> log scanned up to (423920168) [01]       ...done [01] Copying ./IBdata2 to/data/backups/mysql/full_incre_20150319/IBdata2 >> log scanned up to (423920168) >> log scanned up to (423920168) [01]       ...done [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_master_info.ibd [01]       ...done [01] Copying ./mysql/slave_relay_log_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_relay_log_info.ibd [01]       ...done [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_index_stats.ibd [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/full_incre_20150319/mysql/slave_worker_info.ibd [01]       ...done [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/full_incre_20150319/mysql/innodb_table_stats.ibd [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest1.ibd [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest5.ibd [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest8.ibd [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest4.ibd [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest2.ibd [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest6.ibd [01]       ...done [01] Copying ./sbtest/sbtest7.ibd to /data/backups/mysql/full_incre_20150319/sbtest/sbtest7.ibd [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/full_incre_20150319/sbtest/sbtest3.ibd [01]       ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/full_incre_20150319/test/t1.ibd [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/full_incre_20150319/test/t3.ibd [01]       ...done [01] Copying ./test/t.ibd to/data/backups/mysql/full_incre_20150319/test/t.ibd [01]       ...done [01] Copying ./user_db/t1.ibd to/data/backups/mysql/full_incre_20150319/user_db/t1.ibd [01]       ...done [01] Copying ./user_db/test.ibd to/data/backups/mysql/full_incre_20150319/user_db/test.ibd [01]       ...done [01] Copying ./user_db/test2.ibd to/data/backups/mysql/full_incre_20150319/user_db/test2.ibd [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/full_incre_20150319/user_db/t3.ibd [01]       ...done [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/full_incre_20150319/user_db/UC_USER.ibd >> log scanned up to (423920168) [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/full_incre_20150319/user_db/t.ibd [01]       ...done xtrabackup: The latest check point (forincremental): '423920168' xtrabackup: Stopping log copying thread. .>> log scanned up to (423920168)   xtrabackup: Transaction log of lsn(423920168) to (423920168) was copied. [root@data01 ~]#

3，xtrabackup进行增量备份

3.1先录入一张表，实现第一次增量数据：

mysql> create table t4 select 'aaa' asa, 1 as b; Query OK, 1 row affected (0.36 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql> mysql> insert into t4 select 'bbb',2; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql>

3.2 再进行增量备份1

xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup –password="123456" –port=3306 –target-dir=/data/backups/mysql/incre_20150320/–incremental-basedir=/data/backups/mysql/full_incre_201503192>/tmp/incre_backup_1.log

查看备份日志信息：

[root@data01 ~]# more/tmp/incre_backup_1.log xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423920168 isenabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0,set to 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423925813) xtrabackup: Generating a list oftablespaces xtrabackup: using the full scan forincremental backup [01] Copying ./IBdata1 to/data/backups/mysql/incre_20150320/IBdata1.delta >> log scanned up to (423925813) [01]       ...done [01] Copying ./IBdata2 to/data/backups/mysql/incre_20150320/IBdata2.delta >> log scanned up to (423925813) [01]       ...done >> log scanned up to (423925813) [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta [01]       ...done [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta [01]       ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150320/test/t1.ibd.delta [01]       ...done [01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150320/test/t4.ibd.delta [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150320/test/t3.ibd.delta [01]       ...done [01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150320/test/t.ibd.delta [01]       ...done [01] Copying ./user_db/t1.ibd to /data/backups/mysql/incre_20150320/user_db/t1.ibd.delta [01]       ...done [01] Copying ./user_db/test.ibd to/data/backups/mysql/incre_20150320/user_db/test.ibd.delta [01]       ...done >> log scanned up to (423925813) [01] Copying ./user_db/test2.ibd to /data/backups/mysql/incre_20150320/user_db/test2.ibd.delta [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta [01]       ...done [01] Copying ./user_db/UC_USER.ibd to /data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150320/user_db/t.ibd.delta [01]       ...done xtrabackup: The latest check point (forincremental): '423925813' xtrabackup: Stopping log copying thread. .>> log scanned up to (423925813)   xtrabackup: Transaction log of lsn(423925813) to (423925813) was copied.  

3.3 再建立表2，实现第二天增量数据

mysql> use test; Database changed mysql> create table t_incre_2 select'cc' as a,2 as b; Query OK, 1 row affected (0.25 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql> insert into t_incre_2 select'dd',2; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql> select * from t_incre_2; +----+---+ | a | b | +----+---+ | cc | 2 | | dd | 2 | +----+---+ 2 rows in set (0.05 sec)   mysql>

3.4 进行第二天的增量备份

备份命令：

xtrabackup –defaults-file=/etc/my.cnf–backup –user=backup –password="123456" –port=3306 –target-dir=/data/backups/mysql/incre_20150321/–incremental-basedir=/data/backups/mysql/incre_20150320 2>/tmp/incre_backup_2.log

备份信息记录如下：

[root@data01 ~]# more /tmp/incre_backup_2.log xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423925813 isenabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0,set to 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423931610) xtrabackup: Generating a list oftablespaces xtrabackup: using the full scan forincremental backup [01] Copying ./IBdata1 to/data/backups/mysql/incre_20150321/IBdata1.delta >> log scanned up to (423931610) >> log scanned up to (423931610) >> log scanned up to (423931610) >> log scanned up to (423931610) >> log scanned up to (423931610) [01]       ...done >> log scanned up to (423931610) [01] Copying ./IBdata2 to/data/backups/mysql/incre_20150321/IBdata2.delta >> log scanned up to (423931610) >> log scanned up to (423931610) [01]       ...done [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta [01]       ...done [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta [01]       ...done >> log scanned up to (423931610) [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to /data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta [01]       ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/incre_20150321/test/t1.ibd.delta [01]       ...done [01] Copying ./test/t4.ibd to/data/backups/mysql/incre_20150321/test/t4.ibd.delta [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/incre_20150321/test/t3.ibd.delta [01]       ...done >> log scanned up to (423931610) [01] Copying ./test/t.ibd to/data/backups/mysql/incre_20150321/test/t.ibd.delta [01]       ...done [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta [01]       ...done [01] Copying ./user_db/t1.ibd to/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta [01]       ...done [01] Copying ./user_db/test.ibd to /data/backups/mysql/incre_20150321/user_db/test.ibd.delta [01]       ...done [01] Copying ./user_db/test2.ibd to/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta [01]       ...done [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/incre_20150321/user_db/t.ibd.delta [01]       ...done >> log scanned up to (423931610) xtrabackup: The latest check point (forincremental): '423931610' xtrabackup: Stopping log copying thread. .>> log scanned up to (423931610)   xtrabackup: Transaction log of lsn(423931610) to (423931610) was copied. 

4，xtrabackup进行增量恢复

4.1为了验证比对，先删除两个增量备份前表里面的数据

mysql> delete from t4; Query OK, 2 rows affected (0.05 sec)   mysql> delete from t_incre_2; Query OK, 2 rows affected (0.04 sec)   mysql> select * from t4; Empty set (0.00 sec)   mysql> select * from t_incre_2; Empty set (0.00 sec)   mysql>

4.2开始全备份恢复PREPARE THE BASE BACKUP

备份命令如下：

xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password="123456"  –apply-log-only–target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/full_restore.log

备份信息记录如下：

[root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --prepare --user=backup--password="123456" --apply-log-only --target-dir=/data/backups/mysql/full_incre_20150319 xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) xtrabackup: cd to/data/backups/mysql/full_incre_20150319 xtrabackup: This target seems to be alreadyprepared. xtrabackup: notice: xtrabackup_logfile wasalready used to '--prepare'. xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 xtrabackup: Starting InnoDB instance forrecovery. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter) InnoDB: Using atomics to ref count bufferpool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size =100.0M InnoDB: Completed initialization of bufferpool InnoDB: Setting log file ./ib_logfile101size to 64 MB InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0 InnoDB: New log files created,LSN=423920168 InnoDB: Highest supported file format isBarracuda.   [notice (again)]   Ifyou use binary log and don't use any hack of group commit,   thebinary log position seems to be: InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 423920652 [root@data01 mysql]# 

4.3 恢复到第一次增量的时刻

恢复命令：

xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password="123456"  –apply-log-only–target-dir=/data/backups/mysql/full_incre_20150319–incremental-dir=/data/backups/mysql/incre_20150320 2>/tmp/restore_incre_1.log

恢复过程：

[root@data01 mysql]# more /tmp/restore_incre_1.log xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423920168 isenabled. xtrabackup: cd to/data/backups/mysql/full_incre_20150319 xtrabackup: This target seems to be alreadyprepared. xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423925813) xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320 xtrabackup:   innodb_log_files_in_group = 1 xtrabackup:   innodb_log_file_size = 2097152 xtrabackup: Generating a list oftablespaces xtrabackup: page size for /data/backups/mysql/incre_20150320/IBdata2.deltais 16384 bytes Applying/data/backups/mysql/incre_20150320/IBdata2.delta to ./IBdata2... xtrabackup: page size for/data/backups/mysql/incre_20150320/IBdata1.delta is 16384 bytes Applying /data/backups/mysql/incre_20150320/IBdata1.deltato ./IBdata1... xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150320/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150320/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta is16384 bytes Applying/data/backups/mysql/incre_20150320/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150320/mysql/slave_master_info.ibd.delta to./mysql/slave_master_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd... xtrabackup: page size for /data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.deltais 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.delta is 16384 bytes Applying /data/backups/mysql/incre_20150320/sbtest/sbtest8.ibd.deltato ./sbtest/sbtest8.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t3.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/test/t3.ibd.delta to ./test/t3.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/test/t.ibd.delta to ./test/t.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t4.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/test/t4.ibd.delta to ./test/t4.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/test/t1.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/test/t1.ibd.delta to ./test/t1.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t3.ibd.delta is 16384 bytes Applying /data/backups/mysql/incre_20150320/user_db/t3.ibd.deltato ./user_db/t3.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/user_db/t.ibd.delta to ./user_db/t.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/user_db/test2.ibd.delta to./user_db/test2.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/test.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/user_db/test.ibd.delta to./user_db/test.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/user_db/t1.ibd.delta to ./user_db/t1.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150320/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd... xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150320 xtrabackup:   innodb_log_files_in_group = 1 xtrabackup:   innodb_log_file_size = 2097152 xtrabackup: Starting InnoDB instance forrecovery. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter) InnoDB: Using atomics to ref count bufferpool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 100.0M InnoDB: Completed initialization of bufferpool InnoDB: Highest supported file format isBarracuda. InnoDB: The log sequence numbers 423920652and 423920652 in ibdata files do not match the log sequence number 423925813 inthe ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information fromthe .ibd files... InnoDB: Restoring possible half-writtendata pages InnoDB: from the doublewrite buffer... InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   [notice (again)]   Ifyou use binary log and don't use any hack of group commit,   thebinary log position seems to be: InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 423925813 

4.4 恢复到第二次增量备份前面：

恢复命令：

xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password="123456"  –apply-log-only –target-dir=/data/backups/mysql/full_incre_20150319–incremental-dir=/data/backups/mysql/incre_201503212>/tmp/restore_incre_2.log

恢复过程：

[root@data01 mysql]# more/tmp/restore_incre_2.log xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423925813 isenabled. xtrabackup: cd to/data/backups/mysql/full_incre_20150319 xtrabackup: This target seems to be alreadyprepared. xtrabackup: xtrabackup_logfile detected:size=2097152, start_lsn=(423931610) xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321 xtrabackup:   innodb_log_files_in_group = 1 xtrabackup:   innodb_log_file_size = 2097152 xtrabackup: Generating a list oftablespaces xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata2.delta is 16384 bytes Applying /data/backups/mysql/incre_20150321/IBdata2.deltato ./IBdata2... xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata1.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/IBdata1.delta to ./IBdata1... xtrabackup: page size for /data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.deltais 16384 bytes Applying/data/backups/mysql/incre_20150321/mysql/slave_worker_info.ibd.delta to./mysql/slave_worker_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150321/mysql/innodb_table_stats.ibd.delta to./mysql/innodb_table_stats.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta is 16384bytes Applying/data/backups/mysql/incre_20150321/mysql/innodb_index_stats.ibd.delta to./mysql/innodb_index_stats.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta is16384 bytes Applying/data/backups/mysql/incre_20150321/mysql/slave_relay_log_info.ibd.delta to./mysql/slave_relay_log_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.delta is 16384bytes Applying /data/backups/mysql/incre_20150321/mysql/slave_master_info.ibd.deltato ./mysql/slave_master_info.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest4.ibd.delta to./sbtest/sbtest4.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest3.ibd.delta to./sbtest/sbtest3.ibd... xtrabackup: page size for /data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.deltais 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest1.ibd.delta to./sbtest/sbtest1.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest7.ibd.delta to./sbtest/sbtest7.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest6.ibd.delta to./sbtest/sbtest6.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest8.ibd.delta to./sbtest/sbtest8.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest2.ibd.delta to./sbtest/sbtest2.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/sbtest/sbtest5.ibd.delta to./sbtest/sbtest5.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t3.ibd.delta is 16384 bytes Applying /data/backups/mysql/incre_20150321/test/t3.ibd.deltato ./test/t3.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/test/t_incre_2.ibd.delta to./test/t_incre_2.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/test/t.ibd.delta to ./test/t.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t4.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/test/t4.ibd.delta to ./test/t4.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/test/t1.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/test/t1.ibd.delta to ./test/t1.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/t3.ibd.delta to ./user_db/t3.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/t.ibd.delta to ./user_db/t.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/test2.ibd.delta to./user_db/test2.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/test.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/test.ibd.delta to./user_db/test.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/t1.ibd.delta to ./user_db/t1.ibd... xtrabackup: page size for/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta is 16384 bytes Applying/data/backups/mysql/incre_20150321/user_db/UC_USER.ibd.delta to./user_db/UC_USER.ibd... xtrabackup: using the following InnoDBconfiguration for recovery: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir =/data/backups/mysql/incre_20150321 xtrabackup:   innodb_log_files_in_group = 1 xtrabackup:   innodb_log_file_size = 2097152 xtrabackup: Starting InnoDB instance forrecovery. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter) InnoDB: Using atomics to ref count bufferpool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomicbuiltins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size =100.0M InnoDB: Completed initialization of bufferpool InnoDB: Highest supported file format isBarracuda. InnoDB: The log sequence numbers 423925813and 423925813 in ibdata files do not match the log sequence number 423931610 inthe ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information fromthe .ibd files... InnoDB: Restoring possible half-writtendata pages InnoDB: from the doublewrite buffer... InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   [notice (again)]   Ifyou use binary log and don't use any hack of group commit,   thebinary log position seems to be: InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 423931610 [root@data01 mysql]# 

4.5 恢复整个库

恢复命令：

xtrabackup –defaults-file=/etc/my.cnf–prepare –user=backup –password="123456"–target-dir=/data/backups/mysql/full_incre_20150319 2>/tmp/restore_end_3.log

停止mysql服务：

service mysqld5612 stop

开始rsync数据文件：

cd /data/backups/mysql/full_incre_20150319

rsync -rvt –exclude’xtrabackup_checkpoints’ –exclude ‘xtrabackup_logfile’  ./ /home/data/mysql/data

授予mysql访问权限：

chown -R mysql:mysql /home/data/mysql/data

启动mysql服务：

service mysqld5612 start

4.6，验证

登录mysql，看到以前在备份之后删除的t4以及t_incre_2表的数据已经通过2次增量备份恢复过来了，如下所示：

mysql> select * from t4; +-----+---+ | a  | b | +-----+---+ | aaa | 1 | | bbb | 2 | +-----+---+ 2 rows in set (0.05 sec)   mysql> select * from t_incre_2; +----+---+ | a | b | +----+---+ | cc | 2 | | dd | 2 | +----+---+ 2 rows in set (0.06 sec)   mysql>

5，innobackupex先做全量备份

命令如下：

innobackupex –defaults-file=/etc/my.cnf–user=backup –password="123456" –host=192.168.52.129/data/backups/mysql/innobackupex_full_20150319/ –no-timestamp 2>/tmp/innobackupex_full.log

备份过程信息：

[root@data01 mysql]# more/tmp/innobackupex_full.log   InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.   This software is published under the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.   Get the latest version of PerconaXtraBackup, documentation, and help resources: http://www.percona.com/xb/p   150319 02:31:18  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup;host=192.168.52.129'as 'backup'  (using  password: YES). 150319 02:31:18  innobackupex: Connected to MySQL server 150319 02:31:18  innobackupex: Executing a version checkagainst the server... 150319 02:31:18  innobackupex: Done. 150319 02:31:18  innobackupex: Starting the backup operation   IMPORTANT: Please check that the backup runcompletes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".   innobackupex:  Using server version 5.6.12-log   innobackupex: Created backup directory/data/backups/mysql/innobackupex_full_20150319   150319 02:31:18  innobackupex: Starting ibbackup with command:xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/ innobackupex_full_20150319 --tmpdir=/tmp--extra-lsndir='/tmp' innobackupex: Waiting for ibbackup(pid=13118) to suspend innobackupex: Suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2'   xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0, setto 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423920964) xtrabackup: Generating a list oftablespaces [01] Copying ./IBdata1 to/data/backups/mysql/innobackupex_full_20150319/IBdata1 >> log scanned up to (423920964) [01]       ...done [01] Copying ./IBdata2 to/data/backups/mysql/innobackupex_full_20150319/IBdata2 >> log scanned up to (423920964) >> log scanned up to (423920964) [01]       ...done >> log scanned up to (423920964) [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.ibd [01]       ...done [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.ibd [01]       ...done [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.ibd [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.ibd [01]       ...done [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.ibd [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.ibd [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.ibd [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.ibd [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.ibd [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.ibd [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.ibd [01]       ...done [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.ibd [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.ibd [01]       ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t1.ibd [01]       ...done [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t4.ibd [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t3.ibd [01]       ...done [01] Copying ./test/t.ibd to /data/backups/mysql/innobackupex_full_20150319/test/t.ibd [01]       ...done [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.ibd [01]       ...done [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t1.ibd [01]       ...done [01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test.ibd [01]       ...done [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/test2.ibd [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t3.ibd [01]       ...done [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.ibd >> log scanned up to (423920964) [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackupex_full_20150319/user_db/t.ibd [01]       ...done xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2' withpid '13118'   150319 02:31:24  innobackupex: Continuing after ibbackup hassuspended 150319 02:31:24  innobackupex: Executing FLUSH TABLES WITHREAD LOCK... 150319 02:31:24  innobackupex: All tables locked and flushedto disk   150319 02:31:24  innobackupex: Starting to backup non-InnoDBtables and files innobackupex: in subdirectories of'/home/data/mysql/data/' innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files) >> log scanned up to (423920964) innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files) >> log scanned up to (423920964) innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm' innobackupex: Backing up file '/home/data/mysql/data//user_db/t.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt' innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm' 150319 02:31:26  innobackupex: Finished backing up non-InnoDBtables and files   150319 02:31:26  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS... 150319 02:31:26  innobackupex: Waiting for log copying tofinish   >> log scanned up to (423920964) xtrabackup: The latest check point (forincremental): '423920964' xtrabackup: Stopping log copying thread. .>> log scanned up to (423920964)   xtrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_log_copied' with pid'13118' xtrabackup: Transaction log of lsn(423920964) to (423920964) was copied. 150319 02:31:27  innobackupex: All tables unlocked   innobackupex: Backup created in directory'/data/backups/mysql/innobackupex_full_20150319' innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120 150319 02:31:27  innobackupex: Connection to database serverclosed 150319 02:31:27  innobackupex: completed OK! [root@data01 mysql]# 

6，innobackupex做增量备份

6.1做第一次增量备份

先录入增量数据

mysql> create table incre1 select'incre1 table ' as incre1, 'a' as id; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql> select * from incre1; +---------------+----+ | incre1        | id | +---------------+----+ | incre1 table  | a  | +---------------+----+ 1 row in set (0.00 sec)   mysql>

再进行增量备份，命令如下：

innobackupex –incremental/data/backups/mysql/innobackex_incre_1–incremental-basedir=/data/backups/mysql/innobackupex_full_20150319/–user=backup –password="123456" –host=192.168.52.129 –no-timestamp2>/tmp/innobackex_incre_1.log

备份过程信息如下：

[root@data01 mysql]# more/tmp/innobackex_incre_1.log   InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.   This software is published under the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.   Get the latest version of PerconaXtraBackup, documentation, and help resources: http://www.percona.com/xb/p   150319 02:38:04  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES). 150319 02:38:04  innobackupex: Connected to MySQL server 150319 02:38:04  innobackupex: Executing a version checkagainst the server... 150319 02:38:04  innobackupex: Done. 150319 02:38:04  innobackupex: Starting the backup operation   IMPORTANT: Please check that the backup runcompletes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".   innobackupex:  Using server version 5.6.12-log   innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_1   150319 02:38:04  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_1 --tmpdir=/tm p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackupex_full_20150319/' innobackupex: Waiting for ibbackup(pid=13358) to suspend innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2'   xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423920964 isenabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0,set to 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:   innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423925361) xtrabackup: Generating a list oftablespaces xtrabackup: using the full scan forincremental backup >> log scanned up to (423925361) >> log scanned up to (423925361) >> log scanned up to (423925361) >> log scanned up to (423925361) [01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_1/IBdata1.delta >> log scanned up to (423925361) [01]       ...done >> log scanned up to (423925361) [01] Copying ./IBdata2 to /data/backups/mysql/innobackex_incre_1/IBdata2.delta [01]       ...done [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.ibd.delta [01]       ...done [01] Copying./mysql/slave_relay_log_info.ibd to /data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.ibd.delta [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.ibd.delta [01]       ...done >> log scanned up to (423925361) [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.ibd.delta [01]       ...done >> log scanned up to (423925361) [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.ibd.delta [01]        ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_1/test/t1.ibd.delta [01]       ...done [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_1/test/t4.ibd.delta [01]       ...done [01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_1/test/incre1.ibd.delta [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_1/test/t3.ibd.delta [01]       ...done [01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_1/test/t.ibd.delta [01]       ...done [01] Copying ./test/t_incre_2.ibd to/data/backups/mysql/innobackex_incre_1/test/t_incre_2.ibd.delta [01]       ...done [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t1.ibd.delta [01]       ...done [01] Copying ./user_db/test.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test.ibd.delta [01]       ...done [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_1/user_db/test2.ibd.delta [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t3.ibd.delta [01]       ...done [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.ibd.delta [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_1/user_db/t.ibd.delta [01]       ...done >> log scanned up to (423925361) xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_suspended_2' with pid'13358' >> log scanned up to (423925361)   150319 02:38:15  innobackupex: Continuing after ibbackup hassuspended 150319 02:38:15  innobackupex: Executing FLUSH TABLES WITHREAD LOCK... 150319 02:38:15  innobackupex: All tables locked and flushedto disk   150319 02:38:15  innobackupex: Starting to backup non-InnoDBtables and files innobackupex: in subdirectories of'/home/data/mysql/data/' innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files) >> log scanned up to (423925361) innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files) >> log scanned up to (423925361) innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest3.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm' innobackupex: Backing up file '/home/data/mysql/data//sbtest/sbtest7.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm' innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt' innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm' 150319 02:38:17  innobackupex: Finished backing up non-InnoDBtables and files   150319 02:38:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS... 150319 02:38:17  innobackupex: Waiting for log copying tofinish   xtrabackup: The latest check point (forincremental): '423925361' xtrabackup: Stopping log copying thread. .>> log scanned up to (423925361)   xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_1/xtrabackup_log_copied' with pid '13358' xtrabackup: Transaction log of lsn(423925361) to (423925361) was copied. 150319 02:38:18  innobackupex: All tables unlocked   innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_1' innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120 150319 02:38:18  innobackupex: Connection to database server closed 150319 02:38:18  innobackupex: completed OK! [root@data01 mysql]# 

6.2 基于全备和第一个增量备份来做第二次增量备份

先录入增量数据录入

mysql> create table incre2 select'incre2' as a,3 as id; Query OK, 1 row affected (0.12 sec) Records: 1 Duplicates: 0  Warnings: 0   mysql> mysql> select * from incre2; +--------+----+ | a     | id | +--------+----+ | incre2 | 3 | +--------+----+ 1 row in set (0.00 sec)   mysql>  

开始进行第二次增量备份，备份命令：

innobackupex –incremental/data/backups/mysql/innobackex_incre_2 –incremental-basedir=/data/backups/mysql/innobackex_incre_1/–user=backup –password="123456" –host=192.168.52.129 –no-timestamp2>/tmp/innobackex_incre_2.log

备份过程信息如下：

[root@data01 innobackex_incre_1]# more/tmp/innobackex_incre_2.log   InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy and Percona LLC and/or its affiliates2009-2013.  All Rights Reserved.   This software is published under the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.   Get the latest version of PerconaXtraBackup, documentation, and help resources: http://www.percona.com/xb/p   150319 02:51:09  innobackupex: Connecting to MySQL server withDSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.52.129' as'backup'  (using password: YES). 150319 02:51:09  innobackupex: Connected to MySQL server 150319 02:51:09  innobackupex: Executing a version checkagainst the server... 150319 02:51:09  innobackupex: Done. 150319 02:51:09  innobackupex: Starting the backup operation   IMPORTANT: Please check that the backup runcompletes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".   innobackupex:  Using server version 5.6.12-log   innobackupex: Created backup directory/data/backups/mysql/innobackex_incre_2   150319 02:51:10  innobackupex: Starting ibbackup with command:xtrabackup --defaults-group="mysqld" --backup --suspend-at-end--target-dir=/data/backups/mysql/innobackex_incre_2 --tmpdir=/tm p --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackex_incre_1/' innobackupex: Waiting for ibbackup(pid=13694) to suspend innobackupex: Suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2'   xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: ) incremental backup from 423925361 isenabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /home/data/mysql/data xtrabackup: open files limit requested 0,set to 1024 xtrabackup: using the following InnoDBconfiguration: xtrabackup:   innodb_data_home_dir = ./ xtrabackup:  innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend xtrabackup:   innodb_log_group_home_dir = ./ xtrabackup:   innodb_log_files_in_group = 3 xtrabackup:   innodb_log_file_size = 67108864 >> log scanned up to (423929601) xtrabackup: Generating a list oftablespaces xtrabackup: using the full scan forincremental backup >> log scanned up to (423929601) [01] Copying ./IBdata1 to/data/backups/mysql/innobackex_incre_2/IBdata1.delta >> log scanned up to (423929601) [01]       ...done [01] Copying ./IBdata2 to/data/backups/mysql/innobackex_incre_2/IBdata2.delta [01]       ...done [01] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_master_info.ibd.delta [01]       ...done >> log scanned up to (423929601) [01] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/innobackex_incre_2/mysql/slave_relay_log_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_index_stats.ibd.delta [01]       ...done [01] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/innobackex_incre_2/mysql/slave_worker_info.ibd.delta [01]       ...done [01] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/innobackex_incre_2/mysql/innodb_table_stats.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest5.ibd to /data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.ibd.delta [01]       ...done [01] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.ibd.delta [01]       ...done [01] Copying ./test/incre2.ibd to/data/backups/mysql/innobackex_incre_2/test/incre2.ibd.delta [01]       ...done [01] Copying ./test/t1.ibd to/data/backups/mysql/innobackex_incre_2/test/t1.ibd.delta [01]       ...done [01] Copying ./test/t4.ibd to/data/backups/mysql/innobackex_incre_2/test/t4.ibd.delta [01]       ...done [01] Copying ./test/incre1.ibd to/data/backups/mysql/innobackex_incre_2/test/incre1.ibd.delta [01]       ...done [01] Copying ./test/t3.ibd to/data/backups/mysql/innobackex_incre_2/test/t3.ibd.delta [01]       ...done >> log scanned up to (423929601) [01] Copying ./test/t.ibd to/data/backups/mysql/innobackex_incre_2/test/t.ibd.delta [01]       ...done [01] Copying ./test/t_incre_2.ibd to /data/backups/mysql/innobackex_incre_2/test/t_incre_2.ibd.delta [01]       ...done [01] Copying ./user_db/t1.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t1.ibd.delta [01]       ...done [01] Copying ./user_db/test.ibd to /data/backups/mysql/innobackex_incre_2/user_db/test.ibd.delta [01]       ...done [01] Copying ./user_db/test2.ibd to/data/backups/mysql/innobackex_incre_2/user_db/test2.ibd.delta [01]       ...done [01] Copying ./user_db/t3.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t3.ibd.delta [01]       ...done [01] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.ibd.delta [01]       ...done [01] Copying ./user_db/t.ibd to/data/backups/mysql/innobackex_incre_2/user_db/t.ibd.delta [01]       ...done >> log scanned up to (423929601) xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_suspended_2' with pid'13694'   150319 02:51:16  innobackupex: Continuing after ibbackup hassuspended 150319 02:51:16  innobackupex: Executing FLUSH TABLES WITHREAD LOCK... 150319 02:51:16  innobackupex: All tables locked and flushedto disk   150319 02:51:16  innobackupex: Starting to backup non-InnoDBtables and files innobackupex: in subdirectories of '/home/data/mysql/data/' innobackupex: Backing up files'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(53 files) >> log scanned up to (423929601) innobackupex: Backing up files'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(74 files) >> log scanned up to (423929601) innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest3.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest4.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest6.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest8.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest1.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/db.opt' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest2.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest7.frm' innobackupex: Backing up file'/home/data/mysql/data//sbtest/sbtest5.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t_incre_2.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t4.frm' innobackupex: Backing up file '/home/data/mysql/data//test/incre2.frm' innobackupex: Backing up file'/home/data/mysql/data//test/incre1.frm' innobackupex: Backing up file'/home/data/mysql/data//test/t1.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/UC_USER.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t3.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/test.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/db.opt' innobackupex: Backing up file'/home/data/mysql/data//user_db/test2.frm' innobackupex: Backing up file'/home/data/mysql/data//user_db/t1.frm' 150319 02:51:17  innobackupex: Finished backing up non-InnoDBtables and files   150319 02:51:17  innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS... 150319 02:51:17  innobackupex: Waiting for log copying tofinish   xtrabackup: The latest check point (forincremental): '423929601' xtrabackup: Stopping log copying thread. .>> log scanned up to (423929601)   xtrabackup: Creating suspend file'/data/backups/mysql/innobackex_incre_2/xtrabackup_log_copied' with pid '13694' xtrabackup: Transaction log of lsn(423929601) to (423929601) was copied. 150319 02:51:18  innobackupex: All tables unlocked   innobackupex: Backup created in directory'/data/backups/mysql/innobackex_incre_2' innobackupex: MySQL binlog position:filename 'mysql-bin.000066', position 120 150319 02:51:19  innobackupex: Connection to database serverclosed 150319 02:51:19  innobackupex: completed OK! 

7，innobackupex做增量恢复

7.1先删除两次增量数据，用来查看验证恢复结果

mysql> delete from incre1; Query OK, 1 row affected (0.05 sec)   mysql> delete from incre2; Query OK, 1 row affected (0.01 sec)   mysql> select * from incre1; Empty set (0.00 sec)   mysql> select * from incre2; Empty set (0.00 sec)

7.2 开始做恢复，恢复全备份

备份命令如下：

innobackupex –user=backup–password="123456" –host=192.168.52.129  –apply-log/data/backups/mysql/innobackupex_full_20150319/2>/tmp/innobackex_restore_full.log

备份过程信息如下：

...... InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053   xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 423921634 150319 03:06:57  innobackupex: completed OK!

7.3 基于全备份进行第一字增量备份的恢复

innobackupex –defaults-file=/etc/my.cnf–user=backup –password="123456" –host=192.168.52.129  /data/backups/mysql/innobackupex_full_20150319/–incremental-dir=/data/backups/mysql/innobackex_incre_1  –apply-log2>/tmp/innobackex_restore_incre_1.log

备份过程信息如下：

…… xtrabackup: starting shutdown withinnodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequencenumber 423925452 innobackupex: Starting to copy non-InnoDBfiles in '/data/backups/mysql/innobackex_incre_1' innobackupex: to the full backup directory'/data/backups/mysql/innobackupex_full_20150319' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_info' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/xtrabackup_binlog_info' to'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_binlog_info' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_global_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/threads.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/threads.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history_long.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_instance.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_user_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_account_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_user_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_user_by_event_name.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_current.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_lock_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_lock_waits_summary_by_table.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_instances.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_actors.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_actors.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_table.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_table.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_account_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_account_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/cond_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/cond_instances.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/users.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/users.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/db.opt' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_instance.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_instance.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_thread_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_current.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_current.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/objects_summary_global_by_type.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/objects_summary_global_by_type.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_thread_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_host_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_history.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_history.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_thread_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_thread_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_global_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_global_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_timers.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_timers.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/hosts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/hosts.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_host_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_host_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/host_cache.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/host_cache.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_objects.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_objects.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/setup_consumers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_consumers.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_account_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_account_connect_attrs.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_host_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_host_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/socket_summary_by_instance.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/socket_summary_by_instance.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_global_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_global_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_summary_by_user_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_summary_by_user_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/mutex_instances.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/mutex_instances.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/setup_instruments.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/setup_instruments.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_summary_by_digest.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_summary_by_digest.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/rwlock_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/rwlock_instances.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_summary_by_event_name.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_summary_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_summary_by_account_by_event_name.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_summary_by_account_by_event_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_stages_current.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_stages_current.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/session_connect_attrs.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/session_connect_attrs.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/performance_timers.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/performance_timers.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/performance_schema/events_waits_history_long.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_waits_history_long.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/file_instances.frm'to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/file_instances.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/events_statements_history_long.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/events_statements_history_long.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/table_io_waits_summary_by_index_usage.frm'to '/data/backups/mysql/innobackupex_full_20150319/performance_schema/table_io_waits_summary_by_index_usage.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/performance_schema/accounts.frm' to'/data/backups/mysql/innobackupex_full_20150319/performance_schema/accounts.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/event.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYI' to '/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYI' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSV' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.frm' to '/data/backups/mysql/innobackupex_full_20150319/mysql/db.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYD' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_worker_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_worker_info.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.CSM' to '/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.CSM' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYD' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/event.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYI' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/slave_relay_log_info.frm'to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_relay_log_info.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/func.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/func.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/procs_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/procs_priv.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_index_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_index_stats.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYD' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.frm'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/tables_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/tables_priv.MYD' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition_type.MYI'to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition_type.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSM' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSM' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/ndb_binlog_index.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/ndb_binlog_index.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slave_master_info.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slave_master_info.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYI'to '/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/db.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/db.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_leap_second.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_leap_second.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_relation.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_relation.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proxies_priv.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proxies_priv.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_keyword.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_keyword.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/servers.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/servers.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_transition.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_transition.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_category.MYI' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_category.MYI' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/innodb_table_stats.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/innodb_table_stats.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/slow_log.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/slow_log.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest6.frm' to '/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/test/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_1/user_db/db.opt'to '/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_1/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm' 150319 03:12:51  innobackupex: completed OK! 

7.4, 基于全备份和第一次增量备份，恢复第二次增量备份

备份命令如下：

innobackupex –defaults-file=/etc/my.cnf–user=backup –password="123456" –host=192.168.52.129  /data/backups/mysql/innobackupex_full_20150319/–incremental-dir=/data/backups/mysql/innobackex_incre_2  –apply-log2>/tmp/innobackex_restore_incre_2.log

备份过程信息如下：

…… innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/user.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/user.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/time_zone_name.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/time_zone_name.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/columns_priv.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/columns_priv.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/plugin.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/plugin.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/general_log.CSV' to'/data/backups/mysql/innobackupex_full_20150319/mysql/general_log.CSV' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/event.MYD' to '/data/backups/mysql/innobackupex_full_20150319/mysql/event.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/proc.MYD' to'/data/backups/mysql/innobackupex_full_20150319/mysql/proc.MYD' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/mysql/help_topic.frm' to'/data/backups/mysql/innobackupex_full_20150319/mysql/help_topic.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest3.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest3.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest4.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest4.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest6.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest6.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest8.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest8.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest1.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/db.opt' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest2.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest7.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest7.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/sbtest/sbtest5.frm' to'/data/backups/mysql/innobackupex_full_20150319/sbtest/sbtest5.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t_incre_2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t_incre_2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/t3.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/test/t4.frm'to '/data/backups/mysql/innobackupex_full_20150319/test/t4.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre2.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/incre1.frm' to'/data/backups/mysql/innobackupex_full_20150319/test/incre1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/test/t1.frm' to '/data/backups/mysql/innobackupex_full_20150319/test/t1.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/UC_USER.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/UC_USER.frm' innobackupex: Copying '/data/backups/mysql/innobackex_incre_2/user_db/t.frm'to '/data/backups/mysql/innobackupex_full_20150319/user_db/t.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t3.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t3.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/db.opt' to'/data/backups/mysql/innobackupex_full_20150319/user_db/db.opt' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/test2.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/test2.frm' innobackupex: Copying'/data/backups/mysql/innobackex_incre_2/user_db/t1.frm' to'/data/backups/mysql/innobackupex_full_20150319/user_db/t1.frm' 150319 03:18:34  innobackupex: completed OK! [root@data01 mysql]# 

7.5 恢复整个数据库

停止数据库

 service mysqld5612 stop

 清空数据目录下所有文件

 mkdir -p /tmp/mysqldatabak/

 mv/home/data/mysql/data/* /tmp/mysqldatabak/

 将恢复好的数据按照配置文件的需求拷贝到相应目录

 innobackupex –defaults-file=/etc/my.cnf–user=backup –password="123456" –host=192.168.52.129–defaults-file=/etc/my.cnf –copy-back/data/backups/mysql/innobackupex_full_20150319/ 2>/tmp/innobackex_incre_restore_end.log

赋予mysql账号权限

chown -R mysql:mysql /home/data/mysql/data

启动mysql服务

service mysqld5612 start

PS：–copy-back过程如下：

innobackupex: Starting to copy InnoDB undotablespaces innobackupex: in'/data/backups/mysql/innobackupex_full_20150319' innobackupex: back to'/home/data/mysql/data'   innobackupex: Starting to copy InnoDB logfiles innobackupex: in'/data/backups/mysql/innobackupex_full_20150319' innobackupex: back to original InnoDB logdirectory '/home/data/mysql/data' innobackupex: Copying '/data/backups/mysql/innobackupex_full_20150319/ib_logfile1'to '/home/data/mysql/data/ib_logfile1' innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile2' to'/home/data/mysql/data/ib_logfile2' innobackupex: Copying'/data/backups/mysql/innobackupex_full_20150319/ib_logfile0' to'/home/data/mysql/data/ib_logfile0' innobackupex: Finished copying back files.   150319 03:23:49  innobackupex: completed OK!

7.6 check已经被delete的数据

登录mysql界面，查看表incre1和表incre2，数据已经恢复，如下所示：

mysql> select * from incre1; +---------------+----+ | incre1        | id | +---------------+----+ | incre1 table  | a  | +---------------+----+ 1 row in set (0.04 sec)   mysql> select * from incre2; +--------+----+ | a     | id | +--------+----+ | incre2 | 3 | +--------+----+ 1 row in set (0.07 sec)   mysql>

参考文档：http://www.percona.com/doc/percona-xtrabackup/2.2/howtos/recipes_xbk_inc.html

 ——––——-—————————————————————––——————————
<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!>
原博客地址：       http://blog.itpub.net/26230597/viewspace-1468124/
原作者：黄杉 (mchdba)
——––—–—————————————————————————————————

欢迎大家阅读《MySQL 利用xtrabackup开展增量备份详细过程汇总_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)