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先录入一张表，实现第一次增量数据：