基于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 主从库核心配置

server-id = 3306171 #注意主从的server-id不能一致，建议设置成端口+ip最后一位
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.sql
scp db1-20161004.sql root@172.16.123.172:/root

4 从库
在db1-20161004.sql中找到CHANGE MASTER TO并记录下来
mysql -S /tmp/mysql.sock -uroot -p < db1-20161004.sql
CHANGE MASTER TO
MASTER_HOST=’172.16.123.171′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl4slave’,
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1027;

start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试
主库插入一条数据
insert into db1.tb1(name) values(‘王伍’);

从库执行show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes