Tutorilas | Technical Tips | Articles | Interview Tips

Introduction
Database Replication is an interesting concept. Data written to one mysql server is replicated or written to another mysql server in near-zero time. It plays a key role in achieving the following
a) Scaling out the applications.
A single database server cannot cater the need of large scale applications. Replication helps to create multiple copies of the data which ensures high availability. For improving performance the operations can be divided in to these multiple servers running with the same data. A good and mostly followed way is to direct all db writes to master and reads to slave servers.
b) Securing data
Replication creates one or more copies of data which can be used as backups. In case of master server crash, one of the slave can be uplifted to act as the next master. Data in the slaves can be used for anything without disturbing the master server.

Following are the steps to create s simple replication setup. The aim here is to create a replication environment with one master and one slave.

Configure Master

a) Comment out the following 2 lines in /etc/mysql/my.cnf to enable net working and to make the MySql Server to listen on all IP services
#skip-networking
#bind-address = 127.0.0.1

b) Next step is to enable the binary log. Bin log file records all database operations recorded as sql statements. Slave servers read this file and executes themselves. Following 3 lines need to be inserted in the /etc/mysql/my.cnf file. First line tells the name of bin log file. Second line tells the name of database which need to be replicated. Changes related to that database only is written to binary log. The final line assigns a server id for the master server. The server id need to be unique in a replication group.
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=my_db
server-id=1

c) Restart MySql service
d) Log into the MySql database as root and create a user with replication privileges. This user account is used by the slaves to connect to the master

GRANT REPLICATION SLAVE ON *.* TO ’slave_user’@'%’ IDENTIFIED BY ”; (Replace with a real password!)
FLUSH PRIVILEGES;
e) Execute the following commands in the shell.
USE my_db;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output of show master (log file and position ) should be noted to use in slave.

f) Exit the MySql –shell using the quit; command
g) Unlock the tables in the my_db
mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;

Configure Slave
a) Create the my_db first
mysql -u root -p
Enter password:
CREATE DATABASE my_db;
quit;

b) Edit the /etc/my.cnf
Following lines tell the server id for slave and the name of database to be replicated.
server-id=2
replicate-do-db=my_db

master-connect-retry=60

Next three lines specifies the details of the master server
master-host=MASTER_IP
master-user=slave_user
master-password=passwd

A better alternative is to use CHANGE MASTER TO method, in which case the above three lines can be omitted from the my.cnf file.
[See section (e) for more details]
c) Restart mysql

d) Execute the following commands
mysql -u root -p
Enter password:
SLAVE STOP;

e) Execute the following command with proper values for all variables

CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=”, MASTER_LOG_FILE=’mysql-bin.006′, MASTER_LOG_POS=183;
o MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
o MASTER_USER is the user we granted replication privileges on the master.
o MASTER_PASSWORD is the password of MASTER_USER on the master.
o MASTER_LOG_FILE is the file MySql gave back when you ran SHOW MASTER STATUS; on the master.
o MASTER_LOG_POS is the position MySql gave back when you ran SHOW MASTER STATUS; on the master

f) Execute the following commands to start slave

START SLAVE;
quit;
The replication will be started after completing the above step. SHOW SLAVE STATUS can be used to check whether replication is working.

Note:
As described above there exist two ways to specify the master details in slave server. Directly specifying in the my.cnf or by executing the CHANGE MASTER TO command. The latter one is preferred as it is more dynamic and flexible.