MySQL Master-Master Replication setup in 5 easy steps

MySQL Master-Master Replication setup in 5 easy steps

Introduction

Master-Master replication, also known as mirror, is by far the simplest technique you can use to increase the performance and the reliability of your MySQL server installation. If you don't know what it is, just imagine two MySQL server instances continuosly updating each other in real-time while fullfilling their job. In order to do that you'll need a second machine/server, meaning you'll have to sustain more costs: don't make this stop you - an investment like that is hardly worthless: conversely, it will most likely be a substantial improvement for your system. That's what you'll basically gain:

  • A 1:1 mirror your entire database pool (or a selection of your choice), ensuring you won't lose anything due to an hardware (or software/OS) crash.
  • A more robust, fail-proof and scalable environment, as long as you'll connect different web sites/services/apps to each of your DB and install a software or hardware http load balancer to distribute clients between them.

One of the most common scenarios involving Master-Master replication is when you have a 2 VPS (or hosting servers) environment/farm, each one providing an http web server (such as IIS or Apache) containing one or more web sites connected to a local MySQL server instance on the same machine. Once you put up a Master-Master replication mechanism between the two MySQL instances you'll basically have setup a real-time web mirror, which you can use as a disaster recovery (see picture below), and/or a 2-nodes web cluster managed by an hardware load-balancer responding to a third IP - or any other balancing technique you might want to adopt.

mysql-master-master-replication-diagram

Requirements

In order to setup a MySQL Master-Master replication you'll need two different Server system instances (VPS, Hosting, virtualized instances... anything will do), each one responding to a specific IP address: they don't need to be identical, as long as they can both properly run the same version of  MySQL Server (v5.1 or above is strongly suggested). We'll be calling them Server A and Server B from now on.

Step 1. Installing MySQL Server

The first thing you need to do is to install MySQL on Server A and Server B. If you already have a MySQL instance up and running on one of them, just upgrade it to the latest version you feel confident to use and then install that same identical version to the other one.

As soon as you're finished, edit the main config file - /ProgramData/MySQL/MySQL Server 5.x/my.ini on Windows/etc/mysql/my.cnf on Linux - and type-in the following settings, replacing the existing ones if present:

Server A

Server B

As you can see the cfg is almost identical, except for the server-id  and auto-increment-offsets  values.

Please notice that this configuration will replicate/mirror each and every db with the sole exception of test and information_schema. You might also want to keep out other databases such as mysql or other ones: if that's the case, just add those you want to exclude by adding a binlog-ignore-db and replicate-ignore-db command for each one of them.

IMPORTANT NOTE: replicating the mysql database seems to be a bad idea for Debian-based system: check this comment for details. If you're unsure about doing that, you might just choose to exclude that: the only thing you'll be missing would be syncing your users automatically, which isn't critical as long as you don't do that frequently.

It's also worth mentioning that you can also use an opt-in logic instead of the opt-out we've used here: just delete each and every binlog-ignore-db and replicate-ignore-db replacing them with binlog-add-db and replicate-add-db instead, specifying only the database you need/want to mirror.

Once you did that, you can stop the servers and restart them.

Step 2. Create the Replicator User(s)

Next thing you need to do is to create the replicator user in either Server A and Server B. You can do that using MySql shell using the following commands:

Replace [root_password] with your MySQL root user password and [replicator_password] with any password of your choice: you'll need to use that later on (see Step 4.5).

Fullfill this step for Server A and Server B: you can either use the same replicator password for both servers or pick a different one for Server A and Server B, as long as you'll use the right one during Step 4 and Step 5 ahead.

Step 3. Perform a full Sync

This is a task you'll need to do manually: you need to be sure that the two MySQL instances starts with the same data. If you're using MyISAM engine only, you can do that either by copying the relevant tables from your MySQL /data/ folder: alternatively, you can export / import your tables using MySQL statements/commands such as those described in the official MySQL manual. If you prefer to keep scripting to the minimum you can always use some external software who can take care of that, such as the Database Migration Tool included in the MySQL Workbench suite like shown in the above picture (read the how-to here):

ETXIo

Or the ultra-neat Copy Database to different Host/Database function featured by the excellent Webyog's SQLyog, which is also one of the most spectacular MySQL GUI ever made: if you aren't using it already, I strongly suggest you to download and install the Community Edition (multi-platform and open-source) from its official GitHub repo: you'll hardly regret that.
sqlyog.copy.db

Step 4. Configure replication from Server A to Server B

The next thing we need to to is to replicate all the commands received by Server A to Server B: in other words, we need to configure Server B as a slave of Server A. In order to do so, connect to Server A (via SQLyog, query browser or console) and type the following sql command:

You'll receive a status response from Server A like the following:

You're going to use the File and Position column values in a few.

Now connect to Server B (via SQLyog, query browser or console) and type the following sql commands:

Replace Server A IP Address with Server A numerical IPv4 address, [replicator_password] with the password you choose for your replicator user during Step 2, mysql-bin.000001 and 107 with the File and Position column values retrieved above.

