基于GTID的复制环境搭建

基础环境
操作系统: CentOS 7.2
MySQL版本: mysql-5.7.14-linux-glibc2.5-x86_64
主库IP: 172.16.123.171
从库IP: 172.16.123.172
端口: 3306

1 主从库核心配置
gtid-mode = on
enforce_gtid_consistency = 1

server-id = 3306171
binlog_format = row
log-bin=/data/mysql/mysql3306/logs/mysql-bin

skip_slave_start = 1 #禁止mysql服务启动时启动主从关系

2 主库创建复制用的账号
create user 'repl'@172.16.123.%' identified by 'repl4slave';
grant replication slave on *.* to 'repl'@'172.16.123.%';

3 主库
root@localhost [(none)]>create database db1;
root@localhost [(none)]>use db1;
root@localhost [db1]>create table tb1(id int(11) not null auto_increment, name varchar(10) not null default '', primary key(id));
root@localhost [db1]>insert into tb1(name) values('张三'),('李四');

mysqldump -S /tmp/mysql.sock -uroot -p --master-data=2 --single-transaction -A > db1-20161004_gtid.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.
以上警告说是备份包含GTID, 备份文件中可以看到SET @@GLOBAL.GTID_PURGED='76814042-89dc-11e6-85e3-00155d016707:1';
如果是部分备份，不需要GTID的时候，可以加上--set-gtid-purged=OFF

scp db1-20161004_gtid.sql root@172.16.123.172:/root

4 从库
mysql -S /tmp/mysql.sock -uroot -p < ~/db1-20161004_gtid.sql
CHANGE MASTER TO
MASTER_HOST='172.16.123.171',
MASTER_USER='repl',
MASTER_PASSWORD='repl4slave',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Executed_Gtid_Set: 76814042-89dc-11e6-85e3-00155d016707:1-7
测试
主库插入一条数据
insert into db1.tb1(name) values('王伍');
主库和从库执行show master status;查看Executed_Gtid_Set是否一致

如果导入备份数据提示错误ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
解决办法reset master;