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.


Launch Windows Console (Run as Administrator)

Change to MySQL bin folder

cd C:\Program Files\Deepnet DualShield\mysql\bin

Login into MySQL console by entering

mysql -u root -p

When prompted, enter the password of the root mysql user.

We need to create a pseudo-user that will be used for replicating data across all servers. The examples in this article will assume that you name this user "replicator". Replace "password" with the password you wish to use for replication.

mysql> create user 'replicator'@'%' identified by 'password';

Next, we need to give this user permissions to replicate our mysql data: 

mysql> grant replication slave on *.* to 'replicator'@'%';  
mysql> flush privileges;



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. 

To configure a MySQL to become a replication master, we must enable binary logging (log-bin) and establish a unique server ID (server-id). 

1. Launch Windows Console (Run as Administrator)

2. Change to MySQL folder

cd C:\Program Files\Deepnet DualShield\mysql

3. Open "das.ini" file in a text editor, e.g. Notepad, insert the following settings:

[mysqld]
server-id=1/2/.../N
auto_increment_increment=N
auto_increment_offset=1/2/.../N
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
replicate-do-db=DualShield
replicate-same-server-id=0
binlog_do_db=DualShield

- "server-id" is to uniquely identify the current server in the replication cluster.  

- "auto_increment_increment" controls the increment between successive AUTO_INCREMENT values.

- "auto_increment_offset" determines the starting point for AUTO_INCREMENT column values.

Assuming that we have N servers in the replication cluster, then each server must have a different value for "server-id" and "auto_increment_offset" (1, 2, ..., N), and "auto_increment_increment" has the value N on all servers.

- "replicate-do-db" indicates which database we want to replicate across servers.

By default, the database name created for DualShield is called "DualShield". If you have changed the default name then you need to change it here accordingly.

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. 

If the master database contains existing data it is necessary to copy this data to each slave.

Before preceding it may be a good idea to truncate the log files as this process could take some time.  This will help shorten the process: Truncate audit trail [MC-TKTD]

To create a snapshot of the data in an existing master database, use the mysqldump tool.

1. Start a MySQL console session on the master

2. Flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;

3. Start a different OS console session on the master, run the mysqldump command to export the DualShield database

> mysqldump -u root -p --opt DualShield > DualShield.sql

4. Close the second console session. Go back to the first console session and run the command below to release the lock

mysql> UNLOCK TABLES;

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

When setting up replication with existing data, transfer the snapshot from the master to the slave before starting replication.

Run the command below to import a database snapshot:

> mysql -u root -p DualShield < DualShield.sql

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. 


Launch Windows Console (Run as Administrator)

Change to MySQL bin folder

cd C:\Program Files\Deepnet DualShield\mysql\bin

Login into MySQL console by entering

mysql -u root -p

When prompted, enter the password of the root mysql user.

We need to create a pseudo-user that will be used for replicating data across all servers. The examples in this article will assume that you name this user "replicator". Replace "password" with the password you wish to use for replication.

mysql> create user 'replicator'@'%' identified by 'password';

Next, we need to give this user permissions to replicate our mysql data: 

mysql> grant replication slave on *.* to 'replicator'@'%';  
mysql> flush privileges;



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. 

To configure a MySQL to become a replication master, we must enable binary logging (log-bin) and establish a unique server ID (server-id). 

1. Launch Windows Console (Run as Administrator)

2. Change to MySQL folder

cd C:\Program Files\Deepnet DualShield\mysql

3. Open "das.ini" file in a text editor, e.g. Notepad, insert the following settings:

[mysqld]
server-id=1/2/.../N
auto_increment_increment=N
auto_increment_offset=1/2/.../N
log-bin=mysql-bin
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
replicate-do-db=DualShield
replicate-same-server-id=0
binlog_do_db=DualShield

- "server-id" is to uniquely identify the current server in the replication cluster.  

- "auto_increment_increment" controls the increment between successive AUTO_INCREMENT values.

- "auto_increment_offset" determines the starting point for AUTO_INCREMENT column values.

Assuming that we have N servers in the replication cluster, then each server must have a different value for "server-id" and "auto_increment_offset" (1, 2, ..., N), and "auto_increment_increment" has the value N on all servers.

- "replicate-do-db" indicates which database we want to replicate across servers.

By default, the database name created for DualShield is called "DualShield". If you have changed the default name then you need to change it here accordingly.

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 

To configure the slave to start the replication process at the correct point, you need the master's current coordinates within its binary log.

If the master has been running previously without binary logging enabled, the log file name and position values displayed by SHOW MASTER STATUS are empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

If the master has been binary logging previously, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

mysql> SHOW MASTER STATUS;

The output will looking similar to the following, and will have two pieces of critical information:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The File column shows the name of the log file and the Position column shows the position within the file. In this example, the binary log file is mysql-bin.000001 and the position is 107. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.


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. 

To connect to a master for replication, run the CHANGE MASTER command with the necessary connection information of the master.

1. Run the command below to connect to Server 1, replacing the option values with the actual values we got from the master server 1:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 FOR CHANNEL 'Server-1';
  • MASTER_HOST='IP Address of the master server'
  • MASTER_USER='replicator'
  • MASTER_PASSWORD='password'
  • MASTER_LOG_FILE='Name of the binary log file on the master server '
  • MASTER_LOG_POS=Position of the binary log file on the master server
  • FOR CHANNEL 'Name of the connection channel'

2. Repeat the above process for each other master that we want to connect to, changing the host IP address, binary log info and channel as appropriate.

3. Finally start the slave thread.

mysql> START SLAVES;

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. 

To connect to a master for replication, run the CHANGE MASTER command with the necessary connection information of the master.

1. Run the command below to connect to Server 1, replacing the option values with the actual values we got from the master server 1:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 FOR CHANNEL 'Server-1';
  • MASTER_HOST='IP Address of the master server'
  • MASTER_USER='replicator'
  • MASTER_PASSWORD='password'
  • MASTER_LOG_FILE='Name of the binary log file on the master server '
  • MASTER_LOG_POS=Position of the binary log file on the master server
  • FOR CHANNEL 'Name of the connection channel'

2. Repeat the above process for each other master that we want to connect to, changing the host IP address, binary log info and channel as appropriate.

3. Finally start the slave thread.

mysql> START SLAVES;

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. 

To connect to a master for replication, run the CHANGE MASTER command with the necessary connection information of the master.

1. Run the command below to connect to Server 1, replacing the option values with the actual values we got from the master server 1:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.11', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 FOR CHANNEL 'Server-1';
  • MASTER_HOST='IP Address of the master server'
  • MASTER_USER='replicator'
  • MASTER_PASSWORD='password'
  • MASTER_LOG_FILE='Name of the binary log file on the master server '
  • MASTER_LOG_POS=Position of the binary log file on the master server
  • FOR CHANNEL 'Name of the connection channel'

2. Repeat the above process for each other master that we want to connect to, changing the host IP address, binary log info and channel as appropriate.

3. Finally start the slave thread.

mysql> START SLAVES;


References:

MySQL Multi-Source Replication




  • No labels