How to handle failover for wordpress woocommerce store?

I am working on a project with multiple WordPress + Woocommerce stores. We have 3 dedicated servers. As the traffic grows. Sometimes, the server is down. I am looking for a failover solution that works with those wordpress+woocommerce sites.

First, I found CloudFlare. It will automatically failover to the other “A” record. So I just need to replicate all files and database to other server as backup. When the first server is down, it will point to another server.

But this comes with another problem. When it points to the second server. All the data in the database goes back to when the backup is created. And when the original server is fixed, the domain points back to the original server. Then all data written in second server is gone as it didn't write into the original server.

Then I looked into the master-slave replication of database. Assuming the original server is master and the second server is slave, each time when a customer bought a product or wrote a comment, it writes into the master database. Then the slave database sync the master database. But the problem is that when the original server is down. It switches to the slave database with is read-only. So even the customers could load the website. They could not make purchase or comment.

I have no idea what to do next. Please help. Thank you. We would like to archive the followings:

• switch servers when one is down
• sync database anytime. No data loss during the switching process

Solution 1:

Since you have 3 dedicated servers, you can use a Galera 3-node cluster in order to have active-active database clusters, that way you don't have to point to a specific node, but rather they all work as one.

https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/

You will also need a load balancer, but you can use haproxy on each of the servers with a virtual-IP in order to load balance and make the setup in HA mode.

Then, you will also need 3 instances of WP, so you could use something like a NFS disk to export the same mount point to all servers, or, sync via a cron to the servers every say, 1 minute.

Of course, this is much easier with a cloud provider, but it's definitely doable with your 3-dedicated servers setup.