About MySQL semi-sync Replication

MySQL Semisynchronous Replication

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

Disadvantages of asynchronous replication:

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

The concept of semi-synchronous replication:
I.
When the Server Load balancer host is connected to the Master node, it can check whether the Server Load balancer is in a semi-synchronous replication mechanism.

Ii.
When the semi-synchronous replication function is enabled on the Master, at least one Slave should be enabled. At this time, a thread will be blocked when committing a transaction on the Master until it knows that a Server Load balancer with semi-synchronous replication enabled has received all events of the transaction or waits for timeout.

Iii.
When a transaction event has been written to its relay-log and refreshed to the disk, Slave will notify you that it has been received.

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

V.
The semi-synchronous replication function must be enabled on both the Master and Slave nodes. The semi-synchronous replication function will take effect. Otherwise, the half-synchronous replication function is still asynchronous replication when only one side is enabled.

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

Synchronous replication: The Master submits the transaction until the transaction has been committed in all Slave, then the client is returned, and the transaction is completed. Disadvantage: there may be a great delay in completing a transaction.

Asynchronous replication: binlog is requested from the Master only when the Slave is ready. Disadvantage: Some events cannot be received by all Slave events.

Semi-synchronous replication: The mechanism of semi-synchronous replication is between synchronous and asynchronous, and the transaction commit of the Master is blocked, as long as a Slave has received the event of the transaction and has been recorded. It does not wait for all Slave to inform that it has received it, and it only receives it, instead of waiting for it to be fully executed and submitted.

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

Control Variable

Variable Name	Variable Scope	Dynamic Variable	Type	Default	Effect Host
Rpl_semi_sync_master_enabled	Global	Yes	Boolean	OFF	Master
Rpl_semi_sync_slave_enabled	Global	Yes	Boolean	OFF	Slave
Rpl_semi_sync_master_timeout	Global	Yes	Numeric	10000	Master

Explanation:

Rpl_semi_sync_master_enabled is used to control whether the Master node enables semi-synchronization. If it is enabled or not, it is set to ON or OFF (1or0 ).

Rpl_semi_sync_master_timeout is to control how long the Master waits to be notified that Slave has received the time-out.

Rpl_semi_sync_slave_enabled controls whether or not Slave enables semi-synchronization. If it is enabled or not, it is set to ON or OFF (1or0 ).

Monitor status variables of semi-synchronous replication (several common)

Rpl_semi_sync_master_clients: Check the number of Slave plug-ins that enable semi-sync replication.

Rpl_semi_sync_master_status: Check whether the semi-sync replication ON the Master is running. If the value is ON, it indicates that the Master has enabled semi-Sync and has been notified that Slave has been received. If the value is OFF, this indicates that the Master node has not enabled semi-sync or is not notified because of timeout and other reasons.

Rpl_semi_sync_master_no_tx: check how many transactions are being copied without the semi-synchronous replication mechanism.

Rpl_semi_sync_master_yes_tx: check how many transactions are successfully copied through the semi-synchronous replication mechanism.

Rpl_semi_sync_slave_status: Check whether the Server Load balancer half-sync replication works normally. If the value is ON, it means that the Server Load balancer is performing semi-sync replication and the Server Load balancer I/O is running. If the value is OFF, vice.

Instructions for other SEE: http://dev.mysql.com/doc/refman/5.5/en/index.html

Installation of semi-synchronous replication, configure http://www.bkjia.com

Environment requirements:
I.
MySQL5.5 or later

Ii.
To install plug-ins on MySQL, the database must support dynamic loading. Check whether it is supported. Use the following check:

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 the replication environment. That is to say, an existing replication environment exists before semi-synchronous replication is configured.

Installation:

Run the following command on the Master:

Mysql> install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';

Run the following commands on each Slave:

Mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';

If you do not know the directory of Plugin, use the following search:

Mysql> show global variables like 'in in _ dir ';

+ --------------- + ---------------------------------- +

| Variable_name | Value |

+ --------------- + ---------------------------------- +

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

+ --------------- + ---------------------------------- +

Check whether Plugin is correctly installed:

Mysql> show plugins;

Or

Mysql> select * from information_schema.plugins;

Configuration:

Run the following command on the Master:

Mysql> set global rpl_semi_sync_master_enabled = 1;

Mysql> set global rpl_semi_sync_master_timeout = N;

Run the following command on Slave:

Mysql> set global rpl_semi_sync_slave_enabled = 1;

(Http://www.bkjia.com

If you enable semi-synchronous replication on a running Slave, you must stop Slave I/O and enable semi-synchronization before enabling Slave I/O.

Mysql> stop slave IO_THREAD; start slave IO_THREAD;

If this is not done, Slave still replicates data asynchronously.

As you know, if you do not write the variable settings to the configuration file, the next time you restart the database, it will become invalid. Write the configuration file:

On the 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

Conclusion: semi-synchronous replication is a strategy that maintains data integrity and security. It is worthwhile to lose some performance. The configuration is very simple. The key is to understand its working mechanism.