How to Setup MySQL Semi Sync Replication

A post from dbpandit

Async Replication?

By Default, MySQL supports async replication where Master commits transactions and respond to client. It doesn't wait for slaves to commit that transaction. In such replication, slaves might lag behind however, it doesn't impact master in any way.

Semi-Sync Replication?

In Semi-sync replication, Master doesn't respond to client until one of its slaves respond after pooling data to its relay log and flush it to disk. Though, There is a problem with mentioned semi sync replication i.e. master db just delay respond to client until it receive response from slave however, it actually committed way before that time. In such a case, if master crashes and no slave received that transaction but application has seen it by that time. Such situations are called 'phantom reads'. In MySQL 5.7, Master commit any transaction at its end only after getting response from any of the slave or till rplsemisyncmastertimeout limit.

How to setup Semi-Sync Replication?

Semi-sync replication has been in place since MySQL 5.5 as a plugin. We can use it by installing that plugin Master -> 'semisyncmaster.so', Slave -> 'semisyncslave.so'.

Verify First

show variables like 'rplsemisync%';
If nothing comes in output, Plugin is not installed.

Install Plugin:

You need to install semisync_master.so on master and semisync_slave.so on slave

On Master: mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

On Slave: mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

After installing above plugin, you can see it in your plugin list with following command :
mysql>show plugin;

If plugin named : rplsemisync_master is available in list, you got it.

You should also be able to see variables by running :

mysql> show variables like 'rpl_semi_syn%';

+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 5000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Enable semi-synchronous replication
On Master :

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 5000;

By Default, It's 10 secs.

On Slave :

SET GLOBAL rpl_semi_sync_slave_enabled = 1;`
mysql> stop slave io_thread;
mysql> start slave io_thread;

Monitoring :

On Master :
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     |

+--------------------------------------------+-------+

On replication slave we have only 1 variable Rplsemisyncslavestatus.3 varaibles are important for us to watch
Rpl_semi_sync_master_status -> Whether semi-synchronous replication currently is operational on the master
Rpl_semi_sync_master_yes_tx -> The number of commits that were acknowledged successfully by a slave
Rpl_semi_sync_master_no_tx -> The number of commits that were not acknowledged successfully by a slave
Currently we see that Rplsemisyncmasterstatus is ON, that means the semi-synchronous replication is working fine.

Ref Link