Create a Highly Available MySQL Cluster

This document is intended to walk you through setting up multiple servers to store your databases.  You can of course get by with a single server, but then this document would not be for you.  Our requirement is to configure a cluster of servers to allow us to perform maintenance on any one server in the cluster while maintaining access to the data stored in the cluster. 

It is important to note that this will not speed up access to data.  That is not the goal.  Only one node will be operating as a master at any given time.  The others are replication partners that are grabbing any changes to the master and copying those changes into their copy of the database.  They are not directly serving any applications or any other function than ensuring that the data exists in multiple locations for use in the even the master is no longer available.

To keep data in sync between servers, each acts as both a master and a slave.  Node 1 is the master replicating date to the slave of node 2.  Node 2 is also working as a master replicating its date to the slave of node 3.  Finally, node 3 is working as a master replicating its data back to node 1.  This round robin approach keeps the data consistent between all nodes.  To keep from having to determine which server is the master, we assign a separate name and IP address to the cluster and let the clustering service determine who the master is at any given time.

This does require increased resources overall, but since the slave nodes are replaying the changes in the background, they can generally do this faster than the time taken to make modifications in your application unless you have super-human AI typing abilities.  Even then, the nodes will eventually catch up and data will remain consistent between nodes.

External resources and acknowlegments

These instructions are based on a very well written resource by Markku Leiniö.  The original source document can be found at https://majornetwork.net/2020/01/high-availability-clustering-with-zabbix-on-debian/.  In addition, I have incorporated information from Edmunds Vesmanis from a presentation he gave at the Zabbix Summit 2019 in China on setting up HA clusters for use in Zabbix.  That source presentation can be found at https://assets.zabbix.com/files/events/conference_china_2019/Edmunds_Vesmanis-Zabbix_HA_cluster_setups.pdf.  There is also a video presentation from the event at https://www.youtube.com/watch?v=vdoUWkwk9QU.  Both authors are worth the read and use slightly different approaches.  I have opted to follow Markku’s approach of using GTID for replication because it is easy to follow and works for my situation. 

If you are using PostgreSQL, these instructions will not work.  For that, I would recommend the “PostgreSQL 12 High Availability Cookbook - Third Edition” written by Shaun Thomas for Packt Publishing.  It is a much more involved process and utilizes different technologies for replication between nodes.  There may be a newer version by the time you read this, so make sure that the book you are purchasing covers high availability.

Yes, the above source links are heavily focused on Zabbix, but the instructions below should work for any application that relies on MySQL as the database backend. 

Servers

For this exercise, we are going to need three servers.  One node will be the master that handles all incoming requests to the cluster.  The others will be slaves initially, but their role can change to master if the primary server experiences a fault and is unreachable.  One is also a witness which helps with the election process in determining who the master should be.  You should always have an odd number of servers in your cluster to aide with the election process.  If you have an even number of servers, the election could result in a tie and cause the cluster to fail.

I use Proxmox in my home network, so I have chosen to create three new virtual servers.  Make sure you know how many resources you will need for your project before building the servers.  It is much easier to start out with appropriate resources than it is to go back and modify them later; although, it is always possible to extend resources at a later date if necessary.  In my case, each has two virtual CPUs, 4GB of RAM, and 50GB of storage space. 

I have chosen Ubuntu 24.04 as the operating system for these servers.  This is a Debian-based operating system, and the commands below will be specific to Debian-based systems.  You can use any operating system you desire as MySQL can run on any flavor of Linux or Windows.  I tend to stay away from Windows due to licensing costs and limited resources.  You don’t really need to have a desktop environment for any of these services, so why waste the resources on things you will never use? 

If you are using a RedHat based system, apt will be replaced by yum.  Gentoo uses portage as its package manager.  Arch uses its own package manager as well.  On Mac computers, you can use homebrew.  Just be sure to modify the commands in the subsequent instructions to accommodate your package manager.  I will not get into the minutiae of modifying the commands below to cover every situation.  I am only sticking to Debian commands.

Don’t forget to make sure that all of the current patches are applied to the servers.  You can do this with the following command.

