MySQL实现两主多从架构

梦里梦外; 2022-06-14 08:20 1149阅读 0赞

**1.实现目标**  
![ed3c78d6-2734-31d5-8567-dc47dbac48e1.png][]  
 目标清单：

1）Master（192.168.31.230）为正常运行环境下的主库，为两个Slave（192.168.31.231和192.168.31.232）提供“主-从”复制功能；

2）Master\_Backup(192.168.31.233)是Master的备份库，只要Master是正常的，它不对外提供服务。它与Master之间属于"主-主"复制关系，即自己既是主机，又是对方的从机；

3）同理，192.168.31.234和192.168.31.235为Slave\_Backup，分别为192.168.31.231和 192.168.31.232的备份库，只要Slave是正常的，对应的备份机不对外提供服务；

4）Slave在此架构中的目的是为了实现读写分离，对应用程序来说，Master只负责写，两个Slave只负责读。Slave的数据来源于Master的复制操作；

5）如果Master由于某种原因（例如：宕机和断电等）导致不能正常运行，则此时需要让Master\_Backup自动切换为新主机，而Slave和Slave\_Backup也能自动切换数据源到Master\_Backup；

6）同理，如果Slave由于某种原因（例如：宕机和断电等）导致不能正常运行，则此时需要让对应的Slave\_Backup自动切换为新从机；

7）无论是Master还是切换后的Master\_Backup，它们向客户端提供的连接地址应保持一致，如上图提供的VIP+Port，即192.168.31.201:3306，Slave和Slave\_Backup也应如此，对外提供的连接地址始终是192.168.31.202:3306和192.168.31.203:3306。

** 2.实现过程**

MySQL安装步骤不在此讲述。

**2.1实现Master-Master结构**

2.1.1修改Master和Master\_Backup配置文件，vi /etc/my.cnf

主要在\[mysqld\]内添加如下配置项：

Sql配置代码   ![收藏代码][icon_star.png]

1.  \# log文件名，必填  
2.  log-bin = mysql-bin  
3.  \# 服务器Id，必须唯一  
4.  server-id = 230  
5.  \# 不参与同步的数据库名，有多个则添加多个配置项  
6.  binlog-ignore-db = mysql  
7.  \# Master-Master结构必须的  
8.  log-slave-updates  
9.  slave-skip-errors = all  
10. sync\_binlog = 1  
11. read\_only = 0

2.1.2为复制请求方提供链接账号和密码

由于是Master-Master结构，因此需在双方终端中执行如下SQL命令：

Sql代码   ![收藏代码][icon_star.png]

1.  **GRANT** REPLICATION SLAVE **ON** \*.\* **to** 'slave'@'%' identified **by** 'slave123';

可在mysql实例的user表中查询到记录，重点关注Repl\_slave\_priv字段的值是否为Y，此账号（用户名：slave，密码：slave123）主要用于定位复制点

2.1.3在从机上指定Master数据源

1）在Master上执行

Sql代码   ![收藏代码][icon_star.png]

1.  SHOW MASTER STATUS;

得到的结果如下：  
![45abe84e-6a3b-31bd-a8e6-877c52096895.png][]

重点关注File和Position两个字段值

2）在Master\_Backup也执行上述步骤，由于是初始状态，得到的结果和上图一样；

3）在Master上执行如下SQL命令，填入Master\_Backup的host、链接账号和密码、File和Position值

Sql代码   ![收藏代码][icon_star.png]

1.  SLAVE STOP;  
2.  CHANGE MASTER **TO** MASTER\_HOST='192.168.31.233',MASTER\_USER='slave',MASTER\_PASSWORD='slave123',MASTER\_LOG\_FILE='mysql-bin.000001',MASTER\_LOG\_POS=107;  
3.  SLAVE START;

4）在Master\_Backup上执行如下SQL命令，填入Master的host、链接账号和密码、File和Position值

Sql代码   ![收藏代码][icon_star.png]

1.  SLAVE STOP;  
2.  CHANGE MASTER **TO** MASTER\_HOST='192.168.31.230',MASTER\_USER='slave',MASTER\_PASSWORD='slave123',MASTER\_LOG\_FILE='mysql-bin.000001',MASTER\_LOG\_POS=107;  
3.  SLAVE START;

5）重启Master和Master\_Backup

