Breaking Semisynchronous Replication in MySQL 5.5

Question

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7.

My application is running and updating the database of the master server and same is being updated in the slave database server.

But due to some unknown reasons sometimes, Replication breaks.

On running the command:

SHOW STATUS LIKE 'Rpl_semi_sync%';

It gives this status:

'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', '338846'
'Rpl_semi_sync_master_tx_wait_time', '29479685'
'Rpl_semi_sync_master_tx_waits', '87'
'Rpl_semi_sync_master_wait_pos_backtraverse', '0'
'Rpl_semi_sync_master_wait_sessions', '0'
'Rpl_semi_sync_master_yes_tx', '3106'

Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave. Please help us in this regard.

Answer 1

The one thing you can probably do is to increase the sensitivity of the acknowledgement

Please look for this variables in /etc/my.cnf

[mysqld]
rpl_semi_sync_master_timeout=5000

When rpl_semi_sync_master_timeout (Default is 10000 or 10 seconds) is set to 5000, Replication should switch a Master from SemiSync to Async if no acknowledgement is received from the Slave in 5 seconds (5000 milliseconds). You may want to lower this even less than 5000.

You may need to check your network performance. As long as you not doing geographic distance replication, you should the following: On a Separate NIC, use a crossover cable over 192.168.xx.xx so as not have faster replication response. Also, check the switch for dropped packets.

You should not have to restart the server every time. As a quick-and-dirty band-aid, make up this SQL script:

STOP SLAVE IO_THREAD;
SELECT SLEEP(5);
START SLAVE IO_THREAD;

Simply write a cronjob to execute these commands every 5 minutes. Again, this is a band-aid. Otherwise, due diligence on the networking side is in order.