apt update && apt upgrade -y

Pay attention to any packages that have been held back.  You can manually install these by specifying their name.  For example, my new systems installed all updates with the exceptions of python3-distupgrade and ubuntu-release-upgrader-core.  Running the following command got those to install for me along with all of their dependancies.

apt install python3-distupgrade ubuntu-release-upgrader-core

You can run the following command to ensure that there are no further updates that need to be installed.

apt update && apt list —upgradable

It should return with “All packages are up to date.”

Network configuration

Before creating the cluster, we want to make sure that we have a range of four continuous IP addresses on our network.  It is not required, but makes things a lot easier to remember and keep track of.  One of the IP addresses will be the master address for the entire cluster.  The cluster services will listen on this address and internally handle processing of information to each node of the cluster.  To make things simple for me, I choose an address in the high end of my range ending in zero.  The three subsequent IP addresses are used for node 1, node 2, and node 3 in the cluster.

I am using a single class C network of 192.168.50.0/24 as issued by my router.  Since this is a new network, I can see that nothing is using 192.168.50.230 through 192.168.50.233.  You can test this from a terminal by pinging each of the IP addresses.  They should come up with either a time-out or no response.  After confirming, I have the following network settings.

We also have to have names for the servers.  In my case, I like to be able to look at the name of the server to determine what it does and what operating system is installed.  To quickly determine that I am using Ubuntu 24.04 as the server operating system, my machine names begin with u24-.  The next part is the service that the server is performing.  Even though I am using MariaDB, this is for all intents and purposes MySQL — just a more freely available and less restrictive licensed version of the same software.  I then place the node number at the end of the server name. 

Don’t forget to name the virtual IP as well.  We don’t want to have to constantly remember things by IP address.  Since the virtual IP does not have its own server behind it, I eliminated the u24- but added -ha to the end of the name to indicate to me that it is the high available cluster address.  My chosen list of server names are as follows.

If you are using a DHCP server on your router, it is also a good idea to make sure the IP addresses will remain static and not assigned to other devices on your network.  Because of this, I set reservations in the DHCP server based on the MAC address of the new virtual servers.  Whenever the network card with the given MAC address tries to get an IP form DHCP, it will be assigned the appropriate number in the table.  We will not have a MAC address for the cluster itself, so we must tell DHCP to never issue this address to any devices on the network.

There are multiple ways to determine the MAC address of your server.  If you are using a virtualization solution such as VMware, Proxmox, or Nutanix, you can look at the hardware of your virtual server.  The network interface card will have a 6-octet hexadecimal number associated with it.  This is the MAC address.

You can also determine the MAC address within the terminal of the server itself by running the command ‘ip a’. This will give you a list of all network cards and settings.  You will want the MAC address of the card that has the assigned IP.  Ignore the card named lo that has an address of 127.0.0.1.  This is just a loop back card for the machine to be able to use TCP/IP to see itself.

Security

Finally, we need a couple of passwords for services.  There is a user and password used for internal replication between database services, and there is a master password for the cluster.  Since these passwords will never change and need to be secure, choose a randomly generated lengthy password.  I like to make mine at least 60 characters in length.  Due to their complexity, make sure you are storing these in a password manager so that you can access them if you ever have to replace or add a node to the cluster.  These are the only times after the initial build that these passwords should be needed, but if you don’t make a record of them, you will most likely have to rebuild your cluster from scratch if they are lost or misplaced.

You will need passwords for the following two accounts.

• hacluster
• replicator

Initial DB Server Setup

Now we are ready to get into the complicated portion of the install — installing the database server and configuring the high availability.  Before beginning, you should have completed all of the requirements listed above.  If you have not, you will most definitely run into issues.

Configure the hosts file - All nodes

On each server to be used in the cluster, we need to modify the hosts file to reference each node in the cluster.  If you are using DNS within your network, your DNS zones should also be modified to include A records for these servers. 

Using your favorite editor (mine is vim but nano will work as well), open the file /etc/hosts.  It should look like the following.

127.0.0.1 localhost
127.0.1.1 u24-mysql01

