MySQL's semi-synchronous replication

1> the occurrence of semi-synchronous replication:

By default, replication is asynchronous, that is, the client commits the transaction to the main library, and the main library writes the transaction to the storage engine and Binlog and returns it to the client immediately telling them that the transaction executed successfully. If the transaction is not yet replicated to the slave library at this point, if the main library crashes at this time or the server goes down, it will cause a master-slave switchover, when the client accesses the newly elected main library, it will not see the data just submitted.

2> the principle of semi-synchronous replication:

mysql5.5 begins to support semi-synchronous replication through plug-ins, and the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one of the transactions received from the library to be returned to the client.

Semi-synchronous replication is a compromise between full-sync and asynchronous synchronization. Compared to asynchronous replication, semi-synchronous replication improves the security of the data---can guarantee that the transaction exists at least two places, and also increases the latency (at least a TCP/IP round-trip time), preferably in a low-latency network, such as a LAN.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/73/1A/wKiom1X04pWSFXQaAAEPJfLLJ6A295.jpg "title=" 1.png " alt= "Wkiom1x04pwsfxqaaaepjfllj6a295.jpg"/>

3> installation configuration for semi-synchronous replication

The following conditions must be met:

MySQL5.5 or above version

has_dynamic_loading system variable set to Yes

The replication is already configured and running.

Semi-synchronous replication is implemented in the form of plug-ins. So the semisync_master* file is copied to the Plugins folder in the main library, and the semisync_slave* file is copied to all the plugins folders from the library.

Next, execute the Install plugin command loading plugin in the main library and from the library respectively.

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME "semisync_master.so";

Executing from the Library

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME "semisync_slave.so";

To view the installation of the plugin:

Mysql>show PLUGINS;

The master and slave both turn on the semi-synchronous plugin:

Open the semi-synchronous plugin on the main library:

Mysql>set GLOBAL rpl_semi_sync_master_enabled = 1;

Mysql>set GLOBAL rpl_semi_sync_master_timeout = n;//Unit is milliseconds

To turn on the semi-synchronous plugin from the library:

Mysql>set GLOBAL rpl_semi_sync_slave_enabled = 1;

Rpl_semi_sync_master_timeout indicates that the main library waits for the corresponding supermarket time from the library, and if not received from the library in time, replication switches to asynchronous mode.

Stop the slave IO thread, and then turn the thread back on. So that the slave IO thread is newly connected to the main library and registered in the Master library in a semi-synchronous manner.

Restart the slave IO thread by executing the following command from the library:

Mysql>stop SLAVE Io_thread;

Mysql>start SLAVE Io_thread;

Note the point:

Writes global variables to the configuration file before MySQL starts. To avoid forgetting the settings so that the semi-sync does not take effect.

Main Library configuration file

[Mysqld]

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_master_timeout=10000 #10秒

From the library configuration file

[Mysqld]

rpl_semi_sync_slave_enabled = 1

MySQL's semi-synchronous replication