You can setup two types of replication in MySQL. First is the master master replication and other one is master slave replication.

But master slave replication provides only load balancing for the databases, it does not provide failover. It means if your master server goes down, you can not execute queries directly on the slave server. In master master replication, it will allows data to be copied from one server to the other. This will adds redundancy and increases efficiency when accessing database.

In this tutorial, we will learn how to configure mysql master master replication between two Ubuntu 16.04 host.

Requirements

• Two server running Ubuntu 16.04.
• Static IP address 192.168.15.237 configured on first master server.
• Static IP address 192.168.15.100 configured on second master server.
• A non-root user with sudo privileges configure on both server.

Install and Configure MySQL on First Master Server

First, you will need to install mysql server and client on first server. You can install it with the following command:

sudo apt-get install mysql-server mysql-client

Next, you will need to make some changes in my.cnf file:

sudo nano /etc/mysql/my.cnf

Change the file as shown below:

server_id           = 1
bind-address        = 192.168.15.237
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

Once you are done, restart the mysql service:

sudo /etc/init.d/mysql restart

Next, you will need to secure mysql installation, because default mysql installation is not secure.

You can secure it by running the secure_mysql_installation script:

sudo mysql_secure_installation

Next, you will need to create a replication user.

You can do this with the following command:

First, log in to mysql shell with the following command:

mysql -u root -p

Enter your root password to login mysql shell.

Then, create a replication user with the following command:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.15.100' IDENTIFIED BY 'password';

Where 192.168.15.100 is a IP address of the second server.

Install and Configure MySQL on Second Master Server

First, you will need to install mysql server and client on second server. You can install it with the following command:

sudo apt-get install mysql-server mysql-client

Next, you will need to make some changes in my.cnf file:

sudo nano /etc/mysql/my.cnf

Change the file as shown below:

server_id           = 2
bind-address        = 192.168.15.100
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 2

Once you are done, restart the mysql service:

sudo /etc/init.d/mysql

Next, you will need to secure mysql installation, because default mysql installation is not secure.

You can secure it by running the secure_mysql_installation script:

sudo mysql_secure_installation

Next, you will need to create a replication user.
You can do this with the following command:

First, log in to mysql shell with the following command:

mysql -u root -p

Enter your root password to login mysql shell.

Then, create a replication user with the following command:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.15.237' IDENTIFIED BY 'password';

Where 192.168.15.237 is a IP address of the second server.

Once you are done, you can proceed to next step.

Configure MySQL Master on Both Server

Now, you will need to tell each server that other server is master.

Configure Second Server as Master

Before starting, you will need to check master status on the first server.

On the first server, log in to mysql shell and check the master server status with the following command:

mysql -u root -p
mysql> SHOW MASTER STATUS;

You should see the following output:

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

From above output, you will need to note mysql-bin.000001 and Position 276.
Next, tell second server that first server is it’s master.

On the second server, run the following command:

mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='192.168.15.237', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=276;mysql> SLAVE START;

Configure First Server as Master

First, check the master status of the second server and note the file and position values.

mysql> SHOW MASTER STATUS;

You should see the following output:

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

From the above output, you will need to note mysql-bin.000001 and Position 276.

Next, tell first server that second server is it’s master.

On the first server, run the following command:

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='192.168.15.100', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=276;
mysql> SLAVE START;

Once you are done, you can proceed to next step.

Testing Replication

Once everything is setup properlly, it’s time to check whether database replication is working or not.

To do so, create a database with name testingdb on the first server:

mysql -u root -p
mysql>create database testingdb;

Next, on the second server. Check whether this database is created or not.

mysql -u root -p
mysql>show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| limesurvey         |
| mysql              |
| performance_schema |
| testingdb          |
+--------------------+

In the above output, you should see that database testingdb is created.

Next, on the second server. Create a table in this database.

mysql> CREATE TABLE testingdb.testuser ( id INT, name VARCHAR(20));

Next, on the first server. Check whether this table is created or not:

mysql> use testingdb;
mysql> show tables;

+---------------------+
| Tables_in_testingdb |
+---------------------+
|                     |
| testuser            |
+---------------------+

In the above output, you should see that table testuser is created in testingdb database.

Conclusion

Congratulations! You now have a working MySQL master master replication. You can run query on any of the two servers, thus providing us with a fault-tolerant and safe environment.