Starting with MySQL 5.5 it is possible to configure semi-synchronous replication. As you probably know, MySQL replication is asynchronous by default. Asynchronous replication means that events/transactions applied on one server are not immediately applied on the other one.

In the specific context of MySQL such behavior means that the MySQL master writes transactions/events to the local binary logs. However, it has no idea when the slave(s) will get and process them. Of course, in case of a failover, such configuration can lead to some transaction loss since there is no guaranty that the transactions have been transmitted to the standby server(s).

If you want to avoid such a problem, semi-synchronous replication can help you. Basically, what semi-synchronous replication does is ensuring that a transaction/event has been written to at least one slave’s relay log and flushed to disk before doing the commit on the master node (Investigation pending). However, it doesn't mean that the transaction has been applied on the slave side, the application can be slightly delayed or may in the worst case fail. Of course, such semi-synchronous replication might impact performances. But this impact can be reduced by improving network performances.

The requirements in order to be able to configure semi-synchronous replication is to have a working replication and, as stated in the introduction, to have a MySQL version equal or higher than 5.5 installed. Before implementing semi-synchronous replication, two plugins have to be installed: One plugin on the slave server (semsiync_slave.so) and one plugin on the master (semisync_master.so). In case of a Master/Master Active/Slave setup, both plugins have to be installed on each server. It is best practice to install both since any slave server can be promoted to master and vice versa.

Installing plugins is a straight forward process as demonstrated below:

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

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

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

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

Once the plugins are installed, it is mandatory to configure the replication by editing the option/configuration file (my.cnf or my.ini). The following lines need to be appended to the option/configuration file on both slave and master servers.

rpl_semi_sync_slave_enabled =1
rpl_semi_sync_master_enabled =1

Once the modifications are done, MySQL has to be restarted.

mysql@mysql-qual1: ~/ [mysqld1] mysqld_multi stop 1
mysql@mysql-qual1: ~/ [mysqld1] mysqld_multi start 1

The « SHOW STATUS » command can help check that the modifications have been taken into consideration and the replication is working correctly.

mysql> 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             | 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                 | ON    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

Parameters such as “Rpl_semi_sync_master_clients », « Rpl_semi_sync_master_status” and “Rpl_semi_sync_slave_status” indicate that the semi-synchronous replication is working.

If a timeout occurs (higher than 10 seconds by default) without any slave having acknowledged the transaction, the master reverts to asynchronous replication without any message excepting changing the value of “rpl_semi_sync_master_status” from “ON” to “OFF”. In the same way, as soon as at least one semi-synchronous slave catches up, the master then returns to semi-synchronous replication. This behavior is acceptable for service continuity, but is a real weakness from an operating point of view, since this has to be carefully monitored.

Despite the fact that the timeout value can be changed by editing the variable “rpl_semi_sync_master_timeout”, monitoring a disaster situation is really tricky. Moreover, it is difficult to know what was the “last status before crash”. Was the semi-synchronous replication working or not? Are there any lost transactions? What was the last status of variable “rpl_semi_sync_master_status” before the crash ?

I have no definitive answers to these questions. One piece of solution is implementing a rigorous monitoring solution on your server to double check variables such as “rpl_semi_sync_master_no_times”  and “rpl_semi_sync_master_status ».

Continuous and cautious monitoring, as well as following best practice should be part of every high availability project anyway.