MySQL 5.6 Replication: All That Is New, On-Demand
By Mat Keep on Apr 04, 2013
The new MySQL 5.6 GA release delivers a host of new capabilities to support developers releasing new services faster, with more agility, performance and security .
One of the areas with the most far-reaching set of enhancements is MySQL replication used by the largest web, mobile and social properties to horizontally scale highly-available MySQL databases across distributed clusters of low cost, commodity servers.
A new on-demand MySQL 5.6 replication webinar takes you on a guided tour through all of those enhancements, including:
- 5x higher master and slave performance with Binary Log Group Commit, Multi-Threaded Slaves and Optimized Row-based replication
- Self healing replication with Global Transaction Identifiers (GTIDs), utilities and crash-safe masters and slaves
- Replication event checksums for rock-solid data integrity across a replication cluster
- DevOps features with Time-Delayed Replication, Remote binary log backup and new CLI utilities
We had some great questions during the live session - here are a few:
Question: When multi-threaded slave is configured, how do you handle multi-database DML?
Answer: When that happens the slave switches to single-threaded mode for that transaction. After it is applied, the slave switches back to multi-threaded mode.
Question: We have seen in past that replication would sometime break when complex queries get fired or alter statements take place. Neither row based or Statement based replication guaranteed 100% fail safe replication. Does MySQL5.6 address this issue ?
Answer: Replication can fail for several specific reasons. There is no general algorithm enabling "100% fail safe" replication. But we made sure that all that are unsafe are either converted to row based replication or issue warnings.
Question: We are looking for a HA cluster solution where most of the websites are Wordpress based. Do you have any recommendations on what kind of replication solution should we choose? The tables are using innodb
Answer: Using MySQL 5.6 with the master/slave replication we are discussing today, configured with GTIDs and using the mysqlfailover utility will provide you High Availability for your Wordpress cluster and InnoDB tables. Nothing you need to add - it is all part of MySQL you download today
Question: I have question about data loss and translating my-bin.000132 to my-bin.00101 when a master fails?
Answer: That's exactly the problem GTIDs address. Without GITDs it's hard to know the correct position on my-bin.000101 that corresponds to my-bin.000132.
With GTIDs we don't need to know positions, only what the slave has already applied, knowing that it's simple to send to the new master any missing transactions that are on other slaves. The data that was already on my-bin.00132 can be ensured that it was sent to a slave using Semi-syncronous replication.
Question: How do the new utilities work with a Pacemaker/Corosync setup?
Answer: The utilities integrate with Pacemaker/Corosync via extension points. The MySQL Pacemaker agent can call rpladmin to perform a switchover.
Question: Where can I learn more about supported HA solutions for MySQL?
Answer: From the Guide to MySQL HA solutions. I hope you find this useful
Question: Are there any plans to move replication from host-based to either 'database' or 'table' based?
Answer: Current MySQL replication supports replication of specific databases or tables from a Host using Replication filters. This means, we support Replication of a host (all data which could be replicated), replication of specific databases per host and specific tables per host. (You can ignore databases and tables using filters too).
For these and other questions, along with the full replay of the webinar, register here