Tutorial: MySQL master slave replication CentOS 6

In this tutorial we will learn about how to setup MySQL Master Slave replication in CentOS 6.4.In Master-Slave replication one database server (Master) can replicate the databases to one or more MySQL database Server(slave). The Slave server is not required to be connected always with Master Server, once slave server is up and when you start the slave thread(START SLAVE command) it will again take all the replicated updates automatically. First of all lets see the given below details which is important.
Operating System : CentOS 6.4 Arch: i386 MySQL Version : 5.5 Master Server ip address: Slave Server ip address : iptables service was stopped (/etc/init.d/iptables stop) SELINUX was disabled (reference)
Install the prerequisites in both Master and Slave server by given below command

Configuration steps in Master MySQL Server

Step 1: Login into your Master Server with user root. Step 2: Take the backup of my.cnf file (If any problem happen while editing you can restore) Step 3: Now edit the my.cnf with your favorite editor. I use vi or vim. In [mysqld] section paste the given below contents . note: /var/lib/mysql is bydefault path of MySQL data directory if in your case it is different, give correct path in datadir value (find it in below given contents) Replace with your Master Server IP address Reference to read about innodb parameter Step 4:Restart MySQL server Step 5: Login to MySQL Master Server as a root. And create user for replication. Here we are selecting the name replication as MySQL user. Syntax: In our scenario it will be: Step 6: Now take database dump. mysqldump –skip-lock-tables –single-transaction –hex-blob –flush-logs –master-data=2 -u root -p database-name > database-name-backup.sql Step 7: Now login to Master MySQL server again and run below given commands Below Given is my Master Server reference: By ‘show master status‘ command we got two values MASTER_LOG_FILE=mysql-bin.000002 MASTER_LOG_POS=107 Note: You can also get information of MASTER_LOG_FILE,MASTER_LOG_POS from MySQL dump file. eg. Step 8: Now move you database backup dump file to slave server
scp database-name-backup.sql root@slave-ip-address:~
In our scenario:

Configuration steps in MySQL Slave Server

Step 9: Login in Slave MySQL server and take the backup of my.cnf file Step 10: Now edit the my.cnf file and paste below given contents in [mysqld] section Step 11: Restart the mysql service in Slave server Step 12: Login to MySQL and create database and exit Step 13: Now restore the dump file in database Step 14: Now again login in MySQL and run the below given command Note: => From Step 7 in Master Server, you will get the details of MASTER_LOG_FILE, MASTER_LOG_POS => Replace with your Master MySQL ip address. Step 15: Now run the slave and check its status. Now the slave server is up and running and ready to replicate Note:To stop slave run the command
How to know the Slave is working properly or not ? Solution: After using command ‘SHOW SLAVE STATUS’ you will get some values. The below given value will assure us the slave is working properly. Slave_IO_State=“Waiting for master to send event” (It shows slave waiting for master to send event) Last_Error=0 (It shows no. of error) Seconds_Behind_Master=0 (it shows lagging time in seconds) You can also cross check Master-slave replication by creating test database or updating/creating table in Master, the same will be replicated in slave server.
Troubleshooting: I got the issue when Slave_IO_State has status “Waiting for master to send event” but replication was not happening. First I checked slave process(show processlist;) running in background and then run the below given command in SLAVE server note: Increase SQL_SLAVE_SKIP_COUNTER = 1 to GLOBAL SQL_SLAVE_SKIP_COUNTER = 2 or soon until issue is fixed. But also recheck you master slave configuration.

Leave a Reply

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

This website uses cookies to give you the best experience. Most big webs do it also. Agree by clicking the 'Accept' button.