Setting up semi-synchronous replication in MySQL

The steps for setting up MySQL semi-synchronous replication are as follows: 1. Confirm the version supports and load the plug-in; 2. Turn on and enable semi-synchronous mode; 3. Check the status and operation status; 4. Pay attention to timeout settings, multi-slave library configuration and master-slave switching processing. It is necessary to ensure that MySQL version 5.5 and above, install the rpl_semi_sync_master and rpl_semi_sync_slave plugins, enable the corresponding parameters in the master and slave library, and configure automatic loading in my.cnf, restart the service after the settings are completed, check the status through SHOW STATUS, reasonably adjust the timeout time and monitor the plug-in operation.

Semi-Synchronous Replication of MySQL is a compromise replication method. The master library waits for at least one slave library to confirm that the log is received before continuing to execute when submitting a transaction. Compared with asynchronous replication, it is safer and better than full synchronization in performance. It is suitable for scenarios where data consistency requirements are high but cannot accept full synchronization performance losses.

Next, let’s talk about how to set up MySQL semi-synchronous replication, including basic premises, configuration steps and some points that need to be paid attention to.

Confirm MySQL version and plug-in support

To use semi-synchronous replication, first make sure your version of MySQL supports it. Generally speaking:

• MySQL 5.5 and above starts to support
• 5.7 or 8.0 is recommended because the functions are more complete
• Need to load semisync_master and semisync_slave plugins

You can check whether these plugins have been loaded by:

 SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semisync%';

If not loaded, you can execute it on the master and slave libraries separately:

 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Note: The .so files may be called slightly differently in different systems. For example, some are libsemisync_master.so , which are adjusted according to the actual path.

Turn on and enable semi-synchronous copy mode

After installing the plug-ins, you also need to enable them and set them to automatically load to avoid restart failure.

Settings on the main library:

 SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- Timeout time unit is milliseconds

You can also add in my.cnf :

 [mysqld]
plugin_load_add='rpl_semi_sync_master=semisync_master.so'
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

Set from the library:

 SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Add the corresponding configuration file:

 plugin_load_add='rpl_semi_sync_slave=semisync_slave.so'
rpl_semi_sync_slave_enabled=1

After the setup is complete, restart the MySQL service to ensure that the plug-in takes effect.

Check status and operation status

Setting up does not mean that it takes effect immediately. You need to confirm that there is indeed a connection from the library in the replication topology to trigger semi-synchronous behavior.

You can use the following statement to see if it is currently in semi-synchronous:

 SHOW STATUS LIKE 'Rpl_semi_sync_master_status';

If ON is returned, it means that the current main library is using semi-synchronous mode; if OFF , it may be that the slave library is not connected, or the slave library does not have the semi-synchronous plug-in enabled.

You can also see if the timeout occurs:

 SHOW STATUS LIKE 'Rpl_semi_sync_master_no_times';

If this value continues to grow, it means that timeouts often occur, which may be delayed in network or stressed from the library.

Notes and FAQs

• Don't rely on only one slave library : Although semi-synchronously only has one slave library response, if that slave library goes down, the master library will automatically degenerate into asynchronous mode. If you want to keep semi-synchronous as possible, it is recommended to install multiple slave libraries.
• Reasonably set the timeout time : the default is 1 second (1000ms). Too short will lead to frequent degradation, and too long will affect the write response speed. It can be adjusted appropriately according to the network conditions.
• After switching between master and slave, you need to re-enable the plug-in : If the master and slave role changes, remember to enable the semi-synchronous plug-in on the new master library.
• Monitor the status of the plug-in : It is recommended to include the status of the plug-in in the monitoring item to prevent replication degradation due to the failure of the plug-in to start.

Basically that's it. Setting up is not complicated, but what is easy to ignore is whether the plug-in is really loaded, whether it is enabled correctly, and whether the timeout setting is reasonable. As long as you understand these points clearly, semi-synchronous replication can run stably.

The above is the detailed content of Setting up semi-synchronous replication in MySQL. For more information, please follow other related articles on the PHP Chinese website!