Introduction

ProxySQL is a powerful open-source tool that acts as a high-performance MySQL proxy, enabling load balancing and improving database performance. This guide will walk you through the process of installing a load balancing MySQL server using ProxySQL on Debian 11. This setup can be particularly beneficial when using a Windows VPS UK to manage your database load efficiently.

Prerequisites

• A server running Debian 11
• Root or sudo access to the server
• MySQL server installed
• Basic knowledge of Linux commands

Step 1: Update Your System

Begin by updating your system packages to ensure everything is current:

sudo apt update && sudo apt upgrade -y

Step 2: Install Required Packages

Install the necessary dependencies for ProxySQL:

sudo apt install -y wget gnupg2

Step 3: Add ProxySQL Repository

Next, add the ProxySQL APT repository:

echo "deb https://repo.proxysql.com/ProxySQL/ProxySQL-2.0.x/debian $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/proxysql.list
wget -O - https://repo.proxysql.com/ProxySQL/repo.gpg | sudo apt-key add -

Step 4: Install ProxySQL

Now, update the package list and install ProxySQL:

sudo apt update
sudo apt install proxysql -y

After installation, start and enable ProxySQL:

sudo systemctl start proxysql
sudo systemctl enable proxysql

Step 5: Configure ProxySQL

ProxySQL configuration is done through its admin interface. Connect to ProxySQL using the following command:

mysql -u admin -p -h 127.0.0.1 -P 6032

Once connected, you can configure backend MySQL servers. For example:

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'mysql_server_1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'mysql_server_2', 3306);
LOAD MYSQL SERVERS TO MEMORY;

Replace mysql_server_1 and mysql_server_2 with the actual hostnames or IP addresses of your MySQL servers.

Step 6: Configure Load Balancing

You can set up load balancing rules. For example, to balance traffic across the MySQL servers added previously:

INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup) VALUES (1, '^SELECT.*', 0);
LOAD MYSQL QUERY RULES TO MEMORY;

Step 7: Test Your Configuration

After configuring ProxySQL, you can test your setup by connecting to ProxySQL using a MySQL client:

mysql -u your_user -p -h 127.0.0.1 -P 6033

This should route your queries through ProxySQL and distribute the load across your MySQL servers.

Step 8: Conclusion

You have successfully set up a load balancing MySQL server using ProxySQL on Debian 11. This configuration enhances your database's performance and reliability, especially when combined with a Windows VPS. For more options, consider exploring VPS UK Windows, including Windows Virtual Private Server Hosting and Windows VPS Hosting UK for optimal database management.