3

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.

QUESTIONS

  • 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 ?
1

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?

If you are still looking for synchronous replication, you could look into using DRBD with UCARP or Linux Heartbeat for DB VIP and Failover Management.

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

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.

0

You should look at MHA, a perl script that performs the failover automatically for mysql replication.

0

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!).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.