Listing all tables

Key Configuration Tables

mysql_servers

Table mysql_servers defines all the MySQL servers:

The fields have the following semantics:

  • hostgroup_id: the hostgroup in which this mysqld instance is included. Notice that the same instance can be part of more than one hostgroup
  • hostname, port: the TCP endpoint at which the mysqld instance can be contacted
  • gtid_port: the backend server port where ProxySQL Binlog Reader listens on for GTID tracking
  • status:
    • 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, new incoming connections aren’t accepted anymore, while the existing connections are kept until they become inactive. In other words, connections are kept in use until the current transaction is completed. This makes it possible to gracefully detach a backend
    • OFFLINE_HARD – when a server is put into OFFLINE_HARD mode, the existing connections are dropped, while new incoming connections aren’t accepted either. This is equivalent to deleting the server from a hostgroup, or temporarily taking it out of the hostgroup for maintenance work
  • 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
  • compression – if the value is greater than 0, new connections to that server will use 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
  • max_replication_lag – if greater than 0, ProxySQL will regularly monitor replication lag and if it goes beyond such threshold it will temporary shun the host until replication catches up
  • use_ssl – if set to 1, connections to the backend will use SSL
  • 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 checker 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.

Each row in mysql_replication_hostgroups represents a pair of writer_hostgroup and reader_hostgroup .
ProxySQL will monitor the value of read_only for all the servers in specified hostgroups, and based on the value of read_only 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 that all traffic will be sent to by default, nodes that have read_only=0 in MySQL will be assigned to this 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.
  • check_type – the MySQL variable checked when executing a Read Only check, read_only by default (alternatively super_read_only can be used as well). For AWS Aurora innodb_read_only should be used.
  • 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_group_replication_hostgroups

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

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 and max_writers, ProxySQL will put the additional nodes (in excess of max_writes) 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 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 thewriter_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_hostgroup as well as the writer_hostgroup after it is promoted.
  • 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.

mysql_galera_hostgroups

Table mysql_galera_hostgroups (available in ProxySQL 2.x and higher) defines hostgroups for use with Galera Cluster / Percona XtraDB Cluster.

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 and max_writers, ProxySQL will put the additional nodes (in excess of max_writes) 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 host is OFFLINE it will be put into the offline_hostgroup
  • active - when enabled, ProxySQL monitors the hostgroups and moves servers 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_hostgroup
  • writer_is_also_reader – determines if a node should be added to the reader_hostgroup as well as the writer_hostgroup after it is promoted.
  • 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_queue Galera 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, used to connect to backends.

The fields have the following semantics:

  • username, password – credentials for connecting to the mysqld or ProxySQL instance. 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
  • 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 (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 rules
  • fast_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 instance
  • backend – if set to 1, this (username, password) pair is used for authenticating to the mysqld servers against any hostgroup
  • max_connections – defines the maximum number of allowable frontend connections for a specific user.
  • 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.

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.

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 runtime.
  • 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 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 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.digest
  • match_digest – regular expression that matches the query digest. See also mysql-query_processor_regex
  • match_pattern – regular expression that matches the query text. See also mysql-query_processor_regex
  • negate_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 against match_pattern or match_digest
  • 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 matches and not just the first). For backward compatibility, only CASELESS is 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 (see mysql_users table).
  • cache_ttl – the number of milliseconds for which to cache the result of the query. Note: in ProxySQL 1.1 cache_ttl was in seconds
  • cache_empty_result – controls if resultset without rows will be cached or not
  • 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 specific threshold, the query is automatically killed. If timeout is not specified, global variable mysql-default_query_timeout applies
  • 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, 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, allowing to give priority to some queries instead of others. This value is added to the mysql-default_query_delay global variable that applies to all queries. Future versions of ProxySQL will provide a more advanced throttling mechanism.
  • mirror_flagOUT and mirror_hostgroup – setting 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 this (like user variables or transactions). If 2, multiplexing is not disabled for just the current query. See wiki Default is NULL, thus not modifying multiplexing policies
  • gtid_from_hostgroup – defines which hostgroup should be used as the leader for GTID consistent reads (typically the defined WRITER hostgroup in 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 to 1 when rule is applied (apply=1) or after processing all query rules and its log attribute is set to 1
  • apply – when set to 1 no further queries will be evaluated after this rule is matched and processed (note: mysql_query_rules_fast_routing rules will not be evaluated afterwards)
  • comment – 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+).

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 default schema (in mariadb/mysql schemaname this is equivalent to databasename)
  • flagIN – evaluated in the same way as flagin is in mysql_query_rules and correlates to the flagout / apply specified in the mysql_query_rules table
  • 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 (see mysql_users table)
  • 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

For reference, an example of how global_variables looks at the time of writing (version 1.2.4):

scheduler

Table scheduler defines jobs to be executed at regular intervals.

Further details about the scheduler can be found here

mysql_collations

Here is the statement used to create the mysql_collations table:

The available (charset, collation) pairs supported by ProxySQL. In principle, ProxySQL will validate that incoming connections have a supported charset, and will make sure that the pooled backend connections are switched to the correct charset before using them.

Runtime tables

All the configuration tables listed above have a matching runtime_ table:

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.

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

then grant select on sys.* to monitoring_user