ProxySQL - another mysql-proxy
On Habré, this tool was repeatedly mentioned for proxying SQL queries, but, unfortunately, I did not find a single article describing its work, moreover, documentation in Russian was not found either. Well, let's try to fill this gap. In this article we will look at the structure of ProxySQL, configuration and usage example.
What is ProxySQL?
This application is for proxying SQL database queries for MySQL forks, such as MariaDB and Percona (in the future, the developers promise to add support for other different 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 gives the result to the application. ProxySQL can also modify incoming requests according to templates.
ProxySQL architecture.
ProxySQL has a rather complicated but easy to configure system, thanks to which it is possible:
This is achieved by using a multi-layer 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.
Memory layer — Or the main layer is a SQLite3 database that is in memory, used to provide configuration information and the configuration itself. Configuration is done through standard MySQL client using SQL commands.
The Disk Layer - It is a normal SQLite3 file in which data entered through the Memory layer is saved (by the user)
Conf. file - the ProxySQL configuration file (proxysql.cnf) is used at the time of initialization, contains information about finding 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:
From Runtime to Memory:
From disk (layer 3) to memory
From memory (layer 2) to disk (layer 3)
From disk (layer 3) to memory (layer 2)
In the same way, moving can be done for other tables / variables. List of available:
QUERY RULES - Requests for proxying.
VARIABLES - variables of MySQL-server and administrative settings.
Installation
Since this application is quite new and is under development, the best option would be to collect it from source code, which can be obtained on github: github.com/sysown/proxysql
For RedHat (CentOS) and Debian (Ubuntu) OS binary packages are compiled: github.com/sysown/proxysql/releases
Install the package for CentOS 7:
After installation, conf. the file will be located at: /etc/proxysql.cnf
Open it in your favorite editor:
datadir is the location of the SQLite3 database file, the default is / var / lib / proxysql
admin_variables - administrative interface settings
mysql_variables - contains global variables for the server of incoming mysql requests.
We will add backend servers and other settings via the mysql interface.
First run and initialize
Initialize the settings.
Initialization transfers server settings from conf. file (layer 3) to the SQLite3 database in memory (layer 2), while resetting all the settings that were stored in memory (layer 2) and renaming the file on disk (layer 3).
Configuring ProxySQL on the fly (Runtime)
To configure ProxySQL on the fly, we will use the standard mysql client.
Now we are in admin. interface. Let's see what tables there are:
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 requests that go through a proxy.
global_variables - contains global variables (which we set up in the conf. file) of the MySQL-server ProxySQL and administrative settings.
mysql_replication_hostgroups - a list of host groups to which backends will be attached, to which query rules will in turn be applied.
mysql_query_rules - query proxy rules.
Let's add backends, but first make sure that the mysql_servers, mysql_replication_hostgroups and mysql_query_rules tables are empty.
Indeed, the necessary tables are empty. Before adding we need to decide what and where we will be proxying, 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 the typical master- scheme 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 be engaged in writing to the database and consist in the host group 1:
The second server is configured on our slave and we will only read from it, put it in group 2:
The mysql_replication_hostgroups table has 2 fields, the first writer_hostgroup contains the numbers of the groups that include the write hosts. In reader_hostgroup is readable.
Add 2 host groups (1,2) to the mysql_replication_hostgroups table:
Now we transfer the data about the backend servers and host groups from memory to runtime so that they take effect immediately:
and save the data to disk (layer 3):
It's time to add rules for proxying queries, for this there is a table mysql_query_rules:
The table has the following structure:
rule_id - rule number
active - the rule is on, 0 is off
username and schemaname - If non-NULL, then the rule will be executed only if the username / schemaname is correctly matched 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 I give the original text from the official documentation so far, if anyone can translate correctly and clearly, please. "get the chains of rules" that get you applied one after the other. An input flag is set to 0, and only rules with flagIN = 0 are considered at the beginning. The flag will be flagged out and the flag will be flagged out. If the flagOUT differs from the flagIN, it will exit from the flagIN as the new input flag. This happens until there are no more matching rules.
match_pattern is a regular expression, the rules that fall under it will be proxied.
replace_pattern is a regular expression for replacing a proxied request or part of it.
destination_hostgroup is the host number of the group to which the rule will apply.
cache_ttl - the number of seconds on which the request will be cached.
reconnect - not yet used
timeout - timeout for execution of match_pattern or replace_pattern, if the request takes more time, it is killed.
delay - The delay before the request to the backend is executed, useful if for example a SELECT query goes right after INSERT / UPDATE to give time for replication.
Add 3 rules to the mysql_query_rules table
The first rule redirects all SELECT UPDATE requests to the master server.
The second rule redirects all queries to the slave SELECT server
Finally, the third rule redirects all other requests to the master server.
Users
Now add users to the mysql_users table. ProxySQL needs all users that are present on all servers connected to it. Request to add root user for both host groups:
Transfer the changes to Runtime and save 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 backend-s, and, of course, of itself.
References:
Off site: http://www.proxysql.com/
Off Documentation: https://github.com/sysown/proxysql/tree/master/doc
Configure Master-Slave replication using ProxySQL and configuration from conf. File: http://unix-admin.su/scalable-mysql-cluster/
What is ProxySQL?
This application is for proxying SQL database queries for MySQL forks, such as MariaDB and Percona (in the future, the developers promise to add support for other different 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 gives the result to the application. ProxySQL can also modify incoming requests according to templates.
ProxySQL architecture.
ProxySQL has a rather complicated but easy to configure system, thanks to which it is possible:
- Make automatic changes to the configuration, 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 perform change rollbacks, if suddenly something was configured incorrectly.
This is achieved by using a multi-layer 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.
Memory layer — Or the main layer is a SQLite3 database that is in memory, used to provide configuration information and the configuration itself. Configuration is done through standard MySQL client using SQL commands.
The Disk Layer - It is a normal SQLite3 file in which data entered through the Memory layer is saved (by the user)
Conf. file - the ProxySQL configuration file (proxysql.cnf) is used at the time of initialization, contains information about finding 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 In the same way, moving can be done for other tables / variables. List of available:
QUERY RULES - Requests for proxying.
VARIABLES - variables of MySQL-server and administrative settings.
Installation
Since this application is quite new and is under development, the best option would be to collect it from source code, which can be obtained on github: github.com/sysown/proxysql
For RedHat (CentOS) and Debian (Ubuntu) OS binary packages are compiled: 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 is the location of the SQLite3 database file, the default is / var / lib / proxysql
admin_variables - administrative interface settings
mysql_variables - contains global variables for the server of incoming mysql requests.
We will add backend servers and other settings via the mysql interface.
First run and initialize
Initialize the settings.
Initialization transfers server settings from conf. file (layer 3) to the SQLite3 database in memory (layer 2), while resetting all the settings that were stored in memory (layer 2) and renaming the file on 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 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 requests that go through a proxy.
global_variables - contains global variables (which we set up in the conf. file) of the MySQL-server ProxySQL and administrative settings.
mysql_replication_hostgroups - a list of host groups to which backends will be attached, to which query rules will in turn be applied.
mysql_query_rules - query proxy rules.
Let's add backends, but first make sure that 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 necessary tables are empty. Before adding we need to decide what and where we will be proxying, 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 the typical master- scheme 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 be engaged in writing to the database and consist in the 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 on our slave and we will only read from it, put 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 writer_hostgroup contains the numbers of the groups that include the write hosts. In reader_hostgroup is readable.
Add 2 host groups (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 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 rules for proxying queries, for this there is a table mysql_query_rules:
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 - the rule is on, 0 is off
username and schemaname - If non-NULL, then the rule will be executed only if the username / schemaname is correctly matched 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 I give the original text from the official documentation so far, if anyone can translate correctly and clearly, please. "get the chains of rules" that get you applied one after the other. An input flag is set to 0, and only rules with flagIN = 0 are considered at the beginning. The flag will be flagged out and the flag will be flagged out. If the flagOUT differs from the flagIN, it will exit from the flagIN as the new input flag. This happens until there are no more matching rules.
match_pattern is a regular expression, the rules that fall under it will be proxied.
replace_pattern is a regular expression for replacing a proxied request or part of it.
destination_hostgroup is the host number of the group to which the rule will apply.
cache_ttl - the number of seconds on which the request will be cached.
reconnect - not yet used
timeout - timeout for execution of match_pattern or replace_pattern, if the request takes more time, it is killed.
delay - The delay before the request to the backend is executed, useful if for example a SELECT query goes right 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 requests to the master server.
The second rule redirects all queries to the slave SELECT server
Finally, the third rule redirects all other requests to the master server.
Users
Now add users to the mysql_users table. ProxySQL needs all users that are present on all servers connected to it. Request to add 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 backend-s, and, of course, of itself.
References:
Off site: http://www.proxysql.com/
Off Documentation: https://github.com/sysown/proxysql/tree/master/doc
Configure Master-Slave replication using ProxySQL and configuration from conf. File: http://unix-admin.su/scalable-mysql-cluster/
Source: https://habr.com/ru/post/303612/
All Articles