MySQL Tables

ℹInfo

Note: Changes made to the configuration on this page must be explicitly loaded to the runtime to take effect. Please refer to the Admin Commands documentation for details on the LOAD and SAVE commands.

Introduction

The ProxySQL Admin stores configuration in tables. If you connect to Admin using admin-admin_credentials credentials, you can 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. This is a MySQL specific summary:

Admin> SHOW TABLES FROM main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
...
| 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_hostgroup_attributes                         |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_servers_ssl_params                           |
| mysql_users                                        |
...
+----------------------------------------------------+

Key Configuration Tables

TablenameConfigures
mysql_serversBackend MySQL Servers
mysql_servers_ssl_paramsBackend MySQL Server specific SSL Parameters
mysql_usersFrontend and Backend MySQL Users
mysql_query_rulesQuery Rules for MySQL traffic
mysql_query_rules_fast_routingQuery Rules for MySQL traffic specialized in routing
mysql_galera_hostgroupsMySQL clusters using Galera replication
mysql_group_replication_hostgroupsMySQL clusters using Group Replication
mysql_hostgroup_attributesHostgroup-specific attributes that override global settings
mysql_replication_hostgroupsMySQL replication clusters with servers in RW or RO mode
mysql_collationsKnown MySQL charsets and collations
mysql_aws_aurora_hostgroupsMySQL clusters using Amazon Aurora with native discovery
mysql_firewall_whitelist_rulesQuery firewall whitelist rules per user and schema
mysql_firewall_whitelist_usersUsers and their assigned firewall enforcement mode
mysql_firewall_whitelist_sqli_fingerprintsSQLi fingerprints used for injection detection and blocking

In the following sections you will find a detailed description of all the configuration tables.

mysql_aws_aurora_hostgroups

Table mysql_aws_aurora_hostgroups defines hostgroups for use with Amazon Aurora clusters. ProxySQL uses the Aurora discovery mechanism to automatically detect cluster topology changes, including writer and reader endpoint assignments.

CREATE TABLE mysql_aws_aurora_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),
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    aurora_port INT NOT NULL DEFAULT 3306,
    domain_name VARCHAR NOT NULL DEFAULT '',
    max_lag_ms INT CHECK (max_lag_ms>=0) NOT NULL DEFAULT 600000,
    check_interval_ms INT CHECK (check_interval_ms>=0) NOT NULL DEFAULT 1000,
    check_timeout_ms INT CHECK (check_timeout_ms>=0) NOT NULL DEFAULT 800,
    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,
    new_reader_weight INT CHECK (new_reader_weight >= 0 AND new_reader_weight <=10000000) NOT NULL DEFAULT 1,
    add_lag_ms INT NOT NULL DEFAULT 30,
    min_lag_ms INT NOT NULL DEFAULT 30,
    lag_num_checks INT NOT NULL DEFAULT 1,
    comment VARCHAR,
    UNIQUE (reader_hostgroup)
)

The fields have the following semantics:

  • writer_hostgroup - the hostgroup where the Aurora writer instance is placed. ProxySQL will route write traffic to this hostgroup.
  • reader_hostgroup - the hostgroup where Aurora reader instances are placed. Read traffic should be directed here via query rules or a read-only user.
  • active - when set to 1, ProxySQL actively monitors the Aurora cluster and adjusts the hostgroup membership based on topology discovery. Set to 0 to disable Aurora monitoring for this cluster.
  • aurora_port - the TCP port used when connecting to Aurora instances. Defaults to 3306.
  • domain_name - the Aurora cluster DNS domain suffix used to resolve instance hostnames during topology discovery.
  • max_lag_ms - the maximum acceptable replication lag in milliseconds for a reader to remain in the reader_hostgroup. Readers exceeding this threshold are temporarily shunned.
  • check_interval_ms - how frequently (in milliseconds) ProxySQL polls Aurora’s topology endpoint to discover cluster changes.
  • check_timeout_ms - the maximum time (in milliseconds) ProxySQL waits for a topology check response before treating it as a failure.
  • writer_is_also_reader - if set to 1, the current writer instance is also added to the reader_hostgroup, making it eligible to serve read traffic.
  • new_reader_weight - the weight assigned to newly discovered reader instances when ProxySQL places them into the reader_hostgroup automatically.
  • add_lag_ms - additional buffer added to the measured lag when evaluating max_lag_ms thresholds.
  • min_lag_ms - the minimum lag value considered valid. Measurements below this value are treated as 0.
  • lag_num_checks - the number of consecutive lag checks that must exceed max_lag_ms before a reader is shunned.
  • comment - free form text field for any user-defined purpose.

