I've been wondering since I started working with replication. Currently my setup is master/master replication. But I've been researching that master/slave replication is faster compared to master/master replication. My question with master/slave replication is what if the visitor has opened the website in slave server and submit a comment on one of the article? Since it's only a slave, it cannot send back the changes to the master server. Is the slave server good for backup only? Or anyone here use it for live site? And how would you deal with site's comments or anything that a visitor can write to the database on slave server.
On a website, you have typically way more read traffic in the database than write traffic. if the website and cms you use is able to split read and write traffic, then you can use a setup where read traffic is directed to a MySQL slave database while write traffic goes to the master database. If this is nothing that your site or cms can do, then you need either MySQL master/master replication or a MySQL database cluster. But the question is, do you really need MySQL replication? In the past, I used MySQL replication as well on some of my systems, master/master and also master/slave. Especially when it comes to master/master replication, my experience was that the added complexity of the setup caused more downtime in the long run than what a non replicated setup would have caused me. So the uptime of a simple single MySQL node was in the end way better than the one of a replicated MySQL master/master database system. My personal strategy for is now to use virtualization for all my server systems which means I can do snapshots whenever I want (e.g. before an update) plus I can restore the virtual machine completely from backup within minutes, if needed. And if one wants to have a really up to date backup e.g. when you ran a big e-commerce system, one can still set up a master/slave config for MySQL but use the slave just as a backup spare system and in case of an emergency, you can switch your site to use that or use it as copy to restore on the master.
Basically, you can use any kind of Linux virtualization, the most common one is probably KVM. There are container solutions like LXC which are more lightweight, but they have their own issues like lack of quota support. In the past I used OpenVZ on my own root servers, but the classic OpenVZ does not exist anymore. Instead of virtualizing my systems on my own root servers I then switched to using the cloud from hetzner.com, which works well for me and is a cost-effective solution. There are many Cloud providers out there, the more expensive ones like Amazon or Google Cloud or providers like Hetzner, Digitalocean, or Upcloud.
