MySQL Semi-synchronous replication

1. Introduction to Semi-synchronous replication

What is semi-synchronous replication mode? Here we first understand the asynchronous replication mode, which is the default replication option for MySQL. Asynchronous replication is the master database that sends the Binlog log to the slave database, and then it's gone. Exposing a problem here, when the slave server fails, it will certainly result in inconsistent data from the master-slave database server.

To solve the above problem, MySQL5.5 introduces a mode called semi-synchronous replication. This mode can be used to ensure that the slave database receives the Binlog log sent by the master database and writes it to its own trunk log, and then feeds back to the master database informing that it has been copied.

When this mode is turned on, the primary database automatically reverts to asynchronous replication mode when a timeout occurs until at least one binlog from the server to the primary database is received and fed back to the primary database. The primary database is then switched back to semi-synchronous replication mode.

Attention:

The semi-synchronous replication mode must be turned on both the primary and slave servers, otherwise the asynchronous replication mode will default.

2. Environmental description

Two Linux virtual hosts

Linux version CentOS6.6, MySQL 5.5

ip:192.168.95.11 (Master), 192.168.95.12 (from)

3. Installation and Configuration

3.1. Installation Prerequisites

1, must be MySQL5.5 or above

2, MySQL must have the automatic loading function, that is, the have_dynamic_loading variable is yes (because we are in MySQL to load the installation of this feature plugin)

Show variables like ' have_dynamic_loading '; #检查是否具有自动加载功能

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309131559328-1747926966. JPG "style=" margin:0px;padding:0px;border:none; "/>

3, master-slave replication has been configured, and has been working on ING.

Master-Slave replication Configuration tutorial: H TT p://w w w.cn blo gs.co m/ph pstud y2015-6/p/6485819.html

3.2. Installation

　　　　192.168.95.11 Load Installation:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

1 Mysql>install plugin rpl_semi_sync_master soname ' semisync_master.so '; 2 3 mysql>show plugins;  #查看是否加载成功4 5 mysql>set GLOBAL rpl_semi_sync_master_enabled = 1; #开启半同步复制, the default is off

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309131656828-1162180234. JPG "style=" margin:0px;padding:0px;border:none; "/>

　　　　192.168.95.12 Loading Installation

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

1 mysql>install PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';   2 3 Mysql>show plugins;  #查看是否加载成功 4 5 mysql>set GLOBAL rpl_semi_sync_slave_enabled = 1; #开启半同步复制, the default is to turn off the 6 7 #重启从服务器IO线程, manually switch the asynchronous mode to semi-synchronous mode 8 9 mysql>stop SLAVE io_thread;10 mysql>start SLAVE io_thread;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

3.3. Configuration files

1 rpl_semi_sync_master_enabled=1 #主库配置文件添加, which means that MySQL will automatically turn on the semi-synchronous replication 2 3 rpl_semi_sync_slave_enabled=1 #从库配置文件添加 in the future, ditto

4. View relevant parameters

1, the master-slave execution command show variables like '%semi% ';

Master

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132156297-39217780. JPG "style=" margin:0px;padding:0px;border:none; "/>

Rpl_semi_sync_master_enabled=on to turn on semi-synchronous replication

rpl_semi_sync_master_timeout=1000 default 1000 milliseconds, which is 10 seconds timeout, will switch to asynchronous replication

Rpl_semi_sync_master_wait_no_slave Indicates whether Master is allowed to wait for slave to receive acknowledgement, default to On

RPL_SEMI_SYNC_MASTER_TRACE_LEVEL=32 indicates the debug level used to turn on semi-synchronous replication, default 32

　Slave

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132259813-1175604936. JPG "style=" margin:0px;padding:0px;border:none; "/>

Rpl_semi_sync_slave_enabled=on indicates that the semi-synchronous replication mode has started in slave

RPL_SEMI_SYNC_SLAVE_TRACE_LEVEL=32 indicates the debug level used to turn on semi-synchronous replication, default 32

　　2, the master-slave execution command show status like '%semi% ';

Master

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132430688-1578746824. JPG "style=" margin:0px;padding:0px;border:none; "/>

Rpl_semi_sync_master_status indicates whether the primary server uses asynchronous or semi-synchronous replication

Rpl_semi_sync_master_client indicates how many configurations are made to semi-synchronous replication from a server

RPL_SEMI_SYNC_MASTER_YES_TX indicates the number of successful commits that were confirmed from the server

RPL_SEMI_SYNC_MASTER_NO_TX indicates the number of failed commits that were acknowledged from the server

Slave

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132501406-1269147257. JPG "style=" margin:0px;padding:0px;border:none; "/>

Rpl_semi_sync_slave_status to turn on semi-synchronous replication from the server

5. Testing

Analog slave hangs, master waits for 10s to still not receive the feedback signal, then goes to asynchronous copy mode, continues to execute

First synchronize the creation of the database AA

1, slave execution stop slave; turn off master-slave replication

2, Master in the AA database to create the table TAB1, did not receive the feedback signal, wait 10 seconds (rpl_semi_sync_master_timeout=1000 wait timeout), continue to execute

Master

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132555719-1012381545. JPG "style=" margin:0px;padding:0px;border:none; "/>

Slave

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132610469-5158186.jpg "Style=" margin:0px;padding:0px;border:none; "/>

3, Master in the database re-create TAB2, do not need to wait for feedback, direct execution

"When feedback times out, master switches to asynchronous replication mode. This is the asynchronous mode and does not need to wait "

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132648969-1667309201. JPG "style=" margin:0px;padding:0px;border:none; "/>

4, slave execution start slave, data start synchronization, establish TAB1, tab2, feedback to master, and switch to semi-synchronous replication

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132702422-1566693580. JPG "style=" margin:0px;padding:0px;border:none; "/>

5, slave execution stop slave; turn off master-slave replication

6, Master in the database to create table TAB3, at this time need to wait 10s, receive slave feedback signal; Wait timeout, switch to asynchronous replication mode, continue execution

"Step 4 o'clock, data synchronization has been fed back to master, when Master is already in semi-synchronous mode"

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/789055/201703/789055-20170309132725281-37870781. JPG "style=" margin:0px;padding:0px;border:none; "/>

6. Summary

The performance and concurrency of the semi-synchronous replication mode is lower than the asynchronous replication pattern, because each copy is given feedback, which is one more step.

To be honest, I still don't understand why semi-synchronous replication can maintain the integrity of the data? If slave hangs, master does not change back to asynchronous replication. There is no difference between the full async and the previous. There is even a more information feedback, but the feedback back in addition to let master confirm what role? If slave, there is no feedback information, that master still no further processing method AH.

MySQL Semi-synchronous replication