Semi synchronous replication of MySQL master-slave replication

MySQL Semi synchronous replication of master-slave replication

    The disadvantage of asynchronous replication is , When the main warehouse event After writing binary log , I don't know whether the slave library has accepted and applied logs ; If the main library goes down unexpectedly or breaks down , It is very likely that the things submitted by the master library are not transmitted to any slave library machine . Perform active and standby switching under the highly available cluster architecture , It will cause the new master database to lose data .

   mysql5.5 The semi synchronous replication function is introduced after version , The master-slave server must install the semi synchronous replication plug-in , To enable the copy function . This function ensures that the data transferred from the main library is received from the library binlog The content has been written into your own relay log Inside the , Will notify the waiting thread on the main library , The operation is completed .

    If the wait times out , exceed rpl_semi_sync_master_timeout Parameter setting time , Turn off semi synchronous replication , And automatically converted to asynchronous replication mode , Until at least one slave notifies the master that it has received binlog Information location .

  Semi synchronous replication improves the consistency of data between master and slave , Make replication more secure , stay mysql5.7 The version has added rpl_semi_sync_master_wait_point Parameters , It is used to control the return of the main library to session How to commit a transaction before it succeeds .

This parameter has two values ：

AFTER_SYNC（ The default value ）： The master server writes each transaction to its binary log and the slave server , And synchronize the binary log to disk . After synchronization , The master device waits for the slave device to acknowledge the receipt of the transaction . After receiving the confirmation , The master server submits the transaction to the storage engine , And return the result to the client , Then the client can continue .

AFTER_COMMIT： The master server writes each transaction to its binary log and the slave server , Synchronize binary logs , And submit the transaction to the storage engine . After the master commits, wait for the slave server to confirm the receipt of the transaction . After receiving the confirmation , The host returns the result to the client , Then the client can continue .

2.1 Semi synchronous installation

Install the replication plug-in and enable the semi synchronous replication function on the main database ：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

set global rpl_semi_sync_master_enabled=on;

 

show variables like '%rpl_semi_sync_master%';

 

Add main library parameter file ：

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

show plugins;

 

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

 

Similarly, the slave library can also install plug-ins and enable semi synchronous replication ：

Install the replication plug-in from the library and enable the semi synchronous replication function ：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

set global rpl_semi_sync_slave_enabled=on;

 

Add parameter file of standby database ：

rpl_semi_sync_slave_enabled=1

Because it used to be asynchronous replication , The slave library needs to be restarted IO Threads , Activate semi synchronous replication .

STOP SLAVE IO_THREAD;

START SLAVE IO_THREAD;

 

View the main library ：

There is already a slave library connected to the master library . And it's semi synchronous .

show variables like '%rpl_semi_sync_master%';

 

2.2 Switch from semi synchronous mode to asynchronous mode

1、 The main library timeout exceeds the specified parameter time

rpl_semi_sync_master_timeout Time is 10s. It is recommended to set as large as possible in production .

set global rpl_semi_sync_master_timeout=xxx;

 

2、 Manual switching

Main library

set rpl_semi_sync_master_enabled=off;

Slave Library ：

set rpl_semi_sync_slave_enabled=off

Close slave Library I/O thread

stop salve io_thread;

start salve io_thread;

---

---

About Me