ProxySQL как балансировщик нагрузки MySQL в Ubuntu 16.04

17 февраля, 2018 12:18 пп 6 737 views | Комментариев нет

mySQL, Ubuntu | Amber | Комментировать запись

ProxySQL – это прокси-сервер MySQL с открытым исходным кодом; он служит посредником между сервером MySQL и приложениями, которые обращаются к базам данных. ProxySQL может повысить производительность, распределяя трафик между пулом нескольких серверов баз данных, а также улучшить доступность, автоматически переходя в режим ожидания, если один или несколько серверов баз данных вышли из строя.

Данный мануал поможет настроить ProxySQL как балансировщик нагрузки для нескольких серверов MySQL с автоматическим восстановлением после сбоев. Для примера в мануале используется реплицированный кластер из трех серверов MySQL, но вы можете использовать аналогичный подход и для других конфигураций кластера.

Требования

Сервер Ubuntu 16.04, настроенный по этому мануалу. Этот сервер предназначен для ProxySQL.
Три сервера MySQL, связанные в кластер по мануалу Создание группы репликации MySQL в Ubuntu 16.04. Выбирая режим между Single Primary и Multi-Primary, выберите Multi-Primary.

1: Установка ProxySQL

Разработчики ProxySQL предоставляют официальные пакеты всех релизов ProxySQL для Ubuntu на странице релизов GitHub. Загрузите последнюю версию пакета и установите ее.

Вы можете найти последний пакет в списке релизов. Соглашение об именах – proxysql_версия-дистрибутив.deb, где версия представляет собой строку типа 1.4.4, а дистрибутив – строку типа ubuntu16_amd64 (64-битная система Ubuntu 16.04).

Загрузите последний официальный пакет – на данный момент это 1.4.4 – в каталог /tmp:

cd /tmp curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb

Установите пакет с помощью dpkg, который используется для управления пакетами программного обеспечения .deb. Флаг -i указывает файл, из которого нужно установить пакет.

Читайте также: Основы управления пакетами: apt, yum, dnf, pkg

На этом этапе вам больше не нужен файл .deb, поэтому его можно удалить.

rm proxysql_*

Затем вам понадобится клиентское приложение MySQL для подключения к экземпляру ProxySQL. Это связано с тем, что ProxySQL внутренне использует MySQL-совместимый интерфейс для выполнения административных задач. Используйте инструмент командной строки mysql, который является частью пакета mysql-client, доступного в репозиториях Ubuntu.

Обновите индекс пакетов, чтобы удостовериться, что вы загрузите последнюю предварительно упакованную версию, а затем установите пакет mysql-client.

sudo apt-get update sudo apt-get install mysql-client

Теперь все готово к запуску ProxySQL. Сервер не запускается автоматически после установки, запустите его вручную:

sudo systemctl start proxysql

Теперь ProxySQL запущен и использует стандартные параметры. Чтобы убедиться в этом, используйте systemctl.

systemctl status proxysql

Команда должна вернуть:

proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago Docs: man:systemd-sysv-generator(8) Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited, status=0/SUCCESS) Tasks: 23 Memory: 30.9M CPU: 86ms CGroup: /system.slice/proxysql.service ├─12355 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql └─12356 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

Строка active (running) значит, что ProxySQL установлен и запущен.

2: Пароль администратора ProxySQL

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

Чтобы установить пароль администратора в ProxySQL, подключитесь к этой базе данных конфигурации и обновите соответствующие переменные.

Сначала откройте интерфейс администратора. При этом будет запрошен пароль, по умолчанию это admin.

mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

-u определяет пользователя, через которого нужно подключиться. Здесь это admin, стандартный пользователь для выполнения административных задач, таких как изменение настроек конфигурации.

-h 127.0.0.1 помогает mysql подключиться к локальному экземпляру ProxySQL. Это нужно определить явно, потому что ProxySQL не прослушивает файл сокета, который mysql принимает по умолчанию.

