ProxySQL - another mysql-proxy

This tool for proxying SQL queries was mentioned more than once on the hub, but, unfortunately, I did not find a single article describing its work, in addition, no documentation in Russian was found either. Well then, let's try to fill this gap. In this article, we will look at the ProxySQL structure, configuration, and usage example.


What is ProxySQL?
This is an application for proxying SQL queries to databases for MySQL forks, such as MariaDB and Percona (in the future, developers promise to add support for various other databases). It works as a separate daemon, all SQL queries that need to be proxied are processed, then, according to predefined rules, the daemon connects to the required MySQL server and executes the query, and after that it returns the result to the application. ProxySQL can also modify incoming queries according to patterns.

ProxySQL Architecture
ProxySQL has a fairly complex, but easy to configure system, thanks to it it is possible:

• Carry out automatic configuration changes, which is important for large systems. This is done through a MySQL-like administrative interface.
• Most changes can be made in runtime mode without restarting the ProxySQL daemon.
• It is easy to roll back changes if suddenly something was configured incorrectly.

This is achieved by using a multilayer configuration system, which is divided into 3 layers:



Runtime layer - This configuration layer is directly used by the ProxySQL daemon and contains all the configuration information for proxying requests.

Layer Memory - Or the main layer is an SQLite3 database that is in memory, used to provide configuration information and the configuration itself. Configuration is done through the standard MySQL client with SQL commands.

Layer Disk - This is a regular SQLite3 file into which (by the user) data entered through the Memory layer is saved

Conf. file - the ProxySQL configuration file (proxysql.cnf) is used at the time of initialization, contains information about finding the SQLite3 database, information about the administrative interface, as well as the initial configuration of the daemon.

There are several administrative commands for moving configurations between layers:

To move user configurations (USERS) between Memory (layer 2) and Runtime:

MySQL [(none)]> LOAD MYSQL USERS FROM MEMORY
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME 

From Runtime to Memory:

MySQL [(none)]> SAVE MYSQL USERS TO MEMORY
MySQL [(none)]> SAVE MYSQL USERS FROM RUNTIME 

From disk (layer 3) to memory

MySQL [(none)]> LOAD MYSQL USERS TO MEMORY
MySQL [(none)]> LOAD MYSQL USERS FROM DISK 

From memory (layer 2) to disk (layer 3)

MySQL [(none)]> SAVE MYSQL USERS FROM MEMORY
MySQL [(none)]> SAVE MYSQL USERS TO DISK 

From disk (layer 3) to memory (layer 2)

LOAD MYSQL USERS FROM CONFIG 

Moving in the same way can be done for other tables / variables. List available:
QUERY RULES - Requests for proxies.
VARIABLES - variables of the MySQL server and administrative settings.

Installation
Since this application is quite new and is under development, the best option would be to compile it from the sources that can be obtained on github: github.com/sysown/proxysql
Binary packages for RedHat (CentOS) and Debian (Ubuntu) OS: github.com/sysown/proxysql/releases

Install the package for CentOS 7:

rpm -ihv https://github.com/sysown/proxysql/releases/download/v1.2.0i/proxysql-1.2.0-1-centos7.x86_64.rpm

After installation, conf. the file will be located at: /etc/proxysql.cnf
Open it in your favorite editor:

datadir="/var/lib/proxysql"
admin_variables=
{
	admin_credentials="admin:admin" # логин и пароль администратора
	mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" #хост и порт для административного интерфейса
	refresh_interval=2000 #Интервал обновления счетчиков статистики в микросекундах
	debug=true 
	admin-stats_credentials=stats:stats #логин и пароль к админ.интерфейсу для сбора статистики(только чтение)
}
mysql_variables=
{
        threads=4 #количество потоков для обработки входящих запросов
        max_connections=2048 #максимальное количество соединений, которое прокси может обрабатывать одновременно.
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true  #на данный момент не используется
        poll_timeout=2000
        interfaces="127.0.0.1:3306;/tmp/proxysql.sock"
        default_schema="information_schema"
        stacksize=1048576 # размер стека для потоков и соединений с backend-сервером.
        server_version="5.1.30"
        connect_timeout_server=10000
        monitor_history=60000
        monitor_connect_interval=200000
        monitor_ping_interval=200000
        ping_interval_server=10000
        ping_timeout_server=200
        commands_stats=true
        sessions_sort=true
}

datadir - location of the SQLite3 database file, by default / var / lib / proxysql
admin_variables - settings of the admin interface
mysql_variables - contains global variables for the server of incoming mysql queries.

We will add backend servers and other settings through the mysql interface.

First launch and initialization
Initialize settings.

Initialization transfers server settings from conf. file (layer 3) to the SQLite3 database in memory (layer 2), resetting all the settings that were stored in memory (layer 2) and renaming the file to disk (layer 3).

proxysql --initial

Configuring ProxySQL on the fly (Runtime)
To configure ProxySQL on the fly, we will use the standard mysql client.

mysql -h 127.0.0.1 -P6032 -uadmin -p
Enter password:
MySQL [(none)]> 

