MySQL semi-synchronous replication practice

Semi-synchronous replication

MySQL's master-slave replication only supports asynchronous replication before the 5.5 release, meaning that the main library performs some transactions, regardless of the progress of the library from the repository, this way the greatest benefit is the speed and efficiency, the disadvantage is that after the main library is down, the data consistency from the library and the main library cannot be ensured.

The advantage of semi-synchronous replication is that the main library waits for the standby to accept the log after each transaction, and if it is a small transaction, the delay between the two hosts is small, which can guarantee 0 data loss in the event of a small loss of performance.

Principle

650) this.width=650; "Src=" Http://www.lichengbing.cn/ueditor/php/upload/image/20160718/1468852926209698.png " Title= "1468852926209698.png" alt= "MySQL semi-synchronous copy. png"/>

1) The Master library reads the logs for each transaction, ensuring that at least one of the data on the repository and its own complete

2) If the main library still does not receive a standby response within a timeout timeout, auto Q switches back to asynchronous mode, guaranteeing business operation

3) Semi-synchronous replication only guarantees that the log is read from the library and does not guarantee that the data is written to the database from the library, but also from the library SQL process performance

4) If there is progress from the library to catch up with the main library, the mode continues to switch back to the semi-synchronous state

Environment Construction Practice

MySQL semi-synchronous replication plug-in is provided by Google, the 5.5 version can be used directly after the

!！ Note that the two hosts must have implemented a master-slave replication before the semi-synchronous replication refer to the blog post: http://www.lichengbing.cn/archivers/82.html

Mysql> Show variables like ' plugin_dir '; +---------------+--------------------------------+| variable_name | Value |+---------------+--------------------------------+| Plugin_dir | /application/mysql/lib/plugin/| #插件位置 +---------------+--------------------------------+1 row in Set (0.00 sec)

Main Library installation plug-in

mysql> install plugin rpl_semi_sync_master soname  ' semisync_master.so ';mysql>  show variables like  '%rpl_semi_sync% ';  #查看配置状态 +---------------------------------+--- ----+| variable_name                    | value |+---------------------------------+-------+| rpl_semi_ sync_slave_enabled     | off   | |  rpl_semi_sync_slave_trace_level | 32    |+-------------------------------- -+-------+2 rows in set  (0.00 sec) mysql> set global rpl_semi_sync_ master_enabled = 1;  #开启Semi-sync function Mysql> set global rpl_semi_sync_master_ timeout = 10000;  #设置超时时间为10秒mysql > show variables like  '%rpl_semi_sync% ' ; +------------------------------------+-------+|&nbsP 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_no_slave | on    |+----------------------------- -------+-------+

Add to config file in my.cnf

[mysqld]rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 10000

From the Library

Installing plugins

mysql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';mysql> show variables like '%rpl_semi_sync% ' ;+---------------------------------+-------+| variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | OFF | | Rpl_semi_sync_slave_trace_level |  |+---------------------------------+-------+2 rows in Set (0.00 sec) mysql> Set global rpl_semi_sync_slave_enabled = 1;  #开启插件mysql > Stop slave; #重启从库IO进程mysql > Start slave;

mysql> show global status like  '%rpl_semi% '; +------------------------------------- -------+-------+| variable_name                               |  value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients                | 1      |  #正常连接的客户端台数 | 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              | 2      | |  Rpl_semi_sync_master_no_tx                  | 1     | |  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     |+--------------------------------------------+- ------+14 rows in set  (0.01 SEC)

Do a few simple tests: the main library inserts a row of data

Mysql> SELECT * from test;+----+--------+| ID |  Name |+----+--------+| 1 | Xiaoming |+----+--------+1 row in Set (0.00 sec) mysql> INSERT INTO test values (2, ' test '); Query OK, 1 row Affected (0.00 sec) #可以看见延迟几乎可以忽略

From the library side, the data is OK.

Mysql> SELECT * from test;+----+--------+| ID |  Name |+----+--------+| 1 |  Xiao Ming | | 2 | Test |+----+--------+2 rows in Set (0.00 sec)

Failure of the Slave library under simulation

mysql> stop slave Io_thread; Query OK, 0 rows affected (0.01 sec)

We insert the data in the master and slave

Mysql> insert into test values (3, ' test '); query ok, 1 row affected  (10.01 sec)   #插入时间很久, 10 seconds, 10 seconds timeout, auto switch back to asynchronously replicated MySQL > show global status like  '%rpl_semi% '; +------------------------------------------ --+-------+| variable_name                               |  value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients                | 1      | |  rpl_semi_sync_master_net_avg_wait_time     | 498   | |  rpl_semi_sync_master_net_wait_time         | 1495   | |  rpl_semi_sync_master_net_waits              | 3     | |  Rpl_semi_sync_master_no_times               | 3     | |  Rpl_semi_sync_master_no_tx                  | 2     | |  Rpl_semi_sync_master_status                 | OFF   |  #关闭状态 | rpl_semi_sync_master_timefunc_failures      | 0     | |  rpl_semi_sync_master_tx_avg_wait_time      | 799   | |  rpl_semi_sync_master_tx_wait_time          | 1599   | |  rpl_semi_sync_master_tx_waits              | 2      | |  rpl_semi_sync_master_wait_pos_backtraverse | 0     | |  Rpl_semi_sync_master_wait_sessions         | 0      | |  Rpl_semi_sync_master_yes_tx                 | 2     |+--------------------------------------------+- ------+14 rows in set  (0.00 SEC)

recovery io thread

mysql> start slave io_thread; query ok, 0 rows affected  (0.00 sec) mysql> show global status  like  '%rpl_semi% '; +--------------------------------------------+-------+| variable_name                                | value |+------------------------ --------------------+-------+| rpl_semi_sync_master_clients                | 1     | |  rpl_semi_sync_master_net_avg_wait_time     | 4526  | |  rpl_semi_sync_master_net_wait_time         | 18107  | |  rpl_semi_sync_master_net_waits             | 4     | |  Rpl_semi_sync_master_no_times               | 3     | |  Rpl_semi_sync_master_no_tx                  | 2     | |  Rpl_semi_sync_master_status                 | ON    |  #重新开启了 | rpl_semi_sync_master_timefunc_ failures     | 0     | |  rpl_semi_sync_master_tx_avg_wait_time      | 799   | |  rpl_semi_sync_master_tx_wait_time          | 1599   | |  rpl_semi_sync_master_tx_waits               | 2     | |  rpl_semi_sync_master_wait_pos_backtraverse | 0     | |  Rpl_semi_sync_master_wait_sessions         | 0      | |  Rpl_semi_sync_master_yes_tx                 | 2     |+--------------------------------------------+- ------+14 rows in set  (0.00 SEC)

This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1828769

MySQL semi-synchronous replication practice