-P определяет порт, к которому нужно подклюиться. Интерфейс администратора ProxySQL прослушивает порт 6032.

–prompt –опциональный флаг, который меняет стандартную командную строку (обычно это mysql>). Здесь она заменяется строкой ProxySQLAdmin> – это поможет вам понять, что вы находитесь в интерфейсе админа ProxySQL, и избежать путаницы, когда вы подключитесь к интерфейсам MySQL на реплицированных серверах баз данных.

Подключившись, вы увидите строку ProxySQLAdmin>:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ProxySQLAdmin>

Измените пароль учетной записи администратора, обновив переменную admin-admin_credentials в базе данных global_variables. Не забудьте заменить password в приведенной ниже команде надежным паролем:

UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials'; Query OK, 1 row affected (0.00 sec)

Это изменение не будет выполнено немедленно, потому что так работает система конфигурации ProxySQL. Она состоит из трех отдельных слоев:

memory, который изменяется при внесении изменений из интерфейса командной строки.
runtime, который используется ProxySQL в качестве текущей конфигурации.
disk, который используется для сохранения конфигурации при перезапуске.

На данный момент внесенное вами изменение находится в слое memory. Чтобы изменения вступили в силу, необходимо скопировать настройки памяти в область runtime, а затем сохранить их в disk.

LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;

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

Теперь, когда ProxySQL установлен и запущен с новым паролем администратора, нужно настроить 3 ноды MySQL, чтобы ProxySQL мог ими управлять. Пока не закрывайте интерфейс ProxySQL – мы будем использовать его позже.

3: Настройка мониторинга MySQL

ProxySQL должен связываться с нодами MySQL, чтобы оценить их состояние. Для этого он должен иметь возможность подключаться к каждому серверу с помощью специального пользователя.

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

Примечание: Поскольку группа репликации MySQL уже запущена, все последующие действия нужно выполнить только на одной ноде.

Откройте второй терминал и подключитесь по ssh у одной из нод.

Загрузите файл SQL, содержащий некоторые функции, необходимые для поддержки группы репликации ProxySQL.

curl -OL https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322/raw/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql

Примечание: Этот файл предоставляется авторами ProxySQL, но специальным образом: это gist в личном хранилище GitHub, что означает, что файл может переместиться или устареть. В будущем его могут поместить в официальный репозиторий ProxySQL.

Просмотреть содержимое файла можно с помощью этой команды:

less addition_to_sys.sql

Выполните команды в файле. Вам будет предложено ввести пароль администратора MySQL.

mysql -u root -p < addition_to_sys.sql

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

Затем нужно создать выделенного пользователя, который будет использоваться ProxySQL для мониторинга работоспособности экземпляров.

Откройте интерактивную строку MySQL, которая снова запросит пароль root.

mysql -u root -p

Затем создайте выделенного пользователя (здесь он называется monitor). Обязательно укажите надежный пароль.

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

Предоставьте пользователю права запрашивать состояние сервера MySQL у пользователя monitor.

GRANT SELECT on sys.* to 'monitor'@'%';

Сбросьте привилегии, чтобы обновить настройки:

FLUSH PRIVILEGES;

Благодаря репликации пользователь появится на всех трех нодах, как только вы как только вы добавите его на одну из нод MySQL.

Затем нужно предоставить ProxySQL информацию об этом пользователе, чтобы он мог получить доступ к нодам MySQL.

4: Настройка мониторинга ProxySQL

Чтобы настроить ProxySQL для использования новой учетной записи пользователя при мониторинге нод, нужно обновить соответствующую конфигурационную переменную. Подобное вы уже делали в разделе 2 при обновлении пароля.

Вернитесь в интерфейс администратора ProxySQL и обновите переменную mysql-monitor_username, указав имя нового пользователя.

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

