MASTER – SLAVE MYSQL REPLICATION TUTORIAL FOR NEWBIE

In this guide I will show you practical steps (with pictures) how to setup MySQL Replication in Master – Slave mode on either CentOS or Ubuntu server. I believe MySQL replication technique is not expert-only stuff but even newbies can learn and use this technique. In case if you didn’t know yet, let’s start with a short introduction about what is it.

MEET MYSQL REPLICATION

MySQL Replication is a method to create synced backup of your MySQL database. You can call it hot backup or a backup that’s always up-to-date and ready-to-use in case the main one crashes or down. Master – Slave replication is just one of available mode that is commonly used in many production system. So this tutorial is the simplest one (one master will send information to a single slave) which newbies can follow and use it at their production servers. Better take backup than not!

What’s the main purpose of MySQL Replication? It is to create a redundant MySQL server setup which is also very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics (to analyze it without using the main database), etc.

INGREDIENTS:

What you’ll need:

• Two VPS servers: this can be in single node, same providers, or at separate / different nodes (different providers / locations). Need recommendation? See my list of recommended providers or the 20 low end cloud servers.
• Either CentOS or Ubuntu running on top of it
• Note down Master and Slave IPs.
• About 30 minutes of your time
• A cup of coffee or tea

What I use in this tutorial:

• A 512MB RAM cloud server from Atlantic.net as Master server.
• A 512MB RAM droplet from Digital Ocean as Slave server.
• Both running CentOS 6.x 32-bit
• MySQL version I use is v5.1.73 at both Master and Slave.
• A cup of Torabika coffee.

PREPARATION

First thing first, you have to install MySQL server on both Master and Slave server. In case if you haven’t installed it yet, you can follow my previous articles:

• How to install MySQL server on CentOS (Index)
• How to install MySQL server on Ubuntu (Index)

Do not forget to note down the password of MySQL root user.

FYI, you can also have MySQL server installed via Control Panel software like Vesta CP at Master server while install ONLY MySQL server manually on Slave.

Before we step up, I use these imaginary IPs which you can replace with your own IPs:

Master server: 111.111.111.111

Slave server: 222.222.222.222

SETUP MYSQL MASTER NODE

Do these steps on Master Node:

Step 1 – Edit MySQL configuration file using your favorite text editor like vi or Nano. Command below uses Nano editor:

You may firstly need to edit IP binding configuration (especially in Ubuntu). Change 127.0.0.1 at bind-address with the IP of your Master server

Step 2 – Now put these lines right after the [mysqld] section:

Change databasename with the database name you want to replicate / copy it to the Slave server. You can include more than one database by repeating this line for all of the databases you will need. I use servermomdb as my database name for this example. Screenshot pic:

Once done, save changes and exit the editor. In Nano it is Control+O then Control+X.

Step 3 – You can now restart mysql service:

Step 4 – Login to MySQL shell as root using password you defined :

Step 5 – Here’s a little bit tricky part. Pay attention closely to each command is issued inside MySQL shell. What you are going to do is: to create the slave user and grant privileges for replication task, flushing privileges and lock the database.

Do not forget to replace replicauser, 222.222.222.222 and replicapass accordingly.

• replicauser = is new MySQL user for replicating purpose.
• 222.222.222.222 = is Slave IP
• replicapass = is password for the new MySQL user.

Here’s an illustration of mine. I use servermom as user and a1b2c3d4 as password:

Step 6 – You’ll need to find out the mysql-bin name and its Position then note it down (I simply open up Notepad and note it there). Still in MySQL shell, use command below:

it should give you output similar to this:

In my case I have to note the mysql-bin.000002 and 106, yours will be different.

Step 7 – You can now exit MySQL shell:

and continue by exporting the database file you wish to copy it to the Slave:

change databasename with yours.

TRANSFER THE DATABASE TO SLAVE

It’s time to transfer / copy your database to the Slave server. You can do this using SCP.

This is the easiest method available. The command is:

Change 22 with specific SSH port (if you changed it) of your Slave server., also changedatabasename.sql and 222.222.222.222 with yours. Example of mine:

SETUP MYSQL SLAVE NODE

Leave the first server (Master node) for a while and now login to your second server which is the Slave node as root. Issue all command in this section only on Slave node.

Step 1 – Edit MySQL configuration file using your favorite text editor like vi or Nano. Command below uses Nano editor:

Step 2 – Now let’s configure MySQL configuration file in your Slave server. Copy paste these configuration right after the [mysqld] section:

Change 111.111.111.111 with IP address of Master server. Change replicauser, replicapass and databasename with yours. Example:

Save changes and exit text editor which in Nano it is Control+O then Control+X.

Step 3 – Login to MySQL shell as root:

pic:

Step 4 – Create new database with same name then exit MySQL shell:

replace databasename with your own real database name. Example:

Step 5 – Restore database you have transferred previously using this command:

Step 6 – Restart MySQL server to apply / load changes:

example:

Step 7 – Login back to MySQL shell as root to do some important tasks:

First task is to stop the Slave so you can define where to look for Master log file.

Change 111.111.111.111, replicauser, replicapass, mysql-bin.000001 and 12345 with yours. Example of mine:

Once done, now you can start Slave and show its status with this command:

It will give you an output similar to this:

If you see something similar to above pic then you are success. There are few lines to pay more attention with: Last_Error will be blank, and Slave_IO_State will report “Waiting for master to send event“, also the Seconds_Behind_Master will indicate how far behind it is. If you see those then congratulation you have configured MySQL slave replication.

You can now exit MySQL shell:

FINISHING UP

Leave Slave server for a while and go back to Master server. Login back to MySQL shell at Master server:

this is important, as you locked the tables of MySQL database in Master server, you can now safely unlock it:

pic:

In production environment this is very important to make sure database between Master and Slave can be synced easily, but visitors / users of your websites will not able to make changes (post comment, add article, etc) during your time setting up MySQL replication above. But however you’ll not suffer any downtime during the process. That’s it.

Additional Note:

Once Master – Slave mode is enabled and succeed, the MySQL root password of Slave server will change following its Master.

If you accidentally change a row of data on your slave or if there is an issue in connecting, try issuing this command inside MySQL shell:

Do not forget to follow me on twitter to get notified quicker when a new article is posted. Have fun!