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为了验证比对,先删除两个增量备份前表里面的数据