Effective MySQL: Backup and Recovery (2012)

INTRODUCTION

Disaster is inevitable. Total failure is avoidable.

While many organizations plan, practice, and invest for scalability, few plan and practice for business resilience as the result of a disaster or a “Choas Monkey*”. One of the most critical tasks of an operational database administrator (DBA) is to perform, test, document, and verify adequate backup and recovery procedures to ensure business continuity. While this may be considered a much loathed and less prioritized task, this is the single most comforting element in a well functioning and stable production environment. Backups are not just used for recovery. Other uses of backups that can be incorporated into daily operations to assist in the verification process can include additional scalability and higher availability infrastructure, testing, and benchmarking.

Understanding what limitations and quirks exist with the various approaches to MySQL backups is important in being confident that the crucial business information is backed up. The methods you have used to recover your information must meet your business needs to obtain a mean time to recovery (MTTR) and the recovery point objective (RPO).

MySQL provides no one single unbreakable solution as yet. The use of MySQL storage engines, MySQL replication, configuration settings for durability, hardware configuration, database uptime, and locking requirements are all factors in choosing an applicable approach matching your business requirements. This book will cover these approaches detailing the relative strengths and weaknesses and leading the reader to identify and implement an appropriate backup and recovery strategy.

The final chapter of this book also discusses advancements in MySQL availability in the cloud and the benefits and risks for an optimal backup and recovery strategy.

Conventions

All code examples are provided in a proportional font. For example:

Any SQL syntax within text or code examples will be in uppercase. For example, the FLUSH TABLES WITH READ LOCK statement will hold a global read lock. These statements are not provided in a different font. If a specific syntax or value from a code example is described in general text, this is provided in a proportional font, for example, the --log-bin configuration option.

For any Unix/Linux command, this is prefixed with a $ to indicate a shell prompt. For example:

Any MySQL SQL statement that can be executed is prefixed with mysql> to indicate execution with the mysql command line client that is included with a full MySQL distribution. For example:

All SQL statements listed with this prefix can generally be performed in any alternative MySQL client GUI tool; however, some additional syntax may not be applicable—for example: \G for vertical display is a mysql command line client specific directive.

About MySQL

The MySQL database server is an open source product released under the GPL V2 license. More information about the GPL license can be found at http://www.mysql.com/about/legal/licensing/index.html. The copyright owner of MySQL at the time of this publication is Oracle Corporation. Oracle Corporation provides continued product development and also provides commercial licenses for OEM providers and comprehensive subscription services for websites and enterprises.

More information about MySQL can be found at the official MySQL website at http://mysql.com and the MySQL developer zone at http://dev.mysql.com.

The current generally available (GA) version of MySQL is version 5.5. This book is written to support MySQL versions 5.0 and better with specific version differences noted when applicable. The current development version of MySQL 5.6 is also referenced to indicate expected new functionality in an upcoming release; however, these features may operate differently or not be provided in any final future MySQL product.

Code Examples

All examples detailed in this book are available for download from the Effective MySQL site at http://effectivemysql.com/book/backup-recovery/. Code, scripts, and sample data are also available at GitHub.

A separate text document of all URLs used is also included on the website to enable quick access to these references.

References

The MySQL Reference Manual on the MySQL developer zone is an invaluable resource. This can be found at http://dev.mysql.com/doc/refman/5.5/en/index.html.

Access to manuals for older MySQL versions can also be found at http://dev.mysql.com/doc.

The Planet MySQL website at http://planet.mysql.com provides an aggregation of thousands of MySQL bloggers detailing great insight on all things MySQL.

Additional open source products referenced in this book including Xtra-Backup and Percona Toolkit from Percona, mylvmbackup, and mydumper have various sources of additional online information. These are detailed at the appropriate time.

*The “Choas Monkey” and the “Simian Army” created by Netflix highlight that proactively creating disasters esures the best approach for being prepared. More at http://techblog.netflix.com/2011/07/netflix-simian-army.html