• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
sharadchhetri

sharadchhetri

Tutorials On Linux, Unix & Open Source

  • Home
  • Linux Commands
  • Resources
    • Learn Linux
  • My WordPress plugins

Setup MySQL master slave replication in CentOS 6

November 21, 2013 by Sharad Chhetri 11 Comments

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: 192.168.56.121
Slave Server ip address : 192.168.56.108

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

yum install mysql mysql-server mysql-devel

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)

cp -p /etc/my.cnf /etc/my.cnf.orig.`date +%F`

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 192.168.56.121 with your Master Server IP address

vi /etc/my.cnf
[mysqld]    (Do not write this line,find this [mysqld] section and write below given contents)

bind-address=192.168.56.121
server-id=1
binlog-ignore-db = "mysql"
binlog-format = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Reference to read about innodb parameter

Step 4:Restart MySQL server

/etc/init.d/mysqld restart

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:

mysql > CREATE USER replication-mysql-username@ip-address-of-Slave-Server;
mysql > GRANT REPLICATION SLAVE ON *.* TO replication-mysql-username@ip-address-of-Slave-Server IDENTIFIED BY 'password';
mysql > flush privileges;
mysql > exit;

In our scenario it will be:

CREATE USER replication@192.168.56.108;
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.56.108 IDENTIFIED BY 'password';
flush privileges;

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

mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status;

mysql> unlock tables;

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

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Note: You can also get information of MASTER_LOG_FILE,MASTER_LOG_POS from MySQL dump file.

eg.

[root@localhost ~]# head -100 database-name-backup.sql | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;
[root@localhost ~]#

Step 8: Now move you database backup dump file to slave server

scp database-name-backup.sql root@slave-ip-address:~

In our scenario:

scp  database-name-backup.sql root@192.168.56.108:~

Configuration steps in MySQL Slave Server

Step 9: Login in Slave MySQL server and take the backup of my.cnf file

cp -p /etc/my.cnf /etc/my.cnf.orig.`date +%F`

Step 10: Now edit the my.cnf file and paste below given contents in [mysqld] section

vi /etc/my.cnf
[mysqld]       (Do not write this line,find this [mysqld] section and write below given contents)

server-id=50
binlog-format=mixed
log_bin=mysql-bin
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

Step 11: Restart the mysql service in Slave server

/etc/init.d/mysqld restart

Step 12: Login to MySQL and create database and exit

mysql > create database database-name;
mysql > exit;

Step 13: Now restore the dump file in database

mysql -u root -p database-name < database-name-backup.sql

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 192.168.56.121 with your Master MySQL ip address.

mysql > CHANGE MASTER TO MASTER_HOST='192.168.56.121',MASTER_USER='replication',MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

Step 15: Now run the slave and check its status.

mysql > START SLAVE;
mysql > SHOW SLAVE STATUS G;

Now the slave server is up and running and ready to replicate

Note:To stop slave run the command

mysql > STOP SLAVE;

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


mysql > STOP SLAVE;
mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql > START SLAVE;

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.

Share this:

  • Twitter
  • Facebook
  • More
  • Print
  • Email
  • LinkedIn
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Mastodon

Related posts:

  1. Set delay replication to MySQL slave server
  2. Setup Master Slave Chroot BIND DNS in CentOS 6 or Red Hat 6
  3. Find mysql commands history without mysql server login
  4. master admin password openerp 7.x
  5. How to install mysql server in RedHat and CentOS
  6. How to create multiple mysql instance in CentOS 6.4 and Red Hat 6.4
  7. How to install MySQL Server 5.6 on CentOS 7 / RHEL 7
  8. error: src refspec master does not match any
  9. Install MySQL Server 5.6 in CentOS 6.x and Red Hat 6.x Linux
  10. How to check default storage engine in MySQL server

Filed Under: Linux, mysql Tagged With: master-slave, mysql

Reader Interactions

