MySQL (Master-Master) cluster with High Availability – Part 1 of 2

In this tutorial I will show you how to create a MySQL Replication (Master-Master) cluster
with Cluster-IP for High Availability.

MySQL replication (Master-Master) cluster adds redundancy for active websites. With replication, two separate MySQL servers act as a cluster. Database clustering is particularly useful for high availability website configurations. Use two separate Linux servers to configure the database replication.

Note: This guide is written for Debian 8 and written for a non-root user.
Commands that require elevated privileges are prefixed with [sudo]

Servers
I have created 2 Debian servers with 2 network adapters. One for LAN and the other for Replication.

Install & Configure MySQL on server MYSQL01

First we need to install NTP and NTPdate.
Time is essential to create a good working cluster

user@MYSQL01:~$ sudo apt-get install ntp ntpdate -y

Next step is installing MySQL

user@MYSQL01:~$ sudo apt-get install mysql-server mysql-client -y

We need to add/edit the following lines in /etc/mysql/my.cnf on MYSQL01.
open the /etc/mysql/my.cnf file for editing. I use nano

user@MYSQL01:~$ sudo nano /etc/mysql/my.cnf

Change the following lines

[mysqld]
bind-address = 127.0.0.1
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_ignore_db = include_database_name

to

[mysqld]
#bind-address = 127.0.0.1
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-ignore-db = mysql
log_bin_index = /var/log/mysql/mysql-bin.log.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1

Once completed, we need to restart MySQL

user@MYSQL01:~$ sudo service mysql restart

Now we need to login into mysql

user@MYSQL01:~$ sudo mysql -u root -p

mysql>

Once we are logged in, we need to enter a few commands
Create a replication user. Replace <password> with your own.

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY '<password>';

Give this user permissions to replicate our mysql data

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

Show the status of the Master

mysql> show master status;

The output will looking similiar to the following, and will have two pieces of critical information.

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 358      |              | mysql            |
+------------------+----------+--------------+------------------+

Write down the following information “File” and “Position” which will be used in the next step.

Install & Configure MySQL on server MYSQL02

Repeat the exact same steps that we followed on MYSQL01.
Start once again by installing NTP, NTPdate and MySQL

user@MYSQL02:~$ sudo apt-get install ntp ntpdate mysql-server mysql-client -y

We need to add/edit the following lines in /etc/mysql/my.cnf on MYSQL02.
open the /etc/mysql/my.cnf file for editing. I use nano

user@MYSQL02:~$ sudo nano /etc/mysql/my.cnf

Change the following lines

[mysqld]
bind-address = 127.0.0.1
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_ignore_db = include_database_name

to

[mysqld]
#bind-address = 127.0.0.1
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog-ignore-db = mysql
log_bin_index = /var/log/mysql/mysql-bin.log.index
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2

Once completed, we need to restart MySQL

user@MYSQL02:~$ sudo service mysql restart

Now we need to login into mysql

user@MYSQL02:~$ sudo mysql -u root -p

mysql>

Once we are logged in, we need to enter a few commands
Create a replication user. Replace <password> with your own.

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY '<password>';

Give this user permissions to replicate our mysql data

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

In the next steps we need to use the information that we took a note of in de previous step and apply it to our mysql instance. Replace <password> that you used for the replication user. Use the values of MASTER_LOG_FILE and MASTER_LOG_POS.
Note: The values may be different than those below.

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST = '10.90.0.3', MASTER_USER = 'replication', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin.000009', MASTER_LOG_POS = 358;
mysql> slave start;

The last thing we have to do is to make note of the master log file and position to replicate in the other direction (from MYSQL02 to MYSQL01).

mysql> show master status;

The output will looking similiar to the following.

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 358      |              | mysql            |
+------------------+----------+--------------+------------------+

Write down the following information “File” and “Position” to complete the Master-Master replication.

Completing Replication on MYSQL01 

On MYSQL01 we need to complete the configuration. Run this command in mysql.
Replace the values of MASTER_LOG_FILE and MASTER_LOG_POS with values or above.
Note: Keep in mind that your values may differ from those below.

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST = '10.90.0.4', MASTER_USER = 'replication', MASTER_PASSWORD = '<password>', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 358;
mysql> slave start;

Test the Master-Master Replication

Now that we have completed the configuration, we’re going to test it by creating a example database.

Login into mysql and create a database on MYSQL01.

mysql> create database example;

Let’s check if the databse exits on MYSQL02
Login into mysql and show our database.

mysql> show databases;

We shoud see the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mysql              |
| performance_schema |
+--------------------+

The last test is to delete out databse for MYSQL02 and check if it’s deleted for MYSQL01.

Enter on MYSQL02

mysql> drop database example;

Check if the database deleted on MYSQL01

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Next: High Availability