mysql_collations

Table mysql_collations is a representation of all the known and available (charset, collation) pairs supported by ProxySQL.

CREATE TABLE mysql_collations (
    Id INTEGER NOT NULL PRIMARY KEY,
    Collation VARCHAR NOT NULL,
    Charset VARCHAR NOT NULL,
    `Default` VARCHAR NOT NULL
)

mysql_firewall_whitelist_rules

Table mysql_firewall_whitelist_rules defines the per-user, per-schema query whitelist for the ProxySQL firewall module. Only queries whose digest matches an active whitelist rule are permitted when firewall enforcement is active for the given user.

CREATE TABLE mysql_firewall_whitelist_rules (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    username VARCHAR NOT NULL,
    client_addr VARCHAR NOT NULL,
    schemaname VARCHAR NOT NULL,
    flagIN INT NOT NULL DEFAULT 0,
    digest VARCHAR NOT NULL,
    match_digest VARCHAR NOT NULL DEFAULT '',
    error_msg VARCHAR NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, client_addr, schemaname, flagIN, digest)
)

The fields have the following semantics:

  • active - only rules with active=1 are loaded into the runtime firewall and evaluated against incoming queries.
  • username - the MySQL username this rule applies to. The rule is evaluated only when the connecting user matches this value.
  • client_addr - the client IP address this rule applies to. Use an empty string to match all client addresses for the given username.
  • schemaname - the default schema in use when this rule is evaluated. Use an empty string to match any schema.
  • flagIN - correlates with the flagIN/flagOUT chaining mechanism from mysql_query_rules, allowing the firewall to integrate with multi-stage rule evaluation.
  • digest - the exact query digest that is whitelisted. Digests are the same values recorded in stats_mysql_query_digest. This is the primary matching criterion.
  • match_digest - an optional regular expression applied against the query digest as a secondary filter. Leave empty to rely solely on the digest column.
  • error_msg - when set, this message is returned to the client when a query is blocked by the firewall, overriding the default error response.
  • comment - free form text field for any user-defined purpose.

mysql_firewall_whitelist_sqli_fingerprints

Table mysql_firewall_whitelist_sqli_fingerprints holds SQL injection fingerprints used by the ProxySQL firewall to detect and block injection attacks. When a query’s fingerprint matches an entry in this table and the relevant user is in DETECTING or PROTECTING mode, ProxySQL will log or block the query.

CREATE TABLE mysql_firewall_whitelist_sqli_fingerprints (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    fingerprint VARCHAR NOT NULL,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (fingerprint)
)

The fields have the following semantics:

  • active - only fingerprints with active=1 are loaded into the runtime firewall and used for SQLi detection.
  • fingerprint - the normalized query fingerprint that represents a known SQL injection pattern. ProxySQL compares incoming query fingerprints against this table during firewall evaluation.
  • comment - free form text field for any user-defined purpose, such as the CVE or attack pattern name associated with the fingerprint.

mysql_firewall_whitelist_users

Table mysql_firewall_whitelist_users controls which users are subject to firewall enforcement and in what mode. This table is consulted first when a query arrives: if the connecting user is not listed here, the firewall does not apply.

