Save your database from catastrophe by having a backup always on and ready! This video will show setting up basic MySQL replication. This is great for distributing expensive read queries across multiple database servers, running expensive report queries without affecting your application database, or as part of a database failover strategy!Replication in MySQL, in it's simplest form, has a master instance of MySQL, which accepts all write queries, and a replica instance, which replays all write queries to keep in sync with the master database.

You can add one or more "slave" servers, which I'll call "replica" servers. Replicas copy the binary log from the master instance to a local relay log, and then replays each query which might alter data - inserts, updates, deletes, alters, drops, new indexes and so on.

Both master and replica servers can be used for "read" queries (typically select statements). The master server is the only one that shoud accept write queries, or else those changes won't be replicated across all instances of MySQL.

While you can set only certain databases within your MySQL server to be copied, it's recommended that you do not. The reason why is covered on this MySQL Performance article.

Let's dig in!

Two Starting Points

There's two scenarios to adding a Master-Replica setup:

1. You have a new database with no data in it yet
2. You have an existing database with a schema and/or data in it

If you have an in-production database, the setup to create a Master-Replica setup will force you to freeze your database temporarily.

I'll assume you're in scenario 2, as I think it's likely a more common scenario.

To start, I'll pretend you have 2 database installed on 2 servers. One database likely has data in it (the master database) while the replica database is a new, empty MySQL instance.

Configure Master Server

We'll start by configuring the Master MySQL instance.

Edit the my.cnf file. In this video, we're using Ubuntu, so that file is located at /etc/mysql/my.cnf:

# Give it a unique server id
server-id               = 1

# Ensure there's a binary log defined
log_bin                 = /var/log/mysql/mysql-bin.log

Restart the server once that's done (sudo service mysql restart)!

Then log into MySQL on the Master MySQL server and create the replication user - the user who will connect to the master server to copy it.

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

In this case, I made a user "replication_user" who can connect from any host (%). You may want to create one that can only connect from specific hosts - your replica server(s).

Configure Replica Server

After logging into the replica MySQL server we'll edit the my.cnf to give it a unique ID and setup some logs:

Edit /etc/mysql/my.cnf:

# Give the server a unique ID
server-id               = 2

# Ensure there's a relay log
relay-log               = /var/log/mysql/mysql-relay-bin.log

# Keep the binary log on
log_bin                 = /var/log/mysql/mysql-bin.log

Restart the server (sudo service mysql restart) once that's done and we can move onto the meat of this process.

The Process

Once the user is created, we need to do a few things:

1. Lock the master database from accepting new queries that may change the database
2. Get the current "position" of where MySQL is in the bin log (where it's left off writing to the binary log)
3. Get the binary log file name

What we're doing here is finding out exactly where MySQL is currently in writing to the binary log. We'll get this information, export the database in this state, and then release the lock.

We can then update the replicate database with the master database's "state" (aka mysqldump from master and import that dump into the replica) and tell it the binary log position. The relica database then knows where in the binary log to start reading in order to replay queries made on the master database.

Phew. Let's see that process.

Lock Master Server & Get Position

We need to lock the master database and get it's status. Log into the MySQL server and run the following queries:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

That will look something like this:

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      120 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Backup Master and Restore to Replica

While the master database is still locked, we'll get an export of the database in its current state:

# Exporting one database specifically
mysqldump -u root -p newdatabase > newdatabase.sql

Once we have the binary log information and a dump of the database, we can unlock it and let it continue on as normal.

mysql -u root -p -e "UNLOCK TABLES;"

Start Replica at Binlog Position

On the replica database, create a new database (or any new databases) that is used on the replica:

CREATE DATABASE newdatabase;

Then import the master database dump into the replica database:

# We're assuming just one database is in both master and replica
# So I set the name of the database to import into
mysql -u root -p newdatabase < /path/to/newdatabase.sql

Finally, log into the replica database and let it know where to find the master database:

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='replication_user', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  120;

# Once that's set, start the database
# "slave" operation, so it starts replicating
# from the master database
START SLAVE;

Once that's done, any changes in the master database should get replicated to the replication MySQL instance!

Test Replica

To get the relica database's status, log into the replica database and run the following:

SHOW SLAVE STATUS;

That will show any error messages. For example, when trying this before recording, I ran into a connection error where the replica could not connect to the master database, due to a typo in the username used to connect to the master server.

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.