This tutorial describes how to manually create a multi-master replication system using the MySQL Console

The examples in this article will be based on 3 MySQL server machines as shown below:

Following steps are needed to set up a multi-master replication system consisting of 3 servers as shown above:

  1. Configure Server 1 as a Master
  2. Configure Server 2 as a Master
  3. Configure Server 3 as a Master
  4. Record the Master Binary Log Info on all server
  5. Configure Server 3 as a Slave
  6. Configure Server 2 as a Slave
  7. Configure Server 1 as a Slave

Before we start the configuration process, stop the DualShield service on all machines. If we're going to make changes to an existing replication cluster, such as adding a new master/slave server, then stop the slave process on all MySQL servers in the replication cluster.

Step 1: Configure Server 1 as a Master

We will carry out the tasks below on server 1:

  1. Creating a user for replication
  2. Setting up the replication Master
  3. Taking a database snapshot (optional)

1.1 Creating a user for replication

We need to create a pseudo-user on this server (master) that will be used by other servers (slaves) to pull replication data from this server.

1.2 Setting up the replication Master

We will now enable this server as a replication master. This involves making changes in the MySQL configuration file. 

1.3 Taking a database snapshot

If the DualShield database has already been running for some times, then we need to take a snapshot of the database which will be copied and imported into the save servers. 

Step 2: Configure Server 2 as a Master 

We will carry out the tasks below on server 2: 

  1. Importing the database snapshot (optional)
  2. Creating a user for replication
  3. Setting up the replication Master

2.1 Importing the database snapshot

2.2 Creating a user for replication 

We need to create a pseudo-user on this server (master) that will be used by other servers (slaves) to pull replication data from this server. 

2.3 Setting up the replication Master 

We will now enable this server as a replication master. This involves making changes in the MySQL configuration file. 

Step 3: Configure Server 3 as a Master 

Follow the same procedure in Step 2 to to configure Server 3 as a Master

Step 4: Record the Master Binary Log Info on all Servers 

We need to obtain and record the master binary log file and position on all servers 

Step 5: Configure Server 3 as a Slave 

Now, we will set up the replication slave on Server 3, connecting to Server 1 & 2 as the master. 

Step 6: Configure Server 2 as a Slave

Now, we will set up the replication slave on Server 2, connecting to Server 1 & 3 as the master. 

Step 7: Configure Server 1 as a Slave

Now, we will set up the replication slave on Server 1, connecting to Server 2 & 3 as the master. 


References:

MySQL Multi-Source Replication