tech-tips/Программное обеспечение/СУБД/MySQL - MariaDB/Master-slave репликация MySQL 5.7.md

---
source: https://axenov.dev/шпаргалка-master-slave-репликация-mysql-5-7/
tags:
    - "репликация"
---

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

### Master

Настраиваем кофиг

```SQL
[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 # путь к бинлогу
# ...
```

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

```shell
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

Настраиваем кофиг

```
[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 # путь к индексу лога
# ...
```

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

```shell
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> exit # выходим из мускуля
```

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

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

```SQL
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. Расхождение слейва с мастером**

История:

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; # и на мастере
```

Повторяем шаги, описанные выше:

- блокировка мастера
- снимаем дамп БД
- смотрим `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`
- В теории, если обе базы идентичны и актуальны:
    - Остановить слейв и мастер
    - Отключить запись в реплицируемые базы
    - Поменять местами параметры репликации, заданные в конфигах мастера и слейва, закомментировать лишние, обратить внимание на значения
    - Следовать инструкциям как поднять репликацию с нуля, описанным выше, исключая шаг с созданием дампа