How to Setup MySQL Master-Master Replication

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.

Requirements

• Two different servers.
• Every server with it’s specific IP address.
• The same version of MySQL on both servers (v5.1 or above is strongly suggested).

Installing MySQL Server

The first thing you need to do is to install MySQL on Server A and Server B.

For more information about installing MySQL server:
How To Install MySQL on CentOS 7

Configure MySQL servers

– Main config file

Edit the following file on both servers:

vi /etc/my.cnf

Type-in the following settings, replacing the existing ones if present:

Server A

server-id=1
log-bin="mysql-bin"
binlog-ignore-db=test
binlog-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 1

Server B

server-id=2
log-bin="mysql-bin"
binlog-ignore-db=test
binlog-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 2

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

This configuration will replicate/mirror each and every db with the exception of test and information_schema.

To exclude any other database, just add those you want to exclude by adding a binlog-ignore-db and replicate-ignore-db command for it.

Once you did that, you can restart MySQL servers:

systemctl restart mysqld

– Create the Replicator User(s)

Now, we will need to create the replicator user in either Server A and Server B.

Enter MySQL shell:

mysql -u root -p'[root_password]'

Create the replicator user and grant him Replication privileges:

CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY '[replicator_password]';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' WITH GRANT OPTION;

– Perform a full Sync

You will need to do this manually, you need to be sure that the two MySQL instances starts with the same data.

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.

Configure replication from Server A to Server B

Now, we need to replicate all the commands received by Server A to Server B.

Server A

Connect to MySQL console and type the following SQL command:

SHOW MASTER STATUS;

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.

Server B

Connect to MySQL console and type the following SQL command:

> STOP SLAVE;
> CHANGE MASTER TO MASTER_HOST = 'Server A IP Address', MASTER_USER = 'replicator', MASTER_PASSWORD = '[replicator_password]', MASTER_LOG_FILE = 'mysql-bin.000008', MASTER_LOG_POS = 5567; 
> START SLAVE;

Configure replication from Server B to Server A

Also, we need to replicate all the commands received by Server B to Server A.

Server B

Connect to MySQL console and type the following SQL command:

SHOW MASTER STATUS;

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.

Server A

Connect to MySQL console and type the following SQL command:

> STOP SLAVE;
> CHANGE MASTER TO MASTER_HOST = 'Server B IP Address', MASTER_USER = 'replicator', MASTER_PASSWORD = '[replicator_password]', MASTER_LOG_FILE = 'mysql-bin.000007', MASTER_LOG_POS = 4852; 
> START SLAVE;

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.