Users Configuration

Users are configured in table mysql_users.

Note: Before proceeding reading the following section, please make sure you understand the multi-layer configuration system used by ProxySQL.
Specifically:

  • changes in mysql_users table do not take effect without running LOAD MYSQL USERS TO RUNTIME
  • changes in mysql_users table are lost after a restart/crash if not saved to disk running SAVE MYSQL USERS TO DISK

All changes in mysql_users table do not take effect immediately, neither are persistent.
Changes to mysql_users are to be considered as editing a config file without saving it or reloading the service.

Copying mysql users from memory to runtime

loads MySQL users from the in-memory database to the runtime data structures

Admin> LOAD MYSQL USERS TO RUNTIME;

Other alias accepted:

  • LOAD MYSQL USERS TO RUN
  • LOAD MYSQL USERS FROM MEM
  • LOAD MYSQL USERS FROM MEMORY

Copying mysql users from memory to disk

persists the MySQL users from the in-memory database to the on-disk database

Admin> SAVE MYSQL USERS TO DISK;

Other alias accepted:

  • SAVE MYSQL USERS FROM MEM
  • SAVE MYSQL USERS FROM MEMORY

Copying mysql users from runtime to memory

persists the MySQL users from the runtime data structures to the in-memory database

Admin> SAVE MYSQL USERS TO MEMORY;

Other alias accepted:

  • SAVE MYSQL USERS TO MEM
  • SAVE MYSQL USERS FROM RUN
  • SAVE MYSQL USERS FROM RUNTIME

Copying mysql users from disk to memory

loads MySQL users from the on-disk database to the in-memory database

Admin> LOAD MYSQL USERS TO MEMORY;

Other alias accepted:

  • LOAD MYSQL USERS TO MEM
  • LOAD MYSQL USERS FROM DISK

Using encrypted passwords

ProxySQL supports hashed passwords, and a detailed description is available here
In the examples below passwords are in clear text. Although this is acceptable for testing, it is not suitable for production. For production you should only use hashed passwords

Creating a new user

In order to create a new user, it must be defined inserting a new row in mysql_users table.
Note that the table has several column with defaults.

Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_users(username,password) VALUES ('user1','password1');
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_users\G
*************************** 1. row ***************************
              username: user1
              password: password1
                active: 1
               use_ssl: 0
     default_hostgroup: 0
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
1 row in set (0.00 sec)

Create a new user changing several defaults

In this example we will create a new user that:

  • uses dbtest1 as default schema
  • by default will be connected to a host in hostgroup 10 
    Admin> INSERT INTO mysql_users(username,password,default_hostgroup,default_schema) VALUES ('user2','password2',10,'dbtest1');
Query OK, 1 row affected (0.00 sec)

Limiting the number of connections a user can create to proxysql

An example:

Admin> SELECT username,max_connections FROM mysql_users;
+----------+-----------------+
| username | max_connections |
+----------+-----------------+
| user1    | 10000           |
| user2    | 10000           |
+----------+-----------------+
2 rows in set (0.00 sec)

Admin> UPDATE mysql_users SET max_connections=100 WHERE username='user2';
Query OK, 1 row affected (0.01 sec)

Admin> SELECT username,max_connections FROM mysql_users;
+----------+-----------------+
| username | max_connections |
+----------+-----------------+
| user1    | 10000           |
| user2    | 100             |
+----------+-----------------+
2 rows in set (0.00 sec)

Disabling routing across hostgroups once a transaction has started for a specific user

Once a transaction is started, it is possible that some queries are sent to a different hostgroup based on query rules.
To prevent this to happen, it is possible to enable transaction_persistent. An example:

Admin> SELECT username, transaction_persistent FROM mysql_users;
+----------+------------------------+
| username | transaction_persistent |
+----------+------------------------+
| user1    | 0                      |
| user2    | 0                      |
+----------+------------------------+
2 rows in set (0.00 sec)

Admin> UPDATE mysql_users SET transaction_persistent=1 WHERE username='user2';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT username, transaction_persistent FROM mysql_users;
+----------+------------------------+
| username | transaction_persistent |
+----------+------------------------+
| user1    | 0                      |
| user2    | 1                      |
+----------+------------------------+
2 rows in set (0.00 sec)