2.1.4测试

1）当Master和Master\_Backup都正常运行时，在任意一端更新数据后都会同步到另一段；

2）当Master处于不可运行时，在Master\_Backup更新数据后重启Master，这时在Master上可得到最新的数据；

3）当Master\_Backup处于不可运行时，在Maste更新数据后重启Master\_Backup，这时在Master\_Backup上可得到最新的数据。

**2.2实现Master-Slave结构**

2.2.1实施过程  
     将2.1.1和2.1.3的过程在所有Slave上操作一遍即可，需要注意配置文件中server-id一定要唯一，还有在执行CHANGE MASTER TO命令时，MASTER\_HOST为192.168.31.230

Sql代码   ![收藏代码][icon_star.png]

2.2.2测试

1）当Master和Master\_Backup都正常运行时，在任意一端更新数据后都会同步到两个Slave上；

2）当Master处于正常运行时，在此端更新数据后都会同步到两个Slave上，而无论Master\_Backup是否正常；

3）当Master处于不可运行时，Master\_Backup通过Monitor（Keepalived）成为接管者，在Master\_Backup更新数据后都会同步到所有Slave上，并且重启Master后，最新数据也会同步到此端。

可事与愿违，在第3）种场景下，Master\_Backup不会将数据同步给Slave，即使后来在Slave上将MASTER\_HOST指定为Keepalived提供的VIP（192.168.31.201）也无济于事：

Sql代码   ![收藏代码][icon_star.png]

1.  SLAVE STOP;  
2.  CHANGE MASTER **TO** MASTER\_HOST='192.168.31.201',MASTER\_USER='slave',MASTER\_PASSWORD='slave123',MASTER\_LOG\_FILE='mysql-bin.000001',MASTER\_LOG\_POS=107;  
3.  SLAVE START;

在Slave上执行SHOW SLAVE STATUS;

得出如下结果：  
![26952732-f1b4-3e7d-89e9-c6da35697328.png][]  
     究其原因，如上图所示，Master\_Server\_Id为230，仍然指向的是已经处于不可运行的Master，而预期结果是希望它能自动的更新定位到Master\_Backup(233)上，达到自动切换的目的。

没办法，只有自己执行CHANGE MASTER TO...手动定位了。我草...，一不注意就会定位错误，造成数据丢失的问题，而且也不满足快速响应容灾切换的目的。

**3.最终方案**

最终方案将选择mysql-mmm结合半同步机制来实现容灾自动切换。

**3.1在master(230和233)上安装semisync master并设置**

Sql代码   ![收藏代码][icon_star.png]

1.  INSTALL PLUGIN rpl\_semi\_sync\_master SONAME 'semisync\_master.so';  
2.  INSTALL PLUGIN rpl\_semi\_sync\_slave SONAME 'semisync\_slave.so';  
3.  **SET** **GLOBAL** rpl\_semi\_sync\_master\_enabled = 1;  
4.  **SET** **GLOBAL** rpl\_semi\_sync\_slave\_enabled = 1;

vi /ect/my.cnf后加入如下配置：

配置代码   ![收藏代码][icon_star.png]

1.  rpl\_semi\_sync\_master\_enabled = 1  
2.  rpl\_semi\_sync\_slave\_enabled = 1

**3.2在slave(231、232、234和235)上安装slave插件并设置**

Sql代码   ![收藏代码][icon_star.png]

1.  INSTALL PLUGIN rpl\_semi\_sync\_slave SONAME 'semisync\_slave.so';  
2.  **SET** **GLOBAL** rpl\_semi\_sync\_slave\_enabled = 1;

vi /ect/my.cnf后加入如下配置：

配置代码   ![收藏代码][icon_star.png]

1.  rpl\_semi\_sync\_slave\_enabled = 1

**3.3所有mysql实例停止slave并开启slave，使半同步机制生效**

Sql代码   ![收藏代码][icon_star.png]

1.  stop slave;  
2.  start slave;

**3.4查看semisync状态**

Sql代码   ![收藏代码][icon_star.png]

1.  show status like '%emi%';

重点关注:

1）Rpl\_semi\_sync\_master\_clients：与当前master建立半同步连接的客户端数；

2）Rpl\_semi\_sync\_master\_status：作为半同步master端的就绪状态（ON：就绪，OFF：未就绪）

