MySQL Semi-synchronous replication

MySQL Semi-synchronous replication
A Introduced
Starting from MYSQL5.5, semi-automatic replication is supported. Previous versions of MySQL replication were asynchronous (asynchronous), and the master library did not Tabeku progress after performing some transactions. If the repository is unfortunate, and more unfortunately, the main library is now crash (for example, downtime), then the data in the standby library is incomplete. In short, in the event of a failure of the main library, we cannot use the standby to continue to provide data-consistent services.
Semi-synchronous replication (Semi synchronous Replication) is to some extent guaranteed that committed transactions have been passed to at least one repository.
In semi synchronous, only the transaction is guaranteed to be delivered to the repository, but it does not ensure that it has been completed on the standby.
The purpose of introducing the semi-synchronous replication function is to ensure that at least one slave data is intact at the time of the master problem. In the case of time-outs, it is also possible to temporarily transfer to asynchronous replication, guaranteeing the normal use of the business until a salve is catching up and continuing to switch to semi-synchronous mode.
After MySQL loads and opens the Semi-sync plugin, each transaction waits for the repository to receive the log before returning it to the client. If you do a small transaction, and the latency of the two hosts is small, then Semi-sync can achieve 0 data loss in the event of a small loss of performance.
So we can do multiple repositories, and any one repository receives the completion log, and the main library can be returned to the client.

Two Configuration
2.1 Prerequisites
MySQL version: 5.6.34
Master:mysql-db01
Slave:mysql-db02
Have finished master-slave replication (asynchronous replication)
2.2 Master Configuration
#登录数据库
[Email protected] ~]# mysql-uroot-poldboy123
#查看是否有动态支持
Mysql> show global variables like ' have_dynamic_loading ';

+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES | #YES证明有动态支持
+----------------------+-------+
#安装自带插件 (with plug-in location)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#修改配置文件
[Email protected] ~]# VIM/ETC/MY.CNF
Add the following #在 the [Mysqld] tab (without restarting the library)
[Mysqld]
Rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
#半同步插件的位置
[Email protected] plugin]# pwd
/application/mysql/lib/plugin
[email protected] plugin]# LL
Total 2140
-rwxr-xr-x 1 7161 wheel 15437 Jan adt_null.so
-rwxr-xr-x 1 7161 wheel 25619 Jan auth.so
-rwxr-xr-x 1 7161 wheel 12364 Jan auth_socket.so
-rwxr-xr-x 1 7161 wheel 25096 Jan auth_test_plugin.so
-rw-r--r--1 7161 wheel 227 Jan Daemon_example.ini
Drwxr-xr-x 2 root root 4096 Mar 6 21:18 Debug
-rwxr-xr-x 1 7161 wheel 567877 Jan innodb_engine.so
-rwxr-xr-x 1 7161 wheel 42633 Jan libdaemon_example.so
-rwxr-xr-x 1 7161 wheel 584163 Jan libmemcached.so
-rwxr-xr-x 1 7161 wheel 17539 Jan mypluglib.so
-rwxr-xr-x 1 7161 wheel 18111 Jan qa_auth_client.so
-rwxr-xr-x 1 7161 wheel 23806 Jan qa_auth_interface.so
-rwxr-xr-x 1 7161 wheel 12926 Jan qa_auth_server.so
-rwxr-xr-x 1 7161 Wheel 415334 Jan semisync_master.so # Master Plugin
-rwxr-xr-x 1 7161 Wheel 250238 Jan semisync_slave.so # Slave plug-in
-rwxr-xr-x 1 7161 wheel 139172 Jan validate_password.so
2.3 Check if Master is successfully installed
#登录数据库
[Email protected] ~]# mysql-uroot-poldboy123

#检查安装
Mysql> select * from Information_schema.plugins;
Mysql> Show variables like ' rpl% ';
+------------------------------------+----------+
| variable_name | Value |
+------------------------------------+----------+
| rpl_semi_sync_master_enabled | On |
| Rpl_semi_sync_master_timeout | 1000 |
| Rpl_semi_sync_master_trace_level | 32 |
| Rpl_semi_sync_master_wait_no_slave | On |
| Rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
#查询复制状态
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 | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| 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 |
+--------------------------------------------+-------+
Rows in Set (0.03 sec)
2.4 Slave configuration
#登录数据库
[Email protected] ~]# mysql-uroot-poldboy123
#安装slave半同步插件 (The plugin location is shown above)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启io线程使其生效
mysql> stop slave Io_thread;
mysql> start slave io_thread;
#编辑配置文件 (no need to restart the database)
[Email protected] ~]# VIM/ETC/MY.CNF
#在 [mysqld] tab, add the following:
[Mysqld]
Rpl_semi_sync_slave_enabled =1

Note: Relevant parameter description
Rpl_semi_sync_master_timeout=milliseconds
Set this parameter value (MS), in order to prevent the semi-synchronous replication from clogging if no acknowledgement is received, if Master does not receive any acknowledgement before timing out, it will revert to normal asynchronous replication and continue with a copy operation that is not semi-synchronous.
rpl_semi_sync_master_wait_no_slave={on| OFF}
If a transaction is committed, but Master does not have any slave connections, it is not possible to send the transaction elsewhere for protection. By default, Master continues to wait for the slave connection within the time limit and confirms that the transaction has been correctly written to disk.
You can use this parameter option to turn off this behavior, in which case the master reverts to asynchronous replication if there is no slave connection.

Three Test semi-synchronous
#创建两个数据库, Test1 and Test2.
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.04 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row 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 | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | On |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| rpl_semi_sync_master_wait_sessions | 0 |
#此行显示2, which indicates that the two libraries you just created have been semi-synchronized
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
Rows in Set (0.06 sec)
#从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| Test1 |
| Test2 |
+--------------------+
#关闭半同步 (1: Turn on 0: OFF)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步状态
Mysql> show global status like ' rpl_semi% ';
+--------------------------------------------+-------+
| variable_name | Value |
+--------------------------------------------+-------+
| rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| 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 |
+--------------------------------------------+-------+
Rows in Set (0.00 sec)

#再一次创建两个库
mysql> CREATE DATABASE test3;
Query OK, 1 row Affected (0.00 sec)

mysql> CREATE DATABASE test4;
Query OK, 1 row 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 | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| rpl_semi_sync_master_wait_sessions | 0 |
#此行还是显示2, it turns out that the two bars just didn't perform a half-sync, otherwise it would be 4.
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
Rows in Set (0.00 sec)

Note: It is not difficult to find, in the query semi-synchronous state is, turn on the semi-synchronous, the query will have a delay time, after the shutdown is not

MySQL Semi-synchronous replication