MySQL 5.7 基于GTID创建运行主库的从库-xtrabackup+mysqldump

一.GTID innobackupex备份实现主从同步

• 1)master备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --parallel=4 /backup

• 2)拷贝到slave上，并prepare和copy backup

innobackupex --defaults-file=/etc/my.cnf --apply-log --user=root --password=123456 --port=3306 /backup/2018-04-23_17-04-55
rm -rf /mysqlData/data/*
rm -rf /mysqlData/logs/undolog/*
innobackupex --defaults-file=/etc/my.cnf --copy-back --user=root --password=123456 --port=3306 /backup/2018-04-23_17-04-55
chown -R mysql:mysql /mysqlData/
/etc/init.d/mysqld start

• 3)从备份目录的文件xtrabackup_info中获取GTID信息

binlog_pos = filename 'binlog.000176', position '38885756', GTID of the last change '73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1-17477471'

• 4)master中的GTID信息

root@slave01 10:20:  [(none)]> show master status\G
*************************** 1. row ***************************
             File: binlog.000176
         Position: 159643240
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1-17577902
1 row in set (0.00 sec)

• 5)设置GTID

reset slave all;
reset master;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='73c029dc-2034-11e8-90a5-005056a365b6:1-594908,856d79f8-2038-11e8-b511-005056a330bb:1-3,b658767f-2044-11e8-951f-005056a330bb:1-17477471';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

• 6)设置主从命令并启动复制

CHANGE MASTER TO
MASTER_HOST='172.16.3.153',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@',
master_auto_position=1;
#
start slave;

• 7)查看从库的信息master信息

root@slave02 10:24:  [(none)]> show master status\G
*************************** 1. row ***************************
             File: binlog.000001
         Position: 4936475
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1-17480536
1 row in set (0.00 sec)

二.GTID mysqldump新建运行中的slave从库

• 1)主库先备份

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -S=/data/my3306/run/mysql.sock --single-transaction --master-data=2 -A > /home/backup/all.sql

• 2)在备份文件all.sql中有GTID信息

-- GTID state at the beginning of the backup 
--
#
SET @@GLOBAL.GTID_PURGED='73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1-17573387';

• 3)查看master上的GTID信息

root@slave01 09:24:  [(none)]> show master status\G
*************************** 1. row ***************************
             File: binlog.000176
         Position: 155181848
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1-17573561
1 row in set (0.00 sec)

这里已经执行过的GTID和备份all.sql中是不一样的

• 4)在一个将要成为slave的数据库上做恢复

reset slave all;
reset master;
source /backup/all.sql

• 5)恢复完毕后再做一次reset slave;

                   Last_Errno: 1872
                   Last_Error: Slave failed to initialize relay log info structure from the repository

如果不reset slave，会报1872错误

• 6)主从命令

CHANGE MASTER TO 
MASTER_HOST='172.16.3.153',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@',
master_auto_position=1;

• 7)启动复制start slave;
• 8)查看复制信息

           Retrieved_Gtid_Set: b658767f-2044-11e8-951f-005056a330bb:17573388-17575621
            Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,
            856d79f8-2038-11e8-b511-005056a330bb:1-3,
            b658767f-2044-11e8-951f-005056a330bb:1-17574404