MySQL Semi-synchronous replication

Semi-synchronous replication

(1). Semi-synchronous replication principle.

In a semi-synchronous replication architecture, the host ensures that the current transaction has been sent to at least one standby (without waiting for the transaction redo to complete).

The message is returned to the client. If the transaction log is not delivered to the standby during the set time, then the semi-synchronization becomes asynchronous replication.

(2). The difference between semi-synchronous replication and asynchronous replication:

Asynchronous replication: The host does not detect whether the transaction log has been driven to the standby and returns the message to the client. There is a greater risk of data loss in high-load systems.

Semi-synchronous replication; The master opportunity detects whether the transaction log has been transferred to at least one standby, and if it has not been delivered to a standby during the set time,

The host does not return a message to the client, at which point the client is always waiting, but at this point the transaction on the host has been successfully executed and has been committed.

(3). Configure semi-synchronous replication:

When mysql5.6 is used as a plug-in for semi-synchronous replication, it is disabled by default and requires the manual start of semi-synchronous replication.

To install the semi-synchronous replication plug-in on master:

master> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

To install the semi-synchronous replication plug-in on slave:

slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

When master installs, the variables associated with the semi-sync will appear

Mysql> Show variables like ' rpl_semi% ';

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

| variable_name | Value |

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

|  rpl_semi_sync_master_enabled | 0 |

| Rpl_semi_sync_master_timeout | 30000 |

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_no_slave | On |

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

4 rows in Set (0.00 sec)

Slave after installation, there will be variables associated with the semi-sync

Mysql> Show variables like ' rpl_semi% ';

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

| variable_name | Value |

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

|  rpl_semi_sync_slave_enabled | 0 |

| Rpl_semi_sync_slave_trace_level | 32 |

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

2 rows in Set (0.01 sec)

Semi-synchronous replication needs to be activated after installing the semi-synchronous replication plug-in

It is recommended to write directly to the configuration file, otherwise the service restarts the semi-synchronous failure

SET GLOBAL rpl_semi_sync_master_enabled = on;

Mysql> Show variables like ' rpl_semi_sync_master_enabled ';

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

| variable_name | Value |

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

| rpl_semi_sync_master_enabled | On |

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

1 row in Set (0.00 sec)

(4) Variables related to semi-synchronization

Master on the

Mysql> Show variables like ' rpl_semi% ';

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

| variable_name | Value |

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

| rpl_semi_sync_master_enabled | On |

| Rpl_semi_sync_master_timeout | 30000 |

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_no_slave | On |

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

4 rows in Set (0.00 sec)

On the Slave:

Mysql> Show variables like ' rpl_semi% ';

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

| variable_name | Value |

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

| rpl_semi_sync_slave_enabled | On |

| Rpl_semi_sync_slave_trace_level | 32 |

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

2 rows in Set (0.00 sec)

Variable interpretation:

Rpl_semi_sync_master_enabled: Whether semi-synchronous replication is turned on

Rpl_semi_sync_master_timeout: Client side waits for master to return the message timeout time

Rpl_semi_sync_master_wait_no_slave: Indicates that even if no slave will wait for the end of the expiration time, it is the default value.

State variables for the main library:

Mysql> show global status like ' rpl_semi% ';

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

| variable_name | Value |

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

| rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 6482 |

| Rpl_semi_sync_master_net_wait_time | 51861 |

| Rpl_semi_sync_master_net_waits | 8 |

| Rpl_semi_sync_master_no_times | 2 |

| Rpl_semi_sync_master_no_tx | 2 |

| Rpl_semi_sync_master_status | On |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 6287791 |

| Rpl_semi_sync_master_tx_wait_time | 25151166 |

| Rpl_semi_sync_master_tx_waits | 4 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 1 |

| rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 4 |

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

Parameter explanation:

Rpl_semi_sync_master_clients: Number of slave in the connection

Rpl_semi_sync_master_no_times: Number of times from semi-synchronous mode to normal mode (asynchronous mode)

RPL_SEMI_SYNC_MASTER_NO_TX: Number of transactions not executed in semi-synchronous mode

Rpl_semi_sync_master_status: Mode state

RPL_SEMI_SYNC_MASTER_YES_TX: Number of successfully executed transactions in semi-synchronous mode

Rpl_semi_sync_master_wait_sessions: Number of threads waiting for standby response

Network Related:

Rpl_semi_sync_master_net_avg_wait_time: Average time to wait for network response

Rpl_semi_sync_master_net_wait_time: Total time to wait for network response

Rpl_semi_sync_master_net_waits: Total number of waits for network response

Standby State melon:

Rpl_semi_sync_master_tx_avg_wait_time: Average time to wait for standby response

Rpl_semi_sync_master_tx_wait_time: Total time to wait for standby response

Rpl_semi_sync_master_tx_waits: Number of responses waiting for a standby response

State variables for the standby library:

Mysql> show global status like ' rpl_semi% ';

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

| variable_name | Value |

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

| Rpl_semi_sync_slave_status | On |

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

Rpl_semi_sync_slave_status: Status of the Standby library

Note: Semi-synchronous replication is only available in MySQL version 5.5 and later

The following is a reference link:

http://blog.csdn.net/wyzxg/article/details/8487953

http://www.orczhou.com/index.php/2011/06/mysql-5-5-semi-sync-replication-setup-config/

Http://bbs.51cto.com/thread-877655-1.html

http://blog.itpub.net/24945919/viewspace-764369/

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1552821

MySQL Semi-synchronous replication