Как помните, конфигурация не применяется автоматически, поэтому перенесите ее в runtime и сохраните в disk. Обратите внимание, что на этот раз нужно использовать MYSQL вместо ADMIN для обновления этих переменных (потому что редактируются переменные конфигурации MySQL).

LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Учетная запись monitor настроена на всех нодах и на сервере, и следующий шаг – сообщить ProxySQL о самих нодах.

5: Добавление нод MySQL в пул ProxySQL

Чтобы ProxySQL знал о трех нодах MySQL, нужно сообщить ProxySQL, как распределять их по группам хостов, которые определяют наборы нод. Каждая группа хостов идентифицируется положительным числом, например 1 или 2. Группы хостов могут маршрутизировать различные SQL-запросы на разные наборы хостов при использовании маршрутизации запросов ProxySQL.

Поддержка групп репликации ProxySQL автоматически делит все ноды в группе на четыре логических состояния:

writer – ноды MySQL, которые могут принимать запросы, изменяющие данные. ProxySQL присваивает это состояние максимальному количеству ведущих нод.
backup writer – это тоже ноды MySQL, которые могут принимать запросы, изменяющие данные, Однако эти ноды не обозначаются как writer; если количество ведущих нод в группе writer превышено, остальные ведущие ноды хранятся в этом состоянии и переводятся в группу writer, если один из серверов этой группы отказал.
reader – это ноды MySQL, которые не могут принимать запросы, изменяющие данные, и должны использоваться как ноды только для чтения. ProxySQL добавляет сюда только ведомые ноды.
offline – это проблемные ноды.

Каждое из этих четырех состояний имеет соответствующие группы хостов, но числовые идентификаторы групп не назначаются автоматически.

Вы должны сами сообщить ProxySQL, какие идентификаторы он должен использовать для каждого состояния. Здесь используется 1 для offline, 2 для группы writer, 3 для reader и 4 для backup writer.

Чтобы установить эти идентификаторы, создайте новую строку с этими переменными и значениями в таблице конфигурации mysql_group_replication_hostgroups.

INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 3, 1, 100);

Вот дополнительные переменные, заданные в этой строке:

active со значением 1 включает мониторинг ProxySQL для этих групп хостов
max_writers определяет, сколько нод могут выступать в роли writer. Здесь установлено значение 3, потому что в конфигурации multi-primary все ноды можно обрабатывать наравне.
writer_is_also_reader со значением 1 позволяет ProxySQL воспринимать ноды writer и как reader.
max_transactions_behind определяет максимальное количество отложенных транзакций до того, как нода получит состояние offline.

Примечание: Поскольку в примере используется топология multi-primary, в которой все ноды могут выполнять операции записи, все SQL-запросы будут распределяться в группе хостов writer. В других топологиях разделение между writer (ведущими нодами) и reader (ведомыми нодами) может направлять разные запросы в разные группы в зависимости от их цели. ProxySQL не делает этого автоматически, но вы можете настроить маршрутизацию запросов с помощью этих правил.

Теперь, когда ProxySQL знает, как распределять ноды по группам хостов, можно добавить серверы MySQL в пул. Для этого нужно вставить IP-адрес и исходную группу хостов каждого сервера в таблицу mysql_servers, которая содержит список серверов, с которыми может взаимодействовать ProxySQL.

Добавьте каждый из трех серверов MySQL, правильно укажите IP-адреса.

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.1', 3306); INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.2', 3306); INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, '203.0.113.3', 3306);

В этих командах 2 определяет состояние writer, а 3306 – это порт MySQL.

Как и ранее, нужно добавить изменения в runtime и сохранить на disk, чтобы они вступили в силу:

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Теперь ProxySQL должен распределить ноды по группам хостов, как указано. Проверьте это, выполнив запрос SELECT в таблице runtim330e_mysql_servers, которая предоставляет текущее состояние серверов ProxySQL.

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers; +--------------+-------------+--------+ | hostgroup_id | hostname | status | +--------------+-------------+--------+ | 2 | 203.0.113.1 | ONLINE | | 2 | 203.0.113.2 | ONLINE | | 2 | 203.0.113.3 | ONLINE | | 3 | 203.0.113.1 | ONLINE | | 3 | 203.0.113.2 | ONLINE | | 3 | 203.0.113.3 | ONLINE | +--------------+-------------+--------+ 6 rows in set (0.01 sec)

