Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Databases»MariaDB»How to Set Up MySQL Master-Slave Replication

    How to Set Up MySQL Master-Slave Replication

    RahulBy RahulJuly 20, 20133 Mins Read

    This article will help you to setup master-slave replication between MySQL servers. We are using MySQL 5.5 on CentOS 6.3. I hope it will help you to setup replication.

    Setup Details:
    Master Server: 192.168.1.10
    Slave  Server: 192.168.1.20
    Database: mydb
    

    If you are using different – different versions of MySQL on Master and Slave use this link to check compatibility.

    1. Setup MySQL Master Server

    • Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master.
    • mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20' IDENTIFIED BY 'secretpassword';
      mysql> FLUSH PRIVILEGES;
      
    • Block write statement on all the tables, so not changes made after taking backup.
    • mysql> use mydb;
      mysql> FLUSH TABLES WITH READ LOCK;
      mysql> exit;
      
    • Edit MySQL configuration file and add the following lines under [mysqld] section.
    • # vim /etc/my.cnf
      
      [mysqld]
      log-bin=mysql-bin
      binlog-do-db=mydb
      server-id=1
      innodb_flush_log_at_trx_commit=1
      sync_binlog=1
      
      
    • Restart master mysql server to changes take effect.
    • # service mysqld restart
      
    • Check the current binary log file name (File) and current offset (Position) value using following command.
    • mysql > SHOW MASTER STATUS;
      +------------------+----------+--------------+------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +------------------+----------+--------------+------------------+
      | mysql-bin.000002 |      107 | mydb         |                  |
      +------------------+----------+--------------+------------------+
      

      The above output is showing that the current binary file is using mysql-bin.000002 and offset value is 107. Note down these values to use on slave server.

    • Take a backup of database and copy it to slave mysql server.
    • # mysqldump -u root -p mydb > mydb.sql
      # scp mydb.sql 192.168.1.20:/opt/
      
    • After completing backup remove the READ LOCK from tables, So that changes can be made.
    • mysql> UNLOCK TABLES;
      

      2. Setup MySQL Slave Server

    • Edit salve mysql configuration file and add following values under [mysqld] section.
    • # vim /etc/my.cnf
      
      [mysqld]
      server-id=2
      replicate-do-db=mydb
      

      server-id always be an non zero numeric value. These value will never be similar with other master and slave servers.

    • Restart mysql slave server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.
    • # /etc/init.d/mysqld restart
      
    • Restore database backup taken from master server.
    • # mysql -u root -p mydb < mydb.sql
      
    • Setup option values on slave server using following command.
    • mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.10',
          -> MASTER_USER='repl_user',
          -> MASTER_PASSWORD='secretpassword',
          -> MASTER_LOG_FILE='mysql-bin.000002',
          -> MASTER_LOG_POS=107;
      
    • Finally start the slave thread
    • mysql> SLAVE START;
      
    • Check the status of slave server.
    • mysql> show slave status G
      
      *************************** 1. row ***************************
                     Slave_IO_State:
                        Master_Host: 192.168.1.15
                        Master_User: repl_user
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mysql-bin.000002
                Read_Master_Log_Pos: 107
                     Relay_Log_File: mysqld-relay-bin.000001
                      Relay_Log_Pos: 4
              Relay_Master_Log_File: mysql-bin.000002
                   Slave_IO_Running: No
                  Slave_SQL_Running: No
                    Replicate_Do_DB: mydb
                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: 107
                    Relay_Log_Space: 107
                    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: NULL
      Master_SSL_Verify_Server_Cert: No
                      Last_IO_Errno: 0
                      Last_IO_Error:
                     Last_SQL_Errno: 0
                     Last_SQL_Error:
        Replicate_Ignore_Server_Ids:
                   Master_Server_Id: 1
      1 row in set (0.00 sec)
      mysql>
      

    MySQL Master-slave Replication has been configured successfully on your system and in working mode. Thanks for using this tutorial, Kindly do not forgot to share it with your friends.

    master-slave replication MySQL mysql replication replication
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleHow to Remove Empty Lines from File
    Next Article A Simple Bash Script for MySQL Database Backup

    Related Posts

    How To Install Linux, Nginx, MySQL, & PHP (LEMP Stack) on Ubuntu 22.04

    Updated:April 7, 20227 Mins Read

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How To Install LAMP Stack on Ubuntu 22.04 LTS

    Updated:April 20, 20225 Mins Read

    How To Install MariaDB on Debian 11

    4 Mins Read

    How to Rename MySQL Database

    Updated:July 26, 20213 Mins Read

    How To Change MySQL User Password

    2 Mins Read

    7 Comments

    1. Nanda on July 11, 2018 1:01 am

      Don’t forget to write firewall-cmd statements which allows 3306 port and mysql service.

      Reply
    2. buyd on July 17, 2017 2:50 am

      gotta try this

      Reply
    3. Hire Joomla Developers on September 9, 2016 5:33 am

      This is what i was looking for. Thank you so much for sharing this. Very helpful.

      Reply
    4. Sunny on November 4, 2014 2:48 pm

      hi all,

      i have followed all the steps mentioned above.

      But did not work.

      it is not useful …

      Reply
      • Rahul on November 4, 2014 8:10 pm

        Hi Sunny,

        What issue are you facing here…?

        Reply
      • Nanda on July 11, 2018 3:10 am

        have you added port n service in firewall??

        Reply
    5. omprakash on September 13, 2014 12:32 pm

      Hi Rahual,

      Above your all steps are ok if database does not using triggers if database are using triggers then it is not successfully. so please add all steps for with triggers replication.

      Short Example:-
      if database are using triggers then would be configure row-based replication.
      mysqldump -u root –skip-triggers database > database.sql

      master server : my.cnf
      [mysqld]
      binlog_format = row
      mysql> show variables like “binlog_format”;
      …………………………………………………………………………………
      Slave server:- my.cnf
      [mysqld]
      binlog_format = row
      mysql> show variables like “binlog_format”;

      Reply

    Leave A Reply Cancel Reply

    Recent Posts
    • How to Enable / disable Firewall in Windows
    • How to Install JAVA on Ubuntu 22.04
    • Switching Display Manager in Ubuntu – GDM, LightDM & SDDM
    • Changing the Login Screen Background in Ubuntu 22.04 & 20.04
    • How To Install PHP (8.1, 7.4 or 5.6) on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.