Important parameter log_slave_updates of master-Slave synchronization Setup

Description: Recently deployed MySQL cluster environment, details as follows M01 and M02 primary replication, M01 and R01 mainly from replication; During the test, the following problems were discovered:

1, M01 and M02 primary replication is not a problem (from the M01 write data can be synchronized to the M02, from the M02 write data can be synchronized to M01);

2, when the master-slave synchronization, when written from the M01, the data can be written to R01;

3, when writing from the M02, the data can not be written to R01;

Cause of the problem: the Log_slave_updates parameter has a status of no

MySQL's official website explains as follows:

Normally, a slave does not log to it own binary log any updates this are received from a master server. This option tells the "Slave to log" updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also is started with the--log-bin option to enable binary. 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 the MySQL 5.5, only a warning is generated. (Bug #44663)--log-slave-updates is used the want to chain replication servers. For example, your might want to set up replication servers using this arrangement: A-> B-> C Here, A serves as the "Master for" Slave B, and b serves as the master for the slave C. For this to work, B must is both a master and a slave. You are must start both A and B with--log-bin to enable binary logging, and B with the--log-slave-updates option so that upd Ates received from A are logged by B to its binary log.

A) When synchronizing data from M02, the log_slave_updates parameter is used to control whether M01 M01 all operations to binary log, and MySQL is closed by default;

b The update of the R01 data needs to be updated by reading to the M01 binary log, when M01 is not writing binary log, so when the data is written from M02, R01 is not updated.

How to solve the problem:

Log_slave_updates: The default value is off;

Dynamic Variable:no

Processing methods: Modify the/ETC/MY.CNF, add a row of Log_slave_updates=1, restart the database after it;

Summary: After setting this parameter, the database architecture can be set into M01 and M02 as main synchronization, R01 through M01 for master-slave synchronization;

Applied in the write operation M02 above, read the operation R01 above (if read a lot of words, you can set up more than a read-only database M01 above), when the M02 failure, the system's write operation automatically migrated to the M01 above. This architecture can basically guarantee the application requirements of most companies;