MySQL集群搭建(2)-主主从模式

1 环境准备

上次我们搭建了主备架构，如下所示

这次我们的搭建目标是

具体配置信息

IP	系统	端口	MySQL版本	节点	读写	说明
192.168.41.83	Centos6.8	3306	5.7.20	Master	读写	主节点
192.168.41.72	Centos6.8	3306	5.7.20	Standby	只读,可切换为读写	备节点，允许升级为主节点
192.168.41.83	Centos6.8	3307	5.7.20	Slave	只读	从节点

环境我已经准备好了, 如下图所示，如果不知道如何安装 MySQL 和搭建主备可以参考以前的文章

参考配置

Master

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

auto_increment_offset = 1
auto_increment_increment = 2

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
log_slave_updates = 1
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Standby

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

auto_increment_offset = 2
auto_increment_increment = 2

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
log_slave_updates = 1
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Slave

[client]
port = 3307
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3307/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3307
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3307/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3307/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3307
server_id = 833307
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3307/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3307/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3307/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3307/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3307/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

配置解释

• auto_increment_offset 自增字段起始值，设置为 2 表示自增字段从 2 开始算
• auto_increment_increment 自增字段步增数值，设置为 2 表示每次递增 2，根据主节点设置，自增字段的值可能是这样的: 1,3,5,7...
• log_slave_updates 将复制事件写入 binlog, 一台服务器既做主库又做从库此选项必须要开启, 这里在 Master 和 Standby 开启

说明一下，设置 auto_increment_offset, auto_increment_increment 是为了防止两个节点双写时出现主键冲突

2 搭建主主

现在环境已经准备好了，当前部署状态为，Standby 向 Master 同步，Slave 空跑, 现在开始搭建双主架构

2.1 记录 Standy 节点二进制位置

要实现 Master 向 Slave 同步，说简单点就是反着搭建主备同步。我们先去备节点记录当前二进制日志状态

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

正常情况下，主节点一直有数据写入，而我们又设置了 log_slave_updates，有同学会疑惑，在我们记录当前二进制位置后，如果有数据同步过来，二进制位置变化了，而我们从旧数据开始同步，会不会发生数据冲突。其实不会，双主模式下，数据库会帮我们处理这种情况，现在我们模拟这种情况，在主节点插入一些数据

db83-3306>>insert into test1 values (0, 'echoQMC', 24);
Query OK, 1 row affected (0.01 sec)

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
|  7 | echoQMC |  24 |
+----+---------+-----+
6 rows in set (0.00 sec)

插入数据后，我们再看 Standby 二进制位置

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

2.2 在主节点开启同步

根据我们第一次获取到的二进制日志位置，在 Master 节点执行以下命令便可以开启同步

-- 重置复制
-- reset slave;

-- 同步配置
CHANGE MASTER TO
MASTER_HOST='192.168.41.72',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=154;

-- 开启同步
start slave

具体执行过程如下

db83-3306>>CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.72',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000005',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

db83-3306>>start slave;
Query OK, 0 rows affected (0.00 sec)

查看同步状态

db83-3306>>show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.72
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 424
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: information_schema.%,performance_schema.%,sys.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 424
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 723306
                  Master_UUID: f9610603-e99f-11e8-b980-90b11c1a653a
             Master_Info_File: /data/mysql_db/mysql_seg_3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

可以发现同步正常，没有数据冲突的情况，现在测试一下在 Standby 写数据会不会同步到 Master

db72-3306>>insert into test1 values (0, 'Keeming', 24);
Query OK, 1 row affected (0.04 sec)

在 Master 节点查看数据

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
|  7 | echoQMC |  24 |
|  8 | Keeming |  24 |
+----+---------+-----+
7 rows in set (0.00 sec)

数据被同步过来，双主架构已经搭建成功，当前架构如下

仔细观察主键的递增情况，我们发现在 Master 节点插入 echoQMC 这条数据，主键从 5 跳到 7，在 Standby 节点插入 Keeming 这条数据，主键从 7 跳到 8，说明主键递增是不一样的，在 Master 插入数据只会有单数主键， 在 Standby 插入数据只会有双数主键，这样即使同时在两个节点写数据，也不会有冲突

2.3 从库开启同步

主主架构已经搭建成功，现在我们再增加一个 Slave 节点，向 Master 同步，这个过程就是简单的主备搭建，具体过程就不赘述了。

在使用主主从架构下，为了数据一致性，我们一般只允许一个节点有写操作，其他节点都设为只读，所以在 Slave 配置文件里面加上了 read_only, Standby 节点手动设置只读(方便切换)

所有操作完成后，架构如下

到此，主主备架构搭建完成