How to install and configure ProxySQL

•  A powerful SQL proxy that sits between your application and MySQL servers
•  ProxySQL configuration is designed for high-performance query routing and connection handling
• Helps to balance database load across multiple servers

For Ubuntu/Debian:

apt-get update
apt-get install proxysql

Start the ProxySQL service:

systemctl start proxysql
systemctl enable proxysql

Access the Admin Interface

Use the MySQL client to connect to ProxySQL’s admin interface:

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> '

Default credentials is admin. Port 6032 is used for admin and  6033 is used for SQL traffic

Add a MySQL Backend Server

Now let’s add a backend MySQL server to ProxySQL:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (1, 'mysql-backend.example.com', 3306, 100);

Apply the configuration

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

Check if the server was added:

SELECT * FROM mysql_servers;

Add a MySQL User

Add a user that will connect through ProxySQL:

INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('app_user', 'strong_password', 1);

— Apply the configuration

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

Make sure the same user exists on the backend MySQL server

CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON your_database.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

Test the MySQL Connection via ProxySQL

Use the MySQL client to test connecting through ProxySQL:

mysql -h 127.0.0.1 -P6033 -u app_user -p

You should be able to authenticate and start querying the backend database via ProxySQL.

Conclusion

ProxySQL offers a robust and flexible solution for managing MySQL traffic in environments where performance, scalability, and high availability are critical. By acting as a smart proxy, it simplifies backend management, balances load efficiently, and provides advanced features like query routing and failover handling.

If you need expert assistance with ProxySQL configuration, Skynats is here to help. Our professional Linux server management services and high traffic server management solutions ensure your databases and applications run smoothly, securely, and without downtime. Contact us today for 24/7 support from experienced server experts.