CREATE TABLE mysql_firewall_whitelist_users (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    username VARCHAR NOT NULL,
    client_addr VARCHAR NOT NULL,
    mode VARCHAR CHECK (UPPER(mode) IN ('OFF','DETECTING','PROTECTING')) NOT NULL DEFAULT 'OFF',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, client_addr)
)

The fields have the following semantics:

  • active - only entries with active=1 are loaded into the runtime firewall.
  • username - the MySQL username to which this firewall mode applies.
  • client_addr - the client IP address for which this entry applies. Use an empty string to match all client addresses for the given username.
  • mode - the firewall enforcement mode for this user:
    • OFF - the firewall is disabled for this user; all queries pass through unfiltered.
    • DETECTING - the firewall evaluates queries against the whitelist and SQLi fingerprints and logs violations, but does not block traffic.
    • PROTECTING - the firewall evaluates queries and actively blocks any query that is not matched by an active whitelist rule, or that matches a known SQLi fingerprint.
  • comment - free form text field for any user-defined purpose.

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:

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)
)

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=0 in MySQL will be assigned to this hostgroup.
  • backup_writer_hostgroup - if the cluster has multiple nodes with read_only=0 and their count exceeds max_writers, ProxySQL will put the additional nodes in the backup_writer_hostgroup.
  • reader_hostgroup - healthy nodes that have read_only=1 will be assigned to this hostgroup.
  • offline_hostgroup - unhealthy nodes are moved to offline_hostgroup until they become healthy again.
  • 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 the backup_writer_hostgroup.
  • writer_is_also_reader - determines if a node should be added to the reader_hostgroup as well as the writer_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.
  • 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.

For further information on how to configure Galera Cluster / Percona XtraDB Cluster, see the Galera Configuration documentation.

mysql_group_replication_hostgroups

Table mysql_group_replication_hostgroups defines hostgroups for use with Oracle Group Replication / InnoDB Cluster

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)
)

The fields have the following semantics:

  • writer_hostgroup - the hostgroup that all traffic will be sent to by default. Nodes that have read_only=0 in MySQL will be assigned to this hostgroup.
  • backup_writer_hostgroup - if the cluster has multiple nodes with read_only=0 that exceed max_writers, ProxySQL will put the additional nodes (in excess of max_writers) in the backup_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 have read_only=1 will be assigned to this hostgroup.
  • offline_hostgroup - when ProxySQL’s monitoring determines a node is OFFLINE or unhealthy, it will be put into the offline_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 the writer_hostgroup. Nodes in excess of this value will be put into the backup_writer_hostgroup.
  • writer_is_also_reader - determines if a node should be added to the reader_hostgroup as well as the writer_hostgroup. The special value writer_is_also_reader=2 signals that only the nodes in backup_writer_hostgroup are also in reader_hostgroup, excluding the node(s) in the writer_hostgroup.
  • max_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 the transactions_behind field of the sys.gr_member_routing_candidate_status table 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 the mysql-monitor-variables documentation.

mysql_hostgroup_attributes

Table mysql_hostgroup_attributes defines hostgroup-specific settings that override global configuration for the specific hostgroup. Only available from ProxySQL v2.5+

