На хабре не раз упоминался данный инструмент для проксирования 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-запросов.
Серверы и прочие настройки мы добавим через 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/
Автор: ite
