MySQL利用XtraBackup进行增量备份详细过程汇总

MySQL 利用XtraBackup进行增量备份详细过程汇总

Xtrabackup下载、安装以及全量备份请参考：

MySQL管理之使用XtraBackup进行热备

MySQL开源备份工具Xtrabackup备份部署

MySQL Xtrabackup备份和恢复

用XtraBackup实现MySQL的主从复制快速部署【主不锁表】

安装和使用 Percona 推出的 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/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先录入一张表，，实现第一次增量数据：