CREATE TABLE mysql_hostgroup_attributes (
    hostgroup_id INT NOT NULL PRIMARY KEY,
    max_num_online_servers INT CHECK (max_num_online_servers>=0 AND max_num_online_servers <= 1000000) NOT NULL DEFAULT 1000000,
    autocommit INT CHECK (autocommit IN (-1, 0, 1)) NOT NULL DEFAULT -1,
    free_connections_pct INT CHECK (free_connections_pct >= 0 AND free_connections_pct <= 100) NOT NULL DEFAULT 10,
    init_connect VARCHAR NOT NULL DEFAULT '',
    multiplex INT CHECK (multiplex IN (0, 1)) NOT NULL DEFAULT 1,
    connection_warming INT CHECK (connection_warming IN (0, 1)) NOT NULL DEFAULT 0,
    throttle_connections_per_sec INT CHECK (throttle_connections_per_sec >= 1 AND throttle_connections_per_sec <= 1000000) NOT NULL DEFAULT 1000000,
    ignore_session_variables VARCHAR CHECK (JSON_VALID(ignore_session_variables) OR ignore_session_variables = '') NOT NULL DEFAULT '',
    hostgroup_settings VARCHAR CHECK (JSON_VALID(hostgroup_settings) OR hostgroup_settings = '') NOT NULL DEFAULT '',
    servers_defaults VARCHAR CHECK (JSON_VALID(servers_defaults) OR servers_defaults = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT ''
)

The fields have the following semantics:

  • hostgroup_id - the hostgroup to which the settings specified in the below columns will be applied.
  • max_num_online_servers - when the number of ONLINE servers specified is exceeded, no new connections are accepted into the hostgroup. Traffic will continue to be denied until the number of ONLINE servers goes below the threshold. This is a safeguard mechanism that can help with invalid configurations, like for example, a misconfigured READER being placed in the WRITER hostgroup.
  • autocommit - not implemented yet.
  • free_connections_pct - the percentage of open idle connections from the total maximum number of connections for a specific server in a hostgroup. For more information on the related global variable, see mysql-free_connections_pct.
  • init_connect - string containing one or more SQL statements, separated by semicolons, that will be executed by the ProxySQL for each backend connection in the specific hostgroup when created or initialised. For more information on the related global variable, see mysql-init_connect.
  • multiplex - per hostgroup value that either enables or disables multiplexing for the specific hostgroup. For more information on the related global variable, see mysql-multiplexing.
  • connection_warming - per hostgroup value that controls whether ProxySQL will keep opening new connections in the specific hostgroup until the expected number of warm connections is reached. For more information on the related global variable, see mysql-connection_warming.
  • throttle_connections_per_sec - determines the maximum number of new connections that can be opened per second for the specific hostgroup. For more information on the related global variable, see mysql-throttle_connections_per_sec_to_hostgroup.
  • ignore_session_variables - not implemented yet.
  • hostgroup_settings - allows to specify settings for the hostgroup. If the setting is also globally available, a hostgroup setting will override global configuration. Supported values:
    • handle_warnings: Analogous to global variable mysql-handle_warnings. Supports the same values as the global setting.
    • monitor_slave_lag_when_null: Analogous to global variable mysql-monitor_slave_lag_when_null. Supports the same values as the global setting.
  • servers_defaults - allows to specify default values for servers when discovered and placed in the hostgroup by ProxySQL Monitoring module. Currently supported values are: weight, max_connections, use_ssl.
  • comment - text field that can be used for any purpose defined by the user.

The table is useful for several scenarios:

  1. When there are differences in either the version of the backend servers in different hostgroups.
  2. When different behaviors are expected from different hostgroups.
  3. When working with Aurora clusters or Group Replication clusters in which autodiscovery is expected.

As an example of the first scenario, one could have a hostgroup that consists of MySQL 5.7 backend servers, while a different one has MySQL 8.0 backends configured (or even different database servers, i.e. MySQL in one hostgroup, TiDB in another). In this case, it is possible to set different types of SQL statements to be executed on the backends of different hostgroups by tuning the init_connect field of the mysql_hostgroup_attributes table so that ProxySQL sets different variables for different hostgroups when initializing the connection to them.

As an example of the second scenario, you might want to have a different number of new connections that can be opened for different hostgroups based on your workload on a per hostgroup basis (throttle_connections_per_sec), as well as, for example, calculating a different percentage of idle connections being kept open on backend servers on a per hostgroup basis (free_connections_pct). As per the global variable’s documentation: “For each hostgroup/backend pair, the Hostgroup Manager will keep in the connection pool up to mysql-free_connections_pct * mysql_servers.max_connections / 100 connections”.

As an example of the third scenario, we have ProxySQL working with an AWS Aurora cluster, and we have auto scaling for our replicas. We would like ProxySQL to place these replicas in the proper hostgroup, but we would also like being able to configure these replicas, not simply having the default values from mysql_servers, we can achieve this via servers_defaults. E.g:

Admin> UPDATE mysql_hostgroup_attributes SET servers_defaults='{"weight":100,"max_connections":500,"use_ssl":1}' WHERE hostgroup_id=100;
Query OK, 0 rows affected (0.00 sec)

Now replicas being discovered and placed in hostgroup 100 will inherit the supplied defaults.

mysql_query_rules

Table mysql_query_rules defines routing policies and attributes.

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
)

