ProxySQL

/ home / computer / .

ProxySQL is a High Performance MySQL and PostgreSQL Load Balancer and Proxy.

See also:

• ~/fromdual/technology/proxysql
• our Galera Cluster Training
• our HA Training
• ProxySQL documentation

Overview

proxysql> show databases;

proxysql> show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| coredump_filters                                   |
| global_variables                                   |

| mysql_collations                                   |

| proxysql_servers                                   |
| restapi_routes                                     |

| runtime_checksums_values                           |
| runtime_coredump_filters                           |
| runtime_global_variables                           |

| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_hostgroup_attributes                 |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_servers_ssl_params                   |
| runtime_mysql_users                                |

| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+
58 rows in set (0,000 sec)

Create backends

proxysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.2.51', 3306);
proxysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.2.52', 3306);
proxysql> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.2.53', 3306);
proxysql> SELECT * FROM mysql_servers;

proxysql> SAVE MYSQL SERVERS TO DISK;
proxysql> LOAD MYSQL SERVERS TO RUNTIME;

Create application users

proxysql> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app', 'secret', 1);
proxysql> SELECT * FROM mysql_users;

proxysql> SAVE MYSQL USERS TO DISK;
proxysql> LOAD MYSQL USERS TO RUNTIME;

Create Hostgroups

proxysql> INSERT INTO mysql_galera_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind)
VALUES (1, 2, 3, 4, 1, 3, 1, 100);
proxysql> SELECT * FROM mysql_galera_hostgroups;

proxysql> SAVE MYSQL SERVERS TO DISK;
proxysql> LOAD MYSQL SERVERS TO RUNTIME;

Change ProxySQL Listener

proxysql> UPDATE global_variables SET variable_value = '0.0.0.0:3306' WHERE variable_name = 'mysql-interfaces';
proxysql> SAVE MYSQL VARIABLES TO DISK;

$ systemctl restart proxysql

Create a monitoring user

SQL> CREATE USER 'monitor'@'192.168.2.%' IDENTIFIED BY 'monitor';

proxysql> SHOW TABLES FROM monitor;
proxysql> SELECT * FROM mysql_server_ping_log;

ProxySQL Statistics

proxysql> SHOW TABLES FROM stats;

Runtime vs disk vs. configuration

proxysql> SELECT * FROM runtime_mysql_servers;
proxysql> SELECT * FROM mysql_servers;