В таблице результатов каждый сервер отображается дважды: один раз для идентификаторов групп 2 и 3 группы хостов (что указывает на то, что все три ноды имеют состояния writer и reader). Все ноды отмечены как ONLINE, то есть они готовы к использованию.

Однако прежде чем начать работу с ними, нужно настроить учетные данные пользователя для доступа к базам данных MySQL на каждой ноде.

6: Создание пользователя MySQL

ProxySQL работает как балансировщик нагрузки. конечные пользователи подключаются к ProxySQL, а ProxySQL передает подключение выбранной им ноде MySQL. Чтобы получить доступ к конкретной ноде, ProxySQL повторно использует учетные данные, к которым был осуществлен доступ.

Чтобы разрешить доступ к базам данных, расположенным на нодах репликации, нужно создать учетную запись пользователя с теми же учетными данными, что и ProxySQL, и предоставить этому пользователю необходимые привилегии.

Как и в шаге 3, все следующие действия можно выполнить только на одной ноде из группы.

Создайте нового пользователя playgrounduser и установите ему пароль playgroundpassword:

CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

Передайте ему права на тестовую БД playground из мануала Создание группы репликации MySQL в Ubuntu 16.04.

GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

Сбросьте привилегии и закройте командную строку.

FLUSH PRIVILEGES; EXIT;

Вы можете убедиться, что пользователь был создан правильно, попробовав подключиться к БД с новыми учетными данными непосредственно на ноде.

Откройте интерфейс MySQL как новый пользователь и введите пароль.

mysql -u playgrounduser -p

Выполните тестовый запрос к БД playground:

SHOW TABLES FROM playground; +----------------------+ | Tables_in_playground | +----------------------+ | equipment | +----------------------+ 1 row in set (0.00 sec)

Список таблиц подтверждает, что пользователь был правильно создан на всех нодах.

Теперь вы можете отключиться от интерфейса MySQL, но держите терминал с подключением к серверу открытым – нужно использовать его для тестирования на последнем этапе.

EXIT;

7: Создание пользователя ProxySQL

Последний шаг в конфигурации – разрешить соединения ProxySQL с пользователем playgrounduser и передать эти соединения нодам.

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

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('playgrounduser', 'playgroundpassword', 2);

Переместите конфигурацию в runtime и сохраните на disk:

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

Чтобы убедиться, что вы можете подключиться к нодам, используя эти учетные данные, откройте другое окно терминала и подключитесь по SSH к серверу ProxySQL.

ProxySQL прослушивает входящие клиентскихе подключения по порту 6033, поэтому попробуйте подключиться к реальной базе данных (а не к интерфейсу администратора) с помощью playgrounduser и порта 6033. Вам будет предложено ввести пароль playgroundpassword.

mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '

Здесь командная строка будет выглядеть как ProxySQLClient>, чтобы вы могли отличить ее от командной строки интерфейса администратора. При тестировании окончательной конфигурации нужно будет использовать обе эти командные строки.

Командная строка появится, что означает, что учетные данные были приняты ProxySQL.

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ProxySQLClient>

Выполните простой оператор, чтобы проверить, будет ли ProxySQL подключаться к одной из нод. Эта команда запрашивает в базе данных имя хоста сервера, на котором она запущена, и возвращает его.

SELECT @@hostname;

Согласно конфигурации, этот запрос должен быть перенаправлен ProxySQL на одну из трех нод в группе хостов writer. Результат должен выглядеть следующим образом: member1 – имя хоста одной из нод MySQL.

