So far we've looked at many aspects of MySQL, not in any great depth, but hopefully with enough information to get you started and whet your appetite for more.  Now we start to look into areas that aren't in the basic tutorials.

Replication is the technology that allows data to be stored on multiple servers. Typically this is used in "scale out" applications.  "Scale out" is used in contrast to "Scale up" where to scale a solution you buy a bigger box to run it on, where "scale out" means you buy more boxes.  Each has its benefits and drawbacks, with the usual benefit of scale out being that you get more bang for your buck.

The way replication works in MySQL is pretty simple.  One server is identified as the master, and writes every transaction to a file, the binary log.  Other servers (and there may be many) act as slaves and request information from the master. The slave keeps track of where it got up to and asks the master for the next transaction in the file.  In general the master doesn't know or care where the slave is up to, it just sends out the requested transaction to whoever has the right credentials to ask for it.

On the slave there are two threads running, one that requests events from the master binlog (binary log) and writes them to the relay log (which in reality is just another binlog), and the second thread that reads the relay log and executes the queries. In order to avoid non-deterministic outcomes the SQL thread is just that, a single thread.  Long running events can result in the replication lagging well behind the state of the master.

This is the traditional asynchronous replication.  Newer versions of MySQL now support semi-synchronous replication as well.  In this mode the master will not return control back to the querying process until at least one slave reports that it has received the transaction events and written them to its own relay log.  Note that this is written to the relay log but not necessarily written to the database.

You may wonder under what circumstance replication would be of use.  If your application is mainly read-intensive (as many web applications are) then replication gives you the ability to spread reads across multiple slaves to reduce contention.  If your application is mainly write intensive, then replication to multiple slaves will not, of itself, give you any relief.  Indeed, because the replication is single-threaded and all writes on the master must be written to the slaves, you can in fact hurt performance with a replicated setup.

What about more than one master?  This is possible but requires careful thought and planning.  MySQL replication keeps track of which server initiated the transaction, so "circular replication" where a slave acts also as a master should not cause problems, however there are some serious issues to consider.  Take for instance a customer table with the customer ID being auto incremented.  What happens if there are writes to both masters in a master<->master replication setup?  Both will grab the next ID in sequence, then the other will pull that transaction and try and will end up with a duplicate index error, causing replication to fail.  You can get around this by setting the initial auto_increment value on each server and setting the auto_increment_increment value to the number of masters in use.  Remebering however that both masters now have to write all traffic that goes to the pair, there is not a great deal of benefit in such an arrangement.  Other technologies, such as sharding, turn out to offer better scale out opportunities for multi-master deployments.

There are, however, some tools to make multi-master work more easily.  MMM (Multi-Master Replication Manager for MySQL) is one.  MHA (Master High Availability for MySQL) is somewhat different, allowing easy management of master failover.

That is probably enough theory, lets look at how to build a replication system.

For the master, the only requirement is that it writes its transactions to a binary log, and that it has a server ID set.  To do this we need to add the following to the my.cnf file:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

The server-id is an integer that must be unique for all servers in the same replication set.  This is because of the tracking of transactions to avoid circular replication errors.  The log_bin is the path to the binary log file to use.  Each file will be appended with a sequence number to allow the server to rotate log files when they grow too large, or a flush request is received.

One other value that is useful is to set an expiry on the binlogs so that they don't take over all of your disk.  You need to ensure that you have enough binlog capacity for normal replication lag and for handling backup/recovery, but beyond that you can get MySQL to automatically delete the older binlogs.

expire_log_days = 14

Now the master will at least be writing transactions to the binary logs.  Now we have to set up to allow slaves to replicate.  First step is to create a user that the slaves will connect with.  This user must have REPLICATION SLAVE permissions to be able to read the binlog.  Note that this user is created on the master. As a privileged user, in the MySQL client:

CREATE USER 'replicator'@'192.168.%' IDENTIFIED BY 'mypass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.%';

The first command creates our user, the second grants the required permissions.  Note that I've used a wildcarded network address, you can also use a hostname wildcard.

For the slave we need a few things, first we need to set a server-id that is not the same as the master (or any other slave we are creating). Then we need a copy of the data on the master at the point at which the slave is to be started, and we need to then tell the slave about the master.

We can create the server-id in the same manner as we did for the master - but we don't need to specify a binlog (unless we are acting as a relay master).

server-id = 2

To get our first copy of the master data, the best idea is to use mysqldump.  Before we do, we need to stop the master from writing to the binary log and record its current position.  From the mysql command line:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000092 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need to keep this session open so that the lock remains in place, and from another terminal run a mysqldump.

mysqldump --all-databases > all_db.sql

Once the backup is completed we can either drop the lock session or issue:

UNLOCK TABLES;

Great, we now have a backup and we have the position in the binary log at the point at which the backup occurred.  These two together provide us with all we need to create our slave.

First step is to load the above SQL file into the slave.   On the slave you can use the mysql command line client with the SQL file copied from the master.

mysql < all_db.sql

On the slave server we now need to issue a CHANGE MASTER command to tell it where the master is, and where to start replicating from.  This information will be written to the master.info file that is read at startup and updated by the replication process so that the slave will be able to resync with the master on restart.

CHANGE MASTER TO MASTER_HOST = 'master'
  MASTER_USER = 'replicator'
  MASTER_PASSWORD = 'mypass'
  MASTER_LOG_FILE = 'mysql-bin.000092'
  MASTER_LOG_POS = 106;

The MASTER_HOST, MASTER_USER and MASTER_PASSWORD must match the master and the user created above with replication slave permission.  The MASTER_LOG_FILE and MASTER_LOG_POS come from the SHOW MASTER STATUS used when we did the backup of the master.

Now all that is left to do is to start the slave processes:

START SLAVE;

We can use the same backup and the same processes above to create as many slaves as we need.

If you have to set up a complex replication scenario that is not covered by the above, you may find the SkySQL Reference Architecture provisioning system useful.  This service creates master/slave high availability configurations and packages software and configurations together so that you can easily install a system from scratch that meets all your replication needs.

Disclaimer

I am employed by SkySQL Ab, which provides support, training and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.

MySQL is a registered trademark of Oracle Corporation,  MariaDB is a trademark of MontyProgram, and Drizzle is a trademark.