Back to Whitepapers

MySQL Replication Blueprint

The MySQL Replication Blueprint whitepaper includes all aspects of a Replication topology with the ins and outs of deployment, setting up replication, monitoring, upgrades, performing backups and managing high availability using proxies.

Download now

Table of contents

  • 1. Introduction
  • 2. Why a Blueprint for Replication?
    • 2.1. Replication in the pre-MySQL 5.6 era
    • 2.2. Making MySQL Replication Production Ready
    • 2.3. GTID - A Stronger Foundation for Replication
  • 3. Introducing the MySQL Replication Blueprint
    • 3.1. Deployment
    • 3.2. Master/Slave
    • 3.3. Multi Master
    • 3.4. Parallel replication
    • 3.5. Multi source replication
    • 3.6. Management & Monitoring
    • 3.7. Load balancers
  • 4. Monitoring
    • 4.1. Availability
    • 4.2. Performance
    • 4.3. Alerting
  • 5. Management
    • 5.1. Replication topology changes
    • 5.2. Adding new slaves
      • 5.2.1. Why would you delay a slave?
    • 5.3. Repairing a broken replication topology
      • 5.3.1. Slave promotion
      • 5.3.2. Most advanced slave without GTID
      • 5.3.3. Most advanced slave with GTID
      • 5.3.4. Automated slave promotion
    • 5.4. Backups
      • 5.4.1. Logical or physical backups?
      • 5.4.2. Do you need full or incremental backups?
      • 5.4.3. Scheduling
      • 5.4.4. Testing your backups
    • 5.5. Updating to a newer version
    • 5.6. Schema changes
    • 5.7. Configuration changes
  • 6. Load Balancing
    • 6.1. What are the benefits of proxies?
    • 6.2. Read/Write splitting
    • 6.3. Which proxy to choose?
    • 6.4. Query Caching
    • 6.5. Query rewrites

1. Introduction

MySQL Replication has become an essential component of scale-out architectures in LAMP environments. When there is a necessity to scale out, MySQL offers a multitude of solutions, the most common being to add read replicas. The major bottleneck for our data is generally not so much oriented around writing our data but more around reading back this data. Therefore the easiest way to scale MySQL is to add replicas for reading.

The traditional master-slave solution comes with a major flaw: the master is a single point of failure. To overcome this, various solutions emerged that run on top of the MySQL replication topology and try to make it highly available. Tools like MySQL Multi Master (MMM), MySQL HA Master and Percona Replication Manager can manage your replication topology but they do have their cons: in general they are quite difficult to set up and in some cases create another single point of failure. Clustering software like Corosync is able to improve this a bit, but introduces even more complexity.

With today’s cloud environments, where resources are dynamically allocated and deallocated, systems need the ability to automatically adapt to sudden changes. For MySQL Replication, this includes tasks like detecting failures, promoting a slave to master, failing over slaves, and so on.

A load balancer with Virtual IP can also help mask topology changes from the application, and dispatches read and write traffic appropriately.

Systems also undergo configuration changes or version upgrades. Management procedures for these need to be orchestrated so all the system components are in sync. As we’re assembling a system from independent, standalone components, how do we know if something breaks? Therefore, monitoring would be an integral part of any system that goes in production.

Building a production-ready system around MySQL Replication is a major undertaking, but not an impossible one. Facebook, Twitter and Booking.com run thousands of masters and replication slaves in their data centers. These companies invested heavily in building tools to address these issues so they could run replication at scale. The purpose of the Blueprint is to provide an integrated framework for addressing the operational aspects of MySQL Replication.

Want to read the rest?

Download the full whitepaper for free
Download whitepaper