Sign in Subscribe
Web Hosting Articles

A Guide to Setting Up a High-Availability Database Cluster on Your Dedicated Server

A Guide to Setting Up a High-Availability Database Cluster on Your Dedicated Server

Setting up a high-availability (HA) database cluster on a dedicated server is crucial for ensuring continuous access to your data, even in the event of hardware failures or maintenance activities. In this guide, I'll walk you through the steps to set up a MySQL-based high-availability cluster using MySQL Group Replication and ProxySQL.

Prerequisites:

  1. Dedicated Server(s): You'll need at least three dedicated servers. Two will be used for MySQL replication, and the third will be used for ProxySQL.
  2. Linux OS: Ensure that all servers run a compatible Linux distribution. Ubuntu, CentOS, or Debian are popular choices.
  3. SSH Access: You should have SSH access to all servers.

Step 1: Install MySQL Server

  1. On each MySQL server, install MySQL. You can use the package manager or download it from the official website.

bashCopy codesudo apt update
sudo apt install mysql-server

  1. Secure MySQL installation by running:

bashCopy codesudo mysql_secure_installation

Step 2: Configure MySQL Replication

  1. Edit my.cnf file on both MySQL servers to enable binary logging. Add or edit the following lines:

iniCopy codeserver-id = 1 # Change this to a unique ID for each server
log-bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

  1. Restart MySQL to apply the changes:

bashCopy codesudo systemctl restart mysql

  1. Create a replication user on the first MySQL server:

sqlCopy codeCREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

  1. Dump the database and import it on the second MySQL server:

bashCopy codemysqldump -u root -p --all-databases --master-data > dump.sql
mysql -u root -p < dump.sql

  1. Configure replication on the second server:

sqlCopy codeCHANGE MASTER TO
MASTER_HOST = 'first_server_IP',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_AUTO_POSITION = 1;
START SLAVE;

Step 3: Install and Configure ProxySQL

  1. Install ProxySQL on the third server:

bashCopy codesudo apt install proxysql

  1. Edit the configuration file, typically located at /etc/proxysql.cnf. Set the MySQL servers as backends:

iniCopy codemysql_servers =
(
{ address = 'first_server_IP', port = 3306, weight = 1, max_connections = 100 },
{ address = 'second_server_IP', port = 3306, weight = 1, max_connections = 100 }
)

  1. Start ProxySQL:

bashCopy codesudo systemctl start proxysql

Step 4: Configure ProxySQL for High Availability

  1. Add the MySQL servers to ProxySQL's hostgroup:

sqlCopy codeINSERT INTO mysql_servers (hostname, port) VALUES ('first_server_IP', 3306);
INSERT INTO mysql_servers (hostname, port) VALUES ('second_server_IP', 3306);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

  1. Create a MySQL user in ProxySQL:

sqlCopy codeINSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('your_user', 'your_password', 10);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Step 5: Test High Availability

  1. Point your application to the ProxySQL IP and port.
  2. Simulate a failure by stopping MySQL on one of the servers. The traffic should automatically failover to the other server.

Conclusion:

You now have a high-availability MySQL cluster set up with dedicated servers. Remember to regularly monitor and maintain your setup to ensure continuous availability. Additionally, consider setting up automated backups and monitoring for your database cluster.

Read next