MySQL Replication is a feature of the MySQL server that allows you to replicate data from one MySQL database server (called the master) to one or more MySQL database servers (slaves). MySQL Replication has been supported in MySQL for a very long time and is an extremely flexible and powerful technology. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

In this article, by Alex Davies, author of High Availability MySQL Cookbook, we will cover:

• Designing a replication setup
• Configuring a replication master
• Configuring a replication slave without synchronizing data
• Configuring a replication slave and migrating data with a simple SQL dump
• Using LVM to reduce downtime on master when bringing a slave online
• Replication safety tricks

Installing and Managing Multi Master Replication Manager(MMM) for MySQL High Availability is covered seperately.

Replication is asynchronous, that is, the process of replication is not immediate and there is no guarantee that slaves have the same contents as the master (this is in contrast to MySQL Cluster).

Designing a replication setup

There are many ways to architect a MySQL Replication setup, with the number of options increasing enormously with the number of machines. In this recipe, we will look at the most common topologies and discuss the advantages and disadvantages of each, in order to show you how to select the appropriate design for each individual setup.

Getting ready

MySQL replication is simple. A server involved in a replication setup has one of following two roles:

• Master: Master MySQL servers write all transactions that change data to a binary log
• Slave: Slave MySQL servers connect to a master (on start) and download the transactions from the master’s binary log, thereby applying them to the local server

Slaves can themselves act as masters; the transactions that they apply from their master can be added in turn to their log as if they were made directly against the slave.

Binary logs are binary files that contain details of every transaction that the MySQL server has executed. Running the server with the binary log enabled makes performance about 1 percent slower.

The MySQL master creates binary logs in the forms name.000001, name.000002, and so on. Once a binary log reaches a defined size, it starts a new one. After a certain period of time, MySQL removes old logs.

The exact steps for setting up both slaves and masters are covered in later recipes, but for the rest of this recipe it is important to understand that slaves contact masters to retrieve newer bits of the binary log, and to apply these changes to their local database.

How to do it…

There are several common architectures that MySQL replication can be used with. We will briefly mention and discuss benefits and problems with the most common designs, although we will explore in detail only designs that achieve high availability.

Master and slave

A single master with one or more slaves is the simplest possible setup. A master with one slave connected from the local network, and one slave connected via a VPN over the Internet, is shown in the following diagram:

A setup such as this—with vastly different network connections from the different slaves to the master—will result in the two slaves having slightly different data. It is likely that the locally attached slave may be more up to date, because the latency involved in data transfers over the Internet (and any possible restriction on bandwidth) may slow down the replication process.

This Master-Slave setup has the following common uses and advantages:

• A local slave for backups, ensuring that there is no massive increase in load during a backup period.
• A remote location—due to the asynchronous nature of MySQL replication, there is no great problem if the link between the master and the slave goes down (the slave will catch up when reconnected), and there is no significant performance hit at the master because of the slave.
• It is possible to run slightly different structures (such as different indexes) and focus a small number of extremely expensive queries at a dedicated slave in order to avoid slowing down the master.
• This is an extremely simple setup to configure and manage.

A Master-Slave setup unfortunately has the following disadvantages:

• No automatic redundancy. It is common in setups such as this to use lower specification hardware for the slaves, which means that it may be impossible to “promote” a slave to a master in the case of an master failure.
• Write queries cannot be committed on the slave node. This means write transactions will have to be sent over the VPN to the master (with associated latency, bandwidth, and availability problems).
• Replication is equivalent to a RAID 1 setup, which is not an enormously efficient use of disk space (In the previous example diagram, each piece of data is written three times).
• Each slave does put a slight load on the master as it downloads its binary log. The number of slaves thus can’t increase infinitely.

Multi-master (active / active)

Multi-master replication involves two MySQL servers, both configured as replication masters and slaves. This means that a transaction executed on one is picked up by the other, and vice versa, as shown in the following diagram:

A SQL client connecting to the master on the left will execute a query, which will end up in that master’s binary log. The master on the right will pick this query up and execute it. The same process, in reverse, occurs when a query is executed on the master on the right. While this looks like a fantastic solution, there are problems with this design:

• It is very easy for the data on the servers to become inconsistent due to the non-deterministic nature of some queries and “race conditions” where conflicting queries are executed at the same time on each node

  Recent versions of MySQL include various tricks to minimize the likelihood of these problems, but they are still almost inevitable in most real-world setups.

• It is extremely difficult to discover if this inconsistency exists, until it gets so bad that the replication breaks (because a replicated query can’t be executed on the other node).

This design is only mentioned here for completeness; it is often strongly recommended not to use it. Either use the next design, or if more than one “active” node is required, use one of the other high-availability techniques that are available but not covered in this article.