Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»MariaDB»How to Set Up MySQL Master-Master Replication

    How to Set Up MySQL Master-Master Replication

    By RahulJuly 23, 20134 Mins Read

    MySQL Master-Slave replication is to set up slave server to update immediately as soon as changes done in Master server. But it will not update Master if there are any changes done on slave server. Read this article to setup Master-Slave replication.

    Advertisement

    This article will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one.

    Setup Details:
    Master-1: 192.168.1.15
    Master-2: 192.168.1.16
    Database: mydb
    

    If you are using different – different versions of MySQL on either servers use this link to check compatibility.

    Step 1. Set Up MySQL Master-1 Server

    • 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
      
    • Restart MySQL server to changes take effect.
    • # service mysqld restart
      
    • Create an mysql account on Master-1 server with REPLICATION SLAVE privileges to which replication client will connect to master.
    • mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
      mysql> FLUSH PRIVILEGES;
      
    • Block write statement on all the tables, so no changes can be made after taking backup.
    • mysql> use mydb;
      mysql> FLUSH TABLES WITH READ LOCK;
      
    • 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.000003 |      332 | mydb         |                  |
      +------------------+----------+--------------+------------------+
      1 row in set (0.00 sec)
      

      The above output is showing that the current binary file is using mysql-bin.000003 and offset value is 332. Note down these values to use on Master-2 server in next step.

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

      Step 2. Setup MySQL Master-2 Server

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

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

    • Restart MySQL server, If you had already configured replication use –skip-slave-start in start to not to immediate connect to master server.
    • # service mysqld restart
      
    • Restore database backup taken from master server.
    • # mysql -u root -p mydb < /opt/mydb.sql
      
    • Create an mysql account on Master-1 server with REPLICATION SLAVE privileges to which replication client will connect to master.
    • mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'secretpassword';
      mysql> FLUSH PRIVILEGES;
      
    • 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.000001 |      847 | mydb         |                  |
      +------------------+----------+--------------+------------------+
      1 row in set (0.00 sec)
      

      The above output is showing that the current binary file is using mysql-bin.000001 and offset value is 847. Note down these values to use in Step 3.

    • Setup option values on slave server using following command.
    • mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.15',
          -> MASTER_USER='repl_user',
          -> MASTER_PASSWORD='secretpassword',
          -> MASTER_LOG_FILE='mysql-bin.000003',
          -> MASTER_LOG_POS=332;
      

      Step 3: Complete Setup on MySQL Master-1

      Login to MySQL Master-1 server and execute following command.

      mysql>  CHANGE MASTER TO MASTER_HOST='192.168.1.16',
           MASTER_USER='repl_user',
           MASTER_PASSWORD='secretpassword',
           MASTER_LOG_FILE='mysql-bin.000001',
           MASTER_LOG_POS=847;
      
      Step 4: Start SLAVE on Both Servers

      Execute following command on both servers to start replication slave process.

      mysql> SLAVE START;
      

      MySQL Master-Master Replication has been configured successfully on your system and in working mode. To test if replication is working make changes on either server and check if changes are reflecting on other server.

      Thanks for using this tutorial, If it’s helpful for you then please do not forgot to share it with your friends.

    Master-Master Replication mysql replication replication
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Installing MySQL 8.0 on Amazon Linux 2

    How To Install MySQL 8 on Amazon Linux 2

    (Resolved) Unknown collation: utf8mb4_unicode_520_ci

    How To Install phpMyAdmin on Ubuntu 22.04

    How To Install phpMyAdmin on Ubuntu 22.04

    View 11 Comments

    11 Comments

    1. steve on November 20, 2019 11:31 am

      How to do multiple databases? I added them but they never sync’d to the slave server. I tried reset master and updating both my.cnf

      Reply
      • steve on November 20, 2019 5:37 pm

        I completely rebuilt to servers and included the databases along with mydb and at least for now I can see all of my databases. I still would like to know if you can help me with “how” to add additional databases.

        2nd Issue I have is that from the master to the slave (i set it up for master-master with the above instructions” The slave can’t add any tables to the new databases. It sees the changes made on the master , the slave just can’t make any changes. Can you tell me what I need to look for to make that happen?
        TIA

        Reply
    2. kostyanius on October 12, 2017 11:56 am

      Hello,
      You have a typo in last STEP 4:
      There is needed “START SLAVE” instead of “SLAVE START”.

      Reply
    3. Prasath S on March 30, 2017 8:55 am

      Hi,

      I want multiple master to master (3 to 3)..
      i will some update automateing take 3 servers ..

      Reply
    4. ashad on December 6, 2015 9:27 am

      I want Multi master Replication (All
      Masters) in 3 Nods With Out Circular .
      How its possible Please give me an Example .

      Reply
    5. Jane on March 11, 2015 1:30 pm

      Probably you have some mistake
      Step 1. Set Up MySQL Master-1 Server
      server-id=1

      Step 2. Setup MySQL Master-2 Server
      server-id=1

      It is have to be right this on the Master-2
      server-id=2

      Reply
    6. khodadad nejadkoorki on October 16, 2014 5:50 pm

      Dear Rahul ,

      Thanks for the good document,
      I think there is some problem as below:
      In situation that the tables in the replicated DB contain field with type auto-increament ; because this field is written separately by DBMSs, the DB goes to an inconsistent state.

      Best
      Khodadad

      Reply
    7. mydb on February 26, 2014 12:35 am

      OK, this is not working with MySQL 5.6+. You need 4 shells open for this process…

      -it don’t work if you have gtid enabled.
      ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
      i had no solution to this but to disable it.

      -mysql -u root -p mydb < /opt/mydb.sql
      results in ERROR 1049 (42000): Unknown database mydb
      so i created it manually on server 2: create database mydb;

      -Steps CHANGE MASTER TO MASTER_HOST=… result in
      Query OK, 0 rows affected, 2 warnings (0.42 sec)

      SLAVE START; result in
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE START' at line 1

      the correct query is:
      start slave;

      but the db is not syncing…

      Reply
      • Keith Bronstrup on July 16, 2014 4:18 pm

        I know I’m a few months late with this answer, but someone might find it useful.

        If you’re getting ERROR 1840 when trying to import a dump from the master DB while using GTID, you need to
        mysql> reset master;

        Then import the dump as normal; the dump contains the correct GTID information and will set up the master data correctly again.

        Reply
    8. John on July 28, 2013 5:20 pm

      Again an awesome article … I got many useful articles here. thanks again

      Reply
    9. Nitesh Yadav on July 28, 2013 6:40 am

      nice article… thank you so much

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Split Large Archives in Linux using the Command Line
    • System.out.println() Method in Java: A Beginner’s Guide
    • Split Command in Linux With Examples (Split Large Files)
    • Test Your Internet Speed from the Linux Terminal
    • 11 Practical Example of cat Command in Linux
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

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