Is it possible: MySQL semisync and async replication on same master-slave pair?

Question

I want to use master-slave MySQL (or MariaDB) replication to remote server as data protection from hardware failures. I have two goals: to keep low response time and don't lose important data. Good solution would be an asynchronous replications for most of data to keep responsiveness of the system and semisynchronous replication for important tables (payments).

In best case, tables with sync/async replication should be in same database or on same mysql server. But as far as I understand mysql will use semisynchronous replication for all data, if master and slave has semisynchronous replication enabled.

Not so good way is to have two mysql instances as masters (one with semisync enabled and one with disabled). Then I'll need either two slaves or multisource replication. Multisource replication is still in dev version of MySQL and doesn't support semisync in MariaDB.

The only probably working way I found for now based on https://dba.stackexchange.com/a/41491/55235 M1

• DB1 (with async data)

M2

• DB1 (BLACKHOLE, async slave of M1)
• DB2 (with semisync data)

S1 (semisync slave of M2)

• DB1
• DB2

Is it possible to have semisync+async replication with one master-slave pair? If no, is described above scheme a viable solution?

Answer 1

Semisync Replication will switch to async on the fly if needed.

I mentioned this before in my old post MySQL Replication : 1 Slave / Multiple Masters

In that post I said

Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter without waiting for the SQL to be executed, only acknowledged. This keeps any intermittency that standard MySQL Asynchronous Replication would normally cause a MySQL Master to a minimum. In the event of any sudden network latency, MySQL Replication between DataCenters would switch from Semisync to Async. Once conditions improve, then MySQL Replication would it switch back to Semisynch.

In another old post of mine, With MySQL Replication, what level of resilience is possible?, I mentioned the option that shows that semisynch can switch to asynch and back:

rpl_semi_sync_master_wait_no_slave : With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.

Therefore, you cannot setup semisync and async replication on the same instance of MySQL. Then again, you don't need to since it is designed to toggle between them depending on response time or lack of response time.

BTW to setup Semisync Replication, the semisync plugin activated on Master and Slave. See my post Is MySQL Replication Affected by a High-Latency Interconnect? on how to install Semisync Replication.