Mysql Master/Slave Replication on RedHat/Cent OS Linux

Linux OS: RHEL 6.0

Master IP Address: 192.168.176.218

Slave IP Address: 192.168.176.216

Master and Slave are on the same LAN Network

Master and Slave has MySQL version 5.1.47 installed

Master allows remote MySQL connections on port 3306.

Database = apex (This is the database will be replicated to slave)

Phase I: Configure Master Server (192.168.176.218) for Replication

Step 1 #yum install mysql-server mysql

Step 2 #vim /etc/my.cnf (Add the below lines in [mysqld] section)

server-id = 1

binlog-do-db=apex

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

log-bin = /var/lib/mysql/mysql-bin

Step 3 #Restart mysqld server

# /etc/init.d/mysqld restart

Step 4 # Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';

mysql> FLUSH PRIVILEGES;

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 11128001 | apex | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> quit;

Please write down the File (mysql-bin.000003) and Position (11128001) numbers, we required these numbers later on Slave server. Next apply READ LOCK to databases to export all the database and master database information with mysqldump command.

# mysqldump -u root -p --all-databases --master-data > /root/dbdump.db

Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.

mysql> UNLOCK TABLES;

mysql> quit;

Upload the database dump file on Slave Server (192.168.1.2) using SCP command.

scp /root/dbdump.db root@192.168.1.2:/root/

Phase II: Configure Slave Server (192.168.176.218) for Replication

# yum install mysql-server mysql

# vi /etc/my.cnf

server-id = 2

master-host=192.168.176.216

master-connect-retry=60

master-user=slave_user

master-password=yourpassword

replicate-do-db=apex

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

log-bin = /var/lib/mysql/mysql-bin

Restart the MySQL service.

# /etc/init.d/mysqld restart

Login into MySQL as root user and stop the slave. Then tell the slave to where to look for Master log file, that we have write down on master with SHOW MASTER STATUS; command as File (mysql-bin.000003) and Position (11128001) numbers. You must change 192.168.176.216 to the IP address of the Master Server, and change the user and password accordingly.

# mysql -u root -p

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.176.216', MASTER_USER='slave_user', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001;

mysql> slave start;

mysql> show slave status\G

Verifying MySQL Replication on Master and Slave Server

It’s really very important to know that the replication is working perfectly. On Master server create table and insert some values in it.

On Master Server

mysql> create database apex;

mysql> use apex;

mysql> CREATE TABLE employee (c int);

mysql> INSERT INTO employee (c) VALUES (1);

mysql> SELECT * FROM employee;

+------+

| c |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

On Slave Server

Verifying the SLAVE, by running the same command, it will return the same values in the slave too.

mysql> use apex;

mysql> SELECT * FROM employee;

+------+

| c |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

That’s it, finally you’ve configured MySQL Replication in a few simple steps

Reference taken from: http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/