Authors: Charles Bell, Mat Kindahl and Lars Thalmann
Publisher: O'Reilly
Pages: 733
ISBN: 978-1449339586
Aimed at: MySQL administrators and developers who need to keep their database running.
Rating: 4.5
Reviewed by: Kay Ewbank

If you have a database that needs to be available no matter what happens, how can you make sure it doesn't suffer from outages?

This book looks at the techniques used to run a highly available MySQL database using replication and clustered servers. The authors assume you'll have basic knowledge of SQL and MySQL administration, but not a great deal.

The book is split into two halves, one on high availability and scalability, the other on monitoring and managing. Of the two, I thought the first half was more on topic. The authors use the MySQL Replicant Library, a Python library for working with sets of servers, in most of the examples throughout the book, and the book starts with an introduction to high availability and the Replicant Library. Replication fundamentals in terms of the manual and automated procedures for setting up replication are tackled next, including an introduction to the binary log. MySQL uses this to manage replication, and there's a detailed chapter on the structure of the log and the statements you use to interact with it. I felt this sometimes got bogged down with syntax where a higher level 'why' you're doing things might have been more useful, but at least you get the detail.

The next two chapters address the heart of replication – for high availability and scale out. The chapter on high availability includes short Python scripts for tasks such as finding the best slave, and slave promotion, while the scale out chapter covers topologies and techniques for getting better read scalability on large data sets. Both chapters are clear and well written, and probably make it worth buying the book. The following chapter, on sharding, is also good. There’s a useful chapter on more advanced replication topics such as the best way to promote slaves to masters, avoiding database corruption in the case of a crash, and different sorts of replication (multisource, multi-threaded and row-based). The first half of the book ends with a chapter on using the MySQL Cluster tool for high availability.

The second half of the book is less about the concepts of high availability, and more about the day-to-day tools for managing and keeping track of a highly available database. There’s an introductory chapter that tells you what you should be monitoring, then another introducing the different tools for monitoring activity and performance. This chapter ends with a nice little set of checklists of what might be causing different problems such as slow queries, slow applications and slow replication.

The next two chapters look in detail at different elements for monitoring – the storage engine and replication. InnoDB and MyISAM are given equal billing in the chapter on storage engine monitoring. Given there are 50 variables that control the behavior of InnoDB and 40 status variables that can tell you about it, you’re not going to get a complete picture of what you can do in 20 pages, but the main elements – Show Engine, monitoring logfiles and the buffer pool – are covered, along with troubleshooting InnoDB. In contrast, there are few things you can monitor on MyISAM, so the pages on it concentrate largely on the key cache, optimizing disk storage, and monitoring memory use.

There’s a good chapter on replication troubleshooting looking at problems on the master and slaves, and a nice summary of best practices, some of which is obvious but all well worth doing. A chapter on ‘protecting your investment’ is actually about backups and disaster recovery, and the book finishes with chapters on MySQL Enterprise Monitor and the MySQL utilities.

Perhaps the most useful part of the book is in the first appendix. This consists of a list of replication tips and tricks, and has ideas for running, diagnosing, repairing and improving replication. While not all will be applicable, they give good ideas about how to handle the problems if they do occur.

While the technical material is good, one thing that irritated me about this book was a short story running through the book at the start of each chapter about a clueless database administrator learning about MySQL. I realize the authors were attempting to liven up the usual intro of ‘this chapter looks at how to use load balancing and ways of improving recovery in replication’, but the adventures of Joel (the administrator) and his long suffering boss were just distracting. It seemed as though the authors felt the topic was too boring to keep the reader’s attention without some sort of ‘sweetener’, but I just kept hoping one of the server towers would fall over and crush Joel so we could get on without him.

However, if you can ignore the further adventures of Joel, this is a good way to learn about MySQL high availability. It won’t teach you everything, but it will take you a long way into the topic. The authors are good at including examples in SQL, Python and PHP, and overall I’d recommend it.

Related Article

MySQL and MariaDB Database Books in Programmer's Bookshelf