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

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

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