Xtrabackup恢复后以GTID方式搭建主从复制

一.原库已有过主从复制关系时搭建主从复制

1.查看备份时的日志信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

[root@slave1-217 full]# cat xtrabackup_info uuid = 6864e20d-d287-11ec-a3fa-00155d093321 name = tool_name = innobackupex tool_command = --defaults-file=/etc/my.cnf --user= TEST --password=... --host=192.168.9.xxx --port=3306 --stream=tar /tmp tool_version = 2.4.14 ibbackup_version = 2.4.14 server_version = 5.7.19-log start_time = 2022-05-13 14:26:53 end_time = 2022-05-13 14:39:15 lock_time = 0 binlog_pos = filename 'binlog.000001', position '4938', GTID of the last change '1b6f4e0e-c975-11e9-bebc-00155d0d6702:1-5, 393b0a9b-6f30-11e9-968d-18ded7dd891e:1-30320913, 63b78c12-0551-11ea-a200-18ded7dd891e:1-207116, 873d4404-4fa8-11e9-a1f8-18ded7dd891e:1-61, 8e41f4bf-cd31-11ec-acd7-00155d093321:1-11, a2229c41-51f0-11e9-9be6-18ded7dd891e:1-3027248, abb274a8-abba-11ea-959f-f063f9581c90:1-168818, b8f7d7b4-6f2b-11e9-968d-18ded7dd891e:1-14383796, ce9be252-2b71-11e6-b8f4-00212889f856:1-314834886' innodb_from_lsn = 0 innodb_to_lsn = 5215994115 partial = N incremental = N format = tar compact = N compressed = N encrypted = N

　　2.重置GTID

?

1 2 3 4 5 6 7 8 9

mysql> reset master;<br>mysql> SET GLOBAL gtid_purged='1b6f4e0e-c975-11e9-bebc-00155d0d6702:1-5, 393b0a9b-6f30-11e9-968d-18ded7dd891e:1-30320913, 63b78c12-0551-11ea-a200-18ded7dd891e:1-207116, 873d4404-4fa8-11e9-a1f8-18ded7dd891e:1-61, 8e41f4bf-cd31-11ec-acd7-00155d093321:1-11, a2229c41-51f0-11e9-9be6-18ded7dd891e:1-3027248, abb274a8-abba-11ea-959f-f063f9581c90:1-168818, b8f7d7b4-6f2b-11e9-968d-18ded7dd891e:1-14383796, ce9be252-2b71-11e6-b8f4-00212889f856:1-314834886';<br><br>

ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.（执行reset master 后再重置）

　　3.搭建主从

?

1 2	mysql> change master to master_host='192.168.9.xxx',master_user='TEST',master_port=3306,master_password='12345',master_auto_position=1 ; Query OK, 0 rows affected, 2 warnings (1.89 sec)

　　

二.原库没有主从信息直接指向即可

?

1 2	mysql> change master to master_host='192.168.9.xxx',master_user='TEST',master_port=3306,master_password='12345',master_auto_position=1 ; Query OK, 0 rows affected, 2 warnings (1.89 sec)