В этой статье мы разберем распределение базы данных MySQL на 2 сервера, на одном мы будем выполнять операции чтения, на другом операции записи. Осуществлять мы это будем с помощью Репликации MySQL.

Автор: unix-admin.su

Горизонтальное масштабирование сервера. MySQL-сервер.

Горизонтальное масштабирование сервера. MySQL-сервер.

Этим руководством мы начинаем цикл статей о горизонтальном масштабировании сервера. Горизонтальное масштабирование — это увеличение мощности системы за счет добавления новых серверов параллельно выполняющих одну и ту же функцию() и разнесения уже имеющихся сервисов по отдельным физическим серверам.

В этой статье мы разберем распределение базы данных MySQL на 2 сервера, на одном мы будем выполнять операции чтения, на другом операции записи. Осуществлять мы это будем с помощью Репликации MySQL.

Суть техники репликации состоит в постоянном реплицировании(копировании) содержимого базы данных сервера MySQL на другой сервер(или несколько других). В случае Master-Slave репликации, данные записываются только на Master-сервер, после чего копируются на Slave, с которого осуществляется только чтение(операция SELECT). В качестве Slave-сервера может выступать один или несколько MySQL серверов.
Этот подход так же можно использовать для создания резервной копии базы данных, в этом случае при выходе из строя основного Master-сервера, Slave-сервера позволит продолжить работы, на время восстановления основного сервера.

  1. Настройка репликации Баз Данных
  2. Настройка ProxySQL

 

1.Настройка репликации MySQL

Первым делом настроим главный(мастер) сервер для репликации всех баз данных. Для дальнейшего использования mysql-proxy, поменяем порт на 3307.Настройка Master-серера репликации.

Внесем необходимые изменения в конфигурационный файл MySQL:

[mysqld]
server-id = 1
binlog-format = mixed
log-bin = mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
bind-address=_IP_ADDRESS_
port=3307

_IP_ADDRESS_ — Внешний IP-адрес сервера.

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

service mariadb restart

Создадим пользователя для осуществления репликации:

CREATE USER _USER_REPL_@_SLAVE_IP_;
GRANT REPLICATION SLAVE ON *.* TO _USER_REPL_@_SLAVE_IP_ IDENTIFIED BY ‘_USER_PASSWORD_’;
FLUSH PRIVILEGES; 

_SLAVE_REPL_ — имя пользователя для репликаций _PASSWORD_ — пароль для пользователя _SLAVE_IP_ — IP-адрес Slave-сервера (обычно — внешний IP-сервера). Далее нам нужно создать дамп всех баз Master-сервера. При выполнении дампа нельзя изменять структуру таблиц.

mysqldump --skip-lock-tables --hex-blob --single-transaction --flush-logs --master-data=2 -A > /databases_for_slave.sql

Далее переносим сделанный дамп на Slave-сервер.

scp /databases_for_slave.sql user@_SLAVE_IP_:/

Настройка подчиненного Slave-сервера.

Так же как и в настройках Master-сервера открываем коннект к серверу извне:
iptables -A INPUT -s _MASTER_IP_/32 -p tcp -m tcp —dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -m tcp —dport 3306 -j DROP

_MASTER_IP_ — Внешний IP-адрес Master-сервера.

Далее настраиваем MySQL для работы кака Slave-сервер:

[mysqld]
server-id = 2
binlog-format = mixed
log_bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

server-id — Уникальный ID сервера
read-only = 1 — этим параметром указываем MySQL-серверу, что он должен только читать данные.

Перезапускаем сервер.

service mariadb restart

Вернемся к нашему дампу, который мы перенесли в пролом шаге. Нам нужно восстановить дамп на Slave-сервер, но для начала выполним пару команд.

head /databases_for_slave.sql -n100 | grep "MASTER_LOG_FILE"
head /databases_for_slave.sql -n100 | grep "MASTER_LOG_POS"

Запишем где-нибудь результат выполнения этих команд. MASTER_LOG_FILE и MASTER_LOG_POS это имя файла и позиция в бинарном журнале, с которых Slave-сервер начнет чтение изменений с Master-сервера.

Восстанавливаем дамп на Slave-сервере:

mysql -u root -p < /databases_for_slave.sql

Включение репликации

Заходим в консоль mysql:

mysql -uroot -p

Задаем параметры мастер-сервера, командой:

CHANGE MASTER TO MASTER_HOST=_MASTER_IP_, MASTER_PORT=3307 MASTER_USER=‘_USER_REPL_’,MASTER_PASSWORD=‘_USER_PASSWORD_’, MASTER_LOG_FILE=‘_MASTER_LOG_FILE_’, MASTER_LOG_POS=_MASTER_LOG_POS_;

_MASTER_IP_ — Внешний IP-адрес Master-сервера.
_USER_REPL_ — имя пользователя для репликаций
_USER_PASSWORD_ — пароль для пользователя
_MASTER_LOG_FILE_ и _MASTER_LOG_POS_ — результат выполнения тех комманд, которые мы выполнили в шаге 1.2

Включаем репликацию:

START SLAVE;

Теперь данные должны начать синхронизироваться с Master-сервера на Slave. Статус репликации можно посмотреть командой mysql:

SHOW SLAVE STATUS \G

Slave_IO_State — Статус репликации.
Last_Error — Последняя ошибка.
Seconds_Behind_Master — Насколько копия БД на Slave-сервере отстает от Master. 0 — Копии идентичный.

2. Настройка ProxySQL

Теперь давайте сделаем так, чтобы нагрузка на наши MySQL-серверы распределялась. На Master-сервер мы будем писать и читать, Slave-сервер будет работать только на чтение.

Установка и настройка ProxySQL

Более подробно настройку читайте в одной из следующих статей. А в этой мы разберем ее кратко.
Установим пакет proxysql.

Для CentOS 7:

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

datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
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
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
}

mysql_servers =
(
{
address = "_MASTER_IP"
port = 3307
hostgroup = 0
compression = 1
}
{
address = "_SLAVE_IP"
port = 3306
hostgroup = 1
compression = 1
}
)

mysql_users:
(
{ username = "root" , password = "password" , default_hostgroup = 0 , active = 1 },
{ username = "root" , password = "password" , default_hostgroup = 1 , active = 1 },
)

mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^SELECT .* FOR UPDATE$"
destination_hostgroup=0
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=1
apply=1
}
{
rule_id=3
active=1
match_pattern=".*"
destination_hostgroup=0
apply=1
}
)

admin_variables: описывает интерфейс для подключения к административному интерфейсу.
mysql_variables: параметры обработчика SQL-запросов.
mysql_servers: описывает все серверы к которым будет подключаться ProxySQL, в наем случае их всего 2: _MASTER_IP_ и _SLAVE_IP_.
mysql_users: таблица пользователей MySQL на обоих серверах, должны описываться все пользователи на проецируемых серверах.
mysql_query_rules: Правила запросов. В зависимости от шаблона, запросы распределятся на сервера, которые находятся в конкретной hostgroup. В нашем случае вы перенаправляем все запросы «^SELECT» на Slave-сервер, кроме запроса «^SELECT .* FOR UPDATE$», а его и все остальные(«.*») на Master-сервер.

Запуск ProxySQL

Для начало инициализируем БД ProxySQL. ProxySQL хранит все настройки в собственной SQL-базе, доступ к которой можно получить обычным mysql клиентом.

proxysql —initialize

Далее запускаем сервер

service proxysql restart

Про более подрубную настройку ProxySQL можно прочитать тут