MySQL High Availability (2014)
The authors of this book have been creating parts of MySQL and working with it for many years. Dr. Charles Bell is a senior developer leading the MySQL Utilities team. He has also worked on replication and backup. His interests include all things MySQL, database theory, software engineering, microcontrollers, and three-dimensional printing. Dr. Mats Kindahl is a principal senior software developer currently leading the MySQL High Availability and Scalability team. He is architect and implementor of several MySQL features. Dr. Lars Thalmann is the development director and technical lead of the MySQL Replication, Backup, Connectors, and Utilities teams, and has designed many of the replication and backup features. He has worked on the development of MySQL clustering, replication, and backup technologies.
We wrote this book to fill a gap we noticed among the many books on MySQL. There are many excellent books on MySQL, but few that concentrate on its advanced features and applications, such as high availability, reliability, and maintainability. In this book, you will find all of these topics and more.
We also wanted to make the reading a bit more interesting by including a running narrative about a MySQL professional who encounters common requests made by his boss. In the narrative, you will meet Joel Thomas, who recently decided to take a job working for a company that has just started using MySQL. You will observe Joel as he learns his way around MySQL and tackles some of the toughest problems facing MySQL professionals. We hope you find this aspect of the book entertaining.
Who This Book Is For
This book is for MySQL professionals. We expect you to have basic knowledge of SQL, MySQL administration, and the operating system you are running. We provide introductory information about replication, disaster recovery, system monitoring, and other key topics of high availability. See Chapter 1 for other books that offer useful background information.
How This Book Is Organized
This book is divided into two parts. Part I encompasses MySQL high availability and scale-out. Because these depend a great deal on replication, a lot of this part focuses on that topic. Part II examines monitoring and performance concerns for building robust data centers.
Part I, High Availability and Scalability
Chapter 1, Introduction, explains how this book can help you and gives you a context for reading it.
Chapter 2, MySQL Replicant Library, introduces a Python library for working with sets of servers that is used throughout the book.
Chapter 3, MySQL Replication Fundamentals, discusses both manual and automated procedures for setting up basic replication.
Chapter 4, The Binary Log, explains the critical file that ties together replication and helps in disaster recovery, troubleshooting, and other administrative tasks.
Chapter 5, Replication for High Availability, shows a number of ways to recover from server failure, including the use of automated scripts.
Chapter 6, MySQL Replication for Scale-Out, shows a number of techniques and topologies for improving the read scalabilility of large data sets.
Chapter 7, Data Sharding, shows techniques for handling very large databases and/or improving the write scalability of a database through sharding.
Chapter 8, Replication Deep Dive, addresses a number of topics, such as secure data transfer and row-based replication.
Chapter 9, MySQL Cluster, shows how to use this tool to achieve high availability.
Part II, Monitoring and Managing
Chapter 10, Getting Started with Monitoring, presents the main operating system parameters you have to be aware of, and tools for monitoring them.
Chapter 11, Monitoring MySQL, presents several tools for monitoring database activity and performance.
Chapter 12, Storage Engine Monitoring, explains some of the parameters you need to monitor on a more detailed level, focusing on issues specific to MyISAM or InnoDB.
Chapter 13, Replication Monitoring, offers details about how to keep track of what masters and slaves are doing.
Chapter 14, Replication Troubleshooting, shows how to deal with failures and restarts, corruption, and other incidents.
Chapter 15, Protecting Your Investment, explains the use of backups and disaster recovery techniques.
Chapter 16, MySQL Enterprise Monitor, introduces a suite of tools that simplifies many of the tasks presented in earlier chapters.
Chapter 17, Managing MySQL Replication with MySQL Utilities, introduces the MySQL Utilities, which are a new set of tools for managing MySQL Servers.
Appendix A, offers a grab bag of procedures that are useful in certain situations.
Appendix B, shows an implementation for handling failovers with transactions if you are using servers that don’t support GTIDs.
Conventions Used in This Book
The following typographical conventions are used in this book:
Indicates menu titles, table names, options, and buttons.
Indicates new terms, database names, URLs, email addresses, filenames, and Unix utilities.
Indicates command-line options, variables and other code elements, the contents of files, and the output from commands.
Constant width bold
Shows commands or other text that should be typed literally by the user.
Constant width italic
Shows text that should be replaced with user-supplied values.
This element signifies a tip or suggestion.
This element signifies a general note.
This element indicates a warning or caution.