How to Configure MySQL Database Replication with Master and Slave on CentOS 6
To Configure MySQL Database Replication with Master and Slave on CentOS 6
MySQL Database Replication is method to replicate the changes made in the database from One Server to other Server. We can configure Master-Slave for one side Database Replication and Master-Master for two side Database Replication. Configuration of Master-Slave Database Replication is explained in this manual.
Testing Environment
- Operating System &ndash CentOS 6.7
- MySQL Master Ip &ndash 192.168.5.103
- MySQL Slave Ip &ndash 192.168.5.104
To Configure MySQL Database Replication with Master and Slave
Before configuring Database replication, install and and configure Mysql database on both server. Make sure the port 3306 for mysql is added in the Iptables firewall and also mysql services are started and enabled on both master and slave side.
To Create Database and User for Replication
Follow the below steps in both master and slave side, to create Database and Database user for the replication process.
[root@linuxhelp ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with or g. Your MySQL connection id is 10 Server version: 5.1.73 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> create database rep Query OK, 1 row affected (0.11 sec) mysql> CREATE USER ' repuser' @' localhost' IDENTIFIED BY ' 123' Query OK, 0 rows affected (1.45 sec) mysql> GRANT ALL ON rep.* TO ' repuser' @' localhost' Query OK, 0 rows affected (0.04 sec) mysql> flush privileges Query OK, 0 rows affected (0.01 sec) mysql> exit Bye
To Configure Database Replication in Master side
Edit /etc/my.cnf file
[root@linuxhelp ~]# vim /etc/my.cnf
Enter the database name to replicate and server id.
server-id=1 binlog-do-db=rep log-bin=mysql-bin
Now restart the mysql service using following command.
[root@linuxhelp ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
Then it’ s time to login mysql database and create database user.
[root@linuxhelp ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with or g. Your MySQL connection id is 2 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> GRANT REPLICATION SLAVE ON *.* TO ' repuser' @' %' IDENTIFIED BY ' 123' Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES Query OK, 0 rows affected (0.00 sec) mysql> show databases +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | rep | +--------------------+ 3 rows in set (0.03 sec) mysql> use rep Database changed 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 | 323 | rep | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> exit Bye
SHOW MASTER STATUS command will shows you the status of the master server as you can see in above output. Note down the File (mysql-bin.000001) and Position (323) for configuring the mysql slave.
Now take the backup of database in master by using mysqldump command.
[root@linuxhelp ~]# mysqldump -u root -p rep > rep.sql Enter password: [root@linuxhelp ~]# ls anaconda-ks.cfg Desktop django Documents Downloads ez_setup.py install.log install.log.syslog Music Pictures Public rep.sql Templates Videos
Here copy the dump file into mysql slave server using scp command.
[root@linuxhelp ~]# scp rep.sql root@192.168.5.104:/root/
root@192.168.5.104' s password:
rep.sql 100% 1259 1.2KB/s 00:00
Now follow the below steps to unlock the mysql tables.
[root@linuxhelp ~]# 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)
To Configure Database Replication in Slave side
Edit the /etc/my.cnf file on the slave side.
[root@linuxhelp ~]# vim /etc/my.cnf
Enter the database name and server-id. (Slave server-id should not be same as Master server-id)
server-id=2 binlog-do-db=rep log-bin=mysql-bin
Now restart the mysql service using below command.
[root@linuxhelp ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
Here insert the database dump file into the database in the slave side.
[root@linuxhelp ~]# ls anaconda-ks.cfg Desktop django Documents Downloads ez_setup.py install.log install.log.syslog Music Pictures Public rep.sql Templates Videos [root@linuxhelp ~]# mysql -u root -p rep < rep.sql Enter password:
Again login to mysql database, Create database user and run the query with master server details like ip address, database user, password, log file, and log position.
[root@linuxhelp ~]# 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> CREATE USER ' repuser' @' localhost' IDENTIFIED BY ' 123' Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL ON rep.* TO ' repuser' @' localhost' Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO ' repuser' @' %' IDENTIFIED BY ' 123' Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST=' 192.168.5.103' , MASTER_USER=' repuser' , MASTER_PASSWORD=' 123' , MASTER_LOG_FILE=' mysql-bin.000001' , MASTER_LOG_POS=323 Query OK, 0 rows affected (0.15 sec) mysql> SLAVE START Query OK, 0 rows affected (0.03 sec)
Now the slave is ready for the replication.
To Test Replication
Create tables in master side in the rep database.
mysql> show databases +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | rep | +--------------------+ 3 rows in set (0.02 sec) mysql> use rep Database changed mysql> create table sample (a int) Query OK, 0 rows affected (0.03 sec) mysql> insert into sample (a) values (1) Query OK, 1 row affected (0.01 sec) mysql> select * from sample +------+ | a | +------+ 1 row in set (0.00 sec)
Go to slave, login to mysql database and run the below commands to check the changes made in master side.
mysql> show databases +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | rep | +--------------------+ 3 rows in set (0.00 sec) mysql> use rep 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> show tables +---------------+ | Tables_in_rep | +---------------+ | sample | +---------------+ 1 row in set (0.00 sec) mysql> select * from sample +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec)