How to implement mysql failover?

Question

MySQL

There is a small project working on php, with a mysql, rabbitmq base, business processes of a small company are tied to these services. Now we place everything in the Russian Federation on a small cheap VDS, everything suits, the ping is good, the speed is excellent, BUT periodically once every 2-5 months. the provider's network drops for 15 minutes, and at the most inopportune moment. There is a cheap VDS option abroad, literally for the same money you can get 3 VDS. At the moment when the server is unavailable, I want to interrupt A records in DNS and switch to the backup server, like a sync. files are more or less clear, there are a lot of ways. But what to do with the MYSQL database?
Make MASTER-MASTER replication? If server 1 is not available, but there are changes in the database on the standby server, what are the consequences? What solutions exist for this? What reefs are at implementation of such replication? I'm afraid that there is a big chance to screw up the MYSQL database.

Answer 1

1) master-master on mariadb - each server will have its own unique gtid, and it is maria, unlike the original mysql, that can resolve binary logs and even multi-replication well. But you need to at least set the auto_increment shift in the north so that it is unique
2) galera cluster - somewhere 30% slower - the transaction must go through on all nodes, only innodb engine, you need to make sure that there is no myisam, you need a quorum of nodes, that is, it is better not to do two nodes if one goes 100% offline
. In the first case, synchronization may be with a slight delay on complex changes, but the scheme itself is quite working.
All settings recipes are in Google :)

Answer 2

BUT periodically every 2-5 months. the provider's network drops for 15 minutes
This is not the problem when you need to fence a garden in which you are poorly versed. You'll make it even worse.
Surely there are providers without such failures. But I haven't met. Even though not everything depends on them.

Answer 3

Haproxy + galera + Xtradb by percona, look at ready-made ansible playbooks for deployment.
Don't forget about critical read in mysql part and "sticky connections" in haproxy.
Cluster for at least 3 years, so that there is no split brain, if you want Feng Shui, then 5 is better than nodes.