Important parameter log_slave_updates for MySQL master-slave Synchronization

Important parameter log_slave_updates for MySQL master-slave Synchronization

Note: The MySQL cluster environment has been deployed recently. For details, refer to M01 and M02 for Master replication, while M01 and R01 for Master replication. The following problems were found during the test:

1. There is no problem with master-master replication of M01 and M02 (data written from M01 can be synchronized to M02, and data written from M02 can be synchronized to M01 );

2. Data can be written from M01 to R01 During master-slave synchronization;

3. When writing data from M02, data cannot be written to R01;

Cause: the status of the log_slave_updates parameter is NO.

The official MySQL website is described as follows:

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 option to enable binary logging. prior to MySQL 5.5, the server wocould not start when using the -- log-slave-updates option without also starting the server with the -- log-bin option, and wowould 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:

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 to enable binary logging, and B with the -- log-slave-updates option so that updates has ed from A are logged by B to its binary log.

A) When M01 synchronizes data from M02, The log_slave_updates parameter is used to control whether M01 writes all operations to binary log. mysql is disabled by default;

B) R01 data updates can be performed only by reading the binary log of M01. At this time, M01 does not write binary log. Therefore, when data is written from M02, r01 is not updated ..

Solution:

Log_slave_updates: The default value is OFF;

Dynamic Variable: NO

Solution: Modify/etc/my. cnf and add log_slave_updates = 1. Restart the database;

Summary: After this parameter is set, the database architecture can be set to M01 and M02 for master-slave synchronization, and R01 for master-slave Synchronization Through M01;

In the write operation of the application, M02 is performed. In the read operation, R01 is performed. (if there are many read operations, multiple read-only databases can be set up on M01.) When M02 fails, the system's write operations are automatically migrated to M01. This architecture can basically ensure the application requirements of most companies;

 

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

Install MySQL in Ubuntu 14.04

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

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

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

This article permanently updates the link address: