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.

Get the whitepaper

Introducton

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.

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
ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.