In this tutorial we can learn, how to setup MySQL database replication (Master-Slave).
Let’s start the process.

Prerequisite:
1. Two CentOs6 system.
2. MySQL server(5.6) should per-installed on both system.

Here, Our first system we can call Centos-Master and second system we can call Centos-Slave.

Centos-Master’s IP is: ​ 192.168.1.1​​ and Centos-Slave’s IP is: ​ 192.168.1.2
One database named ​ rokomari​​ is in the Centos-Master mysql server.
We want to replicate this ​ rokomari​​ database to Centos-Slave mysql database server.

Configure Master:

  • Login to Centos-Master MySQL server.

  • Here we can see our targeted database “​ rokomari​​ ” in all database list.

  • Exit from MySQL database and edit Centos-Master MySQL server configuration file (​ my.cnf​​ )
[[email protected] ~]# vim /etc/my.cn
  • Add the lines as shown below

  • Save and Exit from this file.
  • Restart Centos-Master MySQL server.
[[email protected] ~]# service mysqld restart
  • Login to Centos-Master MySQL server again and create a slave user for replication purposes.

  • Reload MySQL grant table and do read lock.

* Note down the file (​ mysql-bin.000002​​ ) and position (​199​) for future use.

  • Exit from Centos-Master MySQL server.
  • Now we will take a dump backup of our rokomari database.

  • Login to Centos-master MySQL server again, unlock tables and exit.

  • Send ​ rokomari​​ database dump backup file to our Centos-Slave system with scp.
[[email protected] ~]# scp rokomari.db [email protected]:./
  • Open/Allow MySQL database port 3306 in iptables of both system.

 

Configure Slave:

  • Edit Centos-Slave MySQL server configuration file (​ my.cnf​​ )
[[email protected] ~]# vim /etc/my.cnf
  • Add the lines as shown below, here we mention our replicate-do-db = ​ rokomari​​

  • Restart Centos-Slave MySQL server
[[email protected] ~]# service mysqld restart
  • Login to slave MySQL database and run following query. This ​ master_host​​ is our Centos-Master system IP, ​ master_user​​ is our created slave user in Centos-Master mysql server, ​ master_password​​ is password of slave user, ​ master_port​​ is Centos-Master mysql server port, master_log_file​​ is our previously noted file name of Centos-Master system and ​ master_log_pos​​ is previously noted position number.

  • Create a database with name rokomari​​ and exit.
mysql > create database rokomari;
mysql > quit;
  • Restore rokomari database dump backup file got from Centos-Master system to new created rokomari database in Centos-Slave MySQL server.
[[email protected] ~]# mysql -u root -p rokomari < ./rokomari.db
  • Restart Centos-Slave MySQL server.
[[email protected] ~]# service mysqld restart
  • Login again to MySQL server and check slave status, If “Slave_IO_State” value shows “Waiting for master to send event” then OK & Our replication done. Now if we change or write anything on Centos-Master rokomari database, it will be apply on Centos-Slave rokomari database also.

For more information MySQL Replication Guide.

 

Previous ArticleNext Article

Leave a Reply

Your email address will not be published. Required fields are marked *