Shulou(Shulou.com)05/31 Report--

How to achieve semi-synchronous replication in MySQL, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Semi-synchronous replication:

What is semi-synchronous replication? We know that by default, replication of MySQL is asynchronous, which means that the master server and its slaves are independent. Asynchronous replication can provide * performance because after writing updated data to its binary log (Binlog) file, the master server is free to process other incoming transaction requests without waiting to verify that the updated data has been copied to the slave server. But at the same time, it also brings a high risk. If a failure occurs in the master server or slave server, it will cause the inconsistency of master-slave data, and even cause data loss during recovery.

Semi-synchronous replication is a semi-synchronous replication feature introduced from MySQL5.5, which ensures data consistency and redundancy between the master server and at least one slave server in the access chain. In this configuration structure, a master server and many of its slaves are configured so that in a replication topology, at least one slave server must confirm that updates have been received and written to the relay log (Relay Log) before the parent master server transacts transactions. When a timeout occurs, the source master server must temporarily switch to asynchronous replication mode to re-replicate until at least one slave server that is set in semi-synchronous replication mode receives information in time.

Let's take a look at how to change from normal replication to semi-synchronous replication. Suppose we have built a GTID replication environment with one master and two slaves:

MySQL1:172.16.16.35:3306

MySQL2:172.16.16.35:3307

MySQL3:172.16.16.34:3306

Since I have already set up this environment when I tested MHA, I no longer emphasize how to build a normal GTID replication environment. Let's take a look at how to install it.

(1) to install the plug-in, all three MySQL servers need to execute:

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME' semisync_slave.so'

We can check whether the installation is successful with the following statement:

Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%' +-- +-+ | PLUGIN_NAME | PLUGIN_STATUS | +-+-+ | rpl_semi_sync_master | ACTIVE | | rpl_semi_sync_slave | ACTIVE | +- -+-+ 2 rows in set (0.00 sec)

Add the following parameters to the configuration files of the three MySQL:

Rpl_semi_sync_master_enabled=1 # enables semi-synchronous replication

Rpl_semi_sync_slave_enabled=on; # turn on semi-synchronous replication

Then restart the database. This parameter cannot be recognized without installing semisync_master.so, so this parameter has to be restarted after installation.

(2) after the restart is completed, the default is semi-synchronous replication. Let's take a look at the parameters related to semi-synchronization:

Mysql > show variables like 'rpl_se%' +-+-+ | Variable_name | Value | + -- + | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace _ level | 32 | +-+-+ 8 rows in set (0.00 sec)

Let's take a look at what these parameters mean:

Rpl_semi_sync_master_enabled: whether semi-synchronous replication is enabled in the main library

Rpl_semi_sync_master_timeout: in milliseconds, when the master library waits for the slave library ACK to exceed this value, it will be automatically converted to asynchronous replication

Rpl_semi_sync_master_trace_level: the trace level of master, which is divided into four groups (1mem16, 32pc64), which record different information respectively. 32 can output more detailed information such as network delay, which is also the default value.

Rpl_semi_sync_master_wait_for_slave_count: at least N slave received logs. In the case of one master and multiple slaves, as long as the ACK of one slave is returned to the master database, commit will be performed.

Rpl_semi_sync_master_wait_no_slave: the default is ON. When semi-synchronous replication is converted to asynchronous replication, if the log of the slave database catches up with the master database, it will automatically be converted to semi-synchronous replication. If set to OFF, the conversion will not occur again.

Rpl_semi_sync_slave_enabled: whether semi-synchronous replication is enabled from the library

Rpl_semi_sync_slave_trace_level: trace level

Rpl_semi_sync_master_wait_point: this is a new feature of MySQL5.7. In the mode of setting two values AFTER_SYNC and AFTER_COMMIT,AFTER_COMMIT, master writes each transaction to binlog, passes it to slave and flushes it to disk (relay log), while the main library commits the transaction. Master waits for the slave feedback to receive the relay log, and only after receiving the ACK does the master feedback the commit OK result to the client. In the case of AFTER_SYNC, master writes each transaction to binlog and passes it to slave to flush to disk (relay log). Master waits for slave feedback to receive the ack of relay log before committing the transaction and returning the commit OK result to the client. Even if the main library crash, all transactions that have been committed on the main library are guaranteed to be synchronized to the relay log of slave. We recommend using the default AFTER_SYNC, which can improve performance and reduce wait time.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.