Fixing MySQL Replication

Introduction


MySQL has some powerful replication technology. It can be configured to replicate everything, or subsets of schemas or even individual tables. Those slave databases can themselves be masters as well, feeding still more databases a stream of transactions from the master.


Despite the technology’s obvious power, it is not bulletproof. There are times when you find replication can break, throwing up errors in the slave error-log, and stopping the receiving of transactions in their tracks.


Picking up from where we left off in our last article, we’ll discuss what to do to make your replication setup more resilient. It may not be bullet proof, but by being proactive, you’ll keep your data in sync better, and be alerted when it’s not.


Row-based Replication


As of MySQL 5.1 many of the issues we discuss here have been resolved with row-based replication. This allows MySQL to switch between statement and row-based replication as required by the SQL being written to the binary log. The mixed mode is not enabled by default. It was for earlier versions of 5.1, but they’ve switched to statement as default because of some common application code that breaks with mixed set. You can set this mode (mixed, statement or row) with the –binlog-format option.


As you might guess, there are pros and cons. Giuseppe Maxia says on updates to very large tables, there may be a performance hit.


Testing With A Sandbox Environment


If you haven’t setup your MySQL environment yet, well we commend you for staying ahead of the curve and reading about these problems before they find you. However, even if you already have, it can be quite a boon to have a sandbox environment on which to work, play, test, break, and fix replication. And there is!


Giuseppe Maxia has built a tool, which creates a MySQL sandbox just for this purpose. It creates four different instances of MySQL on the same physical server by running it on different ports, and with different config files. It also comes with handy scripts such as make_replication_sandbox. There are also scripts, which start and stop all the instances, and help you reach the right one when you want to. Even setting the MySQL prompt so you know where you are!


http://datacharmer.blogspot.com/2008/07/mysql-20-has-been-released.html


Verifying MySQL Replication Setup


Now that we’ve gotten you used to the idea that your data on your slaves is probably somewhat out of sync of your master, what can be done about it? The first step would be to find out where your problems lay. For that we’ll apply checksums to our tables.


You might wonder, if my master database is up and running, how can I do checksums on the tables. The folks who developed Maatkit thought of that. It does the checksums on the master, dumps the data into tables, and that data propagates through replication! Neat solution, I must say.


http://www.maatkit.org/tools.html


The MySQL Toolkit provides another checksum based solution, which you can also take a look at:


http://www.xaprb.com/blog/2007/02/26/introducing-mysql-table-checksum/


Monitoring MySQL Replication


As part of your enterprise wide monitoring, you’ll want to monitor your MySQL slaves. Nagios is a good tool to provide this support. If you’d like to go further, wrap up some of the checksum tools in a script, and call that from Nagios as a custom plugin.


Get a copy of Nagios here:


http://www.nagios.org/download/


Learn about writing Nagios plugins here:


http://nagiosplug.sourceforge.net/developer-guidelines.html


Further Reading


Now that you know your replication may have problems, and also how to find those problems, you’re way ahead of the game. Also knowing the things that are going wrong, you can search for some of those issues on Google more easily. Here are some other documents for further reading.


Baron Schwartz – How to Make MySQL Replication Reliable


General Replication documentation


Replication Features and Issues


Giuseppe Maxia’s – Advanced MySQL Replication Techniques


Conclusion


Replication in MySQL promises a lot. It’s fairly trivial to get up and running, almost too easy. But there are lots of headaches you’ll likely encounter along the way.


Follow our guidance, be proactive, and setup monitoring, and you’ll be on your way to a better more reliable replication setup for MySQL.


» See All Articles by Columnist Sean Hull

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at shull@iheavy.com or visit http://www.iheavy.com for more info about consulting services.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles