查看“Xtrabackup备份恢复MySQL从库”的源代码

xtrabackup的流备份恢复MySQL从库步骤
=== 从库关闭MySQL,并移除数据目录 ===
 
=== 主库上授权 (如果无授权) ===
 grant reload,lock tables,replication client,process on *.* to backup@'%' identified by 'password';
 grant replication slave,replication client on *.* to repl@'%' identified by 'fpassword';
=== 主库和从库上分别安装xtrabackup ===
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

https://www.percona.com/redir/downloads/percona-release/redhat

https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
 yum install epel-release
 yum install https://www.percona.com/redir/downloads/percona-release/redhat/1.0-14/percona-release-1.0-14.noarch.rpm
 yum install libev qpress
 yum install perl-DBD-MySQL
 yum install percona-xtrabackup-24

=== 主、从库分别建备份目录 ===
 mkdir -p /data/backups/
=== 主库上发起流备份，并通过管道传输到从库解包 ===
 xtrabackup -ubackup -ppassword -H127.0.0.1 -P3306 \
 --backup \
 --stream=xbstream \
 --compress \
 --target-dir=/data/backups/ | ssh root@192.168.199.199 "xbstream -x -C /data/backups"

=== 从库上解压，解压后删除原压缩文件 ===
 xtrabackup --decompress --parallel=4 --remove-original --target-dir=/data/backups/
=== 从库上prepare阶段，应用redo log ===
 xtrabackup --prepare --use-memory=1GB --target-dir=/data/backups/
从库采用move-back方法，把应用好的数据恢复到数据目录
 xtrabackup --defaults-file=/data/mysql/my.cnf --move-back --parallel=4 --target-dir=/data/backups/
注意:这里有一个坑，不要设置innodb_log_group_home_dir =./ ，xtrabackup会读错路径，导致无法拷贝，解决办法是注释掉这条参数。

=== 从库修改数据目录权限 ===
 chown -R mysql:mysql /database/mysql/
=== 从库启动数据库 ===

=== 从库修改gtid编号 ===
 cat /data/mysql/mysql3306/data/xtrabackup_info |grep binlog_pos
 binlog_pos = filename 'mysql-bin.000009', position '190', GTID of the last change 'c0900221-709b-11e9-b8ea-000c29abbade:1-1539006'
 
 mysql>  reset master;
 mysql>  set global gtid_purged="c0900221-709b-11e9-b8ea-000c29abbade:1-1539006"
=== 从库启动复制 ===
 mysql> CHANGE MASTER TO
        MASTER_HOST='192.168.199.198',
        MASTER_USER='repl',
        MASTER_PASSWORD='fanderpassword',
        MASTER_PORT=3306,
        MASTER_AUTO_POSITION=1;
 
 mysql> start slave;