MySQL High Availability: Tools for Building Robust Data Centers

MySQL High Availability: Tools for Building Robust Data Centers

Mats Kindahl, Lars Thalmann

Language: English

Pages: 762

ISBN: 1449339581

Format: PDF / Kindle (mobi) / ePub

Server bottlenecks and failures are a fact of life in any database deployment, but they don’t have to bring everything to a halt. This practical book explains replication, cluster, and monitoring features that can help protect your MySQL system from outages, whether it’s running on hardware, virtual machines, or in the cloud.

Written by engineers who designed many of the tools covered, this book reveals undocumented or hard-to-find aspects of MySQL reliability and high availability—knowledge that’s essential for any organization using this database system. This second edition describes extensive changes to MySQL tools. Versions up to 5.5 are covered, along with several 5.6 features.

  • Learn replication fundamentals, including use of the binary log and MySQL Replicant Library
  • Handle failing components through redundancy
  • Scale out to manage read-load increases, and use data sharding to handle large databases and write-load increases
  • Store and replicate data on individual nodes with MySQL Cluster
  • Monitor database activity and performance, and major operating system parameters
  • Keep track of masters and slaves, and deal with failures and restarts, corruption, and other incidents
  • Examine tools including MySQL Enterprise Monitor, MySQL Utilities, and GTIDs












contains a lot of other information—for example, a timestamp, which is the number of seconds since the Epoch (a classic Unix moment in time, 1970-01-01 00:00:00 UTC). The Binary Log’s Structure and Content As we explained, the binary log is not actually a single file, but a set of files that allow for easier management (such as removing old logs without disturbing recent ones). The binary log consists of a set of binlog files with the real contents as well as a binlog index file, which

deallocate the query cache buffer. You must also set the size to completely turn off the query cache. For more information about configuring the query cache, see the section titled “Query Cache Configuration” in the online MySQL Reference Manual. You can observe the performance of the query cache by examining several status variables, as shown in Example 11-3. Example 11-3. Query cache status variables mysql> SHOW STATUS LIKE '%Qcache%'; +-------------------------+-------+ | Variable_name |

while some forms of logical backups do not lock or block access to the tables while the backup is running. Deciding which method to use may be harder than you think. For example, if you want to make a copy of a MySQL database server that includes all of its data, you can simply take it offline, shut down the server, and copy the entire mysql directory to another computer. You must also include the data directories for the InnoDB files if you have changed their default locations. This will create

up your MySQL database. It makes use of a technology called the logical volume manager (LVM). Microsoft Windows has a similar technology called Volume Shadow Copy. Unfortunately, there are no generic utilities to make a snapshot of a random partition or folder structure as there are for LVM. You can, however, make snapshots of an entire drive, which can be useful if your database directory is the only thing on that drive. See the Microsoft online documentation for more information. An LVM is a

Checking Status–Checking Status cloning, Cloning a Slave–Cloning a Slave configuring, Configuring the Slave, Cloning the Master connecting to masters, Configuring the Master–Connecting the Master and Slave creating, So, Backups Are Not Needed Then?, Procedures database crashes, Syncing, Transactions, and Problems with Database Crashes–SQL thread syncing delayed slaves, What’s This Replication Stuff, Anyway? events and, Events filtering replication events, Filtering

Download sample