MySQL从库选项log-slave-updates未启用引发的异常
最近核查一个基于从库复制某张特定的表到另外一个主库调整，未配置log-slave-updates导致表无法正常同步。我们的配置文件中使用了
最近核查一个基于从库复制某张特定的表到另外一个主库调整，未配置log-slave-updates导致表无法正常同步。我们的配置文件中使用了replicate-rewrite-db参数以及replicate_wild_do_table参数。具体场景见下面的描述。
1、环境介绍及问题由来
DB1M(Master) ---> DB1S(Slave)
DB2M(Master) ---> DB2S(Slave)
现在的情形是需要将DB1M实例上的特定的表tbname上实时同步到DB2M实例
为减轻DB1M上的压力，我们将DB1S作为DB2M的主库，即最终的拓扑结果如下：
DB1M(tbname) ---> DB1S(tbname) ---> DB2M(tbname) --->DB2S(tbname)
在DB2M上配置了如下参数：
replicate-rewrite-db=DB1->DB2
replicate_wild_do_table=DB2.tbname
经过上述配置后，将tbname表从DB1M一致性导出后同步到DB2M实例的DB2上
配置完毕后，DB2M(Master) ---> DB2S(Slave)上的表tbname并没有彻底同步，总是存在数据丢失的问题
2、分析
a、DB1M(Master) ---> DB1S(Slave)表tbname无异常，排除DB1S做为DB2M主存在问题的可能性
b、DB1S(tbname) ---> DB2M(tbname)表tbname无异常，排除DB1S上启用的相关配置等
b、DB2M(Master) ---> DB2S(Slave)期间表tbname存在问题，也就是说应该是在DB2M上基于表tbname的dml日志并没有写入到binlog
c、在DB2M上基于表tbname的dml日志是来源于DB1S产生的relay log，同步到DB2M(Master)上无异常，一定可以在relay log找到tbname的相关操作
e、验证步骤c，再检查DB2M(Master)上是否有tbname的binlog，如果没有，一定是某个参数未设置或某个特定对的原因而导致在apply relay log时未添加到binlog
3、故障解决
通过上述的分析及验证，果然发现在DB2M(Master)上丢失了参数log-slave-updates
通过在DB2M(Master)添加作为从库的选项log-slave-updates后表tbname同步正常
4、有关log-slave-updates选项
log-slave-updates是一个全局非动态选项，其值为布尔型，即TRUE和FALSE。缺省为FALSE，，修改该参数需要重启实例。
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 [2039] option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates [2004] option without also starting the server with the --log-bin [2039] option, and would fail with an error; in MySQL 5.6, only a warning is generated. (Bug #44663) --log-slave-updates [2004] is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C
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 [2039] to enable binary logging, and B with the --log-slave-updates [2004] option so that updates received from A are logged by B to its binary log.
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
--------------------------------------分割线 --------------------------------------
本文永久更新链接地址：