Setup MySQL master slave replication in CentOS 6

In this tutorial we will learn about how to setup MySQL Master Slave replication in CentOS 6.4.In Master-Slave replication one database server (Master) can replicate the databases to one or more MySQL database Server(slave).
The Slave server is not required to be connected always with Master Server, once slave server is up and when you start the slave thread(START SLAVE command) it will again take all the replicated updates automatically.

First of all lets see the given below details which is important.

Operating System : CentOS 6.4
Arch: i386
MySQL Version : 5.5

Master Server ip address: 192.168.56.121
Slave Server ip address : 192.168.56.108

iptables service was stopped (/etc/init.d/iptables stop)
SELINUX was disabled (reference)

Install the prerequisites in both Master and Slave server by given below command

yum install mysql mysql-server mysql-devel

Configuration steps in Master MySQL Server

Step 1: Login into your Master Server with user root.

Step 2: Take the backup of my.cnf file (If any problem happen while editing you can restore)

cp -p /etc/my.cnf /etc/my.cnf.orig.`date +%F`

Step 3: Now edit the my.cnf with your favorite editor. I use vi or vim.
In [mysqld] section paste the given below contents .
note: /var/lib/mysql is bydefault path of MySQL data directory if in your case it is different, give correct path in datadir value (find it in below given contents)

Replace 192.168.56.121 with your Master Server IP address

vi /etc/my.cnf

[mysqld]    (Do not write this line,find this [mysqld] section and write below given contents)

bind-address=192.168.56.121
server-id=1
binlog-ignore-db = "mysql"
binlog-format = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Reference to read about innodb parameter

Step 4:Restart MySQL server

/etc/init.d/mysqld restart

Step 5: Login to MySQL Master Server as a root. And create user for replication.
Here we are selecting the name replication as MySQL user.

Syntax:

mysql > CREATE USER replication-mysql-username@ip-address-of-Slave-Server;
mysql > GRANT REPLICATION SLAVE ON *.* TO replication-mysql-username@ip-address-of-Slave-Server IDENTIFIED BY 'password';
mysql > flush privileges;
mysql > exit;

In our scenario it will be:

CREATE USER replication@192.168.56.108;
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.56.108 IDENTIFIED BY 'password';
flush privileges;

Step 6: Now take database dump.

mysqldump --skip-lock-tables --single-transaction --hex-blob --flush-logs --master-data=2 -u root -p database-name > database-name-backup.sql

Step 7: Now login to Master MySQL server again and run below given commands

mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status;

mysql> unlock tables;

Below Given is my Master Server reference:

By ‘show master status‘ command we got two values

MASTER_LOG_FILE=mysql-bin.000002
MASTER_LOG_POS=107

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Note: You can also get information of MASTER_LOG_FILE,MASTER_LOG_POS from MySQL dump file.

eg.

[root@localhost ~]# head -100 database-name-backup.sql | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;
[root@localhost ~]#

Step 8: Now move you database backup dump file to slave server

scp database-name-backup.sql root@slave-ip-address:~

In our scenario:

scp  database-name-backup.sql root@192.168.56.108:~

Configuration steps in MySQL Slave Server

Step 9: Login in Slave MySQL server and take the backup of my.cnf file

cp -p /etc/my.cnf /etc/my.cnf.orig.`date +%F`

Step 10: Now edit the my.cnf file and paste below given contents in [mysqld] section

vi /etc/my.cnf

[mysqld]       (Do not write this line,find this [mysqld] section and write below given contents)

server-id=50
binlog-format=mixed
log_bin=mysql-bin
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

Step 11: Restart the mysql service in Slave server

/etc/init.d/mysqld restart

Step 12: Login to MySQL and create database and exit

mysql > create database database-name;
mysql > exit;

Step 13: Now restore the dump file in database

mysql -u root -p database-name < database-name-backup.sql

Step 14: Now again login in MySQL and run the below given command

Note:
=> From Step 7 in Master Server, you will get the details of MASTER_LOG_FILE, MASTER_LOG_POS
=> Replace 192.168.56.121 with your Master MySQL ip address.

mysql > CHANGE MASTER TO MASTER_HOST='192.168.56.121',MASTER_USER='replication',MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

Step 15: Now run the slave and check its status.

mysql > START SLAVE;
mysql > SHOW SLAVE STATUS G;

Now the slave server is up and running and ready to replicate

Note:To stop slave run the command

mysql > STOP SLAVE;

How to know the Slave is working properly or not ?

Solution: After using command 'SHOW SLAVE STATUS' you will get some values. The below given value will assure us the slave is working properly.

Slave_IO_State=“Waiting for master to send event” (It shows slave waiting for master to send event)
Last_Error=0 (It shows no. of error)
Seconds_Behind_Master=0 (it shows lagging time in seconds)

You can also cross check Master-slave replication by creating test database or updating/creating table
in Master, the same will be replicated in slave server.

Troubleshooting: I got the issue when Slave_IO_State has status “Waiting for master to send event” but replication was not happening.

First I checked slave process(show processlist;) running in background and then run the below given command in SLAVE server

mysql > STOP SLAVE;
mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql > START SLAVE;

note: Increase SQL_SLAVE_SKIP_COUNTER = 1 to GLOBAL SQL_SLAVE_SKIP_COUNTER = 2 or soon until issue is fixed. But also recheck you master slave configuration.