На хабре не раз упоминался данный инструмент для проксирования SQL-запросов, но, к сожалению, я не нашел ни одной статьи описывающей его работы, кроме того, документации на русском языке тоже не было найдено. Ну чтож, попробуем заполнить этот пробел. В статье мы рассмотрим структуру ProxySQL, конфигурирование и пример использования.


Что же такое ProxySQL?
Это приложение для проксирования SQL-запросов к базам данных для форков MySQL, таких как MariaDB и Percona(в будущем разработчики обещают добавить поддержку других различных БД). Работает как отдельный демон, все SQL-запросы, которые необходимо проксировать, обрабатываются, затем, по заранее составленным правилам, демон подключается к необходимому MySQL-серверу и выполняет запрос, и уже после этого отдает результат приложению. ProxySQL может так же модифицировать поступающие запросы согласно шаблонам.

Архитектура ProxySQL.
ProxySQL имеет достаточно сложную, но простую для конфигурирования систему, благодаря ней возможно:

• Осуществлять автоматические изменения в конфигурации, что важно для больших систем. Осуществляется это через MySQL-подобный административный интерфейс.
• Большинство изменений можно вносить в runtime-режиме без перезапуска демона ProxySQL
• Легко выполнять откаты изменений, если вдруг что-то было сконфигурировано неправильно.

Это достигается путем использования многослойной системы конфигурации, которая делится на 3 слоя:



Слой Runtime — Этот слой конфигурации непосредственно используется демоном ProxySQL и содержит всю конфигурационную информацию для проксирования запросов.

Слой Память(Memory) — Или слой main представляет собой SQLite3 базу данных, которая находится в памяти, используется для предоставления информации о конфигурации и самого конфигурирования. Конфигурирование осуществляется через стандартный MySQL-клиент SQL-командами.

Слой Диск — Представляет собой обычный SQLite3 файл, в который сохраняются(пользователем) данные внесенные через слой Memory

Конф. файл — файл конфигурации ProxySQL(proxysql.cnf) используется в момент инициализации, содержит информацию о нахождении SQLite3 базы данных, информацию об административном интерфейсе, а так же начальную конфигурацию демона.

Для перемещения конфигураций между слоями существуют несколько административных команд:

Для перемещения конфигураций пользователей(USERS) между Memory(слой 2) и Runtime:

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

Из Runtime в Memory:

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

С диска(слой 3) в память

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

Из памяти(слой 2) на диск(слой3)

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

Из диска(слой 3) в память(слой 2)

LOAD MYSQL USERS FROM CONFIG 

Таким же образом перемещение можно осуществлять и для других таблиц/переменных. Список доступных:
QUERY RULES — Запросы для проксирования.
VARIABLES — переменные MySQL-сервера и административных настроек.

Установка
Так как данное приложение достаточно новое и находится на стадии разработки, наилучшим вариантом будет собрать его из исходных текстов, которые можно получить на github: github.com/sysown/proxysql
Для ОС RedHat(CentOS) и Debian(Ubuntu) собраны бинарные пакеты: github.com/sysown/proxysql/releases

Установим пакет для CentOS 7:

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

После установки, конф. файл будет располагаться по адресу: /etc/proxysql.cnf
Откроем его в любимом редакторе:

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 — расположение файла базы SQLite3, по умолчанию /var/lib/proxysql
admin_variables — настройки административного интерфейса
mysql_variables — содержит глобальные переменные для сервера входящих mysql-запросов.

Backend серверы и прочие настройки мы добавим через mysql-интерфейс.

Первый запуск и инициализация
Инициализируем настройки.

Инициализация переносит настройки сервера из конф. файла(слой 3) в базу SQLite3 в памяти(слой 2), сбрасывая при этом все настройки, которое хранились в памяти(слой 2) и переименовывая файл на диске(слой 3).

proxysql --initial

Конфигурирование ProxySQL на лету(Runtime)
Для конфигурирования ProxySQL на лету, мы будем использоваться стандартный клиент mysql.

