lynk.ly Blog

Easily Replicate your MySQL Database!

Replication is one of those things that is often overlooked when setting up a MySQL database for your web application.  It’s one of those configurations that you should setup when you start out, as it can make a huge difference in the long run.

For the purpose of replication, MySQL defines a master and slave database.  The master database is where records should be written/updated to.  The slave database will automatically get updated every time there is a change on the master database.

There are two main advantages of setting up MySQL replication:

1. Redundancy for your data (i.e. backup)
2. Scaling your application

The Architecture

Other than for backing up your data in real time, replication can be used to improve the performance of your web application.  

Let’s assume you run a website and a cron job that e-mails your users on a daily basis, such as: 

Once you try to scale, whenever the e-mail cron job runs, it will probably put a lot of stress on your database.

That’s where replication can come in handy, and for the purpose of this tutorial, we will be using the following setup:

Setting up MySQL Replication

We’ll be using the above architecture for this tutorial, and we are assuming that you are starting off with an empty database.  We will refer to the database as following:

• Master DB - 192.168.1.1
• Slave DB - 192.168.1.2

The first thing you need to do is to stop your database.  We’ll start off by configuring the master database in the mysql config file located at /etc/mysql/my.cnf.  

MySQL will need to listen on all IP addresses, so comment out this line:

#bind-address 127.0.0.1

To keep the master and the slave database in sync, we will enable binary log files to be generated.  Make sure to have enough disk space, as these binary log files can take up a lot of space.  Enable the binary log files as following: 

log-bin = /var/log/mysql/mydatabase-bin.log
binlog-do-db = mydatabase

Identify the MySQL master database with an ID:

server-id = 1

Now that we are done with the mysql.cnf , open up the mysql admin shell as following:

mysql -u username -p<password> #no space between the p and <password>

Create the database user that the slave master database will use, and grant it access:

CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Restart your MySQL database:

service mysql restart

We are almost done setting up the master database, now we just need to write down the master database’s position in the binary log files:

USE mydatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; 

The above command would return something like this:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | exampledb    |                  |
+---------------+----------+--------------+------------------+

We will use this information when configuring the slave, so write down the bin log file name, and the position.

Configuring the Slave Database

The Slave configuration is similar to what we did for the Master database, and is pretty self-explanatory:

server-id=2
master-host=192.168.1.1 #host IP
master-user=slave_user #replication user
master-password=password
master-connect-retry=60
replicate-do-db=mydatabase #database to replicate from the master

Restart your slave database

service mysql restart

Go to the mysql admin shell, and stop the slave

mysql -u username root -p<password> #no space between the p and <password>
slave stop

Tell the slave to start replicating from the master database:

CHANGE MASTER TO MASTER_HOST='192.168.1.1', 
MASTER_USER='slave_user', 
MASTER_PASSWORD='password', 
MASTER_LOG_FILE='mysql-bin.006', 
MASTER_LOG_POS=183;

Let’s break that command down:

• MASTER_HOST is the IP address of the hostname
• MASTER_USER is the replication user that we created earlier on the master database
• MASTER_PASSWORD is the password for the MASTER_USER
• MASTER_LOG_FILE is the master’s binary log file name that we wrote down earlier
• MASTER_LOG_POS is the position in the master’s binary log file position that we wrote down earlier

We are done configuring the slave, let’s start the replication:

start slave;

You can check if the replication is working by typing:

show slave status;

If the status returned is “Waiting for master to send event”, then you are done!