MySQL HA reloaded by Ivan Zoratti
MySQL HA reloaded – old tricks and cool new tools to guarantee high availability to your MySQL Servers by Ivan Zoratti of SkySQL. This talk is a little longer, so check out: HA Reloaded – many ways to provide High Availability. The slides are already online.
Questions to ask: which level of high availability do I need? Do I require no loss of data? Do I need failover or is switchover enough? Can I provide a reasonable service when a component is down? Remember, five nine’s high availability also means a lot of infrastructure costs.
Other things to clarify: availability vs scalability. HA costs. HA for your entire architecture, not just for your database servers. Review your SLAs.
The best high availability solution today is combined solutions.
MySQL replication – asynchronous & semi-synchronous (lots of people use MySQL 5.5, about 4 people in room were on Percona Server – question asked due to semi-sync replication only being available in 5.5 & greater), there are pros & cons of row based replication vs statement based replication.
MySQL Replication via Multi-Master replication Manager (MMM). Features such as monitoring, automatic failover, data backup & resync. Unfortunately, it has some problems with the stability & automatic failover. The project is not improved anymore, so there are other solutions that you should consider today.
MySQL Replication with MHA is a preferred solution. Something to consider: –read-only=1 and log-bin on slaves. Master IP failover. FIltering rules. Multi-tier replication.
Tungsten Replicator – open source, heterogenous replication. Truly multi-master and fan-in with Global ID. Per-schema multi-thread. You can also use it to replicate to Postgresql, Oracle and other databases. There is also Tungsten Enterprise.
Synchronous replication with DRBD is typical for active/standby environments. People don’t really like this because they feel that there is a server doing nothing. You can always do it active/passive. It works with InnoDB only.
Synchronous replication with Galera works for InnoDB. Its multi-master with no SPOF. Its new/young technology so you may find some issues with it. Application failover must be managed, but the conflict resolution is quite tricky (when you commit a transaction you might be fine, but you may have transactions that are removed due to conflicts).
There is a commercial SchoonerSQL that provides synchronous master-slave replication for InnoDB. Its defined explicitly as a master-slave solution.
Active/Passive clusters using Shared Storage. Points to consider are the fact that redundancy & replication must be guaranteed by the shared storage. InnoDB only. What about filesystems?
Virtualized environments – data storage, high availability & load balancing are provided and managed by the virtualized software. The faults are handled by the software, not the database.
There is also geographical replication for disaster recovery, having a master-master asynchronous replication is used to update the backup data centre. There is also storage snapshots for disaster recovery (not-specific to MySQL, its storage systems based, use only InnoDB).
There is also MySQL Cluster but there is another presentation about this later at FOSDEM. Very nice closing slide, “The absolutely necessary comparison chart” which some may disagree, but Ivan thinks is the best way forward.
