Users Configuration

Users are configured in table mysql_users. Note: Before 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 they 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

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

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

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

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 columns with defaults.

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

Limiting the number of connections a user can create to ProxySQL

An example:

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 from happening, it is possible to enable transaction_persistent. An example: