一.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