How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora

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 CentOS 6.3 Operating System, but also work with older version of Linux distributions with MySQL 5.x.

UPDATE: If you’re looking for MariaDB Master-Slave Replication under CentOS/RHEL 7 and Debian 8 and it’s derivatives such as Ubuntu, follow this guide Setup MariaDB Master-Slave Replication.

mysql replication in Linux

MySQL Master-Slave Replication in RedHat / CentOS / Fedora

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.

  1. Working Linux OS like CentOS 6.3, RedHat 6.3 or Fedora 17
  2. Master and Slave are CentOS 6.3 Linux Servers.
  3. Master IP Address is: 192.168.1.1.
  4. Slave IP Address is: 192.168.1.2.
  5. Master and Slave are on the same LAN network.
  6. Master and Slave has MySQL version installed.
  7. Master allow remote MySQL connections on port 3306.

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 in Master Server

First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.

# yum install mysql-server mysql
Configure a MySQL in Master Server

Open my.cnf configuration file with VI editor.

# vi /etc/my.cnf

Add the following entries under [mysqld] section and don’t forget to replace tecmint with database name that you would like to replicate on Slave.

server-id = 1
binlog-do-db=tecmint
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

Restart the MySQL service.

# /etc/init.d/mysqld restart

Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.

# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 11128001 | tecmint		 |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> quit;

Please write down the File (mysql-bin.000003) and Position (11128001) 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.

#  mysqldump -u root -p --all-databases --master-data > /root/dbdump.db

Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.

mysql> UNLOCK TABLES;
mysql> quit;

Upload the database dump file on Slave Server (192.168.1.2) using SCP command.

scp /root/dbdump.db [email protected]:/root/

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 in Slave Server

If you don’t have MySQL installed, then install it using YUM command.

# yum install mysql-server mysql
Configure a MySQL in Slave Server

Open my.cnf configuration file with VI editor.

# vi /etc/my.cnf

Add the following entries under [mysqld] section and don’t forget to replace IP address of Master server, tecmint with database name etc, that you would like to replicate with Master.

server-id = 2
master-host=192.168.1.1
master-connect-retry=60
master-user=slave_user
master-password=yourpassword
replicate-do-db=tecmint
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

Now import the dump file that we exported in earlier command and restart the MySQL service.

# mysql -u root -p < /root/dbdump.db
# /etc/init.d/mysqld restart

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.000003) and Position (11128001) numbers. You must change 192.168.1.1 to the IP address of the Master Server, and change the user and password accordingly.

# mysql -u root -p
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001;
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 12345100
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 11381900
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: tecmint
          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: 12345100
              Relay_Log_Space: 11382055
              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)

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 Server
mysql> create database tecmint;
mysql> use tecmint;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;
+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)
On Slave Server

Verifying the SLAVE, by running the same command, it will return the same values in the slave too.

mysql> use tecmint;
mysql> SELECT * FROM employee;
+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)

That’s it, finally you’ve configured MySQL Replication in a few simple steps. More information can be found at MySQL Replication Guide.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Ravi Saive

I am Ravi Saive, creator of TecMint. A Computer Geek and Linux Guru who loves to share tricks and tips on Internet. Most Of My Servers runs on Open Source Platform called Linux. Follow Me: Twitter, Facebook and Google+

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

