Блог Аксенова

Шпаргалка: 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

  1. Настраиваем кофиг (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 # путь к бинлогу
# ...
  2. Лезем в консоль 
    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

  1. Настраиваем кофиг (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 # путь к индексу лога
# ...
  2. Лезем в консоль 
    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
Если всё  порядке, то:
  1. Параметр Slave_IO_State = Waiting for master to send event
  2. Параметр Slave_IO_Running = Yes
  3. Параметр Slave_SQL_Running = Yes
  4. Параметр Last_Errno = 0
  5. Параметр Last_Error пустой
  6. Чем ближе Seconds_Behind_Master к нулю, тем лучше.
  7. Position на мастере значительно увеличивается по мере работы и может быть каким угодно большим.
Если будут какие-то ошибки, нужно обратиться к параметрам Last_Errno, Last_Error, а также к логам mysql.

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

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

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

История:
  1. На слейве в одну таблицу были внесены изменения вручную, мимо репликации
  2. В процессе репликации слейву надо выполнить операцию над этой таблицей
  3. Из-за расхождения возникает ошибка выполнения запроса
  4. Репликация продолжается, логи на слейве появляются и ротируются, но запросы не выполняются. Выглядит это так: 
    mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running = Yes
Slave_SQL_Running = No
Last_Errno = <код ошибки>
Last_Error = <текст ошибки>
...
Решение: 
  1. mysql> STOP SLAVE; # стопаем слейв
mysql> RESET SLAVE; # сбрасываем состояние репликации на слейве (позиция, логи)
mysql> RESET MASTER; # ...и на мастере
  2. Повторяем шаги, описанные выше:
    • блокировка мастера
    • снимаем дамп БД
    • смотрим SHOW MASTER STATUS\G, запоминаем позицию (File и Position)
    • разблокировка мастера
    • заливка дампа на слейв
    • установка позиции на слейве (File и Position)
    • старт слейва
При этом перезапускать mysql нет необходимости ни на мастере, ни на слейве. На слейве нужно будет установить свежие параметры File и Position: 
mysql> CHANGE MASTER TO MASTER_LOG_FILE = '<File>', MASTER_LOG_POS = <Position>;

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

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

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

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

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

Некоторые из них я использовал и уже пересказал выше, но там же можно найти ответ по другим вопросам можно найти ответы.
  1. Документация MySQL
  2. Основы репликации в MySQL (habr) 
  3. Как настроить MySQL Master-Slave репликацию? (ruhighload)
  4. Оптимизация репликации в Mysql (ruhighload)
  5. Как восстановить MySQL репликацию без выключения? (ruhighload)
  6. Mysql 1062 Duplicate entry for key PRIMARY при репликации
  7. НАСТРОЙКА РЕПЛИКАЦИИ MASTER-SLAVE В MYSQL (handyhost)
  8. Репликация MySQL в виде Master/Slave
  9. Битрикс: Настройка репликации MySQL, аварийное переключение slave->master

Введение

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

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

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

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

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

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

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

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

История:

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

Решение:

  1. mysql> STOP SLAVE; # стопаем слейв
    mysql> RESET SLAVE; # сбрасываем состояние репликации на слейве (позиция, логи)
    mysql> RESET MASTER; # ...и на мастере
  2. Повторяем шаги, описанные выше:
    • блокировка мастера
    • снимаем дамп БД
    • смотрим SHOW MASTER STATUS\G, запоминаем позицию (File и Position)
    • разблокировка мастера
    • заливка дампа на слейв
    • установка позиции на слейве (File и Position)
    • старт слейва

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

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

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

История:

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

Решение:

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

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

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

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

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

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

  1. Документация MySQL
  2. Основы репликации в MySQL (habr) 
  3. НАСТРОЙКА РЕПЛИКАЦИИ MASTER-SLAVE В MYSQL (handyhost)
  4. Репликация MySQL в виде Master/Slave
  5. Битрикс: Настройка репликации MySQL, аварийное переключение slave->master

Один ответ

  1. Аватар пользователя Илья
    Илья

    Ошибка в статье: сначала FLUSH TABLES WITH READ LOCK; а потом уже SHOW MASTER STATUS\G

    Ответить

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Больше записей

1C Битрикс Android API bash CentOS Delphi Docker Dropbox FOSS git gitea Google Hasee KDE Plasma Laravel Linux MySQL php PHPStorm Quake RAD Studio SSH Syncthing Ubuntu Windows Windows 10 Wordpress ВКонтакте Вейпинг Велосипед Девайсы Железо Информационная безопасность Музыка Ноутбук Перевод Разработка Реальность Резервирование Ремонт Русификация СМИ Софт Флейм Фотография и фототехника