I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine.
I prefer to use MySQL Master – Slave Replication. However, this option provides us Manual fail over of Application from Master to Slave.
- What could be the maximum difference time to have Sync between Master and Slave ?
- Is there any option of having High Availability other than this approach ?
While Percona XtraDB Cluster is great for Multimaster Replication and InnoDB, it is not good for every type of workload. Bill Karwin states MySQL’s Async Replication could be better due to workload : What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
Under that setup, the Master (aka DRBD Primary) has a data volume mounted on DRBD, MySQL running, and a ucarp mechanism for automatic failover.
The Slave (aka DRBD Secondary) does not have the data volume mount. MySQL is not running. DRBD is doing everything in terms of having a Block Device synchronized with the DRBD Primary.
DRBD is best used when having failover set up withing a single data center. DRBD should never be setup to with DRBD Primary and Secondary in two different data center due to synchronization latency. I have discussed this before in these posts
Mar 29, 2011: MySQL high availability, failover and replication with Latency
Mar 26, 2012: 3 Databases, Best Replication Setup?
Aug 20, 2012: Best solution for cross-datacenter MySQL master-slave replication
I recommended this for doing PostgreSQL with DRBD : PostgreSQL Failover – What tools should I use?
The drawback in using MySQL/DRBD for High Availability is that fact there is only one instance of MySQL running. The benefit i shaving a full copy of the data on disk. Even a hard failover will have InnoDB crash recover bring back uncommitted data that is written in the double write buffer (inside ibdata1).
If you want it all, use DRBD Clusters inside a data center but have DRBD Primary in one data data center using MySQL Replication in Circular Fashion with a DRBD Primary in another data center.
You should look at MHA, a perl script that performs the failover automatically for mysql replication.
By far the best resource for this kind of thing that I have found
is here, Henrik Ingo’s slides, which give a great summary.
He’s contributed to both the MySQL and Drizzle codebases and is an ex-MySQL employee.
He appears to favour a multi-master Galera solution (Open Source to boot!).