Tuesday, 30 July 2013

Master-Slave Replication In MySQL

About MySQL Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave. 

This tutorial will use the following IP addresses: - Master Database - Slave Database

1.      Setup

>  sudo su
>  apt-get install mysql-client
>  apt-get install mysql-server

2.      Configure the Master Database

Open up the mysql configuration file on the master server.

>  nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes. The changes are :

bind-address              =
server-id                     = 1
log_bin                       = /var/log/mysql/mysql-bin.log
binlog_do_db             = newdatabase

Now, Refresh MySQL 

>  service mysql restart

Open up the MySQL Shell

>  MySQL -u root –p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

>  GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

Switch to new database

>  USE newdatabase;

Note : Remember File name and position. Here, I’m assuming File- mysql-bin.000001 and Position- 107

>  Exit;

Proceeding the with the database still locked, export your database using mysqldump (make sure you are typing this command in the bash shell, not in MySQL)

>  mysqldump –u root –p –opt newdatabase > newdatabase.sql

>  mysql –u root –p


>  QUIT;

3.      Configure Slave Database

>  sudo su
>  apt-get install mysql-client
>  apt-get install mysql-server

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

>  mysql –u root –p
>  CREATE DATABASE newdatabase;
>  EXIT;

Now we need to configure the slave configuration in the same way as we did the master:

>  nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes. The changes are :

server_id                    = 2
relay-log                     = /var/log/mysql/mysql-relay-bin.log
log_bin                       = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase

Restart MySQL

>  service mysql restart
>  mysql –u root –p

This command accomplishes several things at the same time:

1.      It designates the current server as the slave of our master server.

2.      It provides the server the correct login credentials

3.      Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.


If there is an issue in connecting, you can try starting slave with a command to skip over it:

All Done.
Happy to help you !!


  1. This helped a lot..thanks mr. Arun :)

  2. And please write some details on exception handling in java too :)