The following tutorial aims to provide you a simple step-by-step guide for setting up MySQL(Master-Slave) Replication in RHEL 6.x/5.x, CentOS 6.x/5.x and Fedora 17,16,15,14,13,12 using latest MySQL version. This guide is specially written for CentOS 6.3 Operating System, but also work with older version of Linux distributions with MySQL 5.x.
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.
The MySQL Replication is very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics etc. We use the following things to carry the replication process. In your scenario it would be different.
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.
Master IP Address is: 172.24.0.1. - Master Database
Slave IP Address is: 122.24.0.2. - Slave Database
Master and Slave are on the same LAN network.
Master allow remote MySQL connections on port 3306.
Step 1 —Configure the Master Database Server
Install a MySQL in Master Server
First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.
# yum install mysql-server mysql
Open up the mysql configuration file on the master server.
# vi /etc/my.cnf
Add the following entries under [mysqld] section and don’t forget to replace prakash with database name that you would like to replicate on Slave.
server-id = 1
binlog-do-db=prakash
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 create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.
# mysql -u root -p
Please write down the File (mysql-bin.000001) and Position (107) 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 (172.24.0.2) using SCP command.
# scp /root/dbdump.db root@172.24.0.2:/root/
That’s it we have successfully configured Master server
Step 2 —Configure the Slave Database Server
Configure Slave Server (172.24.0.2) for Replication.
# yum install mysql-server mysql
Configure a MySQL in Slave Server
Open my.cnf configuration file with VI editor.
# vi /etc/my.cnf
Add the following entries under
[mysqld] section and don’t forget to replace
IP address of
Master server,
prakash with database name etc, that you would like to replicate with
Master.
server-id = 2
master-host=
172.24.0.1
master-connect-retry=60
master-user=
slave_user
master-password=
yourpassword
replicate-do-db=
prakash
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.000001) and Position (107) numbers. You must change 172.24.0.1 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='172.24.0.1', MASTER_USER='slave_user', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
mysql> slave start;
mysql> show slave status\G
If there is an issue in connecting, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
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 prakash;
Query OK, 1 row affected (0.00 sec)
mysql> use prakash;
Database changed
mysql> CREATE TABLE employee (c int);
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO employee (c) VALUES (1);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM employee;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
On Slave Server
mysql> use prakash;
Database changed
mysql> SELECT * FROM employee;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
That’s it, finally you’ve configured MySQL Replication in a few simple steps.
If you have any further questions about the specific capabilities of MySQL, feel free to post your questions and I’ll be happy to answer them .
