HOW TO CONFIGURATION MASTER MASTER REPLICATION IN MYSQL 5.7 DATABASES

HOW TO CONFIGURE MASTER MASTER MYSQL 5.7 DATABASE REPLICATION

MySQL Master-Master replication adds speed and redundancy for active websites.
With replication, two separate MySQL servers act as a cluster.
Database clustering is particularly useful for high availability website configurations.

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-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
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 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
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

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.

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 completed, restart the MySQL server:

systemctl restart mysqld

note:Both server A and server B Restart.

STEP 2. CREATE THE REPLICATION USER BOTH SERVER A AND SERVER B.

mysql -u root -p [root_password]

mysql>CREATE USER 'rpl_user'@'%' IDENTIFIED BY '[replicator_password]';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'%' IDENTIFIED BY '[replicator_password]';


STEP 3. 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.

Login to Server A and run following command

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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

Now connect to Server B  and type the following sql commands:

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

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

STEP 4. 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:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Now connect to Server A and type the following sql commands:

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

Reboot both servers now


Step 5: 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.