MySQL High Availability, 2nd Edition

Preface

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.

Appendixes

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:

Plain text

Indicates menu titles, table names, options, and buttons.

Italic

Indicates new terms, database names, URLs, email addresses, filenames, and Unix utilities.

Constant width

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.

Tip

This element signifies a tip or suggestion.

Note

This element signifies a general note.

Caution

This element indicates a warning or caution.

Using Code Examples

Supplemental material (code examples, exercises, etc.) is available for download at at http://bit.ly/mysqllaunch.

This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “MySQL High Availability, by Charles Bell, Mats Kindahl, and Lars Thalmann. Copyright 2014 Charles Bell, Mats Kindahl, and Lars Thalmann, 978-1-44933-958-6.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

Safari® Books Online

Note

Safari Books Online (www.safaribooksonline.com) is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.

Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.

Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://bit.ly/mysql_high_availability.

To comment or ask technical questions about this book, send email to: [e-mail].

For more information about our books, courses, conferences, and news, see our website at: http://www.oreilly.com.

Acknowledgments

The authors would like to thank our technical reviewers of this and the previous edition: Mark Callahan, Morgan Tocker, Sveta Smirnova, Luis Soares, Sheeri Kritzer Cabral, Alfie John, and Colin Charles. Your attention to detail and insightful suggestions were invaluable. We could not have delivered a quality book without your help.

We also want to thank our extremely talented colleagues on the MySQL team and in the MySQL community who have provided comments, including Alfranio Correia, Andrei Elkin, Zhen-Xing He, Serge Kozlov, Sven Sandberg, Luis Soares, Rafal Somla, Li-Bing Song, Ingo Strüwing, Dao-Gang Qu, Giuseppe Maxia, and Narayanan Venkateswaran for their tireless dedication to making MySQL the robust and powerful tool it is today. We especially would like to thank our MySQL customer support professionals, who help us bridge the gap between our customers’ needs and our own desires to improve the product. We would also like to thank the many community members who so selflessly devote time and effort to improve MySQL for everyone.

Finally, and most important, we would like to thank our editor, Andy Oram, who helped us shape this work, for putting up with our sometimes cerebral and sometimes over-the-top enthusiasm for all things MySQL. A most sincere thanks goes out to the entire O’Reilly team and especially our editor for their patience as we struggled to fit so many new topics into what was already a very large book.

Charles would like to thank his loving wife, Annette, for her patience and understanding when he was spending time away from family priorities to work on this book. Charles would also like to thank his many colleagues on the MySQL team at Oracle who contribute their wisdom freely to everyone on a daily basis. Finally, Charles would like to thank all of his brothers and sisters in Christ who both challenge and support him daily.

Mats would like to thank his wife, Lill, and two sons, Jon and Hannes, for their unconditional love and understanding in difficult times. You are the loves of his life and he cannot imagine a life without you. Mats would also like to thank his MySQL colleagues inside and outside Oracle for all the interesting, amusing, and inspiring times together—you are truly some of the sharpest minds in the trade.

Lars would like to thank his amazing girlfriend Claudia; he loves her beyond words. He would also like to thank all of his colleagues, current and past, who have made MySQL such an interesting place to work. In fact, it is not even a place. The distributed nature of the MySQL development team and the open-mindedness of its many dedicated developers are truly extraordinary. The MySQL community has a special spirit that makes working with MySQL an honorable task. What we have created together is remarkable. It is amazing that it started with such a small group of people and managed to build a product that services so many of the Fortune 500 companies today.