MySQL can be deployed in several ways, and that means you can choose a tailor-made path that best meets your needs. With simple services or development systems, many people are using a single server with some backups configured, and then simply take the downtime when a restore is needed.
As the application evolves, additional requirements will appear like hot backups, online schema changes, replication based high availability (which has some caveats). Also, because of asynchronous replication, you can end up having inconsistent data on the nodes and encounter replication errors, which you have to deal with.
Even later, when asynchronous or single-threaded replication is not enough, people can use Percona XtraDB Cluster, which has some of its own caveats, but you can also gain a lot from write set replication.
If the application gets really popular, sooner or later you have to think about write scalability, and implement sharding ultimately.
I will give a talk on this topic at next month’s RAMP conference in Budapest, Hungary. My presentation will provide an overview of some possible deployment and scaling scenarios, when it makes sense to use one or an other, and common pitfalls we typically see in our consulting practice. Please let me know if you plan on attending – and also feel free to post questions in advance below.
