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

Next step is installing MySQL

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

Change the following lines

to

Once completed, we need to restart MySQL

Now we need to login into mysql

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

Give this user permissions to replicate our mysql data

Show the status of the Master

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

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

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

Change the following lines

to

Once completed, we need to restart MySQL

Now we need to login into mysql

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

Give this user permissions to replicate our mysql data

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.

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).

The output will looking similiar to the following.

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.

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.

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

We shoud see the following output:

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

Enter on MYSQL02

Check if the database deleted on MYSQL01

Next: High Availability

 

One thought on “MySQL (Master-Master) cluster with High Availability – Part 1 of 2

  1. Pingback: How to create a HA MediaWiki – KevinTijssen.eu

Leave a Reply

Your email address will not be published. Required fields are marked *