Introduction
ProxySQL’s Admin stores configuration in tables.
If you connect to Admin using admin-admin_credentials credentials, you should be able to see a list of configuration and runtime tables like the following.
The exact list of tables may vary depending from the version in use, and if certain modules of ProxySQL are operating.
Admin> SHOW TABLES FROM main;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| clickhouse_users |
| debug_filters |
| debug_levels |
| 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_clickhouse_users |
| 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 |
+----------------------------------------------------+
36 rows in set (0,03 sec)
In the following sections you will find a detailed description of all the configuration tables.
Key Configuration Tables
| Tablename | Configures |
|---|---|
| mysql_users | Frontend and Backend MySQL Users |
| mysql_servers | Backend MySQL Servers |
| mysql_galera_hostgroups | MySQL clusters using Galera replication |
| mysql_group_replication_hostgroups | MySQL clusters using Group Replication |
| mysql_replication_hostgroups | MySQL replication clusters with servers in RW or RO mode |
| mysql_query_rules | Query Rules for MySQL traffic |
| mysql_query_rules_fast_routing | Query Rules for MySQL traffic specialized in routing |
| global_variables | All variables |
| scheduler | Tasks that the Scheduler can executes |
| mysql_collations | Known MySQL charsets and collations |
| proxysql_servers | List of core nodes in ProxySQL Cluster |
| restapi_routes | RESTAPI endpoints |
mysql_servers
Table mysql_servers defines all the backend servers that are either MySQL servers or using the MySQL Protocol (for example, another ProxySQL instance).
Servers are grouped into hostgroups, where a hostgroup is a set of servers that have the same logical functionality.
Table mysql_servers is defined as following:
Admin> SHOW CREATE TABLE mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.01 sec)
The fields have the following semantics:
- hostgroup_id: the hostgroup in which this backend server is included. Notice that the same instance can be part of more than one hostgroup
- hostname, port: the TCP endpoint at which the backend server can be reached. If
portis 0, the value inhostnameis interpreted as a Unix Socket file - gtid_port: the backend server port where ProxySQL Binlog Reader listens on for GTID tracking
status: the configured of the backend. This does not represent the current status, but the configured one:- ONLINE – backend server is fully operational
- SHUNNED – backend sever is temporarily taken out of use because of either too many connection errors in a time that was too short, or the replication lag exceeded the allowed threshold
- OFFLINE_SOFT – when a server is put into OFFLINE_SOFT mode, no new connections are created toward that server, while the existing connections are kept until they are returned to the connection pool or destructed. In other words, connections are kept in use until multiplexing is enabled again, for example when a transaction is completed. This makes it possible to gracefully detach a backend as long as multiplexing is efficient
- OFFLINE_HARD – when a server is put into OFFLINE_HARD mode, no new connections are created toward that server and the existing connections are immediately dropped. This is equivalent to deleting the server from a hostgroup. Internally, setting a server in OFFLINE_HARD status is equivalent to deleting the server
- weight – the bigger the weight of a server relative to other weights, the higher the probability of the server to be chosen from a hostgroup. ProxySQL default load-balancing algorithm is random-weighted
- compression – if the value is 1, new connections to that server will use compression. Please note that frontend and backend connections do not need to either both use compression or not. Each frontend connection can use or not use compression no matter if the backend connection is using or not using compression
- max_connections – the maximum number of connections ProxySQL will open to this backend server. Even though this server will have the highest weight, no new connections will be opened to it once this limit is hit. Please ensure that the backend is configured with a correct value of max_connections to avoid ProxySQL trying to go beyond that limit. Furthermore, one of the main features of ProxySQL is multiplexing (the ability to use the same backend connection for multiple frontend connections): if efficient, max_connections per backend can be configured to a very small value.
- max_replication_lag – if greater than 0, ProxySQL will regularly monitor replication lag and if it goes beyond the configured threshold it will temporary shun the host until replication catches up
- use_ssl – if set to 1, connections to the backend will use SSL. Please note that frontend and backend connections do not need to either both use TLS or not. Each frontend connection can use or not use TLS no matter if the backend connection is using or not using TLS
- max_latency_ms – ping time is regularly monitored. If a host has a ping time greater than
max_latency_msit is excluded from the connection pool (although the server stays ONLINE) - comment – text field that can be used for any purpose defined by the user. Could be a description of what the host stores, a reminder of when the host was added or disabled, or a JSON processed by some external script.
mysql_replication_hostgroups
Table mysql_replication_hostgroups defines replication hostgroups for use with traditional master / slave ASYNC or SEMI-SYNC replication. In case Group Replication / InnoDB Cluster or Galera / Percona XtraDB Cluster is used for replication the mysql_group_replication_hostgroups or mysql_galera_hostgroups (available in version 2.x) should be used instead.
Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
Each row in mysql_replication_hostgroups represents a pair of writer_hostgroup and reader_hostgroup .
ProxySQL will monitor the variable(s) specified in check_type for all the servers in specified hostgroups, and based on the value of the variable (or binary operation on 2 variable) it will assign the server to the writer or reader hostgroups.
The field comment can be used to store any arbitrary data.
The fields have the following semantics:
writer_hostgroup– the hostgroup where writers are configured. Nodes that have a read only check returning 0 will be assigned to this hostgroup.reader_hostgroup– the hostgroup where readers are configured. Read traffic should be sent to this hostgroup, assuming query rules or a separate read only user is defined to route traffic to this hostgroup. Nodes that have a read only check returning 1 will be assigned to this hostgroup.check_type– the MySQL variable(s) checked when executing a Read Only check, and optionally the logical binary operation.read_onlyis the default.innodb_read_onlyandsuper_read_onlycan be used as well. Before the introduction of Native Support for AWS Aurora,innodb_read_onlyshould be used. Checks onread_onlyandinnodb_read_onlycan be combinedcomment– text field that can be used for any purpose defined by the user. Could be a description of what the cluster stores, a reminder of when the hostgroup was added or disabled, or a JSON processed by some checker script.
mysql_group_replication_hostgroups
Table mysql_group_replication_hostgroups defines hostgroups for use with Oracle Group Replication / InnoDB Cluster
Admin> show create table mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_group_replication_hostgroups
Create Table: CREATE TABLE mysql_group_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
comment VARCHAR,
UNIQUE (reader_hostgroup),
UNIQUE (offline_hostgroup),
UNIQUE (backup_writer_hostgroup))
1 row in set (0.00 sec)
The fields have the following semantics:
writer_hostgroup– the hostgroup that all traffic will be sent to by default. Nodes that haveread_only=0in MySQL will be assigned to this hostgroup.backup_writer_hostgroup– if the cluster has multiple nodes withread_only=0that exceedmax_writers, ProxySQL will put the additional nodes (in excess ofmax_writes) in thebackup_writer_hostgroup.reader_hostgroup– the hostgroup that read traffic should be sent to, query rules or a separate read only user should be defined to route traffic to this hostgroup. Nodes that haveread_only=1will be assigned to this hostgroup.offline_hostgroup– when ProxySQL’s monitoring determines a node isOFFLINEor unhealthy, it will be put into theoffline_hostgroup.active– when enabled, ProxySQL monitors the hostgroups and moves nodes between the appropriate hostgroups.max_writers– this value determines the maximum number of nodes that should be allowed in thewriter_hostgroup, nodes in excess of this value will be put into thebackup_writer_hostgroupwriter_is_also_reader– determines if a node should be added to thereader_hostgroupas well as thewriter_hostgroup. The special valuewriter_is_also_reader=2signals that only the nodes inbackup_writer_hostgroupare also inreader_hostgroup, excluding the node(s) in thewriter_hostgroupmax_transactions_behind– determines the maximum number of transactions behind the writers that ProxySQL should allow before shunning the node to prevent stale reads (this is determined by querying thetransactions_behindfield of thesys.gr_member_routing_candidate_statustable in MySQL).comment– text field that can be used for any purpose defined by the user. Could be a description of what the cluster stores, a reminder of when the hostgroup was added or disabled, or a JSON processed by some checker script.
ProxySQL also offers several configuration variables about monitoring of group replication clusters that can be consulted in mysql-monitor-variables doc.
mysql_galera_hostgroups
Table mysql_galera_hostgroups defines hostgroups for use with Galera Cluster / Percona XtraDB Cluster.
Each row in table mysql_galera_hostgroups represents a single cluster, and the hostgroups part of that cluster.
The table definition is the following:
Admin> show create table mysql_galera_hostgroups\G
*************************** 1. row ***************************
table: mysql_galera_hostgroups
Create Table: CREATE TABLE mysql_galera_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
comment VARCHAR,
UNIQUE (reader_hostgroup),
UNIQUE (offline_hostgroup),
UNIQUE (backup_writer_hostgroup))
1 row in set (0.00 sec)
The fields have the following semantics:
- writer_hostgroup – the hostgroup that all traffic will be sent to by default. Healthy nodes that have
read_only=0in MySQL will be assigned to this hostgroup by default. - backup_writer_hostgroup – if the cluster has multiple nodes with
read_only=0and their number is greater thanmax_writers, ProxySQL will automatically move the additional nodes (in excess ofmax_writes) in thebackup_writer_hostgroup. - reader_hostgroup – healthy nodes that have
read_only=1will be assigned to this hostgroup. Users can create query rules to send read traffic to this hostgroup if needed to remove load from the servers in thewriter_hostgroup - offline_hostgroup – unhealthy nodes are moved to
offline_hostgroupuntil they become healthy again and moved back into a different hostgroup - active – when enabled, ProxySQL monitors the hostgroups and moves servers between the appropriate hostgroups. If disabled, Galera monitoring is disabled for the given cluster and ProxySQL doesn’t perform any reconfiguration, despite the fact that hostgroups and servers are still configured in mysql_servers
- max_writers – this value determines the maximum number of nodes that should be allowed in the
writer_hostgroup, nodes in excess of this value will be put into thebackup_writer_hostgroup - writer_is_also_reader – determines if a node should be added to the
reader_hostgroupas well as thewriter_hostgroupafter changingread-onlyfrom 1 to 0. The special valuewriter_is_also_reader=2signals that only the nodes inbackup_writer_hostgroupare also inreader_hostgroup, excluding the node(s) in thewriter_hostgroup - max_transactions_behind – determines the maximum number of writesets behind the cluster that ProxySQL should allow before shunning the node to prevent stale reads (this is determined by querying the
wsrep_local_recv_queueGalera variable). - comment – text field that can be used for any purpose defined by the user. Could be a description of what the cluster stores, a reminder of when the hostgroup was added or disabled, or a JSON processed by some checker script.
mysql_users
Table mysql_users defines MySQL users that clients can use to connect to ProxySQL, and then used to connect to backends.
Admin> SHOW CREATE TABLE mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
The fields have the following semantics:
- username, password – credentials for connecting to the mysqld or ProxySQL instance. Password can be in clear text, or hashed. See also Password management
- active – the users with active = 0 will be tracked in the database, but will never be loaded in the in-memory data structures
use_ssl– if set to 1, the user is forced to authenticate with using an SSL certificate. See also SSL Supportdefault_hostgroup– if there is no matching rule for the queries sent by this user, the traffic it generates is sent to the specified hostgroupdefault_schema– the schema to which the connection should change by defaultschema_locked– not supported yet (TODO: check)transaction_persistent– if this is set for the user with which the MySQL client is connecting to ProxySQL (thus a “frontend” user – see below), transactions started within a hostgroup will remain within that hostgroup regardless of any other rulesfast_forward– if set it bypasses the query processing layer (rewriting, caching) and passes through the query directly as is to the backend server.frontend– if set to 1, this (username, password) pair is used for authenticating to the ProxySQL instancebackend– if set to 1, this (username, password) pair is used for authenticating to the mysqld servers against any hostgroupmax_connections– defines the maximum number of allowable frontend connections for a specific user.attributes– ToDocomment– text field that can be used for any purpose defined by the user. Could be a description of what the cluster stores, a reminder of when the hostgroup was added or disabled, or a JSON processed by some checker script.
Note, currently all users need both “frontend” and “backend” set to 1 . Future versions of ProxySQL will separate the crendentials between frontend and backend. In this way frontend will never know the credential to connect directly to the backend, forcing all the connections through ProxySQL and increasing the security of the system.
Fast forward notes:
- it doesn’t require a different port : full features proxy logic and “fast forward” logic is implemented in the same code/module
- fast forward is implemented on a per-user basis : depending on the user that connects to ProxySQL , fast forward is enabled or disabled
- fast forward algorithm is enabled after authentication : the client still authenticates to ProxySQL, and ProxySQL will create a connection when the client will start sending traffic. This means that the connections’ errors are still handled during the connect phase.
- fast forward does NOT support SSL
- if using compression, it must be enabled on both ends
Note: users in mysql_users shouldn’t be used also for admin-admin_credentials and admin-stats_credentials
mysql_query_rules
Table mysql_query_rules defines routing policies and attributes.
Admin> SHOW CREATE TABLE mysql_query_rules\G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT CHECK (flagOUT >= 0),
replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
cache_timeout INT CHECK(cache_timeout >= 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
gtid_from_hostgroup INT UNSIGNED,
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
comment VARCHAR)
1 row in set (0.00 sec)
The fields have the following semantics:
rule_id– the unique id of the rule. Rules are processed in rule_id orderactive– only rules with active=1 will be considered by the query processing module and only active rules are loaded into runtime.username– filtering criteria matching username. If it’s non-NULL, a query will match only if the connection is made with the correct usernameschemaname– filtering criteria matching schemaname. If it’s non-NULL, a query will match only if the connection usesschemanameas default schema (in mariadb/mysql schemaname is equivalent to databasename)flagIN,flagOUT,apply– these allow us to create “chains of rules” that get applied one after the other. An input flag value is set to 0, and only rules with flagIN=0 are considered at the beginning. When a matching rule is found for a specific query, flagOUT is evaluated and if NOT NULL the query will be flagged with the specified flag in flagOUT. If flagOUT differs from flagIN , the query will exit the current chain and a new chain of rules enters having flagIN as the new input flag. If flagOUT matches flagIN, the query will be re-evaluated again against the first rule with said flagIN. This happens until there are no more matching rules, or apply is set to 1 (which means this is the last rule to be applied)client_addr– match traffic from a specific sourceproxy_addr– match incoming traffic on a specific local IPproxy_port– match incoming traffic on a specific local portdigest– match queries with a specific digest, as returned bystats_mysql_query_digest.digestmatch_digest– regular expression that matches the query digest. See also mysql-query_processor_regexmatch_pattern– regular expression that matches the query text. See also mysql-query_processor_regexnegate_match_pattern– if this is set to 1, only queries not matching the query text will be considered as a match. This acts as a NOT operator in front of the regular expression matching againstmatch_patternormatch_digestre_modifiers– comma separated list of options to modify the behavior of the RE engine. WithCASELESSthe match is case insensitive. WithGLOBALthe replace is global (replaces all matches and not just the first). For backward compatibility, onlyCASELESSis enabled by default. See also mysql-query_processor_regex for more details.replace_pattern– this is the pattern with which to replace the matched pattern. It’s done using RE2::Replace, so it’s worth taking a look at the online documentation for that: https://github.com/google/re2/blob/master/re2/re2.h#L378. Note that this is optional, and when this is missing, the query processor will only cache, route, or set other parameters without rewriting.destination_hostgroup– route matched queries to this hostgroup. This happens unless there is a started transaction and the logged in user has the transaction_persistent flag set to 1 (seemysql_userstable).cache_ttl– the number of milliseconds for which to cache the result of the query. Note: in ProxySQL 1.1 cache_ttl was in secondscache_empty_result– controls if resultset without rows will be cached or notcache_timeout– ToDoreconnect– feature not usedtimeout– the maximum timeout in milliseconds with which the matched or rewritten query should be executed. If a query runs for longer than the specific threshold, the query is automatically killed. If timeout is not specified, global variablemysql-default_query_timeoutappliesretries– the maximum number of times a query needs to be re-executed in case of detected failure during the execution of the query. If retries is not specified, global variablemysql-query_retries_on_failureappliesdelay– number of milliseconds to delay the execution of the query. This is essentially a throttling mechanism and QoS, allowing to give priority to some queries instead of others. This value is added to themysql-default_query_delayglobal variable that applies to all queries. Future versions of ProxySQL will provide a more advanced throttling mechanism.mirror_flagOUTandmirror_hostgroup– setting related to mirroring .error_msg– query will be blocked, and the specifiederror_msgwill be returned to the clientOK_msg– the specified message will be returned for a query that uses the defined rulesticky_conn– not implemented yetmultiplex– If 0, multiplex will be disabled. If 1, multiplex could be re-enabled if there aren’t any other conditions preventing this (like user variables or transactions). If 2, multiplexing is not disabled for just the current query. See wiki Default isNULL, thus not modifying multiplexing policiesgtid_from_hostgroup– defines which hostgroup should be used as theleaderfor GTID consistent reads (typically the definedWRITER hostgroupin a replication hostgroup pair)log– this column can have three values:1– matched query will be recorded into the events log;0– matched query will not be recorded into the events log;NULL– matched query log attribute will remain the value from the previous match(es). Executed query will be recorded to the events log if its log attribute is set to1when rule is applied (apply=1) or after processing all query rules and its log attribute is set to1apply– when set to1no further queries will be evaluated after this rule is matched and processed (note:mysql_query_rules_fast_routingrules will not be evaluated afterwards)attributes– ToDocomment– free form text field, usable for a descriptive comment of the query rule
mysql_query_rules_fast_routing
Table mysql_query_rules_fast_routing is an extension of mysql_query_rules and is evaluated afterwards for fast routing policies and attributes (only available in ProxySQL 1.4.7+).
Admin> SHOW CREATE TABLE mysql_query_rules_fast_routing\G
*************************** 1. row ***************************
table: mysql_query_rules_fast_routing
Create Table: CREATE TABLE mysql_query_rules_fast_routing (
username VARCHAR NOT NULL,
schemaname VARCHAR NOT NULL,
flagIN INT NOT NULL DEFAULT 0,
destination_hostgroup INT CHECK (destination_hostgroup >= 0) NOT NULL,
comment VARCHAR NOT NULL,
PRIMARY KEY (username, schemaname, flagIN) )
1 row in set (0,00 sec)
The fields have the following semantics:
username– filtering criteria matching username, a query will match only if the connection is made with the correct usernameschemaname– filtering criteria matching schemaname, a query will match only if the connection usesschemanameas default schema (in mariadb/mysql schemaname this is equivalent to databasename)flagIN– evaluated in the same way asflaginis inmysql_query_rulesand correlates to theflagout/applyspecified in themysql_query_rulestabledestination_hostgroup– route matched queries to this hostgroup. This happens unless there is a started transaction and the logged in user has the transaction_persistent flag set to 1 (seemysql_userstable)comment– free form text field, usable for a descriptive comment of the query rule
global_variables
The table global_variables defines Global variables. This is a much simpler table, essentially a key-value store. These are global variables used by ProxySQL and are useful in order to tweak its behaviour.
Global variables are grouped in classes based on their prefix.
Currently there are 2 classes of global variables, although more classes are in the roadmap:
- variables prefixed with
admin-are relevant for Admin module and allow tweaking of the admin interface E.G. changing the admin interface (admin-mysql_ifaces) or admin credentials (admin-admin_credentials) - variables prefixed with
mysql-are relevant for MySQL modules and allow tweaking of MySQL-related features. Specifically they include tuning of variables related to:- handling of MySQL traffic
- monitor operatations (further prefixed with
mysql-monitor_) - query caching
For more information about particular variables, please see the dedicated section on global variables
Admin> SHOW CREATE TABLE global_variables\G
*************************** 1. row ***************************
table: global_variables
Create Table: CREATE TABLE global_variables (
variable_name VARCHAR NOT NULL PRIMARY KEY,
variable_value VARCHAR NOT NULL)
1 row in set (0.00 sec)
For reference, an example of how global_variables looks at the time of writing (version 1.2.4):
Admin> SELECT * FROM global_variables ORDER BY variable_name;
+-----------------------------------------------------+------------------------+
| variable_name | variable_value |
+-----------------------------------------------------+------------------------+
| admin-admin_credentials | admin:admin |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_password | |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-cluster_username | |
| admin-hash_passwords | true |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-read_only | false |
| admin-refresh_interval | 2000 |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-version | v2.0.0-rc1-17-g832aa48 |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| mysql-autocommit_false_is_transaction | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-binlog_reader_connect_retry_msec | 3000 |
| mysql-client_found_rows | true |
| mysql-client_multi_statements | true |
| mysql-commands_stats | true |
| mysql-connect_retries_delay | 1 |
| mysql-connect_retries_on_failure | 10 |
| mysql-connect_timeout_server | 3000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-default_charset | utf8 |
| mysql-default_max_latency_ms | 1000 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-default_reconnect | true |
| mysql-default_schema | information_schema |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-enforce_autocommit_on_reads | false |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-forward_autocommit | false |
| mysql-free_connections_pct | 10 |
| mysql-have_compress | true |
| mysql-have_ssl | false |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-init_connect | |
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-kill_backend_connection_when_disconnect | true |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 4194304 |
| mysql-max_connections | 2048 |
| mysql-max_stmts_cache | 10000 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_transaction_time | 14400000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_enabled | true |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_history | 600000 |
| mysql-monitor_password | monitor |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_username | monitor |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-multiplexing | true |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-query_cache_size_MB | 256 |
| mysql-query_cache_stores_empty_result | true |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-query_retries_on_failure | 1 |
| mysql-reset_connection_algorithm | 2 |
| mysql-server_capabilities | 45578 |
| mysql-server_version | 5.5.30 |
| mysql-servers_stats | true |
| mysql-session_idle_ms | 1000 |
| mysql-session_idle_show_processlist | true |
| mysql-sessions_sort | true |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_cipher | |
| mysql-ssl_p2s_key | |
| mysql-stacksize | 1048576 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-threads | 4 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-throttle_max_bytes_per_second_to_client | 2147483647 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-verbose_query_error | false |
| mysql-wait_timeout | 28800000 |
+-----------------------------------------------------+------------------------+
136 rows in set (0.01 sec)
scheduler
Table scheduler defines jobs to be executed at regular intervals.
Admin> SHOW CREATE TABLE scheduler\G
*************************** 1. row ***************************
table: scheduler
Create Table: CREATE TABLE scheduler (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
filename VARCHAR NOT NULL,
arg1 VARCHAR,
arg2 VARCHAR,
arg3 VARCHAR,
arg4 VARCHAR,
arg5 VARCHAR,
comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.00 sec)
Further details about the scheduler can be found here
restapi_routes
Table restapi_routes defines endpoints that a remote client can call using a REST API endpoint using HTTP in order to trigger the execution of a task by ProxySQL.
The following is the table definition:
mysql> SHOW CREATE TABLE restapi_routes\G
*************************** 1. row ***************************
table: restapi_routes
Create Table: CREATE TABLE restapi_routes (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
timeout_ms INTEGER CHECK (timeout_ms>=100 AND timeout_ms<=100000000) NOT NULL,
method VARCHAR NOT NULL CHECK (UPPER(method) IN ('GET','POST')),
uri VARCHAR NOT NULL,
script VARCHAR NOT NULL,
comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0,03 sec)The table defines:
- id: unique identifier of the endpoint
- active: a REST API endpoint can be either active or not. An endpoint is active by default, and it is possible to disable it setting to 0 without the need to delete it
- timeout_ms: this defines the maximum execution time for the job that will be called by proxysql when that endpoint is reached
- method: a REST API endpoint can be called with either a GET or POST method. This defines what method must be used
- uri: this defines the URI (Uniform Resource Identifier) of the endpoint
- script: this is the script (or other executable) that proxysql will executes at the specified endpoint
- comment: text field that can be used for any purpose defined by the user, for example to describe the endpoint
mysql_collations
Here is the table definition for the mysql_collations table:
Admin> show create table mysql_collations\G
*************************** 1. row ***************************
table: mysql_collations
Create Table: CREATE TABLE mysql_collations (
Id INTEGER NOT NULL PRIMARY KEY,
Collation VARCHAR NOT NULL,
Charset VARCHAR NOT NULL,
`Default` VARCHAR NOT NULL)
1 row in set (0.01 sec)
The table mysql_collations is a representation of all the known and available (charset, collation) pairs supported by ProxySQL.
Column Id represents the numeric value of the collation as defined in MySQL itself.
Column `Default` defines if the given collation is the default collation for the collation it belongs to.
Please note that at time MySQL changes the default collation for a given charset, and ProxySQL may be using a default different than what specified in the backend.
For example, in MySQL 5.7 and ProxySQL the default collation for utf8mb4 is utf8mb4_general_ci , while in MySQL 8.0 the default collation is utf8mb4_0900_ai_ci.
If the client specifies a collation during connection phase or later using SET NAMES ... COLLATE ..., proxysql will track the right collation and use it.
In principle, ProxySQL will validate that incoming connections have a supported charset and collation, and will make sure that the pooled backend connections are switched to the correct charset and collation before using them.
Runtime tables
All the configuration tables listed above have a matching runtime_ table:
- runtime_global_variables : runtime version of global_variables
- runtime_mysql_replication_hostgroups : runtime version of mysql_replication_hostgroups
- runtime_mysql_galera_hostgroups : runtime version of mysql_replication_hostgroups
- runtime_mysql_group_replication_hostgroups : runtime version of mysql_replication_hostgroups
- runtime_mysql_query_rules : runtime version of mysql_query_rules
- runtime_mysql_query_rules_fast_routing : runtime version of mysql_query_rules_fast_routing
- runtime_mysql_servers : runtime version of mysql_servers
- runtime_mysql_users : runtime version of mysql_users
- runtime_proxysql_servers : runtime version of proxysql_servers
- runtime_scheduler : runtime version of scheduler
A note on main schema
Note that all the content of the in-memory tables (main database) are lost when ProxySQL is restarted if their content wasn’t saved on disk database.
Debug config
debug_levels
The table debug_levels defines a series of verbosity levels that can be enabled for ProxySQL when compiled in ‘DEBUG’ mode.
Admin> SHOW CREATE TABLE debug_levels\G
*************************** 1. row ***************************
table: debug_levels
Create Table: CREATE TABLE debug_levels (
module VARCHAR NOT NULL PRIMARY KEY,
verbosity INT NOT NULL DEFAULT 0)
1 row in set (0.00 sec)
For reference, an example showing how ‘debug_levels’ looks at the time of writing (v2.3.2):
Admin> SELECT * FROM debug_levels;\G
+-----------------------------+-----------+
| module | verbosity |
+-----------------------------+-----------+
| debug_generic | 0 |
| debug_net | 0 |
| debug_pkt_array | 0 |
| debug_poll | 0 |
| debug_mysql_com | 0 |
| debug_mysql_server | 0 |
| debug_mysql_connection | 0 |
| debug_mysql_connpool | 0 |
| debug_mysql_rw_split | 0 |
| debug_mysql_auth | 0 |
| debug_mysql_protocol | 0 |
| debug_mysql_query_processor | 0 |
| debug_memory | 0 |
| debug_admin | 0 |
| debug_sqlite | 0 |
| debug_ipc | 0 |
| debug_query_cache | 0 |
| debug_query_statistics | 0 |
+-----------------------------+-----------+
18 rows in set (0.00 sec)
In order to change theses values it’s required to:
- Set a new value for any of the variables, e.g:
UPDATE debug_levels SET verbosity=9 WHERE module='debug_mysql_com' - Load the new configuration:
LOAD DEBUG TO RUNTIME - Enable debugging via
admin-debugvariable, i.e:SET admin-debug='true'; LOAD ADMIN VARIABLES TO RUNTIME;
disk database
The “disk” database has exactly the same tables as the “main” database (minus the runtime_ tables), with the same semantics. The only major difference is that these tables are stored on disk, instead of being stored in-memory. Whenever ProxySQL is restarted, the in-memory “main” database will be populated starting from this database.
Note that all the content of the in-memory tables (main database) are lost when ProxySQL is restarted if their content wasn’t saved on disk database.
config mysql group replication need to add view to mysql instance sys
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
SET result = result +
SUBSTR(gtid_set, next_dash_pos + 1,
LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
ELSE
SET result = result + 1;
END IF;
SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
DELIMITER ;
then grant select on sys.* to monitoring_user