Now we are in the admin. interface. Let's see what tables there are:

MySQL [(none)]> show tables;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                    |
| mysql_replication_hostgroups         |
| mysql_servers                        |
| mysql_users                          |
| runtime_mysql_query_rules            |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers                |
| runtime_scheduler                    |
| scheduler                            |
+--------------------------------------+
11 rows in set (0.00 sec)

mysql_servers - contains a list of backend servers
mysql_users - contains a list of all users who have access to ProxySQL and backend servers.
mysql_query_rules - all the rules for caching, redirecting and replacing SQl queries that pass through the proxy.
global_variables - contains global variables (which we configured in the config file) of the ProxySQL MySQL server and administrative settings.
mysql_replication_hostgroups - a list of host groups to which backends will be attached, to which query rules will in turn apply.
mysql_query_rules - query proxy rules.

Add backends, but first, make sure the mysql_servers, mysql_replication_hostgroups, and mysql_query_rules tables are empty.

MySQL [(none)]> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
MySQL [(none)]> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
MySQL [(none)]> SELECT * from mysql_query_rules;
Empty set (0.00 sec)

Indeed, the required tables are empty. Before adding, we need to decide what and where we will proxy, I will add two servers, one will be written (INSERT, UPDATE, etc.), and from the second we will only read the data (SELECT), in general, a typical master- slave with read-write distribution across different servers. To do this, we will create 2 host groups.

Add backend servers:
The first server we will write to the database and be in host group 1:

MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'192.168.100.2',3307);

The second server is configured for slave and we will only read from it, we will place it in group 2:

MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.100.3',3307);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> SELECT * FROM mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0            | 192.168.100.2 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.100.3 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
2 rows in set (0.00 sec)

The mysql_replication_hostgroups table has 2 fields, the first one is writer_hostgroup - it contains the numbers of groups that hosts the hosts to write to. In reader_hostgroup - for reading.
Add 2 hostgroups (1,2) to the mysql_replication_hostgroups table:

MySQL [(none)]> INSERT INTO mysql_replication_hostgroups VALUES (1,2);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+
| writer_hostgroup | reader_hostgroup |
+------------------+------------------+
| 1                | 2                |
+------------------+------------------+
1 row in set (0.00 sec)

Now we will transfer the data about the backend servers and host groups from memory to runtime so that they take effect immediately:

MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)    

and save the data to disk (layer 3):

MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

It's time to add the rules for proxying queries, for this there is a mysql_query_rules table: The
table has the following structure:

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,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    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,
    delay INT UNSIGNED,
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0
)

rule_id - rule number
active - rule enabled, 0-disabled
username and schemaname - If non-NULL, then the rule will be executed only if the username / schemaname match for the connection
flagIN, flagOUT, apply - These flags make it possible to create a “chain of rules” . In practice, I personally have not yet had to use them, so for now I give the original text from the official documentation, if anyone can correctly and clearly translate, please.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. 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)
match_pattern - regular expression, rules that fall under it will be proxied.
replace_pattern is a regular expression to replace a proxied request or part of it.
destination_hostgroup - the host group number to which the rule will apply.
cache_ttl - the number of seconds the request will be cached.
reconnect - until
timeout is used - timeout for match_pattern or replace_pattern, if the request takes more time, it is killed.
delay - The delay before the backend request is executed, useful if, for example, the SELECT query goes immediately after INSERT / UPDATE to give time for replication.

Add 3 rules to the mysql_query_rules table

MySQL [(none)]> INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1,1,'^SELECT .* FOR UPDATE$',1,1);
MySQL [(none)]> INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1,1,'^SELECT',2,1);
MySQL [(none)]> INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES(1,1,'.*',1,1);

The first rule redirects all SELECT UPDATE queries to the master server.
The second rule redirects all SELECT UPDATE queries to the slave server
. Finally, the third rule redirects all other queries to the master server.

Users
Now add users to the mysql_users table. ProxySQL needs all the users that are present on all servers connected to it. A request to add a root user for both host groups:

MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','password',1);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','password',0);
Query OK, 1 row affected (0.00 sec)

Transfer the changes to Runtime and save to disk:

MySQL [(none)]> LOAD MYSQL USERS FROM MEMORY
MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME 
MySQL [(none)]> SAVE MYSQL USERS FROM MEMORY
MySQL [(none)]> SAVE MYSQL USERS TO DISK 
MySQL [(none)]> LOAD MYSQL QUERY RULES FROM MEMORY 
MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME
MySQL [(none)]> SAVE MYSQL QUERY RULES FROM MEMORY
MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK

Conclusion
After the above steps, we have configured ProxySQL for Master-Slave replication. Of course, this is not all the features of ProxySQL, among other things, it can carry out excellent monitoring of all backends, and, of course, itself.

Links:
Off site: http://www.proxysql.com/
Off. Documentation: https://github.com/sysown/proxysql/tree/master/doc
Configuring Master-Slave replication using ProxySQL and configuring from the config file: http://unix-admin.su/scalable-mysql-cluster/