162 Responses

  1. Chetan says:

    mysqld service is not starting
    below is my my.cnf fine of slave side:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    # Settings user and group are ignored when systemd is used.
    # If you need to run mysqld under a different user or group,
    # customize your systemd unit file for mysqld according to the
    # instructions in http://fedoraproject.org/wiki/Systemd

    server-id = 2
    master-host=172.31.28.246
    master-connect-retry=60
    master-user=repl
    master-password=repl
    replicate-do-db=chetan
    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
    ~

  2. chiranjeevi says:

    Hi Team,

    Good Day…!!!

    Above Steps are not working, kindly check and do the needful.

  3. Adam says:

    Great tutorial…. might want to mention / mark in red on the Slave my.cnf file to change “binlog_do_db” to “replicate_do_db” took me forever to figure that out.

  4. Tony Gillett says:

    Hi,

    Sometime ago now we managed to set-up master/slave replication and everything has been working fine with no issues. Just one question really… How do you go about safely rebooting the master or slave server ?

  5. Ravi Kshirsagar says:

    Hi Ravi,
    Thank you very much for this post, but i am facing one issue.
    when i restart the mysqld service on slave, it doesn’t start.
    Would you please help me? explanation is given below.

    I have a mysql master server-192.168.56.5 and a slave server-192.168.56.6.
    Master COnfiguration:

    [mysqld]
    server-id = 1
    binlog-do-db=khandu
    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

    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

    Slave configuration:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    server-id=2
    master-host=192.168.56.5
    master-connect-retry=60
    master-user=slave_user
    master-password=khandu
    replicate-do-db=khandu
    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

    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    # Settings user and group are ignored when systemd is used (fedora >= 15).
    # If you need to run mysqld under a different user or group,
    # customize your systemd unit file for mysqld according to the
    # instructions in http://fedoraproject.org/wiki/Systemd
    user=mysql

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    i have configured from shell like below .
    CHANGE MASTER TO MASTER_HOST=’192.168.56.5′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’khandu’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=329;

    up to this step replication works perfectly, but when i restart the mysql on slave side, it generates the error

    150318 12:00:06 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    150318 12:00:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    150318 12:00:56 [Note] Plugin ‘FEDERATED’ is disabled.
    150318 12:00:56 InnoDB: The InnoDB memory heap is disabled
    150318 12:00:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    150318 12:00:56 InnoDB: Compressed tables use zlib 1.2.3
    150318 12:00:56 InnoDB: Using Linux native AIO
    150318 12:00:56 InnoDB: Initializing buffer pool, size = 128.0M
    150318 12:00:56 InnoDB: Completed initialization of buffer pool
    150318 12:00:56 InnoDB: highest supported file format is Barracuda.
    150318 12:00:56 InnoDB: Waiting for the background threads to start
    150318 12:00:57 InnoDB: 5.5.36 started; log sequence number 1618405
    150318 12:00:57 [ERROR] /usr/libexec/mysqld: unknown variable ‘master-host=192.168.56.5′
    150318 12:00:57 [ERROR] Aborting

    150318 12:00:57 InnoDB: Starting shutdown…
    150318 12:00:58 InnoDB: Shutdown completed; log sequence number 1618405
    150318 12:00:58 [Note] /usr/libexec/mysqld: Shutdown complete

    150318 12:00:58 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

    again i need to reconfigure this via shell:- CHANGE MASTER TO MASTER_HOST=’192.168.56.5′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’khandu’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=329;

    please help.

    Thanks!!

    • Ravi Saive says:

      @Ravi,
      I think you’ve syntax error in your command, use the correct single quotes as described in this following command.

      CHANGE MASTER TO MASTER_HOST='192.168.56.5', MASTER_USER='slave_user', MASTER_PASSWORD='khandu', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=329;
      
  6. Suraj says:

    I have tried the same but getting mention error. Unable to sync database

    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.1.25
    Master_User: root
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000003
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysql-relay-bin.000004
    Relay_Log_Pos: 251
    Relay_Master_Log_File: mysql-bin.000003
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: testing,test
    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: 106
    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> use testing;
    Database changed
    mysql> SELECT * FROM employee;
    ERROR 1146 (42S02): Table ‘testing.employee’ doesn’t exist
    mysql>

    • Ravi Saive says:

      @Suraj,
      Your replication is working fine, but I think you need to first dump the mysql databases from master to slave server and then take the master binlog to slave server and restart the mysql at slave..

  7. Zuhad says:

    hi ravi boss this its very good boos i have some issue second behind master null after i log out my application

  8. Yogesh says:

    Hello Ravi,

    Thanks for your suggestion.
    I would also want to know how to make slave server as a master in case master gets down. Please assist we want to implement this environment in our data center.

    Thanks in advance.
    Regards,
    Yogesh P.

  9. Yogesh says:

    Hi,

    Nice article.

    We are trying to create new database but it wont replicate. It is showing old database which we have tested during the installation.

    Actually we want to replicate multiple databases so is it necessary to mention each databaase name and database username in my.cnf file ?

    If no, how can we mention the parameter in my.cnf file.

    Please update.
    Thanks
    Yogesh P.

  10. shaqib says:

    Find below my.cnf for my Master server

    server-id = 1
    binlog-do-db=shaqib1
    binlog-to-db=shaqib2
    binlog-to-db=shaqib3
    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

    i save and then i restart mysql, i got the following errors.

    141015 11:33:00 mysqld started
    141015 11:33:00 [ERROR] /usr/libexec/mysqld: unknown variable ‘binlog-to-db=shaqib2’
    141015 11:33:00 mysqld ended

    pls urgent.

    • shaqib says:

      Sorry i notice my mistake.

      I correct it and my mysqld service start successfully.

      Now I am getting another problem, i created a table on my master, but is is not replicated on my slave. where i might be wrong?

Got something to say? Join the discussion.

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.