The fields have the following semantics:

  • rule_id - the unique id of the rule. Rules are processed in rule_id order.
  • active - only rules with active=1 will be considered by the query processing module and only active rules are loaded into the runtime data structures.
  • username - filtering criteria matching username. If it’s non-NULL, a query will match only if the connection is made with the correct username.
  • schemaname - filtering criteria matching schemaname. If it’s non-NULL, a query will match only if the connection uses schemaname as its default schema.
  • flagIN, flagOUT, apply - these allow us to create “chains of rules” that get applied in order. An initial flag value of 0 is assumed. An incoming query will be tested against all rules with flagIN=0. If a rule matches and flagOUT is set, the query will be re-evaluated against rules with flagIN equal to the value of flagOUT. If a rule matches and apply=1, the query is immediately executed. If no rule matches at all, then the destination_hostgroup or default_hostgroup is used.
  • client_addr - match traffic from a specific source
  • proxy_addr - match incoming traffic on a specific local IP
  • proxy_port - match incoming traffic on a specific local port
  • digest - match queries with a specific digest, as returned by stats_mysql_query_digest.
  • match_digest - regular expression that matches the query digest.
  • match_pattern - regular expression that matches the query text.
  • negate_match_pattern - if this is set to 1, only queries not matching the query text will be considered.
  • re_modifiers - comma separated list of options to modify the behavior of the RE engine. With CASELESS the match is case insensitive. With GLOBAL the replace is global (replaces all occurrences of the match pattern, not just the first one).
  • replace_pattern - this is the pattern with which to replace the matched pattern. It’s done using RE2::Replace, so the substitution and the use of capture groups are possible. Note that when replace_pattern is set, match_pattern must also be set.
  • destination_hostgroup - route matched queries to this hostgroup. This happens unless there is a started transaction and the transaction_persistent flag is set for the current user.
  • cache_ttl - the number of milliseconds for which to cache the result of the query. Note: in ProxySQL 1.1.x, cache_ttl was in seconds.
  • cache_empty_result - controls if a resultset without rows will be cached or not.
  • cache_timeout - ToDo
  • reconnect - feature not used
  • timeout - the maximum timeout in milliseconds with which the matched or rewritten query should be executed. If a query runs for longer than the specified timeout, the query is automatically killed.
  • retries - 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, the global variable mysql-query_retries_on_failure applies.
  • delay - number of milliseconds to delay the execution of the query. This is essentially a throttling mechanism and QoS, and could be used to give priority to other queries.
  • mirror_flagOUT and mirror_hostgroup - settings related to mirroring.
  • error_msg - query will be blocked, and the specified error_msg will be returned to the client.
  • OK_msg - the specified message will be returned for a query that uses the defined rule.
  • sticky_conn - not implemented yet
  • multiplex - If 0, multiplex will be disabled. If 1, multiplex could be re-enabled if there aren’t any other conditions preventing it. If 2, multiplexing is disabled for the life of the session.
  • gtid_from_hostgroup - defines which hostgroup should be used as the leader for GTID consistency.
  • log - this column can have three values: 1 - matched query will be recorded into the events log; 0 - matched query will not be recorded; NULL - the global configuration applies.
  • apply - when set to 1 no further queries will be evaluated after this rule is matched and processed.
  • attributes - JSON field to specify load balancing via query rules between hostgroups.
  • comment - free form text field, usable for a descriptive comment of the query rule.

