mysql5.6基于GTID主從復制設置

1.主庫和從庫都必須要開啟binlog
2.主庫和從庫server-id不相同
3.建立主從輔助用戶
grant replication slave on . to 'rep'@'192.168.200.%' identified by 'oldboy';
[mysqld]
server-id=1
log-bin=mysql.bin
binlog_format=row
gtid_mode=ON
enforce_gtid_consistency
log_slave_updates
relay_log_purge=OFF
備注：以上設置必須存在。

change master to master_host='192.168.200.77',
master_port=3306,
master_user='rep',
master_password='oldboy123',
master_auto_position=1;

start slave;
show slave status\G;
reset slave all; 清除所有slave信息

錯誤提示：導出時提示warning，A partial dump from a server that has GTIDs
[root@bastion-IDC ~]# mysqldump -uroot -p xqsj_db > xqsj_db20160811.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that
changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete
dump, pass --all-databases --triggers --routines --events.

關(guān)于GTID是5.6以后，加入了全局事務 ID (GTID) 來強化數(shù)據(jù)庫的主備一致性，故障恢復，以及容錯能力。
官方給的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因為在一個數(shù)據(jù)庫里面唯一，但是當導入其他的庫就有可能重復。所有會有一個提醒。

可以通過添加--set-gtid-purged=off 或者–gtid-mode=OFF這兩個參數(shù)設置。
很有肯能是在導入庫中重新生產(chǎn)GTID，而不用原來的。

[root@master]# mysqldump -uroot -poldboy --set-gtid-purged=off -A -B -F > all.sql #這樣就ok了！