MySQL slave database option log-slave-updates not enabled exception

MySQL slave database option log-slave-updates not enabled exception

Recently, it was verified that a specific table was copied from the slave database to another master database for adjustment. If log-slave-updates is not configured, the table cannot be synchronized normally. The replicate-rewrite-db parameter and the replicate_wild_do_table parameter are used in our configuration file. For specific scenarios, see the description below.

1. Environment Introduction and problem Origin
DB1M (Master) ---> DB1S (Slave)
DB2M (Master) ---> DB2S (Slave)
The current situation is that you need to synchronize the specified table tbname on the DB1M instance to the DB2M instance in real time.
To reduce the pressure on DB1M, we use DB1S as the master database of DB2M. The final topology result is as follows:
DB1M (tbname) ---> DB1S (tbname) ---> DB2M (tbname) ---> DB2S (tbname)
The following parameters are configured on DB2M:
Replicate-rewrite-db = DB1-> DB2
Replicate_wild_do_table = DB2.tbname
After the preceding configuration, the tbname table is exported from DB1M and synchronized to DB2 on the DB2M instance.
After the configuration is complete, the table tbname on DB2M (Master) ---> DB2S (Slave) is not completely synchronized, and there is always a problem of data loss.

2. Analysis
A. DB1M (Master) ---> DB1S (Slave) Table tbname has no exception, and the possibility of DB1S being the DB2M Master being ruled out
B. DB1S (tbname) ---> the tbname IN THE DB2M (tbname) table is not abnormal, and related configurations enabled on DB1S are excluded.
B. There is a problem with the table tbname during DB2M (Master) ---> DB2S (Slave), that is, the dml logs based on the table tbname on DB2M are not written to binlog.
C. dml logs based on table tbname on DB2M are the relay logs generated by DB1S and synchronized to DB2M (Master) without exceptions. operations related to tbname can be found in the relay log.
E. Verify Step c and check whether the binlog of tbname exists on the DB2M (Master). If not, it must be because a parameter is not set or a specific pair causes the relay log to be not added to the binlog when applying the relay log.

3. troubleshooting
Through the above analysis and verification, we found that the log-slave-updates parameter is missing on the DB2M (Master ).
After you add the log-slave-updates option as the slave database to the DB2M (Master), the table tbname synchronization is normal.

4. log-slave-updates options
Log-slave-updates is a global non-dynamic option with a Boolean value, that is, TRUE and FALSE. The default value is FALSE. to modify this parameter, restart the instance.

Normally, a slave does not log to its own binary log any updates that are stored ed from a master server. this option tells the slave to log the updates generated med 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 wocould not start when using the -- log-slave-updates [2004] option without also starting the server with the -- log-bin [2039] option, and wowould 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 has ed from A are logged by B to its binary log.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: