Set up and use semi-synchronous mysql replication (semi_sync_replication)

Set up mysql semi-synchronous replication (semi_sync_replication) and use google to develop a patch for mysql based on semi-synchronous, application and mysql5.0. Mysql is installed with this patch and used in mysql 5.5. What is the concept of semi-synchronous replication? Before the Database Change operation is executed, make sure that the change operation is written to at least one slave disk, which means that at most one transaction is lost due to a master crash for each connection. It mainly guarantees data integrity and prevents the loss of transactions. On the premise of semi-sync configuration, the master-slave replication environment has been set up (Omitted) and the plug-in has been installed for master operations.

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.21 sec)mysql> show variables like 'rpl_%';+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_recovery_rank                  | 0     | | rpl_semi_sync_master_enabled       | OFF   | | rpl_semi_sync_master_timeout       | 10000 | | rpl_semi_sync_master_trace_level   | 32    | | rpl_semi_sync_master_wait_no_slave | ON    | +------------------------------------+-------+5 rows in set (0.00 sec)

 

Set variables to enable semi-synchronous Replication

mysql> set global rpl_semi_sync_master_enabled=on ;Query OK, 0 rows affected (0.02 sec)

 

Server Load balancer plugin installation

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.06 sec)mysql> show variables like 'rpl_semi%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | OFF   | | rpl_semi_sync_slave_trace_level | 32    | +---------------------------------+-------+2 rows in set (0.00 sec)

 

Enable semi-synchronous Replication

mysql> set global rpl_semi_sync_slave_enabled=on ;Query OK, 0 rows affected (0.00 sec)

 

Rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled can also be configured in the master and slave configuration files, so that the server restart will take effect. In this way, the configuration of a semi-synchronous replication is complete. After configuring slave, we need to consider the following two questions: 1. What if all slave crashes? At this time, no Server Load balancer notifies the master that the transaction has been written into the relay log. 2 What if all slave is disconnected? In this case, no Server Load balancer notifies the master to send transactions for security reasons. There are two other parameters to solve the above problem.

rpl_semi_sync_master_timeout=milliseconds

 

To prevent semi-synchronous replication from sending congestion without receiving confirmation, you can use the rpl-semi-sync-mastertimeout = milliseconds option to set the counter. If the master node does not receive confirmation before the timeout period, it will be restored to asynchronous replication.

set global rep-semi-sync-master-timeout=milliseconds

. It should be noted that, as a server variable, the value of server restart is not saved

rpl-semi-sync-master-wait-no-slave=on/off 

 

If a transaction is committed and the master has no slave connection, it is impossible for the slave to send the transaction to other places for storage. By default, the master will wait for the slave in an acute manner within the time limit, confirm that the transaction is correctly written to the disk. In this case, the master will still recover to asynchronous replication. The plug-in for monitoring semi-synchronous replication provides a large number of state variables. You can use these state variables to monitor semi-synchronous replication. You can also briefly describe some of the most useful variables and other variables to query the online manual.

rpl_semi_sync_master-clients  

 

This status variable reports the number of slave connections that support semi-synchronous master-slave replication.

rpl_semi_sync_master_status 

 

The semi-synchronous replication status of the master node 1 is active, and 0 is non-active. Either it is not enabled, or it is restored to asynchronous replication. Use the show status command or use global_status in information_schema mode to query these status variables.

mysql> show status like 'rpl_semi%';+--------------------------------------------+-------+| Variable_name                              | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients               | 0     | | Rpl_semi_sync_master_net_avg_wait_time     | 0     | | Rpl_semi_sync_master_net_wait_time         | 0     | | Rpl_semi_sync_master_net_waits             | 0     | | Rpl_semi_sync_master_no_times              | 0     | | Rpl_semi_sync_master_no_tx                 | 0     | | Rpl_semi_sync_master_status                | ON    | | Rpl_semi_sync_master_timefunc_failures     | 0     | | Rpl_semi_sync_master_tx_avg_wait_time      | 0     | | Rpl_semi_sync_master_tx_wait_time          | 0     | | Rpl_semi_sync_master_tx_waits              | 0     | | Rpl_semi_sync_master_wait_pos_backtraverse | 0     | | Rpl_semi_sync_master_wait_sessions         | 0     | | Rpl_semi_sync_master_yes_tx                | 0     | +--------------------------------------------+-------+

 

Semi-synchronous replication may affect the performance. The primary principle is to ensure data integrity. Here integrity is the most important.