mysql gtid 搭建主从,mysql7.7.22 Gtid主从搭建

1、主从的my.cnf的server_id=x 不一致

主库操作,创建一个用户，然后授权该用户可以再从上进行复制所有库

>create user 'rep1'@'192.168.6.128' identified by '123456';

>grant replication slave on *.* to 'rep1'@'192.168.6.128' identified by '123456';

>flush PRIVILEGES;

2、把主库的数据文件做全量备份

[root@lvs-master mysql]# bin/mysqldump -uroot --master-data=2 --single-transaction --set-gtid-purged=OFF -p123456 --databases $(/home/work/mysql/bin/mysql -uroot -p123456 -N -e "show databases"|egrep -v "performance_schema|information_schema|azkaban"|xargs echo)|gzip > all_dbs_fulldump.sql.gz

3、把主库的备份文件传到从库，解压

[work@lvs-slave mysql]$ gunzip all_dbs_fulldump.sql.gz

[work@lvs-slave mysql]$ ./bin/mysql -u root -p -h localhost -S /home/work/mysql/tmp/mysql.sock < all_dbs_fulldump.sql

4、从库操作

停掉slave,配置主库的信息，启动slave，查看状态

>stop slave;

>change master to master_host='192.168.6.130',master_user='rep1',master_password='123456',master_port=3306,MASTER_AUTO_POSITION=1;

>start slave;

>show slave status\G

如果这里出现：

Last_SQL_Error: Error 'Operation CREATE USER failed for 'rep1'@'192.168.6.128'' on query. Default database: ''. Query: 'CREATE USER 'rep1'@'192.168.6.128' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

则需要删除本地的'rep1'@'192.168.6.128' 账户，然后再进行配置master的Gtid同步

>drop user 'rep1'@'192.168.6.128';

>FLUSH PRIVILEGES;

>stop slave;

>change master to master_host='192.168.6.130',master_user='rep1',master_password='123456',master_port=3306,MASTER_AUTO_POSITION=1;

>start slave;

>show slave status\G

检测

Slave_IO_Running和Slave_SQL_Running 是否都为yes

Retrieved_Gtid_Set和Executed_Gtid_Set都有值