Шпаргалка: Master-slave репликация MySQL 5.7

Этот пост был опубликован мной более года назад. Информация, описанная ниже, уже могла потерять актуальность, но всё ещё может быть полезна.

Введение

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

SET GLOBAL read_only = ON;  # вкл только чтение
SET GLOBAL read_only = OFF; # выкл только чтение

так и эти:

FLUSH TABLES WITH READ LOCK; # вкл только чтение
UNLOCK TABLES; # выкл только чтение

Либо импортирование БД из дампа на слейве можно выполнить и так:

$ mysql -u root -p
> USE mydb;
> SOURCE ~/mydb.sql

и эдак:

$ mysql -u root -p mydb < mydb.sql

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

Здесь же я собрал универсальный рецепт как поднять и восстановить репликацию. Поехали.

Первоначальная установка

Master

Настраиваем кофиг (my.cnf)

[mysqld]
# ...
bind-address = 0.0.0.0 # чтобы можно было достучаться до базы извне
server-id = 1 # уникальный ИД сервера
expire_logs_days = 2 # время жизни бинлогов в днях
max_binlog_size = 100M # макс размер бинлогов
binlog_do_db = mydb # БД для реплицирования ("экспорта" в бинлог)
log_bin = /var/log/mysql/mydb-bin.log # путь к бинлогу
# ...

Лезем в консоль

root@master# service mysql restart # перезапускаем mysql
user@master$ mysql -u root -p # заходим в mysql

mysql> SHOW MASTER STATUS\G # проверяем статус репликации
# запоминаем значения File и Position из результата

# далее создаём пользователя для репликации с нужными правами
mysql> CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password AS 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
mysql> USE mydb; # выбираем нашу реплицируемую БД
mysql> FLUSH TABLES WITH READ LOCK; # блокируем запись во все таблицы
mysql> \q # пока выходим

user@master$ mysqldump -u root -p mydb > mydb.sql # делаем полный дамп БД в файл
# чтобы файл дампа был легче, можно сжать его через tar:
user@master$ tar -czf mydb.tar.gz mydb.sql
# сливаем этот дамп на slave и возвращаемся сюда в консоль

user@master$ mysql -u root -p # заходим в mysql
mysql> USE mydb; # выбираем нашу реплицируемую БД
mysql> UNLOCK TABLES; # снимаем блокировки с таблиц, пусть пишется дальше
mysql> \q # выходим

Slave

Настраиваем кофиг (my.cnf)

[mysqld]
# ...
server-id = 2 # уникальный ИД сервера
master-host = <Master-IP> # адрес мастера
master-user = repl_user # юзер БД
master-password = password # пароль юзера БД
master-port = 3306 # порт mysql мастера
expire_logs_days = 2 # время жизни логов в днях
replicate_do_db = mydb # БД для реплицирования ("импорта" из лога)
relay-log = /var/lib/mysql/mydb-relay-bin # путь к логу
relay-log-index = /var/lib/mysql/mydb-relay-bin.index # путь к индексу лога
# ...

Лезем в консоль

root@slave# service mysql reload # перезапускаем mysql

# если дамп был запакован, распечатываем:
user@slave$ tar -xzf mydb.tar.gz # на выходе будет только mydb.sql

user@slave$ mysql -u root -p # заходим в mysql
mysql> CREATE DATABASE mydb; # создаём БД
mysql> USE mydb; # переходим к ней
mysql> SOURCE ~/mydb.sql # импорт БД из дампа (допустим, он лежит в дом. папке)
mysql> CHANGE MASTER TO MASTER_LOG_FILE = '<File>', MASTER_LOG_POS = <Position>; # указываем настройки мастера и доступа к нему
mysql> START SLAVE; # стартуем репликацию
mysql> \q # выходим из мускуля

Как понять, что слейв работает нормально?

В консоли MySQL запустить:

mysql> SHOW SLAVE STATUS\G

Если всё порядке, то:

Параметр Slave_IO_State = Waiting for master to send event
Параметр Slave_IO_Running = Yes
Параметр Slave_SQL_Running = Yes
Параметр Last_Errno = 0
Параметр Last_Error пустой
Чем ближе Seconds_Behind_Master к нулю, тем лучше.
Position на мастере значительно увеличивается по мере работы и может быть каким угодно большим.

Если будут какие-то ошибки, нужно обратиться к параметрам Last_Errno, Last_Error, а также к логам mysql.

Что если при репликации возникла ошибка?

Смотря что за ошибка. Я рассмотрю парочку. В конце статьи есть ещё пачка ссылок, можешь обратиться туда.

1. Расхождение слейва с мастером

История:

На слейве в одну таблицу были внесены изменения вручную, мимо репликации
В процессе репликации слейву надо выполнить операцию над этой таблицей
Из-за расхождения возникает ошибка выполнения запроса
Репликация продолжается, логи на слейве появляются и ротируются, но запросы не выполняются. Выглядит это так:
```
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running = Yes
Slave_SQL_Running = No
Last_Errno = <код ошибки>
Last_Error = <текст ошибки>
...
```

Решение:

mysql> STOP SLAVE; # стопаем слейв
mysql> RESET SLAVE; # сбрасываем состояние репликации на слейве (позиция, логи)
mysql> RESET MASTER; # ...и на мастере

Повторяем шаги, описанные выше:
- блокировка мастера
- снимаем дамп БД
- смотрим SHOW MASTER STATUS\G, запоминаем позицию (File и Position)
- разблокировка мастера
- заливка дампа на слейв
- установка позиции на слейве (File и Position)
- старт слейва

При этом перезапускать mysql нет необходимости ни на мастере, ни на слейве. На слейве нужно будет установить свежие параметры File и Position:

mysql> CHANGE MASTER TO MASTER_LOG_FILE = '<File>', MASTER_LOG_POS = <Position>;

2. Закончилось место на мастере/слейве

История:

Из-за неоптимальной настройки либо небольшого размера жёсткого диска его свободное место быстро иссякло
Логи репликации некуда писать, mysql отказывается работать в принципе либо выдаёт ошибки

Решение:

Остановить репликацию на слейве: если там ещё не возникли проблемы, то они обязательно возникнут
Удалить все бинлоги в директориях, указанных в конфигах, на том сервере, где нехватает места
Освободить максимум места на всём сервере, пересмотреть и оптимизировать использование места (ротация логов другого софта, удаление ненужных проектов, сжатие всего, что сжимается, и пр.)
Обратиться к конфигам: убедись, что параметры expire_logs_days и expire_logs_days нигде больше не переопределяются значениями, больше необходимого
Восстановить репликацию также, как в прошлой проблеме

Необходимо поменять местами Master и Slave

В теории, если обе базы идентичны и актуальны:

Остановить слейв и мастер
Отключить запись в реплицируемые базы
Поменять местами параметры репликации, заданные в конфигах мастера и слейва, закомментировать лишние, обратить внимание на значения
Следовать инструкциям как поднять репликацию с нуля, описанным выше, исключая шаг с созданием дампа

Использованные и другие полезные материалы

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