The server name on the second line will be different for each server and will be the name of that server.  There may also be a section for IPv6 addresses.  We are not concerned with this section as we will be sticking exclusively to IPv4 addressing.

Retain the first line for localhost, but remove the second line beginning 127.0.1.1 and insert the following text immediately after the localhost entry.  Ensure you have adapted it for your network configuration as outlined in the prior steps.

192.168.50.230 mysql-ha mysql-ha.antimidas.net
192.168.50.231 u24-mysql01 u24-mysql01.antimidas.net
192.168.50.232 u24-mysql02 u24-mysql02.antimidas.net
192.168.50.233 u24-mysql03 u24-mysql03.antimidas.net

Save the file and close it.

Install required software

We need to install the services we will be using to provide the clustering and replication.  This will need to be done on all servers in our cluster.  The first three applications work together to provide the tools necessary to manage the cluster and the backend replication resources.  The last is, of course, the database server software.

apt install corosync pacemaker pcs mariadb-server

We also want to stop the database service because we are not yet ready to use it until it is configured.

systemctl stop mariadb

Set the password for the cluster resources - All nodes

Now we get to use the first of the exceedingly long passwords that we created earlier.  Replace <PASSWORD> in the command below with the password that you chose for your cluster.  We are setting the same password on every node so that they know how to communicate with other nodes in the cluster.

echo ‘hacluster:<PASSWORD>’ | chpasswd

Backup the default configuration - All nodes

We are not going to use the default configuration for Corosync.  But we want to be able to reference it in the future if needed.  To keep the settings from being applied, we will simply rename the file.

mv /etc/corosync/corosync.conf /etc/corosync/corosync.conf.orig

Configure the database server - All nodes

This configuration will be slightly different on each of the servers in our cluster.  Changes will need to be made to the last three lines of the configuration based on which node is being configured.  It is important to pay attention to this part so that you do not duplicate configurations between servers.  Although each server is nearly identical, the last three lines are unique to each node.

Using your preferred editor, create a new file named /etc/mysql/mariadb.conf.d/90-ha.cnf with the following information and then save it.

[mysqld]
skip_name_resolve
bind_address = 0.0.0.0
log_slave_updates
max_binlog_size = 1G
expire_logs_days = 5
innodb_buffer_pool_size = 1G # 70-80% of total RAM
innodb_buffer_pool_instances = 1 # each instance should be at least 1GB
innodb_flush_log_at_trx_commit = 2 # default = 1
innodb_flush_method = O_DIRECT # default = fsync
innodb_io_capacity = 500 # HDD = 500-800, SSD = 2000
query_cache_size = 0

# Change the following values for each server accordingly!
log_basename = u24-mysql01
log_bin = u24-mysql01-bin
server_id = 231 # The last number of the server IP address

Now that our configuration is written, we can start the database service on each node

systemctl start mariadb

Initial clustering configuration

Our base installation is now set and it is time to configure the clustering.  For configuration purposes, we are going to start with the first node and configure it slightly different than all of the others.  We will circle back at the end and configure the slave component of the server, but first we must have the configuration for master.

These steps will be very particular about which server is being configured.  Pay attention to which server you are on.  If the commands are run on the wrong server, you will most likely not get the desired replication topology.

Configuring the cluster

On node 1, we want to create the cluster and set the password for the cluster controller.  The following command tells the service which nodes are members of the cluster and sets the password for communication within the cluster.  Make sure to replace <PASSWORD> with the credentials created previously for the hacluster user.

pcs host auth u24-mysql01 u24-mysql02 u24-mysql03 -u hacluster -p <PASSWORD>

Now that we have specified which nodes will make up the cluster, we need to add them to the cluster itself and start the initial cluster.  We are going to use --force to ensure that the cluster is created as described.

pcs cluster setup mysql24-ha u24-mysql01 u24-mysql02 u24-mysql03 --force

We then want to start the clustering service on all nodes.  The following will start the service on all member nodes that were just defined.

pcs cluster start --all

We then need to enable the other cluster management services now that we have everything defined and running.  This command will start the services and ensure that they are started if the node is rebooted.

