Introduction
Having a fast, reliable database today is crucial. In this tutorial, I will show you how to create a highly available MySQL cluster using two different MySQL servers, HAProxy, and Keepalive. This cluster will perform load balancing, handle failovers, data replication, and be able to handle an incredible amount of connections with ease. There are other more modern cluster solutions available, such as Percona XtraDB, ProxySQL, and Galera; but they may not work for everyone, hence this tutorial. Before moving forward with this kind of setup, research other options and definitely have a backup strategy if you intend to run this as a production database.
The diagram above displays the basic configuration. It's a master to master replication setup load balanced with HAProxy. There are two separate servers (virtual machine or bare metal) running a set of three components: MySQL 8 as the database and replication, HAProxy for load balancing the two MySQL servers, and Keepalive, which provides a virtual IP address that both machines will use to communicate with the outside network.
With this setup, even if you lose an entire server, your cluster will remain online and reachable. I've verified this by shutting down one server, restarting services while the databases were being used, and had no issues. Once the lost server was back online, the newer database data was gracefully replicated. If you want to give this a try, let's get started.
Requirements
To get started with this specific tutorial, you will need the following:
- Two servers, physical machines or virtual, with fresh installations of Rocky Linux 9.2 on each. You can use any Linux distro for this, but you may need to adjust the tutorial to match similar settings for the OS you choose. This tutorial is tailored for RHEL-like distros: Rocky Linux, AlmaLinux, etc.
- At least one physical network interface each set with a static IP per server.
While this tutorial is focused on MySQL 8, a similar process can be done with MariaDB by adjusting a few parameters. Keep in mind, with MariaDB, the replication user made later in this tutorial will fail replication upon restart if the replication user's password is longer than 40 characters.
Step 1: Installing Packages
On both servers, install these packages:
sudo dnf update
sudo dnf install mysql mysql-server haproxy keepalivedStep 2: Configure System Bind Settings & Ports
For HAProxy and MySQL to talk on the same port on the same system, we need Rocky Linux to be able to bind to non-local ports.
To begin, open the file "/etc/sysctl.conf" on both servers with your preferred text editor, and add this line as shown, then save it.
net.ipv4.ip_nonlocal_bind=1
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
net.ipv4.ip_nonlocal_bind=1After saving the file, run this command on both servers to apply the changes without rebooting:
sudo sysctl -pNext, we will need to open ports on the servers to allow SQL connections. On both servers, run these commands. This will open port 3306 and reload the firewall. If you want the stats page for HAProxy later in the tutorial, run it again for port 9999/tcp.
sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reloadStep 3: Configure HAProxy
On both servers, open the file "/etc/haproxy/haproxy.cfg" and modify it to look like this. Be sure to replace the IP addresses with the ones that suit your setup.
#------Basic Global Settings------#
global
user haproxy
group haproxy
defaults
mode http
log global
retries 2
timeout connect 3000ms
timeout server 5000ms
timeout client 5000ms
#------Stats Page------#
listen stats
bind 172.16.0.3:9999
stats enable
stats hide-version
stats uri /stats
stats auth admin:admin
#------SQL Backends------#
listen mysql-cluster
bind 172.16.0.3:3306
mode tcp
balance roundrobin
server mysql-1 172.16.0.1:3306 check
server mysql-2 172.16.0.2:3306 checkIn this configuration, there are a few things to keep in mind:
- Bind Address: Under the "SQL Backends" section, "Bind" refers to the virtual IP address provided by Keepalived on both servers. This is where your applications will connect to find a MySQL server.
- Load Balancing: Just below that, "balance" determines the type of load balancing being used. We're using "Round Robin," which distributes the load to one of the servers based on various factors. You can explore other balancing modes visiting this link.
- Server Addresses: The two "server" sections specify the real IP addresses of your MySQL servers and their ports, which default to 3306. The "check" parameter enables HAProxy to continuously verify that the server is online and healthy. If not, it will be temporarily removed from rotation. You can learn more about this parameter from this link.
- Stats Page: The Stats page allows you to monitor traffic to the cluster. Feel free to change the username and password "admin" to something of your choice. If you don't want to have this feature, you can remove that section from the config entirely.
Remember to save any changes in this file on both servers when you're finished.
The last change for HAProxy we need to make is to allow HAProxy to bind to all TCP sockets. We can tell selinux to allow this by running this command on both servers:
sudo setsebool -P haproxy_connect_any=1Start the haproxy service on both servers:
sudo systemctl enable --now haproxyIf there are any errors, use "sudo systemctl status haproxy" to see what's wrong and address it before continuing. After that, you can start the service manually with "sudo systemctl start haproxy". If HAProxy is having issues binding to the virtual IP address, configure keepalived in the next section before trying to start the HAProxy service again.
Step 4: Configure Keepalived
To provide a virtual IP address for both servers, we'll be using Keepalived. To start, open the file "/etc/keepalived/keepalived.conf" and configure it according to this example. Make sure to replace the virtual IP address and password in the example with the one you intend to use.
vrrp_instance VRRP1 {
state MASTER
# Specify the network interface to which the virtual address is assigned
interface ens33
# The virtual router ID must be unique to each VRRP instance that you define
virtual_router_id 41
# Set the value of priority higher on the master server than on a backup server
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass password
}
virtual_ipaddress {
172.16.0.3/24
}
}For more in depth configuration information for Keepalived, visit this link. Remember to save any changes in this file on both servers when you're finished.
For the interface part of the config here, run "ip a" to find the main network interface name and replace "ens33" in the above example with it.
Once you've saved the config file on both servers, let's bring the service up and do some testing. Run the following command on both to start the keepalived service:
sudo systemctl enable --now keepalivedIf the service fails to start, run "sudo systemctl status keepalived" to see what's preventing it from starting. Resolve the stated error, and run "sudo systemctl start keepalived" to start the service manually.
Ping Test
We need to verify that the virtual IP address is reachable when one of the servers goes down. Open a terminal on your workstation, and start pinging the virtual IP address.
[user@host]$ ping 172.16.0.3
PING 172.16.0.3 (172.16.0.3) 56(84) bytes of data.
64 bytes from 172.16.0.3: icmp_seq=1 ttl=62 time=0.45 ms
64 bytes from 172.16.0.3: icmp_seq=2 ttl=62 time=1.34 msEnsure the ping command remains running. To test, stop the keepalived service on one server and check if the pings continue. You can stop the service with "sudo systemctl stop keepalived" on one server at a time to confirm if the IP address still responds. If you still receive pings when the service on one server is stopped, restart that service and then stop it on the other server. If the pings continue, everything is working correctly. Be sure to restart the service on both servers again before proceeding.
Step 5: Configure MySQL
MySQL will be our database of choice. There are a few steps to getting it up and running before we start the other services.
Initial Setup
On both servers, run the following command to enable the service:
sudo systemctl enable --now mysqldIf MySQL has an error starting, make sure you have the system bind settings set correctly for HAProxy.
Then, run the secure installation script and follow the instructions.
sudo mysql_secure_installationThis will guide you through some questions about your MySQL setup's security. I recommend for this setup the following options:
- Set a strong root user password.
- Validate Password Component: Yes - Level 2 Minimum
- Remove Anonymous Users: Yes
- Disallow Root Login Remotely: Yes
- Remove Test Database & Access To It: Yes
Once finished, and you've set your sql root password allow it to reload the privilege tables. If you want to allow root login remotely, for example, with MySQL workbench, you can say No.
Edit Service Config
Edit the configuration file "/etc/my.cnf.d/mysql-server.cnf" to match these settings. Change bind address to match the local IP of the servers, and be sure auto-increment-offset is set to 2 on server 2.
Server 1:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
bind-address=172.16.0.1
log-bin
log_bin_index = /var/lib/mysql/mysql-bin.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
server-id=1
binlog-format=mixed
auto-increment-increment = 2
auto-increment-offset = 1
max_connect_errors=400
[client]
socket=/var/lib/mysql/mysql.sockServer 2:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
bind-address=172.16.0.2
log-bin
log_bin_index = /var/lib/mysql/mysql-bin.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
server-id=2
binlog-format=mixed
auto-increment-increment = 2
auto-increment-offset = 2
max_connect_errors=400
[client]
socket=/var/lib/mysql/mysql.sockAfter making the changes, save the config files, and restart the MySQL service on both servers.
sudo systemctl restart mysqldCreate Replication User
To make our databases replicate to each other, we need to create the same user on both servers. Choose a username and give it a strong 32-character password. If the password is longer, it will be truncated and you'll see access denied errors when you restart the mysqld service.
Log into MySQL with this command, and enter the password for the root user we set earlier.
mysql -u root -pOnce you are logged in, create the replication user.
CREATE USER 'replication'@'%' IDENTIFIED BY 'superstronglongpassword123';Grant the new user replication permissions.
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'superstronglongpassword123';Setting Up The Slave
Now, we need the log bin file and position information. The information may differ a bit between your servers, that is fine. Run this command on both servers and save the results for the next step:
Server 1:
mysql> show master status;
+------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| file-1-bin.000001 | 344 | | | |
+------------------------------+----------+--------------+------------------+-------------------+Server 2:
mysql> show master status;
+------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------+
| file-1-bin.000002 | 345 | | | |
+------------------------------+----------+--------------+------------------+-------------------+It's important to note that the file and position info from server 2 will be used on server 1 when we run the command to set up the slave, and vise versa.
Alright, let's run the commands using the above information to get the servers replicating to each other. First, still logged into MySQL, execute "stop slave;" on both servers, then perform the following:
Server 1:
mysql> CHANGE MASTER TO master_host='172.16.0.2', master_port=3306, master_user='replication', master_password='superstronglongpassword123', master_log_file='file-1-bin.000002, master_log_pos=345;Server 2:
mysql> CHANGE MASTER TO master_host='172.16.0.1', master_port=3306, master_user='replication', master_password='superstronglongpassword123', master_log_file='file-1-bin.000001, master_log_pos=344;Assuming there are no errors, we are ready to start the slave process. On both servers, run the following:
mysql> start slave;At this point, both MySQL servers should be replicating to each other. Any change made on server 1 should reflect on server 2, and server 2 to server 1. Let's verify! Run this command on both servers to make sure there are no errors:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.0.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File:
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key:
Network_Namespace:This is roughly what your output should look like on both servers, assuming no errors. If you have any errors in this output, you'll need to address them before moving forward. If you see an access denied error, it's likely you set the replication user password to more than 32 characters. If so, change the password to the required length and re-run the slave process.
Testing Replication
Alright, let's do some tests to make sure everything is working. Let's create a database on both servers to see if it replicates. On server 1, create a test database and then list them.
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+----------------------+
| Database |
+----------------------+
| information_schema |
| mysql |
| test1 |
+----------------------+Verify on server 2 that the database replicated:
mysql> show databases;
+----------------------+
| Database |
+----------------------+
| information_schema |
| mysql |
| test1 |
+----------------------+If you do not see the new database on server 2, run the command "SHOW SLAVE STATUS \G" to make sure there aren't any errors.
For more testing, let's create a user on server 2.
mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'password';
Query OK, 1 row affected (0.00 sec)On server 1, list users to see if the user we just made on server 2 is there.
mysql> SELECT user,host,plugin from mysql.user;
+----------------------+-----------+-----------------------+
| user | host | plugin |
+----------------------+-----------+-----------------------+
| testuser | % | caching_sha2_password |
| replication | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
+----------------------+-----------+-----------------------+This indicates that replication between both servers is working! At this point, everything should be working as intended. You can start adding services to this cluster.
If you want to use something like MySQL Workbench, you can point it to connect via the virtual IP set by keepalived for management. If you wish to manage it via terminal, you can perform any changes on either server, and it will replicate to the other server.
Optional: Check HAProxy Stats
On your workstation, open a web browser and go to http://172.16.0.3:9999/stats , and log in with the credentials you set in the HAProxy config. Adjust the IP for the virtual IP you set for the cluster.
Here you will be able to see the two MySQL servers and how HAProxy is distributing the load between them. As you add applications to this cluster, you'll be able to track usage here.
Conclusion
Now that your cluster is online, I strongly recommend implementing a backup solution. Master-to-master replication setups can encounter various issues that can be challenging to resolve. Having a backup in place ensures that if replication fails, you can quickly restore from the backup and resume operations without significant downtime. There are many options online available; I recommend doing research to figure out which one works best for you.
Make sure to direct any applications you want to host on this cluster to the virtual IP address and not the actual IPs of your servers. This will guarantee redundancy is maintained through HAProxy. Enjoy your new cluster, and good luck!
