让我们继续 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 = 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 节点手动设置只读(方便切换)
所有操作完成后,架构如下
到此,主主备架构搭建完成
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。