MySQL数据库基于gtid实现一主多从高可用集群搭建

mysql的高可用集群

环境设定

server3	172.25.34.4	主master
server4	172.25.34.5	从master，slave
server6	172.25.34.6	slave

server3:

安装mysql

[root@server3 ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@server3 ~]# 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@server3 mysql]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_update=ON
log_bin=binlog

初始化

[root@server4 mysql]#  cat /var/log/mysqld.log  | grep password
[root@server4 mysql]# mysql_secure_installation 

进入数据库

[root@server3 mysql]# mysql -uroot -pWestos+001
mysql> grant replication slave on *.* to dd@'172.25.34.%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status 
    -> ;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |      689 |              |                  | 81ace76c-f792-11e9-a2a6-5254006dc583:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

server4:

执行安装及相关操作

编辑配置文件

[root@server4 mysql]# vi /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog

进入 数据库

[root@server4 mysql]# mysql -uroot -pWestos+001
mysql> change master to ##指定master
    -> master_host = '172.25.34.4',
    -> master_user = 'dd',
    -> master_password = 'Westos+001',
    -> master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.34.4
                  Master_User: dd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 689
               Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 896
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server5:

与之前在server4上的操作相同

编辑配置文件

[root@server5 ~]# vi /etc/my.cnf
server_id=3
gtid_mode =ON
enforce-gtid-consistency=true
log_bin = mysql-log
log_slave_updates=ON

进入数据库

[root@server5 mysql]# mysql -uroot -pWestos+001
mysql> change master to
    -> master_host = '172.25.34.4',
    -> master_user = 'dd',
    -> master_password = 'Westos+001',
    -> master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.34.4
                  Master_User: dd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 689
Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 896
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试环境是否搭建成功

server3上创建数据库:

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');

server 4，server5上查看数据是否同步:

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

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

现在模拟主master宕机

[root@server3 mysql]# systemctl stop mysqld

在从server4上查看

mysql>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.34.4
                  Master_User: dd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 689
Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 896
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: connecting
            Slave_SQL_Running: Yes

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

在server5上查看是否同步

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

重新开启主master

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

恢复主master地位

在从master上server4

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