Skip to main content

Users Configuration

Overview​

Users are configured in the mysql_users table. ProxySQL uses a multi-layer configuration system where changes require explicit loading and saving commands to take effect and persist.

Key Commands​

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

Creating Users​

Basic user creation:

INSERT INTO mysql_users (username, password) VALUES ('user1', 'password1');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

With custom settings:

INSERT INTO mysql_users (username, password, default_hostgroup, default_schema)
VALUES ('user2', 'password2', 10, 'dbtest1');

Key User Properties​

ColumnDefaultDescription
active1Enables/disables the user
use_ssl0Require SSL for connections
default_hostgroup0Hostgroup to route traffic to
default_schemaNULLDefault database schema
schema_locked0Restrict user to default schema
transaction_persistent1Keep transactions on the same hostgroup
max_connections10000Maximum frontend connections
backend1Use for backend connections
frontend1Use for frontend connections

Common Configurations​

Limit connections per user:

UPDATE mysql_users SET max_connections=100 WHERE username='user2';
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Enable transaction persistence:

UPDATE mysql_users SET transaction_persistent=1 WHERE username='user2';
LOAD MYSQL USERS TO RUNTIME;
caution

Use hashed passwords rather than cleartext in production. Changes to mysql_users do not take effect without LOAD MYSQL USERS TO RUNTIME and are not persisted without SAVE MYSQL USERS TO DISK.

See Also​