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.

If you liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

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

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

162 thoughts on “How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora”

    • @Anshul,

      You can create x number MySQL clusters, just make sure you’ve mentioned correct MySQL id in /etc/my.conf file for each node..

      Reply
  1. Failed to import dbdump file on slave side….

    Getting error while running the command….

    # mysql -u root -p < /root/dbdump.db (On the slave node)

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

    I have same os on both machine (i.e. Rhel7)

    Plz.. give complete solution

    Reply
    • @Gulshan,

      Please make sure that MySQL started and running, if not you can start it using:

      # service mysqld restart
      OR
      # systemctl restart mysqld
      
      Reply
  2. Hi,

    Getting error while running the command….

    # mysql -u root -p < dbstore.db (On the slave node)

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

    Reply
  3. I’ve two VM located on Digital Ocean and Microsoft Azure, I use my Digital Ocean as master and Microsoft Azure as slave. Can I implement replication in different server like Digital Ocean and Azure?

    Reply
      • Hi Ravi,

        I mean same OS (linux) but different server. I’ve ubuntu in DO and Azure, so can I do master-slave replication?

        Reply
        • @Muhammad,

          As fas as both machine with different Linux OS will work without any issues, yes you can do a master-slave MySQL replication without any doubt…:)

          Reply
  4. thanks for reply..can u please make tutorial on or write blog for how to use replication for wamp server one slave and multiple masters.Thanks.

    Reply
  5. nice post..I am using master on windows,and my server is on cent-OS..whenever I try to run CHANGE MASTER TO…. this gives me error like slave is not configured or server_id is not set on master or slave…Thanks

    Reply
      • @Yogesh,

        If am not wrong WAMP means Windows, Apache, MySQL and PHP? if yes, then how come these commands work on Windows? better have a Linux..

        Reply
      • Slave is not configured or failed to initialize properly. You must at least set –server-id to enable either a master or a slave.
        now I am getting this error..1st time its work after second time when I try to look out through START SLAVE…STOP SLAVE its produced this error..can u explain why?

        +—————+——-+
        | Variable_name | Value |
        +—————+——-+
        | server_id | 11 |
        +—————+——-+

        my.cnf file has
        server_id = 11
        replicate_do_db = dbname

        Reply
          • @Yogesh,

            Sorry, as I said it’s difficult to answer you question, as you’re using two different OS’s for same purpose, and I don’t think it work that way, both machine must be Linux operating systems, in order to work properly…..

Leave a Reply to Tapas Sen Cancel reply

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.

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