The output from Server B should look similar to the following:

 

Step 5. Configure replication from Server B to Server A

Now you need to do the same thing from the other side. That is, replicate all the commands received by Server B to Server A, configuring the latter as a slave of the former. Connect to Server B and type the following sql command:

You'll receive a status response like the following:

Now connect to Server A (via SQLyog, query browser or console) and type the following sql commands:

Replace Server B IP Address with Server B numerical IPv4 address, replicator_password with the password you choose for your replicator user, mysql-bin.000001 and 107 with the File and Position column values retrieved above.

The output from Server A should look similar to the following:

... That's about it.

Test the Replication

Your MySQL Servers are now in Master-Master replication mode. You can test it in a number of ways. such as:

  1. create a new database using your favorite tool (or via MySQL console) on Server A, then connect to Server B to see if it's also there.
  2. create a new user on Server B, then connect to Server A to see if it's also there.
  3. issue some insert, delete and/or update query commands from Server A, and check that the same data will be inserted, deleted and/or modified also in Server B.

Notice that the first two tests will work only if you chose to replicate the mysql database (see Step 1): if you chose to exclude it, or to adopt the opt-in method, you'll need to create/delete the databases and sync the users manually.

UPDATE: If the Replication breaks, loses its sync or stops working, I strongly suggest you to take a look at this other post to fix it and/or to put up some countermeasures to prevent it from happening again.

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

23 Comments on “MySQL Master-Master Replication setup in 5 easy steps”

  1. Pingback: MySQL Replication resync and recovery strategies
  2. Hey fella, thanks for the HowTo. I was stucked for five days hardworking in my Java codes to synchronize data between my server and clients (small-local business) when I suddenly realized that I was doing a completely useless job.. The clients needs an almost instant sync with the servers cause they are kind of a time-critical, ATM-like machine… it was obvious that MySQL would provide replication capabilities. Coundt find a better tutorial than yours though. Your tips were great. Thanks a lot!

  3. I think there is a big problem with this setup:

    What if two queries insert data at the same time in the same database and table, one on server a and the other on server b? Then you have two different entries with the same id.

    You should have in the config entries:
    server 1:
    auto-increment-offset = 1

    server 2:
    auto-increment-offset = 2

    This way you can prevent this from happening.

    One server will only create even id’s: 2 4 6 8
    The other odd id’s: 1 3 5 7

    or do newer versions of mysql take care of something like this automatically?

    Also on debian based systems it is a bad idea to sync the mysql db, because apt creates a user on each one of those with a random password, which is remembered by apt. When apt updates the db on one of the servers the updates will not run anymore …

    1. You’re absolutely right: I’m not running the INSERT commands in both the DBs simultaneously (DR-BC setup), so I forgot to add the offsets. I’ve updated the post accordingly and also mentioned your comment in the post. Thank you for making it better.

      1. Actually, proper settings for auto-increment would be:

        server 1:
        auto-increment-offset = 1
        auto-increment-increment = 2

        server 2:
        auto-increment-offset = 2
        auto-increment-increment = 2

  4. Pingback: Impostare una Master-Master Replication tra due MySQL Server
  5. Pingback: How to Setup MySQL Master-Master Replication | WebSetNet
  6. Pingback: How to Setup MySQL Master-Master Replication – Kreation Next – Support
  7. Love the article and it’s the clearest one I have found for a total noob at advanced mysql. I have one question. The set up I need replication for would necessitate one mysql instance being offline for a few hours each day due to my use of mysql for broadcasting web radio. Sever A being on a machine that is on 24/7 and server B being on a normal use machine. Would this affect the opperation of the master master or would it be able to catch up?

  8. Lovely article, I will like to know if i can follow the instructions for an already existing installation of one server with users and databases, or how do I set up a replica for it if the steps stated are not applicable. Cheers

  9. Hi Ryan ,
    I have a Question , when master server down then automatically high availability slave will become master right. here how the master ip address assign to new master ….? in master slave replication

  10. Do both masters need to be running for this to work, what if one of them goes down or a period of time, and then comes back. For example mirroring the DB to a failover server that is used when the primary disappears due to an outage. It then comes back up , will all data be replicated back and it will carry on as normal?

    1. Both masters need to be running during the setup and until the mirroring has been completed. Once the setup is complete and both masters are up and running, if one of them goes down and then comes back it will be updated and put up on speed by the other one. That’s precisely the point of such kind of configuration.

  11. hello,

    on
    mysql> GRANT REPLICATION SLAVE ON . TO ‘replicator’@’%’ IDENTIFIED BY ‘password’;

    i get this:

    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 ‘IDENTIFIED BY ‘password” at line 1

    debian 10 (buster) here with mysql 8.x

    does this howto work on mysql 8 too?

    kind regards
    marko

  12. Pingback: MySQL master to master replication – error not able to restart service – Software Engineer Life

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.

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