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)
- DB1 (BLACKHOLE, async slave of M1)
- DB2 (with semisync data)
S1 (semisync slave of M2)
Is it possible to have semisync+async replication with one master-slave pair? If no, is described above scheme a viable solution?