+------------+ | @@hostname | +------------+ | member1 | +------------+ 1 row in set (0.00 sec)

Теперь ProxySQL может распределять запросы между нодами.

Осталось только убедиться, что ProxySQL может выполнять операции чтения и записи в базе данных и обрабатывать запросы, даже если некоторые ноды выходят из строя.

8: Проверка конфигурации ProxySQL

Теперь нужно проверить, что права доступа к БД позволяют выполнять операции чтения и записи из ProxySQL, и что эти операторы будут выполняться, даже когда некоторые ноды не работают.

Выполните SELECT в командной строке клиента ProxySQL, чтобы убедиться, что вы можете прочитать данные из БД playground.

SELECT * FROM playground.equipment;

Результат должен содержать три элемента, созданные в предыдущем мануале. Это означает, что данные читаются из базы данных MySQL через ProxySQL.

+----+--------+-------+--------+ | id | type | quant | color | +----+--------+-------+--------+ | 3 | slide | 2 | blue | | 10 | swing | 10 | yellow | | 17 | seesaw | 3 | green | +----+--------+-------+--------+ 3 rows in set (0.00 sec)

Теперь попробуйте записать данные в таблицу:

INSERT INTO playground.equipment (type, quant, color) VALUES ("drill", 5, "red");

Затем заново выполните предыдущую команду SELECT, чтобы убедиться, что данные были добавлены.

SELECT * FROM playground.equipment;

Если в выводе появилась новая строка, это означает, что данные успешно записаны в базу данных MySQL через ProxySQL.

+----+--------+-------+--------+ | id | type | quant | color | +----+--------+-------+--------+ | 3 | slide | 2 | blue | | 10 | swing | 10 | yellow | | 17 | seesaw | 3 | green | | 24 | drill | 5 | red | +----+--------+-------+--------+ 4 rows in set (0.00 sec)

Вы знаете, что ProxySQL теперь может полностью использовать базу данных, но что случится, если на сервере произойдет сбой?

Из командной строки одного из серверов MySQL остановите процесс MySQL, чтобы имитировать сбой.

systemctl stop mysql

Когда БД прекратит работу, попробуйте снова запросить данные из таблицы equipment через ProxySQL:

SELECT * FROM playground.equipment;

Вывод не должен измениться; вы все равно должны увидеть список из таблицы, как и раньше. Это означает, что ProxySQL заметил, что одна из нод отказала, и переключился на другую, чтобы выполнить запрос.

Это можно проверить, запросив таблицу runtime_mysql_servers из командной строки администратора ProxySQL.

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers; +--------------+-------------+---------+ | hostgroup_id | hostname | status | +--------------+-------------+---------+ | 1 | 203.0.113.1 | SHUNNED | | 2 | 203.0.113.2 | ONLINE | | 2 | 203.0.113.3 | ONLINE | | 3 | 203.0.113.2 | ONLINE | | 3 | 203.0.113.3 | ONLINE | +--------------+-------------+---------+ 6 rows in set (0.01 sec)

Остановленная нода находится в состоянии shunned, а это значит, что она временно недоступна, потому трафик распределяется между двумя оставшимися нодами.

ProxySQL будет постоянно следить за состоянием этой ноды и вернет ее в состояние онлайн, если она начнет вести себя нормально, или пометит ее как offline, если она превысит значение, установленное в разделе 4.

Давайте проверим это поведение. Вернитесь на сервер MySQL и восстановите ноду.

systemctl start mysql

Подождите немного, а затем снова запустите таблицу runtime_mysql_servers из командной строки ProxySQL.

ProxySQL быстро заметит, что нода снова доступна и пометит ее как online:

Вы можете повторить этот тест на другой ноде и убедиться, что если хотя бы одна нода работает, вы сможете получить доступ к БД.

Заключение

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

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

Tags: MySQL, ProxySQL, Ubuntu 16.04

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.