MySQL8配置双主双从

准备

一共四台机器：

192.168.100.211:3306 （master-a）
192.168.100.212:3306 （slave-a-1）
192.168.100.213:3306 （master-b）
192.168.100.214:3306 （slave-b-1）

Centos8安装MySQL8

配置

1.配置192.168.100.211master

1. 编辑my.cnf

   vim /etc/my.cnf
   

   加入以下内容：

   #设置服务id
   server-id   = 1
   #启动binlog日志
   log-bin=mysql-bin
   #设置binlog格式 row/mixed/statement
   binlog_format=statement
   #作为从数据库时，有写入操作也要更新二进制日志文件
   log-slave-updates
   

2. 重启服务：

   service mysql restart
   

3. 登录MySQL执行命令允许复制

   mysql -uroot -p
   

   执行：

   mysql> grant replication slave on *.* to 'root'@'%' with grant option;
   Query OK, 0 rows affected (0.01 sec)
   

   mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)
   

   记住Position 的值

   mysql> show master status;
   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 |      157 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)
   

2.配置192.168.100.212slave

1. 编辑my.cnf

   vim /etc/my.cnf
   

   加入以下内容：

   server-id   = 2
   relay-log=mysql-relay
   

2. 重启服务：

   service mysql restart
   

3. 登录MySQL执行复制主机命令

   mysql -uroot -p
   

   执行：

   注意这里的MASTER_LOG_FILE是上面show master status的FILE的值，MASTER_LOG_POS是上面Position 的值；

   mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.211',MASTER_USER='root',MASTER_PASSWORD='Ztx11497',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
   Query OK, 0 rows affected, 8 warnings (0.01 sec)
   

   如果Slave_IO_Running=No或者Slave_SQL_Running=No，要检查datadir下面的auto.cnf的UUID是否重复，修改一下。
   然后重启服务，reset master;stop slave;重新执行绑定；

   mysql> start slave;
   Query OK, 0 rows affected, 1 warning (0.01 sec)
   mysql> show slave status\G
   *************************** 1. row ***************************
              Slave_IO_State: 
                 Master_Host: 192.168.100.211
                 Master_User: root
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 157
              Relay_Log_File: mysql-relay.000001
               Relay_Log_Pos: 4
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             ...
   

3.配置192.168.100.213master

1. 编辑my.cnf

   vim /etc/my.cnf
   

   加入以下内容：

   #设置服务id
   server-id   = 3
   #启动binlog日志
   log-bin=mysql-bin
   #设置binlog格式 row/mixed/statement
   binlog_format=statement
   #作为从数据库时，有写入操作也要更新二进制日志文件
   log-slave-updates
   

2. 重启服务：

   service mysql restart
   

3. 登录MySQL执行命令允许复制

   mysql -uroot -p
   

   执行：

   mysql> grant replication slave on *.* to 'root'@'%' with grant option;
   Query OK, 0 rows affected (0.01 sec)
   

   mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)
   

   记住Position 的值

   mysql> show master status;
   +------------------+----------+--------------+------------------+-------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 |      157 |              |                  |                   |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)
   

4.配置192.168.100.214slave

1. 编辑my.cnf

   vim /etc/my.cnf
   

   加入以下内容：

   server-id   = 4
   relay-log=mysql-relay
   

2. 重启服务：

   service mysql restart
   

3. 登录MySQL执行复制主机命令

   mysql -uroot -p
   

   执行：

   注意这里的MASTER_LOG_FILE是上面show master status的FILE的值，MASTER_LOG_POS是上面Position 的值；

   mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.213',MASTER_USER='root',MASTER_PASSWORD='Ztx11497',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
   Query OK, 0 rows affected, 8 warnings (0.01 sec)
   

   mysql> start slave;
   Query OK, 0 rows affected, 1 warning (0.01 sec)
   mysql> show slave status\G
   *************************** 1. row ***************************
              Slave_IO_State: 
                 Master_Host: 192.168.100.213
                 Master_User: root
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000001
         Read_Master_Log_Pos: 157
              Relay_Log_File: mysql-relay.000001
               Relay_Log_Pos: 4
       Relay_Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             ...
   

5.配置master1与master2互为主备

1. 在master1 192.168.100.211上执行

   CHANGE MASTER TO MASTER_HOST='192.168.100.213',MASTER_USER='root',MASTER_PASSWORD='Ztx11497',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
   

   start slave;
   

2. 在master2 192.168.100.213上执行

   CHANGE MASTER TO MASTER_HOST='192.168.100.211',MASTER_USER='root',MASTER_PASSWORD='Ztx11497',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;
   

   start slave;