Setting Up a MySQL Proxy: Step-by-Step Guide for Beginners

Overview

A MySQL proxy sits between your application and MySQL servers to route, monitor, and modify queries (load balancing, query caching, read/write splitting, failover). This guide uses ProxySQL as the practical example because it’s widely used, actively maintained, and beginner-friendly.

Assumed environment

Linux server (Ubuntu 22.04 LTS assumed)
MySQL 8.0 (or 5.7) running on one or more hosts
SSH access and sudo
Reasonable defaults used where unspecified

1. Install ProxySQL

Add ProxySQL repository and key:

Code
sudo apt update sudo apt install -y wget lsb-release gnupg wget -qO - https://repo.proxysql.com/ProxySQL/repo_pub_key | sudo gpg –dearmour -o /usr/share/keyrings/proxysql-archive-keyring.gpg echo “deb [signed-by=/usr/share/keyrings/proxysql-archive-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./ ” | sudo tee /etc/apt/sources.list.d/proxysql.list sudo apt update sudo apt install -y proxysql

Start and enable service:

Code
sudo systemctl enable –now proxysql

2. Basic configuration concepts

mysql_servers: backend MySQL hosts
mysqlusers: application users ProxySQL accepts

runtime vs saved config: changes apply to runtime; run SAVE MYSQL VARIABLES TO DISK / LOAD MYSQL VARIABLES TO RUNTIME to persist/activate

3. Connect to ProxySQL admin interface

Code
mysql -u admin -padmin -h 127.0.0.1 -P6032

(Default admin user/password: admin/admin; change immediately.)

Change admin password:

Code
UPDATE global_variables SET variable_value=‘newstrongpassword’ WHERE variable_name=‘admin-admincredentials’; SAVE MYSQL VARIABLES TO DISK;

4. Add backend MySQL servers

Insert backend(s):

Code
INSERT INTO mysql_servers(hostgroup_id, hostname, port, max_connections) VALUES (10,‘db-primary.example.local’,3306,200); INSERT INTO mysql_servers(hostgroup_id, hostname, port, maxconnections) VALUES (20,‘db-replica-1.example.local’,3306,200);

Load to runtime and save:

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

Use distinct hostgroupid values for read/write separation (e.g., 10 = writers, 20 = readers).

5. Configure users and routing

Add application user (ProxySQL authenticates and forwards credentials to backend):

Code
INSERT INTO mysql_users(username, password, default_hostgroup, transaction_persistent) VALUES (‘appuser’,‘apppassword’,10,1); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

Enable read/write split via query rules (simple example: route SELECTs to reader hostgroup):

Code
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destinationhostgroup, apply) VALUES (1,1,‘^SELECT’,20,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Order and specificity matter; use regex carefully.

6. Health checks and monitoring

Default healthchecks run; customize:

Code
UPDATE mysql_servers SET max_replication_lag=10 WHERE hostgroupid=20; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Monitor via admin interface:

Code
SELECT hostgroup, hostname, status, backend_connections FROM runtime_mysql_servers;

7. Application connection

Point app to ProxySQL address (127.0.0.1:6033 by default for MySQL protocol). Example DSN:

host=proxysql.example.local port=6033 user=appuser password=app_password

8. Secure and harden

Change admin password and bind admin to localhost.

Use TLS between app and ProxySQL and between ProxySQL and backends if supported.

Restrict network access (firewall) to ProxySQL ports.

Rotate application credentials and minimize privileges per user.

9. Test failover and queries

Simulate primary down, observe reader promotion/failover procedures (ProxySQL doesn’t auto-promote; integrate with orchestrator or use monitoring/automation to update mysqlservers).

Test query rules and slow query capture:

Code
SELECTFROM stats_mysql_query_digest ORDER BY countstar DESC LIMIT 10;

10. Persisting changes and backup

Always SAVE … TO DISK after changes.

Backup ProxySQL config:

Code
mysqldump -u admin -padmin -h127.0.0.1 -P6032 proxysql > proxysql-config.sql

Quick checklist

Install ProxySQL and start service

Change admin credentials

Add backend servers and hostgroups

Add application user(s)

Create query rules for routing

Configure health checks and monitoring

Point application to ProxySQL

Harden security and enable TLS where possible

Test failover and query routing

Save config and backup

If you want, I can generate the exact commands for a different Linux distro, show a sample ProxySQL config file, or explain read/write splitting in more detail.

Setting Up a MySQL Proxy: Step-by-Step Guide for Beginners