MySql Semisynchronous Replication

In the previous blog, we saw How to create MySql GTID-based Master Slave Replication – Single DB, Click here to read more. We will use the same configuration to convert it from asynchronous to semisynchronous.

MySQL replication by default is asynchronous. MySQL 8.0 supports an interface to semisynchronous replication that is implemented by plugins.

Step 1. Precheck for plugin: Make sure plugin_dir system variable is set correctly and semisync plugin is available before you attempt to install plugins. Please note I have used prompt command to change mysql prompt.

MASTER

mysql> show variables like '%plugin_dir%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /u01/mysql-8/lib/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

mysql> system ls -l /u01/mysql-8/lib/plugin/*semisync*
-rwxr-xr-x. 1 mysql mysql 669280 Sep 23 22:21 /u01/mysql-8/lib/plugin/semisync_master.so
-rwxr-xr-x. 1 mysql mysql 304448 Sep 23 22:21 /u01/mysql-8/lib/plugin/semisync_slave.so
mysql>

mysql> prompt mysql-master>
PROMPT set to 'mysql-master>'
mysql-master>

Replica/Slave

mysql> show variables like '%plugin_dir%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)


mysql> system  ls -l /usr/lib64/mysql/plugin/*semi*
-rwxr-xr-x. 1 root root 1677568 Sep 23 16:56 /usr/lib64/mysql/plugin/semisync_master.so
-rwxr-xr-x. 1 root root  788024 Sep 23 16:56 /usr/lib64/mysql/plugin/semisync_slave.so
mysql>

mysql> prompt mysql-slave>
PROMPT set to 'mysql-slave>'
mysql-slave>

Step 2. Install semisync Plugins: Use the below command to install semisync plugin in both master and replica/slave. And query to table INFORMATION_SCHEMA.PLUGINS to make sure plugins are installed and active.

MASTER

mysql-master>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.28 sec)

mysql-master>SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql-master>

Replica/Slave

mysql-slave>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.19 sec)

mysql-slave>SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.09 sec)

mysql-slave>

Step 3. Set system variable in my.cnf file: Set below parameter in my.cnf to below parameters applied after the server is restarted.

MASTER

[root@test-machine02 ~]# vi /etc/my.cnf
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=20000

Replica/Slave

[root@test-machine01 ~]# vi /etc/my.cnf
rpl_semi_sync_slave_enabled=1

Step 4. Apply system variable at runtime: Follow the below steps in the master and replica system. After applying variables in replica restart io_thread to cause the replica to connect to the source and register as a semisynchronous replica.

MASTER

mysql-master>SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.40 sec)

mysql-master>SET GLOBAL rpl_semi_sync_master_timeout = 20000;
Query OK, 0 rows affected (0.00 sec)

mysql-master>SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

Replica/Slave

mysql-slave>SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql-slave>SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

mysql-slave>
mysql-slave>STOP REPLICA IO_THREAD;
Query OK, 0 rows affected (0.06 sec)

mysql-slave>START REPLICA IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql-slave>

Step 5. Test replication: Execute few transactions in the master system after commit you will notice in the master status variable Rpl_semi_sync_master_yes_tx will increment to 1.

MASTER

mysql-master>use repl_test;
mysql-master>insert into repl_test7 select * from repl_test7;
Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql-master>select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.01 sec)

mysql-master>

Slave
mysql-slave>use repl_test;
mysql-slave>select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.01 sec)

mysql-slave>

mysql-master>SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| 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             | 1     |
| 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      | 576   |
| Rpl_semi_sync_master_tx_wait_time          | 576   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

mysql-master>

Replica/Slave

mysql-slave>use repl_test;
mysql-slave>select count(*) from repl_test7;
+----------+
| count(*) |
+----------+
|       28 |
+----------+
1 row in set (0.01 sec)

mysql-slave>

Reference: https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.