Multi layer configuration system
ProxySQL has an advanced yet intuitive configuration system built to facilitate:
- Easy-to-use dynamic runtime configuration system to ensure zero-downtime changes
- Effortless rollback of configuration to its previous state
- A MySQL-compatible administrative interface
This is achieved through a multi-layer configuration system where settings are moved from runtime to memory, and persisted to disk as desired.
The RUNTIME layer represents the in-memory data structures of ProxySQL used by worker threads handling requests.
The runtime variables contain configuration related to:
- the actual values defined in the global variables
- the list of backend servers grouped into hostgroups
- the list of MySQL users that can connect to the proxy
Note: Operators can never modify the contents of the RUNTIME configuration section directly.
The MEMORY (also referred to as main) layer represents an in-memory database which is exposed via a MySQL-compatible interface. Users can connect with a MySQL client to this interface and view / edit the various ProxySQL configuration tables.
Configuration tables available through this interface include tables such as:
mysql_servers— the list of backend servers which ProxySQL connects to
mysql_users— the list of users and their credentials which connect to ProxySQL. Please note that ProxySQL will use the same credentials to connect to the backend servers as well!
mysql_query_rules— the list of query rules which are evaluated when routing traffic to the various backend servers. These rules can also rewrite queries or even cache the result of an executed query.
global_variables— the list of global variables which the proxy is configured to use, and which can be tweaked during runtime.
Examples of global variables:
mysql> select * from global_variables limit 3; +----------------------------------+----------------+ | variable_name | variable_value | +----------------------------------+----------------+ | mysql-connect_retries_on_failure | 5 | | mysql-connect_retries_delay | 1 | | mysql-connect_timeout_server_max | 10000 | +----------------------------------+----------------+
mysql_collations— the list of MySQL collations available for the proxy to work with. These are extracted directly from the client library.
- [only available in debug builds]
debug_levels— the list of types of debug statements that ProxySQL emits together with their verbosity levels and is only available in debug builds because it can affect performance.
The DISK layer represents an on-disk SQLite3 database, with the default location at
/var/lib/proxysql/proxysql.db. The DISK database is available to persist in-memory configs to disk so that the configuration is available after a ProxySQL restart. The CONFIG file is the classical config file, and we’ll see the relationship between it and the other configuration layers in the next section.
During start-up, ProxySQL reads its config file (if present) to determine the defined
datadir and then tries to locate its internal database file in the specified path.
- If the database is found in the specified datadir, ProxySQL will initialize its in-memory configuration from the persisted on-disk database i.e. here the disk configuration is loaded into MEMORY and is also propagated to RUNTIME.
- If the database file is not found (e.g. in case ProxySQL is being run for the first time or the database has been deleted) the config file is parsed and its content is loaded into MEMORY, saved to the DISK database and is also propagated to RUNTIME.
IMPORTANT NOTE: If a database file is found, the
/etc/proxysql.cnf config file is not parsed i.e. ProxySQL initializes its in-memory configuration from the persisted on-disk database ONLY, unless it is not present in which case it will fallback to the configuration file.
As mentioned, during the very first start of ProxySQL, the memory and runtime configuration is populated from the config file, thereafter all persisted configuration is stored in ProxySQL’s internal database.
It is possible to force the initial configuration to re-occur by running proxysql with the
--initial flag, which will reset the SQLite database file to its original state (i.e. the state defined in the config file) and rename the existing SQLite database file in case a rollback is required (check the defined data directory for the older file if required).
If ProxySQL binary is executed with the
--reload flag, it attempts to merge the configuration in the config file with the content of the database file. Thereafter, it will startup using the newly merged configuration.
There is no guarantee that ProxySQL will successfully manage to merge the two configuration source when there are conflicts, and users should always validate that the merge was executed as expected.
Runtime ProxySQL Configuration
Modifying the config at runtime is done through the ProxySQL Admin port of ProxySQL (6032 by default). After connecting to it with a MySQL compatible client, you’ll see a standard MySQL style interface for querying the various underlying ProxySQL configuration and statistics tables:
ProxySQL Admin> show tables; +----------------------------------------------------+ | tables | +----------------------------------------------------+ | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_firewall_whitelist_rules | | mysql_firewall_whitelist_sqli_fingerprints | | mysql_firewall_whitelist_users | | mysql_galera_hostgroups | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | restapi_routes | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_aws_aurora_hostgroups | | runtime_mysql_firewall_whitelist_rules | | runtime_mysql_firewall_whitelist_sqli_fingerprints | | runtime_mysql_firewall_whitelist_users | | runtime_mysql_galera_hostgroups | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_restapi_routes | | runtime_scheduler | | scheduler | +----------------------------------------------------+
Each such table has a well defined role in the admin interface, more information about each table can be found in the Main (runtime) schema documentation.
These tables represent the in-memory database described in the diagram above and can be configured using standard SQL queries. In order to move the configuration from this layer upwards (i.e. in order to be used during runtime) or downwards (in order to be persisted to disk), please review the next section.
Moving config between layers
In order to persist configuration to disk or load configuration to runtime there is a set of different admin commands available which can be executed via the admin interface for each configuration item e.g.
mysql query rules.
The following command syntax can be used to move ProxySQL configuration between the various layers, <item> is a placeholder for the configuration item you wish to configure:
-  `LOAD <item> FROM MEMORY` / `LOAD <item> TO RUNTIME`
- Loads configuration item from the in-memory database to the runtime data structures
-  `SAVE <item> TO MEMORY` / `SAVE <item> FROM RUNTIME`
- Saves the configuration item from runtime to the in-memory database
-  `LOAD <item> TO MEMORY` / `LOAD <item> FROM DISK`
- Loads the persisted configuration item from the on-disk database to the in-memory database
-  `SAVE <item> FROM MEMORY` / `SAVE <item> TO DISK`
- Saves the configuration item from the in-memory database to the on-disk database
-  `LOAD <item> FROM CONFIG`
- Loads the configuration item from the configuration file into the in-memory database
Important Note: Changes will NOT be activated until they are loaded to RUNTIME and any changes which are NOT saved to DISK will NOT be available after a ProxySQL restart.
Sample commands for common configuration items can be found below (this is not a complete list):
Activate / Persist MySQL Users:
# Active current in-memory MySQL User configuration LOAD MYSQL USERS TO RUNTIME; # Save the current in-memory MySQL User configuration to disk SAVE MYSQL USERS TO DISK;
Activate / Persist MySQL Servers and MySQL Replication Hostgroup:
# Active current in-memory MySQL Server and Replication Hostgroup configuration LOAD MYSQL SERVERS TO RUNTIME; # Save the current in-memory MySQL Server and Replication Hostgroup configuration to disk SAVE MYSQL SERVERS TO DISK;
Activate / Persist MySQL Query Rules
# Active current in-memory MySQL Query Rule configuration LOAD MYSQL QUERY RULES TO RUNTIME; # Save the current in-memory MySQL Query Rule configuration to disk SAVE MYSQL QUERY RULES TO DISK;
Activate / Persist MySQL Variables
# Active current in-memory MySQL Variable configuration LOAD MYSQL VARIABLES TO RUNTIME; # Save the current in-memory MySQL Variable configuration to disk SAVE MYSQL VARIABLES TO DISK;
Activate / Persist ProxySQL Admin Variables
# Active current in-memory ProxySQL Admin Variable configuration LOAD ADMIN VARIABLES TO RUNTIME; # Save the current in-memory ProxySQL Admin Variable configuration to disk SAVE ADMIN VARIABLES TO DISK;
Note: the above commands allow the following shortcuts:
- MEM for MEMORY
- RUN for RUNTIME
For example, these two commands are equivalent:
- `SAVE ADMIN VARIABLES TO MEMORY`
- `SAVE ADMIN VARIABLES TO MEM`
Note that final validation is only done when values are loaded to runtime. You can set a value that won’t raise any kind of warning or error when saved to memory, or even saved to disk. Changes are however reverted to their previously saved state when a load to runtime is executed. If this occurs you should check the defined error log.
[WARNING] Impossible to set variable monitor_read_only_interval with value "0". Resetting to current "1500".