Semi-synchronous replication of MySQL _ MySQL

1. Semi-synchronous replication: by default, replication is asynchronous, that is, the client submits the transaction to the master database, after the master database writes the transaction to the storage engine and binlog, it immediately returns the transaction to the client to indicate that the transaction is successfully executed. If the transaction has not been copied to the slave database yet, if the master database 1> Semi-synchronous replication occurs:

By default, replication is asynchronous, that is, when the client commits a transaction to the master database, the master database writes the transaction to the storage engine and binlog, and then immediately returns the transaction to the client to indicate that the transaction is successfully executed. If the transaction has not been copied to the slave database yet, if the master database crashes or the server is down, the master/slave switchover will occur. when the client accesses the new master database, you cannot see the data you just submitted.

2> principles of semi-synchronous replication:

Mysql5.5 supports semi-synchronous replication through plug-ins. after the transaction committed by the client is executed in the master database, it will not be returned to the client immediately, instead, the transaction is returned to the client only after at least one slave database receives the transaction.

Semi-synchronous replication is a compromise between full synchronization and asynchronous synchronization. Compared with asynchronous replication, semi-synchronous replication improves data security-this transaction can exist in at least two places, at the same time, it also increases a certain latency (at least one TCP/IP round-trip time). it is best to use it in a low-latency network, such as a LAN.

3> installation configuration of semi-synchronous replication

The following conditions must be met:

MySQL5.5 or later

Has_dynamic_loading system variable is set to YES

Copy the configuration and it is running.

Semi-synchronous replication is implemented in the form of plug-ins. Therefore, copy the semisync_master * file to the plugins folder of the master database, and copy the semisync_slave * file to the plugins folder of all slave databases.

Next, execute the install plugin command on the master database and slave database to load the plug-in.

Mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so ";

Execute from database

Mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so ";

View the plug-in installation status:

Mysql> show plugins;

Enable the semi-sync plug-in for both the master and slave nodes:

Enable the semi-sync plug-in on the master database:

Mysql> set global rpl_semi_sync_master_enabled = 1;

Mysql> set global rpl_semi_sync_master_timeout = N; // The unit is milliseconds.

Enable the semi-sync plug-in on the slave database:

Mysql> set global rpl_semi_sync_slave_enabled = 1;

Rpl_semi_sync_master_timeout indicates that the master database is waiting for the corresponding supermarket time from the Slave Database. if the slave database is not received within the time, the replication mode is switched to the asynchronous mode.

Stop the slave IO thread and enable it again. In this way, the slave IO thread is re-connected to the master database and registered in the master database in semi-synchronous mode.

Run the following command on the slave database to restart the slave IO thread:

Mysql> stop slave IO_THREAD;

Mysql> start slave IO_THREAD;

Note:

Write global variables to the configuration file before mysql starts. To prevent the semi-sync from taking effect if you forget the settings.

Master database configuration file

[Mysqld]

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = 10000 #10 seconds

Slave database configuration file

[Mysqld]

Rpl_semi_sync_slave_enabled = 1