主从同步设置的重要参数log_slave_updates

冷不防 2022-07-27 16:45 160阅读 0赞

说明：最近部署了mysql的集群环境，详细如下M01和M02为主主复制，M01和R01为主从复制；在测试的过程中发现了以下问题：

1、M01和M02的主主复制是没有问题的（从M01写入数据能同步到M02，从M02写入数据能够同步到M01);

2、主从同步的时候，当从M01写入的时候，数据可以写入到R01；

3、当从M02写入的时候，数据就不能写入到R01；

问题的原因：log\_slave\_updates参数的状态为NO

mysql的官网说明如下：

<table style="word-wrap:break-word; border-collapse:collapse"> 
 <tbody style="word-wrap:break-word"> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0px; border:0.75pt solid rgb(187,187,187)"> <p style="word-wrap:break-word; margin-top:5px; margin-bottom:5px; padding-top:0px; padding-bottom:0px"> <span style="word-wrap:break-word; font-family:宋体; font-size:12pt">Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.5, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:</span></p> <p style="word-wrap:break-word; margin-top:5px; margin-bottom:5px; padding-top:0px; padding-bottom:0px"> <span style="word-wrap:break-word; font-family:宋体; font-size:12pt">A -&gt; B -&gt; C</span></p> <p style="word-wrap:break-word; margin-top:5px; margin-bottom:5px; padding-top:0px; padding-bottom:0px"> &nbsp; &nbsp;</p> <p style="word-wrap:break-word; margin-top:5px; margin-bottom:5px; padding-top:0px; padding-bottom:0px"> <span style="word-wrap:break-word; font-family:宋体; font-size:12pt">Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.&nbsp;</span></p> </td> 
  </tr> 
 </tbody> 
</table>

a) M01同步从M02同步数据过来的时候，log\_slave\_updates参数用来控制M01是否把所有的操作写入到binary log，默认的情况下mysql是关闭的;

b) R01数据的更新需要通过读取到M01的binary log才能进行更新，这个时候M01是没有写binary log的，所以当数据从M02写入的时候，R01也就没有更新了。。

问题的解决方法：

![12679300_14150174872iDG.png][]

log\_slave\_updates：默认值为OFF;

Dynamic Variable：NO

处理方法：修改/etc/my.cnf，增加一行log\_slave\_updates=1，重启数据库后就可以了；

总结：设置完该参数后，数据库的架构就可以设置成M01和M02为主主同步，R01通过M01进行主从同步；

应用的写操作中M02上面进行，读操作中R01上面进行（如果读操作很多的话，可以在M01上面架设多台只读数据库），当M02发生故障后，系统的写操作自动迁移到M01上面。这种架构基本可以保证大部分公司的应用需求；

[12679300_14150174872iDG.png]: http://blog.itpub.net/attachment/201411/3/12679300_14150174872iDG.png