Effective MySQL Replication Techniques in Depth

Book description

The Essential Guide to Scaling Your Business Solution

Written by Oracle ACE Director and MySQL expert Ronald Bradford, with coauthor Chris Schneider, Effective MySQL: Replication Techniques in Depth describes what is needed to understand and implement MySQL replication to build scalable solutions. This book includes detailed syntax examples to demonstrate the features, options, and limitations of native MySQL replication. Providing an evaluation of various new replication features and additional third-party product implementations, this Oracle Press guide helps to ensure your MySQL environment can support the various high-availability needs of your business.

  • Master the strengths and limitations of native asynchronous replication in a MySQL topology
  • Identify the important features to improve replication for growing business requirements
  • Recognize the key business factors to determine your optimal highavailability needs
  • Understand the benefits of using MySQL replication for failover scenarios
  • Identify the key configuration variables and SQL commands affecting master/ slave replication
  • Learn about the advancements in replication techniques provided by new products, including Tungsten Replicator and Galera
  • Optimize your replication management with various utilities and toolkits

Find additional detailed information and presentations at EffectiveMySQL.com.

Table of contents

  1. Cover 
  2. About the Authors
  3. Copyright
  4. Dedication
  5. Contents 
  6. Acknowledgments
  7. Introduction
  8. 1 The Five Minute DBA
    1. The 2 A.M. Alert Notification
      1. Show Slave Status
    2. Identifying the Problem
      1. Show Create Table
    3. Rectifying the Problem
      1. Sql_Slave_Skip_Counter
    4. Addressing the Underlying Cause
      1. Rectifying the Problem Correctly
    5. Understanding Replication Issues
      1. User Security
      2. Configuration Options and Variables
    6. Conclusion
  9. 2 Diagnosing Common Replication Problems
    1. MySQL Replication Architecture Review
    2. Interpreting Replication Information
      1. Binary Logs
      2. Relay Logs
    3. Replication Consistency
      1. Identifying Data Inconsistencies
      2. Identifying Schema Inconsistencies
      3. Causes of Data Inconsistency
    4. Common Replication Errors
      1. MySQL Server ID
      2. Missing Schema Objects
      3. Ignoring Duplicate Rows
    5. Understanding Replication Lag
      1. Primary Causes of Lag
      2. The MySQL Error Log
      3. Simple Techniques to Improve and Minimize Lag
      4. Advanced Techniques to Improve and Minimize Lag
    6. Monitoring Replication
    7. Conclusion
  10. 3 Improving Standard Replication Features
    1. Extending Asynchronous Behavior
    2. Semisynchronous Replication
    3. Synchronous Replication
    4. Securing Replication with SSL
    5. Making MySQL SSL Ready
    6. New Replication Features
    7. New and Improved Data Integrity
    8. New Performance Improvements for Replication
    9. New Replication Management Features
    10. Balancing Read and Write Load
    11. Conclusion
  11. 4 Using Multi-Master Replication
    1. MySQL Replication Failover Capabilities
    2. Active/Passive Multi-Master Replication
      1. Required Multi-Master Configuration Settings
      2. Optional Multi-Master Configuration Settings
      3. Other Configuration Variables to Consider
      4. Example Configuration
      5. Replication Setup
      6. Multi-Master Replication Verification
      7. Manual Failover Process
      8. Real World Usage Complications
      9. Additional Slave Servers
      10. Read and Write Load Balancing
    3. Circular Replication
      1. Other Replication Topologies
    4. Automating High Availability Failovers
    5. Conclusion
  12. 5 MySQL Replication Tools
    1. Various MySQL Toolkits
      1. Openark Kit
      2. Percona Toolkit
      3. MySQL Workbench Utilities
    2. Replication Failover Managers
      1. MySQL MHA
      2. MMM
      3. Flipper
      4. Cluster Control
    3. Cluster Management
      1. Percona Replication Manager (PRM)
    4. Replication Prefetch
    5. MySQL Patches and Variants
      1. Independent Community Users
      2. Commercial Organizations
    6. Conclusion
  13. 6 Extending Replication for Practical Needs
    1. Highly Requested Replication Features
      1. MySQL Cluster
    2. Galera Cluster for MySQL
      1. Current Limitations
      2. References
      3. Installation
      4. Percona XtraDB Cluster
      5. MariaDB Galera Cluster
      6. Galera Wrap-Up
    3. Tungsten Replicator
      1. Features
      2. References
      3. Prerequisites
      4. Installation with Tungsten Sandbox
      5. Manual Tungsten Installation
      6. Alternative Tungsten Deployments
      7. Unique Characteristics
      8. Continuent Tungsten
      9. Continuent Wrap-Up
    4. SchoonerSQL
    5. MySQL Replication Listener
    6. MySQL in the Cloud
      1. Amazon RDS for MySQL
      2. Google Cloud SQL
    7. Other Offerings
    8. Conclusion
  14. 7 MySQL Configuration Options
    1. About MySQL System Variables
    2. Binary Logging
    3. MySQL Replication
      1. Semisynchronous Replication
      2. Security
    4. MySQL Server Variables
      1. InnoDB variables
    5. MySQL 5.6 Features
      1. Universally Unique Identifier (UUID)
      2. Crash-Safe Slaves
      3. Replication Checksums
      4. Multi-Threaded Slaves
      5. Global Transaction Identifier (GTID)
    6. User Privileges
    7. SQL Commands and Functions
      1. Binary Log Statements
      2. Replication Statements
      3. Replication Related Functions
    8. Conclusion
  15. 8 Monitoring Replication
    1. Types of Monitoring
      1. MySQL Configuration
      2. Monitoring Granularity
    2. Important MySQL Information
      1. MySQL Error Log
      2. Show Master Status
      3. Show Slave Status
      4. Show Global Status
      5. Meta Files
      6. Meta Tables
    3. Monitoring Products
      1. Dedicated Monitoring Products
      2. System Monitoring Products
      3. The Implementation of Monitoring
      4. MySQL Enterprise Monitor
      5. Cacti
      6. MySQL Performance Monitor (MPM)
      7. Poor Man’s Replication Monitor
    4. Troubleshooting Replication Incidents
    5. Conclusion
  16. A A MySQL Replication Test Environment
    1. Manual Steps to Configure MySQL Replication
    2. Using MySQL Sandbox
      1. MySQL Sandbox Installation
      2. MySQL Software Releases
      3. Replication Setup with MySQL Sandbox
      4. References
    3. Using Virtual Servers
      1. VirtualBox Installation
    4. Testing and Verifying MySQL Replication
    5. Conclusion
  17. Index

Product information

  • Title: Effective MySQL Replication Techniques in Depth
  • Author(s): Ronald Bradford, Chris Schneider
  • Release date: September 2012
  • Publisher(s): Oracle Press
  • ISBN: 9780071791878