基于gtid实现一主二从

环境设定

base2	172.25.78.12	主master
base3	172.25.78.13	从master，slave
base4	172.25.78.14	slave

配置base2

[root@base2 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@base2 ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm 
mysql-community-common-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm 
mysql-community-server-5.7.24-1.el7.x86_64.rpm 
[root@base2 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id=1 
gtid_mode =ON
enforce-gtid-consistency=true
[root@base2 ~]# systemctl  start mysqld	
[root@base2 mysql]# cat /var/log/mysqld.log  | grep password

[root@base2 mysql]# mysql -p 
Enter password:
mysql> alter user root@localhost identified by 'Ting@666';    # 修改密码
mysql> grant replication slave on *.* to repl@'172.25.78.%' identified by 'Ting@666';  # 授权

配置base3

[root@base3 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@base3 ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm 
mysql-community-common-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm 
mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@base3 ~]# vim /etc/my.cnf
server_id=2
gtid_mode =ON
enforce-gtid-consistency=true
log_bin = mysql-log
log_slave_updates=ON
[root@base3 ~]# systemctl start mysqld
[root@base3 ~]# cat /var/log/mysqld.log | grep password
[root@base3 ~]# mysql -p
Enter password:
mysql> alter user root@localhost identified by 'Ting@666';
mysql> stop slave;
mysql> change master to master_host='172.25.78.12',master_user='repl',master_password='Ting@666',MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;      #保证连接成功

配置base4

[root@base4 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@base4 ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm 
mysql-community-common-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-5.7.24-1.el7.x86_64.rpm 
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm 
mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@base4 ~]# vim /etc/my.cnf
server_id=3
gtid_mode =ON
enforce-gtid-consistency=true
log_bin = mysql-log
log_slave_updates=ON

[root@base4 ~]# cat /var/log/mysqld.log | grep password

[root@base4 mysql]# mysql -p 
Enter password: 
mysql> alter user root@localhost identified by 'Ting@666';
mysql> stop slave;
mysql> change master to master_host='172.25.78.13',master_user='repl',master_password='Ting@666',MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql>  show slave status\G;

# 测试环境是否搭建成功

base2上创建数据库:

mysql> create database test;
mysql> use test;
mysql>  create table userlist(
    -> username varchar(15) not null,
    -> password varchar(25) not null);	
mysql> insert into userlist values ('user1','111');
mysql> insert into userlist values ('user1','222');
mysql>  select * from userlist;

在base3上（base2的slave端)查看数据是否同步:

mysql> use test;
mysql> select * from userlist;

在base4（base3的slave端)查看数据是否同步:

mysql> use test;	
Database changed
mysql> select * from userlist;

# 同步成功就说明一主二从环境搭建成功

# 现在模拟主master宕机

[root@base2 mysql]# systemctl stop mysqld

# 在从master（base3）上查看

mysql>  show slave status\G;

mysql> insert into userlist values ('user2','222');   # 在从master上插入数据
mysql> select * from userlist;

在slave（base4）上查看是否同步

mysql>  select * from userlist;   # 成功同步

重新开启主master（base2）

[root@base2 mysql]# systemctl start mysqld
[root@base2 mysql]# mysql -p
Enter password: 
mysql> use test;
Database changed
mysql>   select * from userlist;   # 不能同步从master上的数据

# 恢复主master地位

在从master上（base3）

mysql> stop slave;
mysql> start slave;
mysql> show slave status\G;