Examples for load balancing with the help of mysql_query_rules:

  1. In case you’d like to send specific amounts of traffic coming on a specific port to different hostgroups, you would use the proxy_port field along with the attributes field to achieve this.
  2. In case you’d like to test a specific query rule for a certain amount of your traffic, you can send only 1% of the incoming traffic matching the rule to a specific hostgroup using the attributes field.

A general point to keep in mind: it is important NOT to set either the destination_hostgroup or apply fields in the first rule that captures all incoming traffic, as this will prevent any other rules from being evaluated.

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.

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)
)

The fields have the following semantics:

  • username - filtering criteria matching username; a query will match only if the connection is made with the correct username.
  • schemaname - filtering criteria matching schemaname; a query will match only if the connection uses schemaname as its default schema.
  • flagIN - evaluated in the same way as flagIN is in mysql_query_rules and correlates to the flagOUT of mysql_query_rules.
  • destination_hostgroup - route matched queries to this hostgroup. This happens unless there is a started transaction and the transaction_persistent flag is set for the current user.
  • comment - free form text field, usable for a descriptive comment of the query rule.

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.

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)
)

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. Please note, however, that these nodes might not be the only ones assigned to this hostgroup. Regarding this behavior, see also the monitor variable [mysql-monitor_writer_is_also_reader][2].
  • check_type - the MySQL variable(s) checked when executing a Read Only check, and optionally the logical binary operation. read_only is the default. innodb_read_only and super_read_only can be used as well. Before the introduction of Native Support for AWS Aurora, innodb_read_only should be used. Checks on read_only and innodb_read_only can be combined.
  • 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_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:

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 port is 0, the value in hostname is interpreted as a Unix Socket file

  • gtid_port: the backend server port where ProxySQL Binlog Reader listens on for GTID tracking

  • status: the configured status of the backend. This does not represent the current status, but the configured one:

    • ONLINE - backend server is fully operational
    • SHUNNED - backend server 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 free connections are immediately dropped, while backend connections currently associated with a client session are dropped as soon as the client tries to use them. 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_ms it 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.

Note: in order for a SHUNNED node to be recognized as ONLINE again, it is not enough that the node is reachable (responding to a ping) — the node is brought back ONLINE only if there is also activity in the connection pool for the specific hostgroup in which the node is configured.

mysql_servers_ssl_params

Table mysql_servers_ssl_params was introduced in ProxySQL 2.6. Table mysql_servers_ssl_params defines SSL parameters that are specific for each backend MySQL server. The table is defined as following:

CREATE TABLE mysql_servers_ssl_params (
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    username VARCHAR NOT NULL DEFAULT '',
    ssl_ca VARCHAR NOT NULL DEFAULT '',
    ssl_cert VARCHAR NOT NULL DEFAULT '',
    ssl_key VARCHAR NOT NULL DEFAULT '',
    ssl_capath VARCHAR NOT NULL DEFAULT '',
    ssl_crl VARCHAR NOT NULL DEFAULT '',
    ssl_crlpath VARCHAR NOT NULL DEFAULT '',
    ssl_cipher VARCHAR NOT NULL DEFAULT '',
    tls_version VARCHAR NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, port, username) )
1 row in set (0.01 sec)

The fields have the following semantics:

  • hostname, port: the TCP endpoint at which the backend server can be reached. If port is 0, the value in hostname is interpreted as a Unix Socket file
  • username: if not empty, this configuration row applies only for backend connections using the specific username
  • ssl_ca: The path name of the Certificate Authority (CA) certificate file
  • ssl_cert: The path name of the client public key certificate file.
  • ssl_key: The path name of the client private key file
  • ssl_capath: The path name of a directory of CA certificate files
  • ssl_crl: The path name of the file containing certificate revocation lists
  • ssl_crlpath: The path name of a directory of certificate revocation-list files
  • ssl_cipher: currently unused
  • tls_version: currently unused
  • comment: free format comment, not a parameter for connection

