Simple steps to set Master / Slave replication in MySQL Database

Why i have to do master-slave replication using MySQL?

1. One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts, update and delete queries and slave for select queries. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2. Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups.

Goal: Master DB – Use db only for insert,delete and updated queries
Slave DB – Use db only for select queries

There are several Master-Slave replication available in MySQL. Below is the one of the best Master-Slave replication.


1. Below configuration is only for a single database replication.
2. Do the step 1 and step 2 in your master server and step 3 and step 4 slave servers.
3. Before doing all steps, you must create database and repective tables in your master and slave server.

Step 1: (Activate binary(bin) log in MySQL)

vi /etc/my.cnf
log-bin = mysql-bin
binlog-do-db = databasename // give your database name
server-id = 1 // unique id depend upon your servers

Step 2: (Creating a new user in Master)

In mysql… login as root user.

CREATE USER username;
GRANT replication SLAVE on *.* TO ‘username’@’%’ IDENTIFIED BY ‘password';

Step 3:

vi /etc/my.cnf

server-id = 2
master-host = master ip address
master-user = username
master-password = password
master-connect-retry = 60
replicate-do-db = databasename

Step 4:

In mysql console…

show slave status;
start slave;

Hope this will help you!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>