ProxySQL’s Configuration CLI

ProxySQL admin interface is an interface that uses the MySQL protocol, making it very easy to be configured by any client able to send commands through such an interface. ProxySQL parses the queries sent through this interface for any command specific to ProxySQL, and if appropriate it sends them to the embedded SQLite3 engine to run the queries.

Please note that SQL syntax used by SQLite3 and MySQL differs, therefore not all commands that work on MySQL will work on SQLite3. For example, although the USE command is accepted by the admin interface, it doesn’t change the default schema as this feature is not available in SQLite3.

When connecting to the ProxySQL admin interface, we can see that there are a few databases available. ProxySQL converts SHOW DATABASES command into the equivalent command for SQLite3.

mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

The purpose of these schemas is the following:

  • main: the in-memory configuration database. Using this database, it’s easy to query and update the configuration of ProxySQL in an automated manner. Using the LOAD MYSQL USERS FROM MEMORY and similar commands, the configuration stored in here can be propagated to the in-memory data structures used by ProxySQL at runtime.
  • disk: the disk-based mirror of “main”. Across restarts, “main” is not persisted and is loaded either from the “disk” database or from the config file, based on startup flags and the existence or not of an on-disk database.
  • stats: contains runtime metrics collected from the internal functioning of the proxy. Example metrics include the number of times each query rule was matched, the currently running queries, etc.
  • monitor: contains monitoring metrics related to the backend servers to which ProxySQL connects. Example metrics include the minimal and maximal time for connecting to a backend server or for pinging it.
  • myhgm: only enabled in debug builds

Also, the access to the admin database is done using two types of users, with these default credentials:

  • user: admin/password: admin — with read-write access to all the tables
  • user: stats/password: stats — with read-only access to statistics tables. This is used for pulling metrics from ProxySQL, without exposing too much of the database

The above credentials are configurable through the variables admin-admin_credentials and admin-stats_credentials.