When creating a new MySQL backend connection, if mysql_servers.use_ssl is set to 1 then an SSL connection is established. Before establishing an SSL connection to the backend, ProxySQL will verify if the backend is configured in mysql_servers_ssl_params, and if so it will use the settings specified in the table mysql_servers_ssl_params. To be more specific, up to two lookups against mysql_servers_ssl_params can be performed:

  • lookup for hostname + port + username. If not present:
  • lookup for hostname + port

If the above lookups return any result, then the SSL parameters specified in mysql_servers_ssl_params will be used. If the above lookups do not return any result, then the default SSL parameters will be used:

  • mysql-ssl_p2s_ca
  • mysql-ssl_p2s_capath
  • mysql-ssl_p2s_cert
  • mysql-ssl_p2s_key
  • mysql-ssl_p2s_crl
  • mysql-ssl_p2s_crlpath

Note that most of the columns/settings accept an empty string as value: in that case, the specific setting is not used when establishing an SSL connection. The configuration in table mysql_servers_ssl_params belongs to the “MYSQL SERVERS” module, therefore it is:

  • Loaded to runtime using LOAD MYSQL SERVERS TO RUNTIME or equivalent command
  • Saved to disk using SAVE MYSQL SERVERS TO DISK or equivalent command
  • Automatically replicated in ProxySQL Cluster as part of the synchronization of mysql_servers module. Note: only the rows in table mysql_servers_ssl_params are synchronized, and the actual files are not copied (they must be already present in the system)

Trying to create a new connection to the backend will fail if ProxySQL is not able to open the file(s) specified in mysql_servers_ssl_params. The most common reasons for failure are:

  • Not existing file
  • Incorrect path
  • Wrong permission
  • Invalid certificate

mysql_users

Table mysql_users defines MySQL users that clients can use to connect to ProxySQL, and then used to connect to backends.

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 1,
    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)
)

The fields have the following semantics:

  • username, password - credentials for connecting to the mysqld or ProxySQL instance
  • active - the users with active = 0 will be tracked in the database, but will never be loaded in the in-memory data structures. active = 0 is equivalent to deleting the user from the in-memory data structures.
  • use_ssl - if set to 1, the user is forced to authenticate using an SSL certificate.
  • default_hostgroup - if there is no matching rule for the queries sent by this user, the traffic it generates is sent to the specified hostgroup
  • default_schema - the schema to which the connection should change by default
  • schema_locked - not supported yet.
  • transaction_persistent - if this is set for the user with which the MySQL client is connecting to ProxySQL, then any started transaction within a hostgroup will remain within the same hostgroup regardless of any other rules.
  • fast_forward - if set it bypasses the query processing layer (rewriting, caching) and passes through the query directly to the backend server. This option is equivalent to using MySQL Proxy, and it’s used mainly for debugging. The following clarifications should be considered:
    • It doesn’t require a different port: Full features proxy logic and “fast forward” logic is implemented in the same code path.
    • Fast forward is implemented on a per-user basis: Depending on the user that connects to ProxySQL, fast forward is enabled or not.
    • Fast forward algorithm is enabled after authentication: the client still authenticates to ProxySQL, and ProxySQL will first verify the credentials locally.
    • Fast forward supports SSL since version v2.4.6.
    • If using compression, it must be enabled on both ends.
  • frontend - if set to 1, this (username, password) pair is used for authenticating to the ProxySQL instance
  • backend - if set to 1, this (username, password) pair is used for authenticating to the mysqld servers
  • max_connections - defines the maximum number of allowable frontend connections for a specific user.
  • attributes - JSON field encoding extra configuration parameters for the user:
    • default-transaction_isolation: Imposes a default transaction isolation in the backend connections for this user.
    • additional_password: Allows to specify a secondary password for a user, this is particularly useful for password rotation.
  • comment - text field that can be used for any purpose defined by the user. Could be a description of what the user does or other relevant information.

Note, currently all users need both “frontend” and “backend” set to 1. Future versions of ProxySQL will separate the two.

Note: users in mysql_users shouldn’t be used also for admin-admin_credentials and admin-stats_credentials.