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

阅读量：5977 次

发布时间：2019-06-20

本文共 9814 字，大约阅读时间需要 32 分钟。

让我们继续 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 = 3306default-character-set=utf8mb4socket = /data/mysql_db/mysql_seg_3306/mysql.sock[mysqld]datadir = /data/mysql_db/mysql_seg_3306basedir = /usr/local/mysql57tmpdir = /tmpsocket = /data/mysql_db/mysql_seg_3306/mysql.sockpid-file = /data/mysql_db/mysql_seg_3306/mysql.pidskip-external-locking = 1skip-name-resolve = 1port = 3306server_id = 833306default-storage-engine = InnoDBcharacter-set-server = utf8mb4default_password_lifetime=0auto_increment_offset = 1auto_increment_increment = 2#### log ####log_timestamps=systemlog_bin = /data/mysql_log/mysql_seg_3306/mysql-binlog_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.indexbinlog_format = rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-binrelay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.indexlog_error = /data/mysql_log/mysql_seg_3306/mysql-error.log#### replication ####log_slave_updates = 1replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%#### semi sync replication settings #####plugin_dir=/usr/local/mysql57/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000

Standby

[client]port = 3306default-character-set=utf8mb4socket = /data/mysql_db/mysql_seg_3306/mysql.sock[mysqld]datadir = /data/mysql_db/mysql_seg_3306basedir = /usr/local/mysql57tmpdir = /tmpsocket = /data/mysql_db/mysql_seg_3306/mysql.sockpid-file = /data/mysql_db/mysql_seg_3306/mysql.pidskip-external-locking = 1skip-name-resolve = 1port = 3306server_id = 723306default-storage-engine = InnoDBcharacter-set-server = utf8mb4default_password_lifetime=0auto_increment_offset = 2auto_increment_increment = 2#### log ####log_timestamps=systemlog_bin = /data/mysql_log/mysql_seg_3306/mysql-binlog_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.indexbinlog_format = rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-binrelay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.indexlog_error = /data/mysql_log/mysql_seg_3306/mysql-error.log#### replication ####log_slave_updates = 1replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%#### semi sync replication settings #####plugin_dir=/usr/local/mysql57/lib/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_master_timeout = 5000

Slave

[client]port = 3307default-character-set=utf8mb4socket = /data/mysql_db/mysql_seg_3307/mysql.sock[mysqld]datadir = /data/mysql_db/mysql_seg_3307basedir = /usr/local/mysql57tmpdir = /tmpsocket = /data/mysql_db/mysql_seg_3307/mysql.sockpid-file = /data/mysql_db/mysql_seg_3307/mysql.pidskip-external-locking = 1skip-name-resolve = 1port = 3307server_id = 833307read_only=1default-storage-engine = InnoDBcharacter-set-server = utf8mb4default_password_lifetime=0#### log ####log_timestamps=systemlog_bin = /data/mysql_log/mysql_seg_3307/mysql-binlog_bin_index = /data/mysql_log/mysql_seg_3307/mysql-bin.indexbinlog_format = rowrelay_log_recovery=ONrelay_log=/data/mysql_log/mysql_seg_3307/mysql-relay-binrelay_log_index=/data/mysql_log/mysql_seg_3307/mysql-relay-bin.indexlog_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/pluginplugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_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 TOMASTER_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: 0Master_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 插入数据只会有双数主键，这样即使同时在两个节点写数据，也不会有冲突