ProxySQL — ще один mysql-proxy

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


Що ж таке ProxySQL?
Це додаток для проксі серверів SQL-запитів до баз даних для форков MySQL гикаю як MariaDB і Percona(в майбутньому розробники обіцяють додати підтримку інших різних БД). Працює як окремий демон, все SQL-запити, які необхідно спроксировать, обробляються, потім за заздалегідь складеним правилами демон підключається до необхідного MySQL-сервера і виконує його, і вже після цього віддає результат додатком. ProxySQL може так само модифікувати надходять запити згідно з шаблонами.

Архітектура ProxySQL.
ProxySQL має досить складну, але просту для конфігурування систему, завдяки ній можливо:
  • Здійснювати автоматичні зміни в конфігурації, що важливо для великих систем. Здійснюється це через MySQL-подібний адміністративний інтерфейс.
  • Більшість змін можна вносити в runtime режимі без перезапуску демона ProxySQL
  • Легко виконувати відкати змін, якщо раптом щось було налаштовано неправильно.


Це досягається шляхом використання багатошарової системи конфігурації, яка ділиться на 3 шари:

image

Шар 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).
--initial proxysql


Конфігурування 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» get that applied one after the other. An input flag value is set to 0, rules and only 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 chain new rules of 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-сервер
Друге правило перенаправляє се завпросы slave SELECT-сервер
І нарешті третє правило перенаправляє всі інші запити на 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/
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.