3）Rpl\_semi\_sync\_slave\_status：作为半同步slave端的就绪状态（ON：就绪，OFF：未就绪）

**3.5安装mysql-mmm**

3.5.1新增一台专门用于监控mysql的服务器（mysql\_monitor），IP为192.168.31.250

3.5.2在mysql\_monitor、master、master\_backup、slave和slave\_backup上安装epel网络源

Linux命令行代码   ![收藏代码][icon_star.png]

1.  yum install http://mirrors.hustunique.com/epel//6/x86\_64/epel-release-6\-8.noarch.rpm

3.5.3在mysql\_monitor上安装mysql-mmm-monitor

Linux命令行代码   ![收藏代码][icon_star.png]

1.  yum -y install mysql-mmm-monitor

3.5.4在master、master\_backup、slave和slave\_backup上安装和配置

1）安装mysql-mmm-agent

Linux命令行代码   ![收藏代码][icon_star.png]

1.  yum -y install mysql-mmm-agent

2）授权monitor访问

Sql代码   ![收藏代码][icon_star.png]

1.  **GRANT** REPLICATION CLIENT **ON** \*.\* **TO** 'mmm\_monitor'@'192.168.31.%' IDENTIFIED **BY** 'monitor';       
2.  **GRANT** SUPER,REPLICATION CLIENT, PROCESS **ON** \*.\* **TO** 'mmm\_agent'@'192.168.31.%' IDENTIFIED **BY**'agent';

3）编辑`mmm_agent.conf`配置文件

vi `/etc/mysql-mmm/mmm_agent.conf`

Mmm\_agent.conf配置文件代码   ![收藏代码][icon_star.png]

1.  \# 包含公用配置文件  
2.  include mmm\_common.conf   
3.  \# 对应<code><span style="color: \#008000;"><span style="color: \#000000;">mmm\_common.conf中定义的某个host名称</span></span></code>  
4.  this db1  
5.  \# 设置成1时，将打印日志到前台，按ctrl+c将结束进程  
6.  debug 0  
7.  max\_kill\_retries 1

``4）编辑`mmm_common.conf`配置文件

vi `/etc/mysql-mmm/``mmm_common.conf`

Mmm\_common.conf代码   ![收藏代码][icon_star.png]

1.  active\_master\_role      writer  
2.    
3.  <host default>  
4.      \# 对应当前主机的网络接口名  
5.      cluster\_interface       eth2  
6.      pid\_path                /var/run/mysql-mmm/mmm\_agentd.pid  
7.      bin\_path                /usr/libexec/mysql-mmm/  
8.      mysql\_port              3306  
9.      agent\_port              9989  
10.     \# 对应GRANT REPLICATION SLAVE ON语句创建的账号和密码  
11.     replication\_user        slave  
12.     replication\_password    slave123  
13.     \# GRANT SUPER,REPLICATION CLIENT, PROCESS ON语句创建的账号和密码  
14.     agent\_user              mmm\_agent  
15.     agent\_password          agent  
16. </host>  
17.   
18. \# master的配置  
19. \# 其中host后面的值定义的是某台数据库服务的别名，一般就用服务器的主机名即可  
20. <host db1>  
21.     ip      192.168.31.230  
22.     mode    master  
23.     \# db1的master对等点  
24.     peer    db2  
25. </host>  
26.   
27. \# master\_backup的配置  
28. <host db2>  
29.     ip      192.168.31.233  
30.     mode    master  
31.      \# db2的master对等点  
32.     peer    db1  
33. </host>  
34.   
35. \# slave的配置  
36. <host db3>  
37.      ip     192.168.31.231  
38.      mode   slave  
39. </host>  
40.   
41. \# slave的配置  
42. <host db4>  
43.      ip     192.168.31.232  
44.      mode   slave  
45. </host>  
46.   
47. \# slave\_backup的配置  
48. <host db5>  
49.      ip     192.168.31.234  
50.      mode   slave  
51. </host>  
52.   
53. \# slave\_backup的配置  
54. <host db6>  
55.      ip     192.168.31.235  
56.      mode   slave  
57. </host>  
58.   
59. \# 定义writer角色，即架构中的master和master\_backup  
60. \# ips为writer对外提供的vip  
61. <role writer>  
62.     hosts   db1, db2  
63.     ips     192.168.31.201  
64.     mode    exclusive  
65. </role>  
66.   
67. \# 定义reader角色，即架构中的两个slave和两个slave\_backup  
68. \# ips为reader对外提供的vip  
69. <role reader>  
70.     hosts    db3, db4, db5, db6  
71.     ips      192.168.31.202, 192.168.31.203  
72.     mode     balanced  
73. </role>

注意，需要将此配置文件复制到mysql\_monitor的同名目录下

3.5.5在master、master\_backup、slave和slave\_backup上启动mmm agent服务，并设置为开机服务

执行如下启动命令：

Linux命令行代码   ![收藏代码][icon_star.png]

1.  /etc/init.d/mysql-mmm-agent start

如果出现如下示例信息：  
![eb640ac4-bd82-3e9e-908e-b30abbaa7545.png][]

则说明配置成功

vi /etc/rc.d/rc.local后，将上述命令行添加到mysql启动命令的下面

3.5.6编辑mysql\_monitor上的配置文件mmm\_mon.conf

vi /etc/mysql-mmm/mmm\_mon.conf

Mmm-mon.conf代码   ![收藏代码][icon_star.png]

1.  include mmm\_common.conf  
2.    
3.  <monitor>  
4.      \# 本机IP  
5.      ip                  192.168.31.250  
6.      port                9988  
7.      pid\_path            /var/run/mysql-mmm/mmm\_mond.pid  
8.      bin\_path            /usr/libexec/mysql-mmm  
9.      status\_path         /var/lib/mysql-mmm/mmm\_mond.status  
10.     \# 所有MySQL服务器的IP  
11.     ping\_ips            192.168.31.230, 192.168.31.231, 192.168.31.232, 192.168.31.233, 192.168.31.234, 192.168.31.235  
12.     auto\_set\_online     0  
13. </monitor>  
14.   
15. <host default>  
16.     \# GRANT REPLICATION CLIENT ON语句创建的账号和密码  
17.     monitor\_user        mmm\_monitor  
18.     monitor\_password    monitor  
19. </host>  
20.   
21. <check mysql>  
22.     \# 每5秒检查一次  
23.     check\_period        5  
24.     trap\_period         10  
25.     \# 检查超时秒数  
26.     timeout             2  
27.     restart\_after       10000  
28.     max\_backlog         60  
29. </check>  
30. <code>\# 设置为1,开启调试模式，打印日志到前台，ctrl+c将结束进程，对于调试有帮助</code>  
31. debug 0

