Background:
Starting from MySQL 5.5, the Semi-synchronous Replication mode (Semi-synchronous Replication) was introduced. In this mode: the master waits until the binlog has been successfully transmitted and written to at least one slave's relay log before committing, thus ensuring data synchronization safety at the cost of some transaction throughput. Semi-synchronous mode is actually implemented as a plugin in MySQL 5.5, with different plugins used for the master and slave.
We conducted some tests regarding this feature of MySQL 5.5, and the details are as follows:
I. Environment Setup
1. Operating System and Database Environment:
OS: Ubuntu 12.04 LTS /n /l
mysql: Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.2
2. Enabling semi-sync feature:
Mysql generates two .so files in the plugin directory after compilation: semisync_slave.so semisync_master.so. Install them:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Master:
$vi my.cnf
…
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
…
Slave:
$vi my.cnf
…
rpl_semi_sync_slave_enabled=1
These parameters can be modified dynamically.
Execute on Master:
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
Execute on Slave:
set global rpl_semi_sync_slave_enabled=1;
Restart slave:
Check status:
show status like "%rpl_semi%";
mysql> show status like "%rpl_semi%";
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 2 |
| 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 |
| Rpl_semi_sync_slave_status | OFF |
+——————————————–+——-+
This indicates that semi-sync has been enabled and the master has 2 connected clients.
II. Test Results:
1. Disable semi_sync, use asynchronous replication, continuously insert three million rows into the master node, and reboot the master host during the insertion process.
Test Results:
Row count on Master:
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 1030000 |
+———-+
Slave 1:
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 870000 |
+———-+
Slave 2:
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 690000 |
+———-+
2. As a comparison to Test 1, enable semi_sync, use semi-synchronous replication, continuously insert three million rows into the master node, and reboot the master host during the insertion process.
Test Results:
Master
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 1050000 |
+———-+
1 row in set (0.00 sec)
Slave 1
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 1050000 |
+———-+
1 row in set (0.00 sec)
mysql>
Slave 2
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 1040000 |
+———-+
1 row in set (0.00 sec)
Conclusion: After repeated tests, it was found that when using reboot (which yields the same result as using the service mysql stop command) to shut down the host, semi-synchronous replication ensures that at least one slave node's data remains consistent with the master node. Asynchronous replication rarely prevents data loss on slave nodes.
3. Disable semi_sync, use asynchronous replication, continuously insert three million rows into the master node, and directly cut off the power to the master node during the insertion process.
Test Results:
Master 53:
Database changed
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2118997 |
+———-+
1 row in set (0.00 sec)
mysql>
Slave 74
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2390000 |
+———-+
1 row in set (0.00 sec
Slave 141
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2390000 |
Strangely, both slave nodes had more data than the master.
Second test:
Master:
Database changed
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 177536 |
+———-+
1 row in set (0.00 sec)
mysql>
Slave 1
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 450000 |
+———-+
1 row in set (0.00 sec
Slave 2
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 430000 |
+———-+
1 row in set (0.00 sec)
In the second test, although the two slave nodes were inconsistent with each other, both had more data than the master.
Checking the slave node status at this point shows error messages:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000017' at 27462756, the last event read from '/var/log/mysql/mysql-bin.000017' at 4, the last byte read from '/var/log/mysql/mysql-bin.000017' at 4.'
Last_SQL_Errno: 0
Result Analysis: Repeated power-off tests consistently showed that the slave nodes ended up with more data than the master node. After analysis, we believe that in MySQL, the slave node likely reads directly from the master node's cached data. If a power failure occurs before this cached data is written to disk, this part of the data will be lost on the master node. Meanwhile, because the slave node has already received this cached data, it will have more data than the master node.
4. As a comparison to Test 3, enable semi_sync, use semi-synchronous replication, continuously insert three million rows into the master node, and cut off the power to the master node host during the insertion process.
Test Results:
Master 53:
Database changed
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2118997 |
+———-+
1 row in set (0.00 sec)
mysql>
Slave 74
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2390000 |
+———-+
1 row in set (0.00 sec
Slave 141
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 2390000 |
Second test:
Master:
Database changed
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 177536 |
+———-+
1 row in set (0.00 sec)
mysql>
Slave 1
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 450000 |
+———-+
1 row in set (0.00 sec
Slave 2
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 430000 |
+———-+
1 row in set (0.00 sec)
Conclusion: In the case of a direct power cut, enabling semi-synchronous replication makes little difference; the slave nodes will still end up with more data than the master node.