MySQL High Availability

MySQL High Availability

by Lars Thalmann, Charles Bell, Mats Kindahl
Released July 2010
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596807306

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Server bottlenecks and failures are a fact of life in any database deployment, but they don't have to bring everything to a halt. MySQL has several features that can help you protect your system from outages, whether it's running on hardware, virtual machines, or in the cloud. MySQL High Availability explains how to use these replication, cluster, and monitoring features in a wide range of real-life situations.

Written by engineers who designed many of the tools covered inside, 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.

  • Explore the binary log, a file for replication that helps in disaster recovery and troubleshooting
  • Get techniques for improving response time and handling large data sets
  • Monitor database activity and performance, as well as major operating system parameters
  • Keep track of what masters and slaves are doing, and deal with failures and restarts, corruption, and other incidents
  • Automate key tasks with code from an open source library written by the authors
  • Learn techniques for using MySQL in virtualized environments, such as Amazon Web Services
  • Use MySQL Cluster to achieve high availability
"MySQL replication is widely deployed but has never been adequately explained. This book changes that."-- Mark Callaghan, MySQL contributor and leader of MySQL engineering efforts at a few of the world's largest Internet companies

Publisher resources

View/Submit Errata

Table of contents

  1. MySQL High Availability
  2. Foreword
  3. Preface
    1. Audience
    2. Organization of This Book
      1. Part I
      2. Part II
      3. Part III
    3. Conventions Used in This Book
    4. Using Code Examples
    5. We’d Like to Hear from You
    6. Safari® Books Online
    7. Acknowledgments
  4. I. Replication
    1. 1. Introduction
      1. What’s This Replication Stuff Anyway?
      2. So, Backups Are Not Needed Then?
      3. What’s with All the Monitoring?
      4. Is There Anything Else I Can Read?
      5. Conclusion
    2. 2. MySQL Replication Fundamentals
      1. Basic Steps in Replication
        1. Configuring the Master
        2. Configuring the Slave
        3. Connecting the Master and Slave
      2. A Brief Introduction to the Binary Log
        1. What’s Recorded in the Binary Log
        2. Watching Replication in Action
        3. The Binary Log’s Structure and Content
        4. Python Support for Managing Replication
        5. Basic Classes and Functions
        6. Operating System
        7. Server Class
        8. Server Roles
        9. Creating New Slaves
        10. Cloning the Master
        11. Cloning the Slave
        12. Scripting the Clone Operation
      3. Performing Common Tasks with Replication
        1. Reporting
          1. Handling reporting in Python
          2. Scheduling tasks on Unix
          3. Scheduling tasks on Windows Vista
      4. Conclusion
    3. 3. The Binary Log
      1. Structure of the Binary Log
        1. Binlog Event Structure
      2. Logging Statements
        1. Logging Data Manipulation Language Statements
        2. Logging Data Definition Language Statements
        3. Logging Queries
          1. Current database
          2. Current time
          3. Context events
          4. Thread ID
        4. LOAD DATA INFILE Statements
        5. Binary Log Filters
        6. Triggers, Events, and Stored Routines
          1. Statements that define or destroy stored programs
          2. Statements that invoke triggers and stored routines
        7. Stored Procedures
        8. Stored Functions
        9. Events
        10. Special Constructions
          1. The LOAD_FILE function
        11. Nontransactional Changes and Error Handling
      3. Logging Transactions
        1. Transaction Cache
          1. How nontransactional statements are logged
          2. How to avoid replication problems with nontransactional statements
        2. Distributed Transaction Processing Using XA
      4. Binary Log Management
        1. The Binary Log and Crash Safety
        2. Binlog File Rotation
        3. Incidents
        4. Purging the Binlog File
      5. The mysqlbinlog Utility
        1. Basic Usage
          1. Reading remote files
        2. Interpreting Events
          1. Query event post header and body
          2. Format description event post header and body
      6. Binary Log Options and Variables
      7. Conclusion
    4. 4. Replication for High Availability
      1. Redundancy
      2. Planning
        1. Slave Failures
        2. Master Failures
        3. Relay Failures
        4. Disaster Recovery
      3. Procedures
        1. Hot Standby
          1. Handling a switchover in Python
        2. Dual Masters
          1. Shared disks
          2. Replicated disks using DRBD
          3. Bidirectional replication
        3. Semisynchronous Replication
          1. Configuring semisynchronous replication
          2. Monitoring semisynchronous replication
        4. Slave Promotion
          1. The traditional method for promoting a slave
          2. A revised method for promoting a slave
          3. Slave promotion in Python
        5. Circular Replication
      4. Conclusion
    5. 5. MySQL Replication for Scale-Out
      1. Scaling Out Reads, Not Writes
      2. The Value of Asynchronous Replication
      3. Managing the Replication Topology
        1. Example of an Application-Level Load Balancer
      4. Hierarchal Replication
        1. Setting Up a Relay Server
        2. Adding a Relay in Python
      5. Specialized Slaves
        1. Filtering Replication Events
          1. Master filters
          2. Slave filters
        2. Using Filtering to Partition Events to Slaves
      6. Data Sharding
        1. Shard Representation
        2. Partitioning the Data
        3. Balancing the Shards
          1. Moving a shard to a different node
          2. Moving data between shards
        4. A Sharding Example
          1. Sharding the database
          2. Partition keys and partition functions
          3. Updating or reading a shard
          4. Implementing a dynamic sharding scheme
          5. Rebalancing the shards
      7. Managing Consistency of Data
        1. Consistency in a Nonhierarchal Deployment
      8. Consistency in a Hierarchal Deployment
      9. Conclusion
    6. 6. Advanced Replication
      1. Replication Architecture Basics
        1. The Structure of the Relay Log
        2. The Replication Threads
        3. Starting and Stopping the Slave Threads
      2. Running Replication over the Internet
        1. Setting Up Secure Replication Using Built-in Support
        2. Setting Up Secure Replication Using Stunnel
      3. Finer-Grained Control over Replication
        1. Information About Replication Status
          1. The state of the I/O and SQL threads
          2. The binary log and relay log positions
      4. Options for Handling Broken Connections
      5. How the Slave Processes Events
        1. Housekeeping in the I/O Thread
        2. SQL Thread Processing
          1. Context events
          2. Thread-specific events
          3. Filtering and skipping events
      6. Slave Safety and Recovery
        1. Syncing, Transactions, and Problems with Database Crashes
          1. I/O thread syncing
          2. SQL thread syncing
        2. Rules for Protecting Nontransactional Statements
      7. Multisource Replication
      8. Row-Based Replication
        1. Options for Row-Based Replication
        2. Mixed-Mode Replication
        3. Events for Handling Row-Based Replication
          1. Table map events
          2. The structure of row events
        4. Event Execution
        5. Events and Triggers
        6. Filtering
      9. Conclusion
  5. II. Monitoring and Disaster Recovery
    1. 7. Getting Started with Monitoring
      1. Ways of Monitoring
      2. Benefits of Monitoring
      3. System Components to Monitor
        1. Processor
        2. Memory
        3. Disk
        4. Network Subsystem
      4. Monitoring Solutions
      5. Linux and Unix Monitoring
        1. Process Activity
          1. The top command
          2. The iostat command
          3. The mpstat command
          4. The ps command
        2. Memory Usage
          1. The free command
          2. The pmap command
        3. Disk Usage
          1. The iostat command
          2. The sar command
          3. Disk Usage Analyzer
        4. Network Activity
          1. The netstat command
          2. The ifconfig command
        5. General System Statistics
          1. The uptime command
          2. The vmstat command
        6. Automated Monitoring with cron
      6. Mac OS X Monitoring
        1. System Profiler
        2. Console
        3. Activity Monitor
      7. Microsoft Windows Monitoring
        1. The Windows Experience
        2. The System Health Report
        3. The Event Viewer
        4. The Reliability Monitor
        5. The Task Manager
        6. The Performance Monitor
      8. Monitoring as Preventive Maintenance
      9. Conclusion
    2. 8. Monitoring MySQL
      1. What Is Performance?
      2. MySQL Server Monitoring
        1. How MySQL Communicates Performance
        2. Performance Monitoring
        3. SQL Commands
        4. The mysqladmin Utility
        5. MySQL GUI Tools
        6. MySQL Administrator
        7. MySQL Query Browser
        8. Server Logs
        9. Third-Party Tools
          1. MySAR
          2. mytop
          3. InnoTop
          4. MONyog
        10. The MySQL Benchmark Suite
      3. Database Performance
        1. Measuring Database Performance
          1. Using EXPLAIN
          2. Using ANALYZE TABLE
          3. Using OPTIMIZE TABLE
        2. Database Optimization Best Practices
          1. Use indexes sparingly but effectively
          2. Use normalization, but don’t overdo it
          3. Use the right storage engine for the task
          4. Use views for faster results via the query cache
          5. Use constraints
          6. Use EXPLAIN, ANALYZE, and OPTIMIZE
      4. Best Practices for Improving Performance
        1. Everything Is Slow
        2. Slow Queries
        3. Slow Applications
        4. Slow Replication
      5. Conclusion
    3. 9. Storage Engine Monitoring
      1. MyISAM
        1. Optimizing Disk Storage
        2. Tuning Your Tables for Performance
        3. Using the MyISAM Utilities
        4. Storing a Table in Index Order
        5. Compressing Tables
        6. Defragmenting Tables
        7. Monitoring the Key Cache
        8. Preloading Key Caches
        9. Using Multiple Key Caches
        10. Other Parameters to Consider
      2. InnoDB
        1. Using the SHOW ENGINE Command
        2. Using InnoDB Monitors
        3. Monitoring Logfiles
        4. Monitoring the Buffer Pool
        5. Monitoring Tablespaces
        6. Using INFORMATION_SCHEMA Tables
        7. Other Parameters to Consider
      3. Conclusion
    4. 10. Replication Monitoring
      1. Getting Started
      2. Server Setup
      3. Inclusive and Exclusive Replication
      4. Replication Threads
      5. Monitoring the Master
        1. Monitoring Commands for the Master
        2. Master Status Variables
      6. Monitoring Slaves
        1. Monitoring Commands for the Slave
        2. Slave Status Variables
      7. Replication Monitoring with MySQL Administrator
      8. Other Items to Consider
        1. Networking
        2. Monitor and Manage Slave Lag
        3. Causes and Cures for Slave Lag
      9. Conclusion
    5. 11. Replication Troubleshooting
      1. What Can Go Wrong
        1. Problems on the Master
          1. Master crashed and memory tables are in use
          2. Master crashed and binary log events are missing
          3. Query runs fine on the master but not on the slave
          4. Table corruption after a crash
          5. Binary log is corrupt on the master
          6. Killing long-running queries for nontransactional tables
        2. Problems on the Slave
          1. Slave server crashed and replication won’t start
          2. Slave connection times out and reconnects frequently
          3. Query results are different on the slave than on the master
          4. Slave issues errors when attempting to restart with SSL
          5. Memory table data goes missing
          6. Temporary tables are missing after a slave crash
          7. Slave is slow and is not synced with the master
          8. Data loss after a slave crash
          9. Table corruption after a crash
          10. Relay log is corrupt on the slave
          11. Multiple errors during slave restart
          12. Consequences of a failed transaction on the slave
        3. Advanced Replication Problems
          1. A change is not replicated among the topology
          2. Circular replication issues
          3. Multimaster issues
          4. The HA_ERR_KEY_NOT_FOUND error
      2. Tools for Troubleshooting Replication
      3. Best Practices
        1. Know Your Topology
        2. Check the Status of All of Your Servers
        3. Check Your Logs
        4. Check Your Configuration
        5. Conduct Orderly Shutdowns
        6. Conduct Orderly Restarts After a Failure
        7. Manually Execute Failed Queries
        8. Common Procedures
          1. Troubleshooting replication failures
          2. Pausing replication
      4. Reporting Replication Bugs
      5. Conclusion
    6. 12. Protecting Your Investment
      1. What Is Information Assurance?
        1. The Three Practices of Information Assurance
        2. Why Is Information Assurance Important?
      2. Information Integrity, Disaster Recovery, and the Role of Backups
        1. High Availability Versus Disaster Recovery
        2. Disaster Recovery
          1. Disaster recovery planning
          2. Disaster recovery workflow
          3. Tools for disaster recovery
        3. The Importance of Data Recovery
          1. Terminology
        4. Backup and Restore
          1. Why back up?
          2. Expectations for backups
          3. Expectations for the restore process
          4. Logical versus physical backup
          5. Forming an archival plan
      3. Backup Utilities and OS-Level Solutions
        1. The InnoDB Hot Backup Application
          1. Performing a backup with ibbackup
          2. Applying the log to a backup
          3. Restoring data with ibbackup
          4. The innobackup script
          5. Performing a backup with innobackup
          6. Restoring data with innobackup
          7. Additional features
        2. Physical File Copy
        3. The mysqldump Utility
        4. XtraBackup
        5. Logical Volume Manager Snapshots
          1. Getting started with LVM
          2. LVM in a backup and restore
        6. Comparison of Backup Methods
      4. Backup and MySQL Replication
        1. Backup and Recovery with Replication
        2. PITR
          1. Restoring after an error is replicated
          2. Recovery example
          3. Recovery images
          4. Backup procedure
          5. PITR in Python
      5. Automating Backups
      6. Conclusion
    7. 13. MySQL Enterprise
      1. Getting Started with MySQL Enterprise
        1. Subscription Levels
        2. Installation Overview
      2. MySQL Enterprise Components
        1. MySQL Enterprise Server
        2. MEM
          1. Enterprise Dashboard
          2. Monitoring agent
          3. Advisors
          4. Query Analyzer (gold and platinum levels)
        3. MySQL Production Support
      3. Using MySQL Enterprise
        1. Installation
        2. Fixing Monitoring Agent Problems
        3. Monitoring
          1. Heat chart
          2. Alert details
          3. Consolidated server graphs
          4. Server details
          5. Replication details
          6. Advisors
        4. Query Analyzer
        5. Further Information
      4. Conclusion
  6. III. High Availability Environments
    1. 14. Cloud Computing Solutions
      1. What Is Cloud Computing?
        1. Cloud Architectures
          1. Virtualization
          2. Grid computing
          3. Transactional computing
          4. Elasticity
          5. Software libraries
        2. Is Cloud Computing an Economical Choice?
      2. Cloud Computing Use Cases
      3. Cloud Computing Benefits
      4. Cloud Computing Vendors
      5. AWS
        1. A Brief Overview of Technologies
          1. Amazon EC2
          2. Amazon S3
          3. Amazon EBS
        2. How Does It All Work?
        3. Amazon Cloud Tools
          1. Amazon console
          2. Browser plug-ins
          3. EC2 command-line tools
            1. EC2 API tools
            2. EC2 AMI tools
        4. Getting Started
          1. Getting an account
          2. Getting your credentials
          3. Amazon login and password
          4. Access key ID and secret access key
          5. SOAP and EC2 command-line tools
          6. Credentials for working with CloudFront
          7. Credentials for working with instances
          8. Other credentials
          9. Running an instance with the AMS Management Console
          10. Launching an instance with the EC2 API tools
        5. Working with Disk
          1. Using instance storage
          2. Using EBS volumes with the AWS Management Console
          3. Using EBS snapshots with the AWS Management Console
          4. Using EBS volumes with the EC2 API tools
          5. Using EBS snapshots with the EC2 API tools
        6. Where to Go from Here
      6. MySQL in the Cloud
        1. MySQL Replication and EC2
        2. Best Practices for Using MySQL in EC2
      7. Open Source Cloud Computing
      8. Conclusion
    2. 15. MySQL Cluster
      1. What Is MySQL Cluster?
        1. Terminology and Components
        2. How Does MySQL Cluster Differ from MySQL?
        3. Typical Configuration
        4. Features of MySQL Cluster
        5. Local and Global Redundancy
        6. Log Handling
        7. Redundancy and Distributed Data
      2. Architecture of MySQL Cluster
        1. How Data Is Stored
        2. Partitioning
        3. Transaction Management
        4. Online Operations
      3. Example Configuration
        1. Getting Started
        2. Starting a MySQL Cluster
          1. Starting the management node
          2. Starting the management console
          3. Starting data nodes
          4. Starting the SQL nodes
        3. Testing the Cluster
        4. Shutting Down the Cluster
      4. Achieving High Availability
        1. System Recovery
        2. Node Recovery
        3. Replication
          1. Replication inside the cluster versus MySQL replication
          2. Replicating inside the cluster
          3. MySQL replication between clusters
          4. Architecture of MySQL Cluster (external) replication
          5. Single-channel and multichannel replication
      5. Achieving High Performance
        1. Considerations for High Performance
        2. High Performance Best Practices
      6. Conclusion
  7. A. Replication Tips and Tricks
    1. My Slave Stopped. Now What?
    2. Examining the Binary Log with Verbose
    3. Using Replication to Repopulate a Table
      1. Statement-Based Logging
      2. Row-Based Logging
    4. Using MySQL Proxy to Perform Multimaster Replication
    5. Using a Default Storage Engine
    6. MySQL Cluster Multisource Replication
    7. Multichannel Replication with Failover
    8. Using the Current Database to Filter
    9. More Columns on Slave Than Master
    10. Fewer Columns on Slave Than Master
    11. Replicate Selected Rows to Slave
    12. Replication Heartbeat
    13. Ignoring Servers in Circular Replication
    14. Feature Preview: Time-Delayed Replication
    15. Feature Preview: Scriptable Replication
    16. Feature Preview: The Oracle Algorithm
  8. Index
  9. About the Authors
  10. Colophon
  11. Copyright

Product information

  • Title: MySQL High Availability
  • Author(s): Lars Thalmann, Charles Bell, Mats Kindahl
  • Release date: July 2010
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596807306