-
Notifications
You must be signed in to change notification settings - Fork 986
Main (runtime)
Nick edited this page Dec 11, 2017
·
33 revisions
Admin> SHOW TABLES FROM main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
18 rows in set (0.00 sec)Table mysql_servers defines all the MySQL servers.
Admin> SHOW CREATE TABLE mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) 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.00 sec)Table mysql_replication_hostsgroups defines replication hostgroups.
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),
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)Each row in mysql_replication_hostgroups represent 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 will assign the server to the writer or reader hostgroups.
The field comment can be used to store any arbitrary data.
Table mysql_users defines MySQL users, 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,
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. See also Passwords management -
active- the users with active = 0 will be tracked in the database, but will be never loaded in the in-memory data structures -
default_hostgroup- if there is no matching rule for the queries send by this users, the traffic is 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 bypass the query processing layer (rewriting, caching) and pass 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
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 connection 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 from 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 connections' errors are still handled during connect phase.
Note: users in mysql_users shouldn't be used also for admin-admin_credentials and admin-stats_credentials
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 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,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
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 order -
active- only rules with active=1 will be considered by the query processing module -
username- filtering criteria matching username. If is non-NULL, a query will match only if the connection is made with the correct username -
schemaname- filtering criteria matching schemaname. If is 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 enters a new chain of rules having flagIN as the new input flag. If flagOUT matches flagIN, the query will be re-evaluate 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 bystats_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 againstmatch_patternormatch_digest -
re_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 the 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 seconds -
reconnect- feature not used -
timeout- the maximum timeout in milliseconds with which the matched or rewritten query should be executed. If a query run for longer than the specific threshold, the query is automatically killed. If timeout is not specified, global variablemysql-default_query_timeoutapplies -
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 variablemysql-query_retries_on_failureapplies -
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 themysql-default_query_delayglobal variable that applies to all queries. Future version 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 client -
sticky_conn- not implemented yet -
multiplex- If 0, multiplex will be disabled. If 1, multiplex could be re-enabled if there are is not 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 policies -
log- query will be logged -
comment- free form text field, usable for a descriptive comment of the query rule
Table global_variables defines Global variables.
Currently there are two types of global variables with different scopes:
- variables prefixed with
admin-are relevant for Admin module - variables prefixed with
mysql-are relevant for MySQL modules, specifically they can tune:- how to handle MySQL traffic
- how Monitor operates (further prefixed with
mysql-monitor_) - query cache
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-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-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-version | 1.2.4.0923 |
| mysql-client_found_rows | true |
| mysql-commands_stats | true |
| mysql-connect_retries_delay | 1 |
| mysql-connect_retries_on_failure | 5 |
| mysql-connect_timeout_server | 10000 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-connection_max_age_ms | 0 |
| mysql-default_charset | utf8 |
| mysql-default_max_latency_ms | 1000 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 10000 |
| mysql-default_reconnect | true |
| mysql-default_schema | information_schema |
| mysql-enforce_autocommit_on_reads | false |
| mysql-eventslog_filename | file1.log |
| mysql-eventslog_filesize | 104857600 |
| mysql-free_connections_pct | 100 |
| mysql-have_compress | true |
| mysql-init_connect | (null) |
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-long_query_time | 1000 |
| mysql-max_allowed_packet | 4194304 |
| mysql-max_connections | 2048 |
| mysql-max_transaction_time | 14400000 |
| mysql-monitor_connect_interval | 200000 |
| mysql-monitor_connect_timeout | 500 |
| mysql-monitor_enabled | true |
| mysql-monitor_history | 60000 |
| mysql-monitor_password | monitor |
| mysql-monitor_ping_interval | 200000 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 500 |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_read_only_interval | 1000 |
| mysql-monitor_read_only_timeout | 100 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_username | monitor |
| mysql-monitor_writer_is_also_reader | true |
| mysql-multiplexing | true |
| mysql-ping_interval_server_msec | 10000 |
| mysql-ping_timeout_server | 200 |
| mysql-poll_timeout | 2000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-query_cache_size_MB | 512 |
| mysql-query_digests | true |
| mysql-query_processor_iterations | 0 |
| mysql-query_retries_on_failure | 1 |
| mysql-server_capabilities | 47626 |
| mysql-server_version | 5.5.30 |
| mysql-servers_stats | true |
| mysql-session_debug | (null) |
| mysql-sessions_sort | true |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-ssl_p2s_ca | /home/vagrant/sandboxes/rsandbox_mysql-5_6_26/master/ca.pem |
| mysql-ssl_p2s_cert | /home/vagrant/newcerts/client-cert.pem |
| mysql-ssl_p2s_cipher | (null) |
| mysql-ssl_p2s_key | /home/vagrant/newcerts/client-key.pem |
| mysql-stacksize | 1048576 |
| mysql-threads | 1 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-wait_timeout | 28800000 |
+----------------------------------------+-------------------------------------------------------------+
75 rows in set (0.01 sec)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
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_hostsgroups
- runtime_mysql_servers : runtime version of mysql_servers
- runtime_mysql_users : runtime version of mysql_users
- runtime_scheduler : runtime version of scheduler