MySQL semi-synchronous replication implementation

MySQL semi-synchronous replication implementation
I. Why do I need semi-synchronous replication?

MySQL replication is asynchronous by default. After the Mysql Master Server transfers its Binary Log through the replication thread, the Mysql Master Sever automatically returns data to the client, however, we do not know whether or when the Slave has been received and processed. Therefore, there is a certain probability that the data in the Slave database and the master database is unequal. 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. In some cases, it is necessary to maintain strong consistency between the master and slave databases. In this case, enabling the semi-synchronous replication feature of MySQL is perfect. Semi_sync_replication is a semi-synchronous patch developed by google for mysql. Since mysql5.5, mysql introduces the semi-sync function to ensure data consistency between the master and slave databases.

In the semi-synchronous replication architecture, when the master sends its binlog to the slave, it must ensure that the slave has received the binary log before returning the data to the client. Two architectures are compared: asynchronous replication ensures a fast response structure for users, but does not ensure that binary logs are indeed on slave; semi-synchronous replication provides a slightly slower response to the customer's request, but it can ensure the integrity of binary logs.

Ii. Principles of semi-synchronous Replication

The semi-synchronous replication architecture is shown as follows:

The concept of semi-synchronous replication:

 

1. When the Slave host is connected to the Master, it can check whether it is in the semi-synchronous replication mechanism.

2. 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.

3. When a transaction event has been written to its relay-log and refreshed to the disk, Slave will notify you that it has received the event. On the Master instance, a dedicated thread (ack_receiver) receives the Response Message from the slave database and uses the notification mechanism to notify the Master database of the logs received by the slave database.

4. If the wait times out, that is, the Master is not notified that it has received the message, 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.

5. the semi-synchronous replication function should be enabled on both the Master and Slave, and semi-synchronous replication will take effect. Otherwise, it is still asynchronous replication only when one side is enabled.

6. The emergence of semi-sync is to ensure the consistency of the master and slave data at any time. Compared with asynchronous replication, semi-synchronous replication requires that at least one slave database be successfully received before being returned to the user.

II. Implementation of semi-synchronous Replication

Configure the master node:

1. Mysql> install plugin rpl_semi_sync_master soname 'semisync _ master. so'; # install the plug-in
2. Query OK, 0 rows affected (0.07 sec)
3.  
4. Mysql> show global variables like '% semi % ';
5. + ------------------------------------ + -------------- +
6. | Variable_name | Value |
7. + ------------------------------------ + -------------- +
8. | Rpl_semi_sync_master_enabled | OFF |
9. | Rpl_semi_sync_master_timeout | 10000 |
10. | Rpl_semi_sync_master_trace_level | 32 |
11. | Rpl_semi_sync_master_wait_no_slave | ON |
12. | Rpl_semi_sync_master_wait_point | AFTER_COMMIT |
13. + ------------------------------------ + -------------- +
14. 5 rows inset (0.00 sec)
15.  
16. Mysql> setglobal rpl_semi_sync_master_enabled = on; # Enable Plug-in
17. Query OK, 0 rows affected (0.02 sec)
18.  
19. Mysql> setglobal rpl_semi_sync_master_timeout = 2000; # Set the timeout time
20. Query OK, 0 rows affected (0.00 sec)
21.  
22. Mysql> show global variables like '% semi % ';
23. + ------------------------------------ + -------------- +
24. | Variable_name | Value |
25. + ------------------------------------ + -------------- +
26. | Rpl_semi_sync_master_enabled | ON |
27. | Rpl_semi_sync_master_timeout | 2000 |
28. | Rpl_semi_sync_master_trace_level | 32 |
29. | Rpl_semi_sync_master_wait_no_slave | ON |
30. | Rpl_semi_sync_master_wait_point | AFTER_COMMIT |
31. + ------------------------------------ + -------------- +
32. 5 rows inset (0.00 sec)
33.  

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 the state variables of semi-synchronous replication (several frequently used ):

 

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.

Use the same steps to configure the slave node. After the slave node is complete, restart io_thread. If the slave node is not restarted, the slave node times out and automatically drops to asynchronous mode after the timeout:

 

1. MariaDB [mydb]> install plugin rpl_semi_sync_master soname 'semisync _ master. so ';
2. MariaDB [mydb]> setglobal rpl_semi_sync_master_enabled = on;
3.  
4. MariaDB [mydb]> stop slave io_thread;
5. Query OK, 0 rows affected (0.01 sec)
6.  
7. MariaDB [mydb]> start slave io_thread;
8. Query OK, 0 rows affected (0.00 sec)
9.  
10. Mysql> show global status like '% semi %'; # view the semi-sync Client
11. + ------------------------------------------ + ------- +
12. | Variable_name | Value |
13. + ------------------------------------------ + ------- +
14. | Rpl_semi_sync_master_clients | 0 |
15. | Rpl_semi_sync_master_net_avg_wait_time | 0 |
16. | Rpl_semi_sync_master_net_wait_time | 0 |
17. | Rpl_semi_sync_master_net_waits | 0 |
18. | Rpl_semi_sync_master_no_times | 1 |
19. | Rpl_semi_sync_master_no_tx | 3 |
20. | Rpl_semi_sync_master_status | OFF |
21. | Rpl_semi_sync_master_timefunc_failures | 0 |
22. | Rpl_semi_sync_master_tx_avg_wait_time | 0 |
23. | Rpl_semi_sync_master_tx_wait_time | 0 |
24. | Rpl_semi_sync_master_tx_waits | 0 |
25. | Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
26. | Rpl_semi_sync_master_wait_sessions | 0 |
27. | Rpl_semi_sync_master_yes_tx | 0 |
28. + ------------------------------------------ + ------- +
29. 14 rows inset (0.00 sec)

Then verify it on your own!

Conclusion: The performance of semi-synchronous replication may be affected, but it is mainly a strategy to maintain data integrity and security, although it will lose some performance, but it is still worth it.

This article permanently updates the link address: