Semi-synchronous replication of MySQL 5.5

In the premise of ensuring database performance, how to ensure the consistency of the data?

In MySQL version 5.5, asynchronous replication is supported and semi synchronous replication is supported.

1, when slave connect master, it will indicate whether it supports semi synchronous replication.

2. When Master enables semisynchronous Replication. And at least one of the slave is enabled, the master end of the transaction is blocked, and waits until the transaction waits for either of the slave to accept the transaction, or to wait longer to commit.

3, the slave end reply to master information based on the slave transaction has been written to the Relay-log and has been painted to disk.

4. If the master waits for a timeout, and no slave gives the information that the transaction has been written to Relay-log, he will automatically go to asynchronous replication, and when one of the slave that supports asynchronous replication is catching up with Master, Master will go to the semi synchronous replication state.

5. Semi-synchronous replication must be supported at both ends.

When Master's thread is blocked (waiting for a message to be returned by the slave), no messages are returned to session sessions. When the blocking ends, master returns to the session message.

If a transaction contains a modification to a rollback table, in the event of an occurrence, master writes the information to the Binlog and also blocks, waiting for the slave side to write.

Semi-synchronous replication requires trade-offs in data consistency and performance.

5.5 Semi-synchronous installation steps

1, this first need 5.5 support dynamic loading module: View variable: have_dynamic_loading is yes;

2, the basic replication framework has been built

3, master-side and slave-side modules are released with the version, implemented at the master end:

INSTALL PLUGIN rpl_semi_sync_master soname ' semisync_master.so ';

Execute at slave end:

INSTALL PLUGIN rpl_semi_sync_slave soname ' semisync_slave.so ';

If the following error occurs during installation, download the missing component: http://dev.mysql.com/downloads/os-linux.html.

ERROR 1126 (HY000): Can ' t open shared Library

'/usr/local/mysql/lib/plugin/semisync_master.so ' (errno:22 libimf.so:cannot Open

Shared object File:no such file or directory)

After the installation is complete, you also need to set the variable to start correctly:

SET GLOBAL rpl_semi_sync_master_enabled = 1;

This column more highlights: http://www.bianceng.cn/database/MySQL/

Set GLOBAL rpl_semi_sync_master_timeout =n setting timeout, the default is 10s (a bit long)

Execute at slave end:

SET GLOBAL rpl_semi_sync_slave_enabled =1

(All the above information can be written to the my.cnf file)

This time replication is still an asynchronous replication, only the slave or restart the slave I/O thread to truly use the semi-synchronous replication feature.

Stop slave io_thread;start slave io_thread;

Monitor the state of semi synchronous replication;

Several important variables are:

Rpl_semi_sync_master_clients the number of connected slave that are supported and have been registered for semi-synchronous replication.

Rpl_semi_sync_master_status Master's semi-synchronous replication state, 1 is active, 0 is inactive, either is not enabled, or switches to asynchronous replication state

The Rpl_semi_sync_slave_status state on the slave, 1, indicates that it is enabled and I/O threads are running, and 0 indicates an inactive state.

Several questions to consider when using semi-synchronous replication:

1, when all the slave crashes, how does the state of the semi synchronous replication change?

To prevent semi-synchronous replication from blocking without receiving confirmation, set a timeout using rpl-semi-sync-mastertimeout=millseconds (milliseconds), and master does not receive any slave response in the specified time. Then the copy will be loaded into asynchronous replication. Note: This option is not saved after server reboot, so you can consider writing to MY.CNF.

2, when master generated new events, at this time there is no slave to keep the connection, this time master how to change?

By default, as in 1 above, there is a time-out period. For more efficient replication, we can set the Rpl-semi-sync-master-wait-no-slave=on|off to turn off the default behavior and turn directly to asynchronous replication

3, for the more busy master, how in the master failure, maintain data master-slave consistency? For situations where partial transaction occur, a third party can be used to automatically intercept part of the Roll back field. Specific reference: Http://www.woqutech.com/files/Partial_Transaction.pdf