关于mmm\_agent.conf、mmm\_common.conf、mmm\_mon.conf和mmm\_mon\_log.conf的具体内容，可以参考 [http://blog.chinaunix.net/uid-16844903-id-3152138.html][http_blog.chinaunix.net_uid-16844903-id-3152138.html]

3.5.7在上mysql\_monitor开启mmm monitor监控，并设置为开机服务

执行如下启动命令：

Linux命令行代码   ![收藏代码][icon_star.png]

1.  /etc/init.d/mysql-mmm-monitor start

如果出现如下示例信息：  
![3987b32a-ce49-3d9c-8e70-00455c9761a4.png][]

则说明配置成功

vi /etc/rc.d/rc.local后，将上述命令行添加于此

3.5.8重启所有服务器系统后测试

1）在mysql\_monitor上执行如下命令，查看各监控机的运行状态

Linux命令行代码   ![收藏代码][icon_star.png]

1.  mmm\_control show

得到如下示例结果：  
![f6667d0f-81c3-380f-841b-b26b348a54fc.png][]  
     理想情况下，所有的MySQL都应该处于“ONLINE”状态，这里的结果与第1节中的目标清单有一定误差，因为202和203这个“读”IP浮动到slave正好与预期结果相反，这和MySQL的启动顺序有关，而且最重要的是在mmm\_common.conf中没有作严格的控制，对于两个slave(231和232)和两个slave\_backup(234和235)来说，得到202和203这两个"读"IP的机会是均等的。

现在重新执行2.2.2节的测试场景3），停掉maste后稍等片刻，再执行mmm\_control show得到如下结果：  
![b3c382b9-43ad-329a-a7ff-302ad45999de.png][]  
     原来的master已处于不可用（HARD\_OFFLINE）状态，master\_backup(233)成为了新的master，此时再在各slave和slave\_backup上执行SHOW SLAVE STATUS;  
![013f6cfd-35ab-350b-a29d-b53a1e51e0a0.png][]  
     可以看出，不用再自己手动定位，就可以让Master\_Host和Master\_Server\_Id自动定位到当前处于“ONLINE”状态的Master\_Backup上。

1）在Master\_Backup上更新数据，在所有的slave和slave\_backup上可以很快的查询到最新的数据；

2）重启Master并稍等片刻后，在这台主机上也可以查询到最新的数据，此时writer权限仍在Master\_Backup上。

**4.最终架构**  
![7cc0306e-7f4f-38cd-86e4-3fa7bedbcfb0.png][]

**5.总结**

接3.5.8测试场景2），虽然Master已恢复了“ONLINE”状态，但整个架构是“非抢占式”的，writer权限仍在Master\_Backup上，所以在Master上更新数据不会同步到其它主机上。因此，在实际使用过程中，除进行数据库维护用真实IP访问之外，其余操作都只使用VIP来进行，201为wirter，202和203为reader。

从第4节的架构图中看出，Mmm\_Mnitor存在单点问题，当Mmm\_Mnitor处于不可运行时，整个主从结构将不能正常运行。可以部署多个监控，结合Keepalived来扩展。

"主-主"和“主-从”架构通常有两个目的：第一是为了进行容灾备份，当数据库发生不可预知的错误导致不可运行甚至丢失数据时，其余的备份机可以继续对外提供数据读写服务。第二是为了分摊数据库压力实现"读写分离"。

读写分离会带来数据延迟达到的问题。假设有一个业务，当数据插入到数据库后要立即又从数据库中将此数据查询出来，因此当数据插入到Master库后，由于网络的延迟，Slave库中不会立即得到这条最新的数据，此时应用程序查询Slave库将得不到预期结果。

通常有如下两个方案解决上述问题：

1）将此类业务控制在一个数据库事务中进行，读写都在master中进行。因此，在mmm\_common.conf``配置文件中，还需要将db1和db2同时配置在reader组：

Mmm\_common.conf配置摘要代码   ![收藏代码][icon_star.png]

1.  <role writer>  
2.      hosts   db1, db4  
3.      ips     192.168.31.201  
4.      mode    exclusive  
5.  </role>  
6.    
7.  <role reader>  
8.      hosts    db1, db4, db2, db3, db5, db6   
9.      ips      192.168.31.202, 192.168.31.203  
10.     mode     balanced  
11. </role>

2）使用Zookeeper来解决分布式一致性问题，后续单独再来介绍。

[ed3c78d6-2734-31d5-8567-dc47dbac48e1.png]: /images/20220614/9933f1bc71a84ac79bdd9872d8267155.png
[icon_star.png]: /images/20220614/b8a0fb169dff4278b6622708b8d3d64e.png
[45abe84e-6a3b-31bd-a8e6-877c52096895.png]: /images/20220614/a42ebc294a064f68b8b9e5401fa719fe.png
[26952732-f1b4-3e7d-89e9-c6da35697328.png]: /images/20220614/c6b1c82eca754ceeaa86d1a749881faf.png
[eb640ac4-bd82-3e9e-908e-b30abbaa7545.png]: /images/20220614/7df81ae14cf7490fa2628d82ba491c48.png
[http_blog.chinaunix.net_uid-16844903-id-3152138.html]: http://blog.chinaunix.net/uid-16844903-id-3152138.html
[3987b32a-ce49-3d9c-8e70-00455c9761a4.png]: /images/20220614/e17c1f5d39b74f6c98b8b4cd1c72c235.png
[f6667d0f-81c3-380f-841b-b26b348a54fc.png]: /images/20220614/583e75bd062f46298f585a3831c395d1.png
[b3c382b9-43ad-329a-a7ff-302ad45999de.png]: /images/20220614/e2f6dc633bc74af2a094991fd794a7b6.png
[013f6cfd-35ab-350b-a29d-b53a1e51e0a0.png]: /images/20220614/1511844345d64b1ebf55e7d73de825dc.png
[7cc0306e-7f4f-38cd-86e4-3fa7bedbcfb0.png]: /images/20220614/986d0ecf15a44659a2e72b52e1b2fcd4.png