Comments

  1. Elex says

    September 2, 2015 at 6:43 am

    Hi, Please add a detail easy to use post on mongodb replication on Centos/RHEL/fedora and Ubuntu/Debian/Mint.
    Thanks.

    Reply
    • sharad chhetri says

      September 2, 2015 at 7:01 am

      Thanks Elex,

      We will cover this topic soon.

      Regards
      Sharad

      Reply
  2. Ravi says

    August 6, 2015 at 1:34 pm

    Sharad,

    Good explanation. I would like to ask you about an issue where mysql server slave seconds behind master keeps on increasing.

    Have you ever faced this kind of situation? Please let me know Any likely points you know which can be used to resolve the issue.

    Reply
  3. Manoj says

    April 23, 2015 at 12:47 am

    Hello sharad, first of thankyou for your wonderful post, I am trying to setup the mysql + apache clustering for my web server using nginx load balancer, I reached a lot but couldn’t find the starting point, do you have any idea

    Thanks
    Manoj Nepal

    Reply
    • sharad chhetri says

      April 24, 2015 at 4:24 pm

      Hello Manoj,

      Lets have a look into below given network diagram link, let me know if something like this you are looking for.

      Network Diagram

      Regards
      Sharad

      Reply
  4. Ankit Agrawal says

    December 4, 2014 at 1:48 am

    Hi Sharad,

    I was able to create multiple instances but I wanted to use one instance as a server and the other as slave. Is it possible? Can you please guide me on this?

    Thanks in advance!

    Thanks and Regards,
    Ankit

    Reply
    • sharad chhetri says

      December 4, 2014 at 3:30 am

      Hello Ankit,

      As you said you have setup the Multiple mysql instance. Now follow this same guide. Before starting, edit /etc/hosts file and use two different loopback address because we are setting up mysql replication in same server. As per tutorial, replace master and slave ip address with respect to loopback address you have assigned.

      127.0.0.2 localhostmysqlmaster
      127.0.0.3 localhostmysqlslave
      

      In case, if your system has different private ip addresses then no need to use loopback. You can use these two private address for Master and Slave.

      Regards
      Sharad

      Reply
  5. Ankit Agrawal says

    December 3, 2014 at 6:43 am

    Hey sharad,

    Can you please assist me with how to set master slave replication on the same machine but at different instances of mysql?

    Thanks in advance!

    Reply
    • sharad chhetri says

      December 3, 2014 at 7:51 am

      Hello Ankit,

      You can setup with the help of MySQL multiple instance in same machine. Both instance will run on different port.
      But I still recommend to go with different server if it is production environment.

      As per your OS. Use any of the tutorial for setting Multiple MySQL instance.
      https://sharadchhetri.com/2013/12/02/create-multiple-mysql-instance-centos-6-4-red-hat-6-4/
      https://sharadchhetri.com/2013/12/04/how-to-configure-multiple-mysql-instance-in-ubuntu/

      Regards
      Sharad

      Reply
  6. Showket says

    February 6, 2014 at 10:46 am

    This is a very useful tutorial for me. As I am very clear on this. Yes I did not try it yet in my practical fields. However Thanks to the author for this tutorial.

    Showket

    Reply
    • sharad chhetri says

      February 6, 2014 at 3:36 pm

      Your Welcome Showket

      Reply

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Our Social Media Presence

  • Facebook
  • GitHub
  • Twitter

Linux Command

What is Linux Internal And External Command

Linux Basic Commands With Examples For Every Beginner

tr command to convert lines to space , tab and vertical tab

smbpasswd command not found on CentOS 7 and RHEL 7

Solution : semanage command not found

Unix / Linux : How to print duplicate lines from file

More Posts from this Category

You Might Like These Articles!

simplecodesyntax wordpress plugin

SimpleCodeSyntax : My Another WordPress Plugin

Install Nginx

How To Install Nginx On Ubuntu 22.04 LTS

Install Latest Git package in Ubuntu Operating System

How To Always Install Latest Git Package In Ubuntu Operating System

Bash script for installing VirtualBox on Ubuntu 22.04 LTS Desktop

Install VirtualBox On Ubuntu 22.04 LTS Desktop (Bash Script)

libfuse

dlopen(): error loading libfuse.so.2 – Got Error On Ubuntu

Failed to open/create the internal network

VirtualBox Error: Failed to open/create the internal network

Always Useful Tips And Tricks

How to convert float to integer number

How to change login banner message in GUI mode in CentOS 6 or above version.

How to zip the directory in linux with command line

How to see line numbers in file through cat command

How To Get SSH Public Key Fingerprint Information

Error: Could not stat() command file ‘/var/lib/nagios3/rw/nagios.cmd’!

How to create a file with cat command

Explore 90+ Article On "Linux Tips And Tricks"

Copyright © 2023 ·
The material in this site cannot be republished either online or offline, without our permission.
Proudly Blogging From Bharat.

  • Contact
  • About Me
  • My WordPress plugins
  • Privacy Policy