mysql -h 127.0.0.1 -P6032 -uadmin -p

Enter password:

MySQL [(none)]> 

Теперь мы находимся в админ. интерфейсе. Посмотрим какие тут есть таблицы:

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 — содержит список backend-серверов
mysql_users — содержит список всех пользователей, которые имеют доступ к ProxySQL и backend-серверам.
mysql_query_rules — все правила кеширования, перенаправления и замены SQl-запросов, которые проходят через прокси.
global_variables — содержит глобальные переменные(которые мы настраивали в конф. файле) MySQL-сервера ProxySQL и административные настройки.
mysql_replication_hostgroups — список групп хостов, к которым будут прикреплены бекенды, к которым в свою очередь будут применяться правила запросов.
mysql_query_rules — правила проксирования запросов.

Добавим бекенды, но для начала убедимся, что таблицыmysql_servers, mysql_replication_hostgroups и mysql_query_rules пусты.

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)

Действительно, необходимые таблицы пусты. Перед добавлением нам надо определиться что и куда мы будем проксировать, я добавлю два сервера, на один будет осуществляться запись(INSERT, UPDATE и т.п), а со второго мы будем только читать данные(SELECT), в общем типичная схема master-slave с распределением чтения-записи по разным серверам. Для этого мы создадим 2 хост группы.

Добавим backend-серверы:
Первый сервер у нас будем заниматься записью в БД и состоять в хост группе 1:

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

Второй сервер у нас настроен на slave и с него мы будем выполнять только чтение, поместим его в группу 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)

Таблица mysql_replication_hostgroups имеет 2 поля, первое writer_hostgroup — в ней находятся номера групп, в которые входят хосты на запись. В reader_hostgroup — на чтение.
Добавим 2 хостгруппы(1,2) в таблицу mysql_replication_hostgroups:

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)

Теперь перенесем данные о backend-серверах и хост группах из памяти в runtime, чтобы они вступили в силу немедленно:

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

и сохраним данные на диск(слой 3):

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

Самое время добавить правила проксирования запросов, для этого существует таблица mysql_query_rules:
Таблица имеет следующую структуру:

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 — номер правила
active — правило включено, 0-выключено
username и schemaname — Если не-NULL, то правило выполнится только в случае правильного соответствия username/schemaname для соединения
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. 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 — регулярное выражение, будут проксироваться правила, которые попадают под него.
replace_pattern — регулярное выражение для замены проксируемого запроса или его части.
destination_hostgroup — номер хост группы к которой будет применяться правило.
cache_ttl — количество секунд на которе будет кешироваться запрос.
reconnect — пока не используется
timeout — таймаут на выполнения match_pattern или replace_pattern, если запрос занимает больше времени, он убивается.
delay — Задержка до выполнения запроса к backend, полезна в случае, если например запрос SELECT идет сразу после INSERT/UPDATE, чтобы дать время на репликацию.

Добавим 3 правила в таблицу mysql_query_rules

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);

Первое правило перенаправляет все запросы SELECT UPDATE на master-сервер
Второе правило перенаправляет все запросы SELECT slave-сервер
И наконец, третье правило перенаправляет все остальные запросы на master-сервер.

Пользователи
Теперь добавим пользователей в таблицу mysql_users. ProxySQL нуждается во всех пользователях, которые присутствуют на всех серверах подключенных к нему. Запрос на добавления пользователя root для обоих хост групп:

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)

Перенесем изменения в Runtime и сохраним на диск:

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

Заключение
После вышеописанных действий, мы имеем настроенный ProxySQL на репликации Master-Slave. Разумеется, это не все возможности ProxySQL, кроме всего прочего он может осуществлять отличный мониторинг всех backend-ов, и, разумеется, самого себя.

Ссылки:
Офф сайт: http://www.proxysql.com/
Офф. Документация: https://github.com/sysown/proxysql/tree/master/doc
Настройка Master-Slave репликации с применением ProxySQL и настройкой из конф.файла: http://unix-admin.su/scalable-mysql-cluster/