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 and 5.7. 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.2 Global Transaction Identifiers (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 Slave (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
  • 5. Connecting Application to the Replication Setup
    • 5.1 Application Connecter
    • 5.2 Fabric-aware Connector
    • 5.3 Reverse proxy/Load Balancer
  • 6. Failover with ClusterControl
    • 6.1 Failure of Master
    • 6.2 Failure of a Slave
  • 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 Slave 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.

This tutorial covers basic information about MySQL Replication, with information about the latest features introduced in 5.6 and 5.7. 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