MySQL Users in ProxySQL
ProxySQL maintains its own user table (mysql_users) that is separate from MySQL's grant tables. When an application connects to ProxySQL on port 6033, ProxySQL authenticates the user against its own mysql_users table and then connects to the backend MySQL using the same credentials.
⚠ Warning: The user must exist in BOTH ProxySQL mysql_users table AND the real MySQL server. ProxySQL does not create MySQL users — it only proxies authentication.
Adding MySQL Users
SQL — Add MySQL User
-- Add a user that connects through ProxySQL
INSERT INTO mysql_users
(username, password, default_hostgroup, active, transaction_persistent,
fast_forward, backend, frontend, max_connections)
VALUES
('appuser', 'SecurePass123!', 10, 1, 1, 0, 1, 1, 10000);
-- Apply
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; mysql_users — Column Reference
| Column | Default | Description |
|---|---|---|
| username | — | MySQL username (must match MySQL server user) |
| password | — | Password — can be plaintext or MySQL hash |
| active | 1 | 1 = user enabled; 0 = user disabled |
| use_ssl | 0 | Force SSL for this user's connections |
| default_hostgroup | 0 | Hostgroup used if no query rule matches |
| default_schema | NULL | Default database for this user |
| schema_locked | 0 | Lock user to default_schema only |
| transaction_persistent | 1 | Keep all queries in a transaction on same backend |
| fast_forward | 0 | Bypass query rules — use for trusted internal users |
| backend | 1 | User can connect to backend MySQL |
| frontend | 1 | User can connect via ProxySQL frontend port 6033 |
| max_connections | 10000 | Max simultaneous connections for this user |
Password Formats
SQL — Password Formats
-- Option 1: Plaintext password (ProxySQL hashes it internally)
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'MyPassword123', 10);
-- Option 2: MySQL native hash (from SELECT PASSWORD() on MySQL 5.x)
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', '*6691484EA6B50DDDE1926A220DA01FA9E575C18A', 10);
-- Option 3: Use mysql_native_password hash from MySQL 8.x
-- Get hash from MySQL:
-- SELECT authentication_string FROM mysql.user WHERE user = 'appuser';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; transaction_persistent Explained
This is one of the most important settings. When transaction_persistent=1, once a connection starts a transaction, ALL queries in that transaction are sent to the same backend server — even if they are SELECTs that would normally go to a replica.
SQL — transaction_persistent
-- Example behavior with transaction_persistent=1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Goes to Master (HG 10)
SELECT balance FROM accounts WHERE id = 1; -- ALSO goes to Master (HG 10) ← consistent!
COMMIT;
-- Without transaction_persistent (=0), the SELECT above might go to replica
-- and see stale data = inconsistency bug
-- Best practice: always keep transaction_persistent=1
UPDATE mysql_users SET transaction_persistent=1 WHERE username='appuser';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; fast_forward Explained
When fast_forward=1, ProxySQL bypasses all query rules, multiplexing, and query cache for that user. The connection goes directly to the default_hostgroup backend. Use this only for internal/trusted applications that don't need query routing.
SQL — fast_forward User
-- Internal admin user that bypasses all routing
INSERT INTO mysql_users (username, password, default_hostgroup, fast_forward)
VALUES ('internal_app', 'InternalPass!', 10, 1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; Multiple Users — Different Routing
SQL — Multiple Users
-- Read-only reporting user (always goes to replicas)
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('reports_user', 'ReportPass!', 20, 0);
-- Read-write app user (goes to master by default)
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app_user', 'AppPass!', 10, 1);
-- ETL user with high connection limit
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections)
VALUES ('etl_user', 'EtlPass!', 10, 50);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
-- View all users
SELECT username, default_hostgroup, transaction_persistent, fast_forward, max_connections, active
FROM mysql_users;💡 Note: Create the same users in MySQL with appropriate GRANTs before adding them to ProxySQL. ProxySQL will use these credentials to connect to the backend.
Create Matching Users in MySQL
SQL — Create Users in MySQL
-- Run these on the MySQL Master
CREATE USER 'appuser'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
CREATE USER 'reports_user'@'%' IDENTIFIED BY 'ReportPass!';
GRANT SELECT ON myapp.* TO 'reports_user'@'%';
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;