Shulou(Shulou.com)06/01 Report--

In addition to supporting the built-in asynchronous replication mechanism, MySQL5.5 also provides a mechanism for interfaces to support semi-synchronous replication.

Disadvantages of asynchronous replication:

MySQL replication defaults to asynchronous replication, and Master writes events to binlog, but does not know whether or when Slave has been received and processed. In the case of an asynchronous replication mechanism, if the Master goes down, the transaction is committed on the Master, but it is likely that these transactions are not transferred to any Slave. Assuming that there is a Master- > Salve failover mechanism, Slave may also lose transactions.

The concept of semi-synchronous replication:

i.

When a Slave host connects to the Master, you can see if it is in the mechanism of semi-synchronous replication.

ii.

When semi-synchronous replication is enabled on Master, at least one Slave should enable it. At this point, a thread commiting a transaction on the Master will be blocked until it is known that a Slave with semi-synchronous replication enabled has received all events for the transaction, or waits for a timeout.

iii.

Slave notifies you that it has been received when the events of a transaction have been written to its relay-log and flushed to disk.

iv.

If the wait times out, that is, the Master is not told that it has been received, the Master is automatically converted to an asynchronous replication mechanism. When at least one semi-synchronous Slave catches up, Master and its Slave automatically switch to a semi-synchronous replication mechanism.

v.

Semi-synchronous replication will not work until Master,Slave is enabled; otherwise, only on one side, it will still be asynchronous replication.

Comparison of synchronous, asynchronous, and semi-synchronous replication:

Synchronous replication: Master commits the transaction, and the transaction is not returned to the client until it has been committed at all Slave, and the transaction is completed. Cons: there may be a big delay in completing a transaction.

Asynchronous replication: when Slave is ready, it requests binlog from Master. Cons: there is no guarantee that some events will be received by all Slave.

Semi-synchronous replication: the mechanism of semi-synchronous replication is between synchronous and asynchronous, and the transaction commit of Master is blocked, as long as an Slave has received and logged the event of the transaction. It does not wait for all Slave to be notified that it has been received, and it just receives it, not for it to be fully executed and submitted.

Control variables for semi-synchronous replication, status monitoring variables:

Control variable

Variable NameVariable ScopeDynamic VariableTypeDefaultEffect Hostrpl_semi_sync_master_enabledGlobalYesbooleanOFFMasterrpl_semi_sync_slave_enabledGlobalYesbooleanOFFSlaverpl_semi_sync_master_timeoutGlobalYesnumeric10000Master

Explanation:

Rpl_semi_sync_master_enabled controls whether Master is enabled, turned on or off, and is set to ON or OFF (1or0).

Rpl_semi_sync_master_timeout controls how long Master waits to be told that Slave has received it, which is the so-called timeout.

Rpl_semi_sync_slave_enabled controls whether Slave is enabled, turned on or off, and is set to ON or OFF (1or0).

Monitor status variables for semi-synchronous replication (several commonly used)

Rpl_semi_sync_master_clients: check the Slave of how many plug-ins that enable semi-synchronous replication

Rpl_semi_sync_master_status: check whether semi-synchronous replication is running on Master. If the value is ON, Master has enabled semi-synchronization and has been informed that Slave has received it. If its value is OFF, Master has not enabled semi-synchronization or has not been informed, due to timeout and other reasons.

Rpl_semi_sync_master_no_tx: see how many transactions are not replicated using semi-synchronous replication.

Rpl_semi_sync_master_yes_tx: see how many transactions are successfully replicated through the semi-synchronous replication mechanism.

Rpl_semi_sync_slave_status: check whether semi-synchronous replication on Slave is working properly. If the value is ON, Slave is passing semi-synchronous replication and Slave Ibino is running; if it is OFF, vice versa.

For more information, please see: http://dev.mysql.com/doc/refman/5.5/en/index.html

Installation of semi-synchronous replication, configuring http://www.linuxidc.com

Environmental requirements:

i.

MySQL5.5 or above

ii.

Installing plug-ins on MySQL requires database support for dynamic loading. Check whether it is supported or not, and use the following test:

Mysql > show global variables like 'have_dynamic_loading'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

1 row in set (0.00 sec)

iii. Semi-synchronous replication is based on a replication environment. That is, before configuring semi-synchronous replication, there is already a replicated environment.

Installation:

Execute on Master:

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

Execute on each Slave:

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

If you don't know the directory of Plugin, look for it as follows:

Mysql > show global variables like 'plugin_dir'

+-- +

| | Variable_name | Value |

+-- +

| | plugin_dir | / opt/usr/local/mysql/lib/plugin/ |

+-- +

Check that Plugin is installed correctly:

Mysql > show plugins

Or

Mysql > select * from information_schema.plugins

Configuration:

Execute on Master:

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

Mysql > SET GLOBAL rpl_semi_sync_master_timeout = N

Execute on Slave:

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

Description: http://www.linuxidc.com

If you turn on semi-synchronous replication on a running Slave, you must first stop Slave Imax O, enable semi-synchronization, and then turn on Slave I Dot O.

Mysql > STOP SLAVE IO_THREAD; START SLAVE IO_THREAD

If not, Slave will still replicate asynchronously.

As you know, if you do not write the settings of the variables to the configuration file, the next time you restart the database, it will fail. Write configuration file:

On Master:

[mysqld]

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

On Slave:

[mysqld]

Rpl_semi_sync_slave_enabled=1

Summary: semi-synchronous replication personal feeling is a strategy to maintain data integrity and security, although it will lose a little performance, it is still worth it. The configuration is simple, and the key is to understand how it works.

The semi-synchronous replication mechanism of mysql is similar to the maximum available mode of oracle DG, the synchronous mechanism of mysql is similar to the maximum protection mode of oracle DG, and the asynchronous mechanism of mysql is similar to the maximum performance mode of oracle DG.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.