The following tutorial aims to provide you a simple step-by-step guide for setting up MySQL (Master-Slave) Replication in RHEL 6.3/6.2/6.1/6/5.8, CentOS 6.3/6.2/6.1/6/5.8 and Fedora 17,16,15,14,13,12 using latest MySQL version. This guide is specially written for Redhat 6.6 Operating System, but also work with older version of Linux distributions with MySQL 5.x.
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.
- Working Linux OS like CentOS 6.3, RedHat 6.3 or Fedora 17
- Master and Slave are Redhat 6.6 Linux Servers.
- Master IP Address is: 192.168.1.1.
- Slave IP Address is: 192.168.1.2.
- Master and Slave are on the same LAN network.
- Master and Slave has MySQL version installed.
- Master allow remote MySQL connections on port 3306.
[root@wachid ~]# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
[root@wachid ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)
[root@wachid ~]#
We have two servers, one is Master with IP (192.168.1.1) and other is Slave as (192.168.1.2). We have divided the setup process in two phases to make things easier for you, In Phase I we will configure Master server and in Phase II with Slave server. Let’s start the replication setup process.
Phase I: Configure Master Server (192.168.1.1) for Replication
In Phase I, we will see the installation of MySQL, setting up Replication and then verifying replication.
Install a MySQL on Master Server
First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.
[root@wachid ~]# yum install yum install mysql mysql-server
If you need detail about mysql 5.1 installation on Redhat 6 you can find in here
Configure a MySQL on Master Server
Open my.cnf with vi editor
[root@wachid ~]# vi /etc/my.cnf
Configuration before edit shown on following figure
[root@wachid ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@wachid ~]#
Add the following entries under [mysqld] section and don’t forget to replaced db_wachid with database name that you would like to replicate on Slave.
server-id = 1
binlog-do-db=db_wachid
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
Save my.cnf and the configuration will be like following figure
[root@wachid ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id = 1
binlog-do-db=db_wachid
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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@wachid ~]#
Restart MySQL service
[root@wachid ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@wachid ~]#
Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and p4ssw0rd with your password.
[root@wachid ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'p4ssw0rd';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 331 | db_wachid | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> quit
Please write down the File (mysql-bin.000001) and Position (331) 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.
[root@wachid ~]# mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@wachid ~]#
Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.
[root@wachid ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
[root@wachid ~]#
Transfer the database dump file on Slave Server (192.168.1.2) using
SCP command.
[root@wachid yum.repos.d]# scp /root/dbdump.db [email protected]:/root/
[email protected]'s password:
dbdump.db 100% 514KB 514.4KB/s 00:00
[root@wachid yum.repos.d]#
That’s it we have successfully configured Master server, let’s proceed to Phase II section.
.
Phase II: Configure Slave Server (192.168.1.2) for Replication
In Phase II, we do the installation of MySQL, setting up Replication and then verifying replication.
Install a MySQL on Master Server
Install MySQL server, if you don't have then perform installation with following command
[root@wachid ~]# yum install yum install mysql mysql-server
Configure a MySQL on Slave Server
Restart MySQL service
[root@wachid ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@wachid ~]#
Now import the dump file to slave server with following command
[root@wachid ~]# mysql -u root -p < /root/dbdump.db
Enter password:
[root@wachid ~]#
You cannot import the dump file while mysql server running as slave, you will get this error
[root@wachid ~]# mysql -u root -p < /root/dbdump.db
Enter password:
ERROR 1198 (HY000) at line 22: This operation cannot be performed with a running slave; run STOP SLAVE first
[root@wachid ~]#
Open my.cnf with vi editor
[root@wachid ~]# vi /etc/my.cnf
Configuration before edit shown on following figure
[root@wachid ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@wachid ~]#
Add the following entries under [mysqld] section and don’t forget to replaced db_wachid with database name that you would like to replicate with master.
server-id = 2
master-host=192.168.1.2
master-connect-retry=60
master-user=slave_user
master-password=p4ssw0rd
replicate-do-db=db_wachid
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
Save my.cnf and the configuration will be like following figure
[root@wachid ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id = 2
master-host=192.168.1.2
master-connect-retry=60
master-user=slave_user
master-password=p4ssw0rd
replicate-do-db=db_wachid
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
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@wachid ~]#
Restart MySQL service again to reload new configuration
[root@wachid ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@wachid ~]#
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 (331) numbers. You must change 192.168.1.1 to the IP address of the Master Server, and change the user and password accordingly.
[root@wachid ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='slave_user', MASTER_PASSWORD='p4ssw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=331;
Query OK, 0 rows affected (0.03 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 331
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_wachid
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 331
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql>
mysql> exit
Bye
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
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
mysql>
mysql> create database db_wachid;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| db_wachid |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql> use db_wachid;
Database changed
mysql>
mysql> create table test_tab (col1 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_tab (col1) values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_tab;
+------+
| col1 |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
On Slave
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 631
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 551
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_wachid
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 631
Relay_Log_Space: 706
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_wachid |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql> use db_wachid;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+---------------------+
| Tables_in_db_wachid |
+---------------------+
| test_tab |
+---------------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from test_tab;
+------+
| col1 |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql>
That’s it, finally you’ve configured MySQL Replication in a few simple steps. More information can be found at MySQL Replication Guide.
