MySQL利用xtrabackup进行增量备份详细过程汇总_MySQL
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/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: 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] ...donextrabackup: 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 再进行增量备份1xtrabackup --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.logxtrabackup 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/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864>> log scanned up to (423925813)xtrabackup: Generating a list oftablespacesxtrabackup: 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] ...donextrabackup: 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，实现第二天增量数据建立一张表t_incre_2：
mysql> use test;Database changedmysql> 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.logxtrabackup 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/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864>> log scanned up to (423931610)xtrabackup: Generating a list oftablespacesxtrabackup: 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_20150319xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )xtrabackup: cd to/data/backups/mysql/full_incre_20150319xtrabackup: 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:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864xtrabackup: using the following InnoDBconfiguration for recovery:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864xtrabackup: Starting InnoDB instance forrecovery.xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)InnoDB: Using atomics to ref count bufferpool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomicbuiltinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size =100.0MInnoDB: Completed initialization of bufferpoolInnoDB: Setting log file ./ib_logfile101size to 64 MBInnoDB: Setting log file ./ib_logfile1 sizeto 64 MBInnoDB: Setting log file ./ib_logfile2 sizeto 64 MBInnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0InnoDB: New log files created,LSN=423920168InnoDB: 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 = 1InnoDB: 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.logxtrabackup 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_20150319xtrabackup: 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:autoextendxtrabackup: innodb_log_group_home_dir =/data/backups/mysql/incre_20150320xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: Generating a list oftablespacesxtrabackup: page size for /data/backups/mysql/incre_20150320/IBdata2.deltais 16384 bytesApplying/data/backups/mysql/incre_20150320/IBdata2.delta to ./IBdata2...xtrabackup: page size for/data/backups/mysql/incre_20150320/IBdata1.delta is 16384 bytesApplying /data/backups/mysql/incre_20150320/IBdata1.deltato ./IBdata1...xtrabackup: page size for/data/backups/mysql/incre_20150320/mysql/slave_worker_info.ibd.delta is 16384bytesApplying/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 16384bytesApplying/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 16384bytesApplying/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 bytesApplying/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 16384bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying /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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying /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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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:autoextendxtrabackup: innodb_log_group_home_dir =/data/backups/mysql/incre_20150320xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: Starting InnoDB instance forrecovery.xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)InnoDB: Using atomics to ref count bufferpool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomicbuiltinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size = 100.0MInnoDB: Completed initialization of bufferpoolInnoDB: 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 pagesInnoDB: 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 = 1InnoDB: 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.logxtrabackup 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_20150319xtrabackup: 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:autoextendxtrabackup: innodb_log_group_home_dir =/data/backups/mysql/incre_20150321xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: Generating a list oftablespacesxtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata2.delta is 16384 bytesApplying /data/backups/mysql/incre_20150321/IBdata2.deltato ./IBdata2...xtrabackup: page size for/data/backups/mysql/incre_20150321/IBdata1.delta is 16384 bytesApplying/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 bytesApplying/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 16384bytesApplying/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 16384bytesApplying/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 bytesApplying/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 16384bytesApplying /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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying /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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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 bytesApplying/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:autoextendxtrabackup: innodb_log_group_home_dir =/data/backups/mysql/incre_20150321xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 2097152xtrabackup: Starting InnoDB instance forrecovery.xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)InnoDB: Using atomics to ref count bufferpool pagesInnoDB: The InnoDB memory heap is disabledInnoDB: Mutexes and rw_locks use GCC atomicbuiltinsInnoDB: Memory barrier is not usedInnoDB: Compressed tables use zlib 1.2.3InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, size =100.0MInnoDB: Completed initialization of bufferpoolInnoDB: 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 pagesInnoDB: 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 = 1InnoDB: 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 Oyand Percona LLC and/or its affiliates2009-2013. All Rights Reserved. This software is published underthe 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 server150319 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 suspendinnobackupex: 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/dataxtrabackup: open files limit requested 0, setto 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: 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] ...donextrabackup: Creating suspend file'/data/backups/mysql/innobackupex_full_20150319/xtrabackup_suspended_2' withpid '13118' 150319 02:31:24 innobackupex: Continuing after ibbackup hassuspended150319 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 filesinnobackupex: 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 120150319 02:31:27 innobackupex: Connection to database serverclosed150319 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 Oyand Percona LLC and/or its affiliates2009-2013. All Rights Reserved. This software is published underthe 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 server150319 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=/tmp --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackupex_full_20150319/'innobackupex: Waiting for ibbackup(pid=13358) to suspendinnobackupex: 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/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864>> log scanned up to (423925361)xtrabackup: Generating a list oftablespacesxtrabackup: 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 hassuspended150319 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 filesinnobackupex: 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 120150319 02:38:18 innobackupex: Connection to database server closed150319 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 Oyand Percona LLC and/or its affiliates2009-2013. All Rights Reserved. This software is published underthe 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 server150319 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=/tmp --extra-lsndir='/tmp'--incremental-basedir='/data/backups/mysql/innobackex_incre_1/'innobackupex: Waiting for ibbackup(pid=13694) to suspendinnobackupex: 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/dataxtrabackup: open files limit requested 0,set to 1024xtrabackup: using the following InnoDBconfiguration:xtrabackup: innodb_data_home_dir = ./xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextendxtrabackup: innodb_log_group_home_dir = ./xtrabackup: innodb_log_files_in_group = 3xtrabackup: innodb_log_file_size = 67108864>> log scanned up to (423929601)xtrabackup: Generating a list oftablespacesxtrabackup: 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 hassuspended150319 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 filesinnobackupex: 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 120150319 02:51:19 innobackupex: Connection to database serverclosed150319 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: st |