Back to Whitepapers

MySQL Replication for High Availability

This tutorial covers information about MySQL Replication, with information about the latest features introduced in 5.6, 5.7 and 8.0. There is also a more hands-on, practical section on how to quickly deploy and manage a replication setup using ClusterControl.

Download now

Table of contents

  • 1. Introduction
  • 2. What is MySQL Replication?
    • 2.1. Replication Scheme
      • 2.1.1. Asynchronous Replication
      • 2.1.2. Semi-Synchronous Replication
    • 2.2. Global Transaction Identifier (GTID)
      • 2.2.1. Replication in MySQL 5.5 and Earlier
      • 2.2.2. How GTID Solves the Problem
      • 2.2.3. MariaDB GTID vs MySQL GTID
    • 2.3. Multi-Threaded Slave
    • 2.4. Crash-Safe Slave
    • 2.5. Group Commit
  • 3. Topology for MySQL Replication
    • 3.1. Master with Slaves (Single Replication)
    • 3.2. Master with Relay Slaves (Chain Replication)
    • 3.3. Master with Active Master (Circular Replication)
    • 3.4. Master with Backup Master (Multiple Replication)
    • 3.5. Multiple Masters to Single Slave (Multi-Source Replication)
    • 3.6. Galera with Replication Slave (Hybrid Replication)
  • 4. Deploying a MySQL Replication Setup
    • 4.1. General and SSH Settings
    • 4.2. Define the MySQL Servers
    • 4.3. Define Topology
    • 4.4. Scaling Out
  • 5. Connecting Application to the Replication Setup
    • 5.1. Application Connector
    • 5.2. Fabric-Aware Connector
    • 5.3. Reverse Proxy/Load Balancer
      • 5.3.1. MariaDB MaxScale
      • 5.3.2. ProxySQL
      • 5.3.3. HAProxy (Master-Slave Replication)
  • 6. Failover with ClusterControl
    • 6.1. Automatic Failover of Master
      • 6.1.1. Whitelists and Blacklists
    • 6.2. Manual Failover of Master
    • 6.3. Failure of a Slave
    • 6.4. Pre and Post-Failover Scripts
      • 6.4.1. When Hooks Can Be Useful?
        • 6.4.1.1. Service Discovery
        • 6.4.1.2. Proxy Reconfiguration
        • 6.4.1.3. Additional Logging
  • 7. Operations - Managing Your MySQL Replication Setup
    • 7.1. Show Replication Status
    • 7.2. Start/Stop Replication
    • 7.3. Promote Slave
    • 7.4. Rebuild Replication Slave
    • 7.5. Backup
    • 7.6. Restore
    • 7.7. Software Upgrade
    • 7.8. Configuration Changes
    • 7.9. Schema Changes
    • 7.10. Topology Changes
  • 8. Issues and Troubleshooting
    • 8.1. Replication Status
    • 8.2. Replication Lag
    • 8.3. Data Drifting
    • 8.4. Errant Transaction
    • 8.5. Corrupted Slave
    • 8.6. Recommendations

1. Introduction

MySQL Replication is probably the most popular high availability solution for MySQL, and widely used by top web properties like Twitter and Facebook. Although easy to set up, ongoing maintenance like software upgrades, schema changes, topology changes, failover and recovery have always been tricky. At least until MySQL 5.6.

Fortunately, MySQL 5.6 brought a number of significant enhancements to Replication, including Global Transaction IDs, event checksums, multi-threaded slaves and crash-safe slaves/masters. Replication got even better with MySQL 5.7 and MySQL 8.0.

This tutorial covers basic information about MySQL Replication, with information about the features introduced in 5.6, 5.7 and 8.0. At the end, you should be able to answer questions like:

  • How do I use GTID with replication?
  • How do I recover my setup if my master fails?
  • How do I upgrade the master and slave servers without downtime?
  • How do I change my database schema across all servers?
  • How do I handle slave lag?
  • etc.

There is also a more hands-on, practical section on how to quickly deploy and manage a replication setup using ClusterControl. You would need 4 hosts/VMs if you plan on doing this.

Want to read the rest?

Download the full whitepaper for free
Download whitepaper