Exploring the latest features in ProxySQL 2.0 (updated to 2.0.9) - FOSDEM 2020

This presentation was given by Nick Vyzas, ProxySQL's Senior Member of Technical Staff at FOSDEM 2020.

The talk abstract is as follows:

"ProxySQL, the high performance, high availability, protocol-aware proxy for MySQL is now GA in version 2.0. This version introduces several new features, like causal reads using GTID, better support for AWS Aurora, native support for Galera Cluster, LDAP authentication and SSL for client connections.

This session provides an overview of the most important new features.

Slide agenda:

Supported OSes & Packaging
Query Cache Tunables
GTID Causal Reads
Native Galera Support
Amazon Aurora Features
LDAP Integration
SSL, Audit Log & Security
JSON Support
Performance Enhancements
MySQL 8"

Transcript

1. Whats new in ProxySQL 2.0 by Nick Vyzas
2. 2 ProxySQL LLC We provide services to help build, support as well as improve the performance & reliability of your Cloud-Based or On-Premise MySQL infrastructure: • ProxySQL Development • Remote DBRE Consulting • ProxySQL Support Services
3. 3 We’re hiring!!! • Experience coding in C/C++? • MySQL DBA / Development? • DevOps / Automation? • Working remotely?
4. 4 Companies using ProxySQL
5. 5 Talk Overview • Performance Enhancements • GTID Causal Reads • Native Galera Support • Percona XtraDB Cluster “pxc_maint_mode” • Amazon Aurora Features • LDAP Integration • SSL, Audit Log & Security
6. 6 Talk Overview • General log JSON format • JSON Support in Admin • Firewall whitelist • SQL injection detection • MySQL 8
7. Performance Enhancements What’s been done to make ProxySQL 2.0 faster than ever before?
8. 8 Upgraded Dependencies Several of the core dependencies have been upgraded, including: • MariaDB Connector C upgraded to 3.0.2 in 2.0.1 • MariaDB Connector C upgraded to 3.1.4 in 2.0.9 • jemalloc upgraded to 5.2.0 • SQLite upgraded to 3.19.2 • libconfig upgraded to 1.7.2
9. 9 Performance Improvements • Improved the performance of MySQL_Thread::refresh_variables() • Several optimizations in the code that generate a variety of stats tables • Multiple improvements in the Monitor module • Traffic handled when fast_forward=1 is now considerably faster • Added support for mysql-keep_multiplexing_variables to define additional MySQL system vars to keep multiplexing enabled
10. 10 Memory Optimizations • Automatic VACUUM of stats schema • When the new global variable mysql-query_digests_normalize_digest_text is enabled, queries statistics store digest_text on a different hash table. This drastically reduces memory usage on setups with many schemas but similar queries patterns • Improved memory usage in jemalloc 5.2.0
11. 11 Query Cache Improvements • A new command was added to flush the query cache: PROXYSQL FLUSH QUERY CACHE • Updating mysql_query_rules.cache_ttl now purges cache entries • Additionally, a new setting was added to avoid caching empty resultsets: mysql_query_rules.cache_empty_result
12. GTID Causal Reads Ensuring consistency by tracking GTIDs across a MySQL cluster
13. 13 GTID Consistency in ProxySQL ProxySQL can now be configured to enforce GTID consistency for reads on any hostgroup / replication hostgroup. The hostgroup will ensure that any subsequent DQL: • Will be routed only to hosts which have executed the previous transaction's GTID for the connection • There is always a node available to serve the DQL statement when the PRIMARY host is part of the hostgroup / READER
14. 14 How ProxySQL tracks GTIDs
15. 15 ProxySQL Binlog Reader • A lightweight process that runs on the MySQL server • Primary task is to provide GTID information about a MySQL server to all connected ProxySQL instances • Designed to be robust and efficient, keeping CPU / Network I/O to an absolute minimum and supporting hundreds of instances • Features an auto-restart mechanism in case of failure and a client side reconnect
16. 16 Where can I get Binlog Reader? • ProxySQL Binlog Reader is available in a separate repository • Packages have been built for Debian / Ubuntu and RHEL / CentOS (coming to the repo soon) • Repository URL: https://github.com/sysown/proxysql_mysqlbinlog
17. Native Galera Support Built-in Galera Clustering configuration support
18. 18 What is Galera Replication? • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Multi-threaded Replication • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes
19. 19 Galera ProxySQL Configuration In ProxySQL 2.0 the concept of the regular replication hostgroup is extended, in addition to “reader_hostgroup” and “writer_hostgroup” we also have the following additional concepts and hostgroup types: • max_writers • writer_is_also_reader • max_transactions_behind • backup_writer_hostgroup • offline_hostgroup
20. 20 Galera Replication Hostgroups 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)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR
21. 21 Galera Replication Hostgroups writer_hostgroup - the hostgroup id that will contain writer nodes (RO=0) backup_writer_hostgroup - when multiple writers are active (RO=0 on more than 1x node) but max_writers is defined all nodes that exceed this value are moved to the backup writer group (standby nodes) reader_hostgroup - the hostgroup id that will contain reader nodes (RO=1) offline_hostgroup - when ProxySQL's monitoring determines a node is offline it will be moved to the offline_hostgroup
22. 22 Galera Replication Hostgroups active - ProxySQL monitors the active hostgroups and makes use only of active hostgroups. Facilitates standby configurations e.g. DC failover max_writers - limits the number of nodes allocated to the writer hostgroup writer_is_also_reader - determines if a node in the writer hostgroup will also belong to the reader hostgroup max_transactions_behind - determines the maximum number of writesets behind the node can fall before shunning the node to prevent stale reads (determined by querying the Galera’s `wsrep_local_recv_queue`).
23. 23 Galera Monitoring The check timeouts are controlled in the “global_variables” table’s “xxx_galera_healthcheck_xxx” variables: ProxySQL Admin> select * from global_variables where variable_name like '%monitor_galera%'; +-------------------------------------------+-------------------------+ | variable_name | variable_value | +----------------------------------------------------+----------------+ | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | +=---------------------------------------------------+----------------+
24. 24 Galera Monitoring In ProxySQL 2.0.4 we added a new variable: mysql-monitor_galera_healthcheck_max_timeout_count • The default value is 3. • The goal of this variable is to prevent flapping by specifying a threshold after which ProxySQL will reconfigure the MySQL servers and replication hostgroups in case a check temporarily fails.
25. 25 Percona XtraDB Cluster “pxc_maint_mode” Only available on Percona PXC If pxc_maint_mode is SHUTDOWN or MAINTENANCE , ProxySQL configure the server as OFFLINE_SOFT
26. Amazon Aurora Features Plug and play support for Amazon Aurora cluster
27. 27 Aurora AWS Integration • ProxySQL tracks metrics and status variables from REPLICA_HOST_STATUS • Automatically detects writer/reader roles • Auto-discovery of new replicas • AZ awareness • Replication lag monitoring with millisecond granularity
28. 28 Aurora AWS Integration • New configuration table 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 CHECK (SUBSTR(domain_name,1,1) = '.'), max_lag_ms INT NOT NULL CHECK (max_lag_ms>= 10 AND max_lag_ms <= 600000) DEFAULT 600000, check_interval_ms INT NOT NULL CHECK (check_interval_ms >= 100 AND check_interval_ms <= 600000) DEFAULT 1000, check_timeout_ms INT NOT NULL CHECK (check_timeout_ms >= 80 AND check_timeout_ms <= 3000) 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 CHECK (add_lag_ms >= 0 AND add_lag_ms <= 600000) DEFAULT 30, min_lag_ms INT NOT NULL CHECK (min_lag_ms >= 0 AND min_lag_ms <= 600000) DEFAULT 30, lag_num_checks INT NOT NULL CHECK (lag_num_checks >= 1 AND lag_num_checks <= 16) DEFAULT 1, comment VARCHAR, UNIQUE (reader_hostgroup))
29. 29 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_log mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_log\G *************************** 1. row *************************** table: mysql_server_aws_aurora_log Create Table: CREATE TABLE mysql_server_aws_aurora_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, success_time_us INT DEFAULT 0, error VARCHAR, SERVER_ID VARCHAR NOT NULL DEFAULT '', SESSION_ID VARCHAR, LAST_UPDATE_TIMESTAMP VARCHAR, replica_lag_in_milliseconds INT NOT NULL DEFAULT 0, estimated_lag_ms INT NOT NULL DEFAULT 0, CPU INT NOT NULL DEFAULT 0, PRIMARY KEY (hostname, port, time_start_us, SERVER_ID))
30. 30 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_check_status mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_check_status\G *************************** 1. row *************************** table: mysql_server_aws_aurora_check_status Create Table: CREATE TABLE mysql_server_aws_aurora_check_status ( writer_hostgroup INT NOT NULL, hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, last_checked_at VARCHAR, checks_tot INT NOT NULL DEFAULT 0, checks_ok INT NOT NULL DEFAULT 0, last_error VARCHAR, PRIMARY KEY (writer_hostgroup, hostname, port))
31. 31 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_failovers mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_failovers\G *************************** 1. row *************************** table: mysql_server_aws_aurora_failovers Create Table: CREATE TABLE mysql_server_aws_aurora_failovers ( writer_hostgroup INT NOT NULL, hostname VARCHAR NOT NULL, inserted_at VARCHAR NOT NULL)
32. 32 Routing and replication lag Ability to specify a max lag in the query: SELECT /* max_lag_ms=20 */ ... Not the measure lag, but the estimated.
33. 33 Writer in reader hostgroup mysql_aws_aurora_hostgroups.writer_is_also_reader Replication lag is always 0 Avoid reads to master setting mysql-aurora_max_lag_ms_only_read_from_replicas
34. LDAP Integration Enterprise grade centralised ProxySQL authentication management
35. 35 LDAP implementation • Client connects to ProxySQL using clear text password • If ProxySQL doesn't know the username specified by the client, it will connect to an LDAP server using the same credentials used by the client • If the connection to LDAP server succeeds, ProxySQL knows that the credentials are correct • ProxySQL will retrieve from LDAP all the attributes for the specified user using filter memberOf • A single user could be part of multiple groups so the list of attributes matching filter memberOf will be used to find the best match (higher priority) to determine which mapping backend user needs to be used to connect to MySQL • ProxySQL connects to MySQL using mapped credentials, or original credentials if no mapped credentials are found
36. 36 LDAP implementation • Available as a loadable plugin • When loaded, it creates new configuration variables • LOAD / SAVE LDAP VARIABLES TO / FROM RUNTIME / MEMORY / DISK Admin> SHOW LDAP VARIABLES; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | ldap-bind_dn_prefix | | | ldap-bind_dn_suffix | | | ldap-cache_ttl_sec | 30000 | | ldap-connect_timeout_ms | 1000 | | ldap-root_dn | | | ldap-uri | | | ldap-verbosity | 0 | +-------------------------+-------+
37. SSL, Audit Log & Security New and improved ProxySQL 2.0 security features
38. 38 SSL Support in ProxySQL 2.0 Frontend SSL support is available in ProxySQL 2.0 • ProxySQL 2.0 includes the mariadb-connector-3.0.2 and supports SSLv2, SSLv3, TLSv1.0, TLSv1.1 and TLSv1.2 Additionally, Backend SSL support is extended to include the following in addition to SSL/TLSv1.0 which was supported in ProxySQL 1.4: • TLSv1.1 and TLSv1.2
39. 39 Frontend SSL To enable frontend SSL in ProxySQL 2.0 enable the global variable “mysql-have_ssl=true” • ProxySQL will automatically generate (if not present) the following files in “/var/lib/proxysql/”: ◦ “proxysql-ca.pem” ◦ “proxysql-cert.pem” ◦ “proxysql-key.pem”
40. 40 Frontend SSL CIPHERs The MySQL Compliant CIPHERs are supported: DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA256 DHE-RSA-AES256-SHA DHE-RSA-CAMELLIA256-SHA AES256-GCM-SHA384 AES256-SHA256 AES256-SHA CAMELLIA256-SHA DHE-RSA-AES128-GCM-SHA256 DHE-RSA-AES128-SHA256 DHE-RSA-AES128-SHA DHE-RSA-SEED-SHA DHE-RSA-CAMELLIA128-SHA AES128-GCM-SHA256 AES128-SHA256 AES128-SHA SEED-SHA CAMELLIA128-SHA DES-CBC3-SHA
41. 41 Verifying Frontend SSL To check the SSL CIPHER between the MySQL client and ProxySQL run “\s” and look at the SSL field: mysql -e 'status' | egrep 'SSL:|Connection:' SSL: Cipher in use is DHE-RSA-AES256-SHA Connection: 127.0.0.1 via TCP/IP
42. 42 Verifying Frontend SSL To verify SSL is working as expected connect to ProxySQL via a mysql-client and check the status of “Ssl_cipher”, this shows the SSL CIPHER between ProxySQL and MySQL: ProxySQL Admin> SHOW SESSION STATUS LIKE "Ssl_cipher” +---------------+----------------------+ | Variable_name | Value | +---------------+----------------------+ | Ssl_cipher | ECDHE-RSA-AES256-SHA | +---------------+----------------------+
43. 43 Verifying Frontend SSL SSL can also be verified from the new stats_mysql_processlist.extended_info using JSON . More details later (we can create an example after describing JSON).
44. 44 QPS Impact of SSL
45. 45 Latency Impact of SSL
46. 46 Audit Log Overview ProxySQL 2.0.5 introduces the Audit Log • This feature allows to track certain connection activities. • The Audit Log is disabled by default, to enable define the `mysql-auditlog_filename` • The supported file format is JSON
47. 47 Audit Log Events (MySQL) The following events are logged for the MySQL Module: • Successful authentication • Failed authentication • Graceful disconnect • Closed connection • Change of schema (COM_INIT_DB)
48. 48 Audit Log Events (Admin) The following events are logged for the Admin Module: • Successful authentication • Failed authentication • Graceful disconnect • Closed connection
49. 49 Audit Log Events (Future) Further extensions are expected in the future, specifically: • Support for change of user (COM_CHANGE_USER) on MySQL Module • Support for events on SQLite3 Server Module • Support for events on ClickHouse Module
50. 50 Audit Log Variables • `mysql-auditlog_filename` : the base name of the audit log where audit events are logged. The filename of the log file will be the base name followed by an 8 digit progressive number ◦ The default value is an empty string (``). • `mysql-auditlog_filesize` : the maximum file size of the audit log before the file is rotated ◦ The default value is `104857600` (100MB).
51. 51 Audit Log Format (1/2) • `client_addr` : address (IP:port) of the client connecting to ProxySQL • `proxy_addr` : address (IP:port) of the bind interface where ProxySQL was listening (available only for MySQL module) • `event` : event type (detailed in a later slide) • `time` : human readable time of when the even happened, with milliseconds granularity • `timestamp` : epoch time in milliseconds • `ssl` : boolean value that specify if SSL is being used or not
52. 52 Audit Log Format (2/2) • `schemaname`: the current schema for successful and established connections • `username`: client's username • `thread_id`: the thread_id (session ID) assigned to the client • `creation_time` : when the session was created, information available only when the session is closed • `duration` : time in milliseconds since the session was created, information available only when the session is closed • `extra_info` : attribute that provides additional information. Currently only used to describe in which part of the code the session is closed.
53. 53 Audit Log Event Types (MySQL) • `MySQL_Client_Connect_OK` : successful connection to MySQL Module • `MySQL_Client_Connect_ERR` : failed connection to MySQL Module • `MySQL_Client_Close` : MySQL Session being closed • `MySQL_Client_Quit` : client sending an explicit `COM_QUIT` to MySQL Module • `MySQL_Client_Init_DB` : client sending an explicit `COM_INIT_DB` to MySQL Module
54. 54 Audit Log Event Types (Admin) • `Admin_Connect_OK` : successful connection to Admin Module • `Admin_Connect_ERR` : failed connection to Admin Module • `Admin_Close` : Admin Session being closed • `Admin_Quit` : client sending an explicit `COM_QUIT` to Admin Module
55. 55 Audit Log Examples (admin) {"client_addr": "127.0.0.1:39954", "event": "Admin_Connect_ERR", "schemaname": "", "ssl": false, "thread_id": 2, "time": "2019-05-20 18:48:47.631", "timestamp": 1558342127631, "username": "admin"} A client (`"client_addr":"127.0.0.1:39954"`) with (`"thread_id":2`) failed to connect to Admin (`"event":"Admin_Connect_ERR"`), without using SSL (`"ssl":false`) using username "admin" (`"username":"admin"`).
56. 56 Audit Log Examples (admin) {"client_addr": "127.0.0.1:43266", "event": "Admin_Connect_OK", "schemaname": "main", "ssl": false, "thread_id": 3, "time": "2019-05-20 19:16:53.313", "timestamp": 1558343813313, "username": "admin"} A successful login on Admin module (`"event":"Admin_Connect_OK"`) from `"client_addr":"127.0.0.1:43266"` , without SSL (`"ssl":false`) with username `admin`.
57. 57 Audit Log Examples (admin) {"client_addr": "127.0.0.1:43266", "event": "Admin_Quit", "schemaname": "main", "ssl": false, "thread_id": 3, "time": "2019-05-20 19:16:56.513", "timestamp": 1558343816513, "username": "admin"} The client listed above explicitly sends a `COM_QUIT` command
58. 58 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "event": "MySQL_Client_Connect_OK", "proxy_addr": "0.0.0.0:6033", "schemaname": "mysql", "ssl":false, "thread_id": 4, "time": "2019-05-20 19:20:26.668", "timestamp": 1558344026668, "username": "sbtest"} A client (`"client_addr":"127.0.0.1:40822"`) successfully connected to MySQL module (`"event":"MySQL_Client_Connect_OK"`) on a given bind interface (`"proxy_addr":"0.0.0.0:6033"`) , without SSL (`"ssl":false`).
59. 59 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "event": "MySQL_Client_Init_DB", "proxy_addr": "0.0.0.0:6033", "schemaname": "sbtest", "ssl": false, "thread_id": 4, "time": "2019-05-20 19:20:29.902", "timestamp": 1558344029902, "username": "sbtest"} A few seconds after, the same client issue a `COM_INIT_DB` (`"event":"MySQL_Client_Init_DB"`), switching schemaname (`"schemaname":"sbtest"`) .
60. 60 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "creation_time": "2019-05-20 19:20:26.668", "duration": "9091.966ms", "event": "MySQL_Client_Close", "extra_info": "MySQL_Thread.cpp:3733:process_all_sessions()", "proxy_addr": "0.0.0.0:6033", "schemaname":"sbtest", "ssl": false, "thread_id": 4, "time": "2019-05-20 19:20:35.760","timestamp":1558344035760,"username":"sbtes t"}
61. 61 General log in JSON format Admin> SHOW VARIABLES LIKE '%events%'; +-----------------------------+-----------+ | Variable_name | Value | +-----------------------------+-----------+ | mysql-eventslog_filename | events | | mysql-eventslog_filesize | 104857600 | | mysql-eventslog_default_log | 0 | | mysql-eventslog_format | 1 | +-----------------------------+-----------+ 4 rows in set (0.01 sec)
62. 62 General log in JSON format New variables: • mysql-eventslog_default_log : boolean ◦ Logs all events not explicitly enabled/disabled • mysql-eventslog_format ◦ 1 : binary format, legacy ◦ 2 : JSON format
63. 63 General log in JSON format $ cat events.00000002 {"client":"127.0.0.1:47702","digest":"0x226CD90D52A2BA0B","duration_us":0,"endtime":"2019-12-16 05:15:10.608672","endtime_timestamp_us":1576433710608672,"event":" COM_QUERY","hostgroup_id":-1,"query":" select @@version_comment limit 1","rows_affected":0,"rows_sent":0,"schemaname":"test","starttime":"2019-12-16 05:15:10.608672","starttime_timestamp_us":1576433710608672,"thread_id":5,"username":"sbtest"} {"client":"127.0.0.1:47702","digest":"0x1E092DAEFFBBF262","duration_us":743,"endtime":"2019-12-16 05:15:11.552753","endtime_timestamp_us":1576433711552753,"event":" COM_QUERY","hostgroup_id":0,"query":"s elect 1","rows_affected":0,"rows_sent":1,"schemaname":"test","server":"127.0.0.1:3306","starttime":"2019-12-16 05:15:11.552010","starttime_timestamp_us":1576433711552010,"thread_id":5,"username":"sbtest"}
64. JSON Support Exporting ProxySQL data & statistics in JSON
65. 65 JSON Data in ProxySQL Currently, JSON format is supported in the following ProxySQL Admin tables: • stats_mysql_processlist • stats_mysql_free_connections JSON within ProxySQL Admin can also be parsed using the standard “json_extract” function.
66. 66 Proc. List Stats - extended_info ProxySQL Admin> select extended_info from stats_mysql_processlist WHERE ThreadID = 0\G *************************** 1. row *************************** extended_info: {"autocommit":true,"autocommit_on_hostgroup":-1,"backends":[{"conn":{"MultiplexDisabled":false,"autocommi t":true,"init_connect":"","init_connect_sent":true,"last_set_autocommit":-1,"mysql":{"affected_rows":1844 6744073709551615,"charset":8,"db":"test","host":"127.0.0.1","host_info":"127.0.0.1 via TCP/IP","insert_id":0,"net":{"fd":28,"last_errno":0,"max_packet_size":1073741824,"sqlstate":"00000"},"opt ions":{"charset_name":"utf8","use_ssl":0},"port":13308,"server_status":16386,"server_version":"5.7.24-log ","unix_socket":"","user":"root"},"no_backslash_escapes":false,"ps":{"backend_stmt_to_global_ids":[],"glo bal_stmt_to_backend_ids":[]},"sql_log_bin":1,"sql_mode":"","status":{"found_rows":false,"get_lock":false, "lock_tables":false,"no_multiplex":false,"temporary_table":false,"user_variable":false},"time_zone":"SYST EM"},"hostgroup_id":11}],"client":{"client_addr":{"address":"127.0.0.1","port":37810},"encrypted":false," proxy_addr":{"address":"0.0.0.0","port":6033},"stream":{"bytes_recv":111,"bytes_sent":89,"pkts_recv":2,"p kts_sent":2},"userinfo":{"password":"*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B","username":"root"}},"conn ":{"autocommit":true,"charset":8,"no_backslash_escapes":false,"ps":{"client_stmt_to_global_ids":[]},"sql_ log_bin":1,"sql_mode":"","status":{"compression":false,"transaction":false},"time_zone":"SYSTEM"},"curren t_hostgroup":11,"default_hostgroup":10,"default_schema":"","last_HG_affected_rows":-1,"last_insert_id":0, "thread_session_id":5,"transaction_persistent":true}
68. 68 KEEP CALM AND LOVE JSON
69. 69 Dissecting extended_info... "autocommit": true, "autocommit_on_hostgroup": -1, "backends": [...], "client": {...}, "conn": {...}, "current_hostgroup": 11, "default_hostgroup": 10, "default_schema": "", "last_HG_affected_rows": -1, "last_insert_id": 0, "thread_session_id": 5, "transaction_persistent": true
70. 70 Backends Element "backends": [{ "conn": { "MultiplexDisabled": false, "autocommit": true, "init_connect": "", "init_connect_sent": true, "last_set_autocommit": -1, "mysql": {...}, "no_backslash_escapes": false, "ps": {"backend_stmt_to_global_ids": [], "global_stmt_to_backend_ids": []}, "sql_log_bin": 1, "sql_mode": "", "status": {...}, "time_zone": "SYSTEM" }, "hostgroup_id": 11 }]
71. 71 Backends “conn”.“mysql” "mysql": { "affected_rows": 18446744073709552000, "charset": 8, "db": "test", "host": "127.0.0.1", "host_info": "127.0.0.1 via TCP/IP", "insert_id": 0, "net": {"fd": 28, "last_errno": 0, "max_packet_size": 1073741824, "sqlstate": "00000"}, "options": {"charset_name": "utf8", "use_ssl": 0}, "port": 13308, "server_status": 16386, "server_version": "5.7.24-log", "unix_socket": "", "user": "root" }
72. 72 Backends “conn”.”status” "status": { "found_rows": false, "get_lock": false, "lock_tables": false, "no_multiplex": false, "temporary_table": false, "user_variable": false },
73. 73 Client Element "client": { "client_addr": {"address": "127.0.0.1","port": 37810}, "encrypted": false, "proxy_addr": {"address": "0.0.0.0","port": 6033}, "stream": {"bytes_recv": 111, "bytes_sent": 89, "pkts_recv": 2, "pkts_sent": 2}, "userinfo": {"password": "XXXX", "username": "root"} }
74. 74 Conn Element "conn": { "autocommit": true, "charset": 8, "no_backslash_escapes": false, "ps": {"client_stmt_to_global_ids": []}, "sql_log_bin": 1, "sql_mode": "", "status": {"compression": false, "transaction": false}, "time_zone": "SYSTEM" }
75. 75 Free Conn. Stats - mysql_info Admin> SELECT mysql_info FROM stats_mysql_free_connections LIMIT 1\G ****************** 1. row ****************** mysql_info: {"affected_rows":18446744073709551615,"charset":8,"db":"t est","host":"127.0.0.1","host_info":"127.0.0.1 via TCP/IP","insert_id":0,"net":{"fd":36,"last_errno":0,"max_ packet_size":1073741824,"sqlstate":"00000"},"options":{"c harset_name":"utf8","use_ssl":0},"port":13307,"server_sta tus":2,"server_version":"5.7.24-log","unix_socket":"","us er":"root"}
76. 76 Using JSON_EXTRACT ProxySQL Admin> SELECT fd, hostgroup hg, srv_host, srv_port, JSON_EXTRACT(mysql_info,'$.server_version') sv , statistics FROM stats_mysql_free_connections ORDER BY JSON_EXTRACT(statistics,'$.questions') LIMIT 1\G fd: 30 hg: 11 srv_host: 127.0.0.1 srv_port: 13307 sv: 5.7.24-log Statistics: {"bytes_recv": 408, "bytes_sent": 551, "myconnpoll_get": 69, "myconnpoll_put": 69, "questions": 68}
77. Firewall Whitelist Firewalling capabilities
78. 78 How to block queries? mysql_query_rules offers error_msg: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, … error_msg VARCHAR, …
79. 79 Does it scale? Mysql_query_rules.error_msg provides a blacklist. We can eventually build a whitelist, and block everything else. Cumbersome if we have thousands or millions of unique queries or users or schemas
80. 80 Whitelist tables • mysql_firewall_whitelist_users • mysql_firewall_whitelist_rules
81. 81 mysql_firewall_whitelist_users CREATE TABLE mysql_firewall_whitelist_users ( active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, username VARCHAR NOT NULL, client_address VARCHAR NOT NULL, mode VARCHAR CHECK (mode IN ('OFF','DETECTING','PROTECTING')) NOT NULL DEFAULT ('OFF'), comment VARCHAR NOT NULL, PRIMARY KEY (username, client_address) )
82. 82 mysql_firewall_whitelist_users Mode: • OFF : allows all queries • DETECTING: allows all queries, but not whitelisted queries are logged in error log • PROTECTING: allows only queries explicitly whitelisted Where whitelisted? • mysql_firewall_whitelist_rules
83. 83 Record traffic stats at runtime CREATE TABLE stats_mysql_query_digest ( hostgroup INT, schemaname VARCHAR NOT NULL, username VARCHAR NOT NULL, client_address VARCHAR NOT NULL, digest VARCHAR NOT NULL, digest_text VARCHAR NOT NULL, count_star INTEGER NOT NULL, first_seen INTEGER NOT NULL, last_seen INTEGER NOT NULL, sum_time INTEGER NOT NULL, min_time INTEGER NOT NULL, max_time INTEGER NOT NULL, sum_rows_affected INTEGER NOT NULL, sum_rows_sent INTEGER NOT NULL, PRIMARY KEY(hostgroup, schemaname, username, client_address, digest))
84. 84 mysql_firewall_whitelist_rules CREATE TABLE mysql_firewall_whitelist_rules ( active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, username VARCHAR NOT NULL, client_address VARCHAR NOT NULL, schemaname VARCHAR NOT NULL, flagIN INT NOT NULL DEFAULT 0, digest VARCHAR NOT NULL, comment VARCHAR NOT NULL, PRIMARY KEY (username, client_address, schemaname, flagIN, digest) )
85. 85 Record traffic stats on disk CREATE TABLE history_mysql_query_digest ( dump_time INT, hostgroup INT, schemaname VARCHAR NOT NULL, username VARCHAR NOT NULL, client_address VARCHAR NOT NULL, digest VARCHAR NOT NULL, digest_text VARCHAR NOT NULL, count_star INTEGER NOT NULL, first_seen INTEGER NOT NULL, last_seen INTEGER NOT NULL, sum_time INTEGER NOT NULL, min_time INTEGER NOT NULL, max_time INTEGER NOT NULL, sum_rows_affected INTEGER NOT NULL, sum_rows_sent INTEGER NOT NULL)
86. 86 Record traffic stats on disk • Manually: SAVE MYSQL DIGEST TO DISK • Automatically: admin-stats_mysql_query_digest_to_disk
87. 87 Configure firewall users INSERT INTO mysql_firewall_whitelist_users (active, username, client_address, mode) SELECT DISTINCT 1, username, '', 'DETECTING', '' FROM mysql_users;
88. 88 Configure firewall rules INSERT INTO mysql_firewall_whitelist_rules (active, username, client_address, schemaname, flagIN, digest, comment) SELECT DISTINCT 1, username, client_address, schemaname, 0, digest, '' FROM stats_history.history_mysql_query_digest;
89. 89 Firewall commands • LOAD MYSQL FIREWALL TO RUNTIME • SAVE MYSQL FIREWALL TO DISK • LOAD MYSQL FIREWALL FROM DISK • SAVE MYSQL FIREWALL FROM RUNTIME
90. 90 Enable firewall globally set mysql-firewall_whitelist_enabled=1; LOAD MYSQL VARIABLES TO RUNTIME;
91. SQL injection detection
92. 92 SQL injection detection Algorithm enabled by variable mysql-automatic_detect_sqli Engine provided by libsqlinjection: https://github.com/client9/libinjection
93. 93 SQL injection detection Which queries is this applied to? • queries explicitly blocked are 1.) not processed and 2.) blocked • queries explicitly whitelisted are 1.) not processed and 2.) executed • queries not explicitly whitelisted are processed before execution
94. 94 SQLi false positives libsqlinjection generates a lot of false positives. SELECT * FROM users WHERE username='user")) AND 1=1 AND (("SnEc"="SnEc' AND password='1a1dc91c907325c69271ddf0c944bc72'
95. 95 SQLi false positives Legit query? SELECT * FROM tablename WHERE column1='abc")) AND 1=1 AND (("SnEc"="SnEc' AND column2='1a1dc91c907325c69271ddf0c944bc72'
96. 96 SQLi fingerprints libsqlinjection generates fingerprints Based on the fingerprint, it determines if it is a possible SQLi 2019-11-28 16:17:34 MySQL_Session.cpp:3323:handler(): [ERROR] SQLinjection detected with fingerprint of 'Eoknk' from client pinkys@192.168.56.104 . Query listed below: SELECT * FROM users WHERE username='user")) AND 1=1 AND (("SnEc"="SnEc' AND password='1a1dc91c907325c69271ddf0c944bc72'
97. 97 mysql_firewall_whitelist_sqli_fingerprints CREATE TABLE mysql_firewall_whitelist_sqli_fingerprints ( active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, fingerprint VARCHAR NOT NULL, PRIMARY KEY (fingerprint) ) Loaded at runtime with LOAD FIREWALL TO RUNTIME
98. Support for MySQL 8
99. 99 MySQL 8 • Auth Plugins a. sha256_password b. caching_sha2_password Frontends have been switched to native_mysql_password - From 2.0.9 , backend connections can use new auth plugins
100. Q&A Any questions?