让我们继续 MySQL 集群搭建之旅，大部分 MySQL 的集群都是采用主主从的经典模式，这也是部署高可用集群的基础。从上文我们已经知道如果搭建 MySQL 的主备模式，如果没看过的可以了解一下。
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 = 5000Standby
[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 = 5000Slave
[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 节点手动设置只读(方便切换)
所有操作完成后，架构如下
到此，主主备架构搭建完成
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。