systemctl enable corosync pacemaker

We need to ensure that one node does not “kill off” another node.  As an aside, this might sound a bit hyperbolic to use the verb “kill” but the stonith setting below does stand for “shoot the other node in the head”, so it is not too far off in verbiage.  A typical cluster has the ability to pull another node from the pull, and we don’t want this to happen in our case.

pcs property set stonith-enabled=false

We also need to set a parameter that controls how strongly a service prefers to stay running on a node.  We are going to use a conservative default of 100.

pcs resource defaults resource-stickiness=100

We also need to create the cluster IP and assign it to the cluster. along with settings on how often to monitor for changes to one node to trigger the replication.

pcs resource create virtual_ip ocf:heartbeat:IPaddr2 ip=192.168.50.230 op monitor interval=5s --group mysql_ha_cluster

On the remaining nodes, we now need to enable the cluster services so they start automatically the same as we did to node 1.  They will use the settings we just applied to the primary node.  Run the following command on the remaining nodes.

systemctl enable corosync pacemaker

Checking the status of the cluster

At any point in the future, we can check on the status of the cluster to determine the status of each node and that all nodes are communicating with each other by running the following command.

pcs status

This will display a “prettified” list of the configuration on each node telling you whether the cluster is online or not, the list of resources assigned to the cluster, and the number of nodes.

Configuring database replication

We now need to configure MySQL itself to replicate data between servers.  This is the portion where we set up the round-robin so that all additions, modifications, and deletions of data are run against each copy of the database across nodes.  Note in the following sections that although the commands appear similar, we are changing the IPs to configure replication to the next node in the chain.

On the first node, open mysql from the terminal and run the following commands to set the second node as its slave.  Replace <PASSWORD> with the password created for the replicator user.

stop slave;
grant replication slave on *.* to 'replicator'@'192.168.50.232’ identified by ‘<PASSWORD>’;
show global variables like 'gtid_current_pos';

Make a note of the gtid_current_pos value.  It should be 0-231-1 or something similar.

On the second server, open mysql and enter the following commands to configure node 1 as its master and node 3 as its slave.  Replace <PASSWORD> with the password created for the replicator user.

stop slave;
set global gtid_slave_pos = ‘0-231-1’;   # The GTID you noted earlier
change master to master_host='192.168.50.231’, master_user='replicator', master_password=‘<PASSWORD>’, master_use_gtid=slave_pos;
grant replication slave on *.* to 'replicator'@'192.168.50.233’ identified by ‘<PASSWORD>’;
reset master;
start slave;
show slave status\G

On the third server, open mysql and enter the following commands to configure node 2 as its master and node 1 as its slave.  Replace <PASSWORD> with the password created for the replicator user.

stop slave;<
set global gtid_slave_pos = ‘0-231-1’;   # The GTID you noted earlier
change master to master_host='192.168.50.232’, master_user='replicator', master_password=‘<PASSWORD>’, master_use_gtid=slave_pos;
grant replication slave on *.* to 'replicator'@'192.168.50.231’ identified by ‘<PASSWORD>’;
reset master;
start slave;
show slave status\G

We now need to complete the ring on the first server to set node 3 as its master and start the slave service.  Replace <PASSWORD> with the password created for the replicator user.

stop slave;
set global gtid_slave_pos = ‘0-231-1’;
change master to master_host='192.168.50.233’, master_user='replicator', master_password=‘<PASSWORD>’, master_use_gtid=slave_pos;
start slave;
show slave status\G

Conclusion

The HA cluster is now configured, but there are no databases on the server.  You can follow whatever instructions you have for your application by using the mysql console on the first server in the cluster.  Ideally, your application should make remote connections to the VIP that was created.  In this case, it is 192.168.50.230.  Any changes made should replicate to the other two servers within five seconds depending on transient network conditions.

As mentioned above, you can also use DNS within your network to ensure that the server names are associated to the appropriate iP addresses using A records.  This includes creating an A record for the cluster name itself (mysql-ha).  You would then be able to make connections to the name of the cluster rather than the IP address.