Быстрое устранение распространенных сбоев репликации MySQL

Сбои репликации MySQL легче исправить, если разделить два вопроса: может ли реплика получать события от источника и может ли она применять уже полученные события? Это разные сбои. Проблема с сетью, отсутствующий бинарный журнал, неверный пароль или неправильная привилегия хоста обычно останавливают поток ввода-вывода. Дублирующийся ключ, отсутствующая строка, несоответствие DDL или расхождение данных обычно останавливают поток SQL.

Начните с вывода статуса. В современном MySQL:

SHOW REPLICA STATUS\G

В старых системах:

SHOW SLAVE STATUS\G

Используйте ту команду, которую поддерживает ваш сервер. В новом выводе используются такие имена, как Replica_IO_Running, Replica_SQL_Running и Seconds_Behind_Source. В старом выводе используются Slave_IO_Running, Slave_SQL_Running и Seconds_Behind_Master.

Первое, что стоит прочитать:

• Replica_IO_Running: подключена ли реплика и читает ли бинарные журналы источника.
• Replica_SQL_Running: применяет ли реплика события журнала ретрансляции.
• Last_IO_Errno и Last_IO_Error: почему не удалось получить данные.
• Last_SQL_Errno и Last_SQL_Error: почему не удалось применить.
• Relay_Master_Log_File, Exec_Master_Log_Pos или более новые поля позиции источника: где находится реплика в потоке.

Не переходите сразу к исправлению. Сначала скопируйте полный вывод статуса в свои заметки об инциденте. Как только вы выполните RESET REPLICA, пропустите транзакцию или перенаправите реплику, часть лучших доказательств исчезнет.

Если поток ввода-вывода остановлен

Когда Replica_IO_Running равно No, реплика не может успешно читать с источника. Поток SQL может еще некоторое время применять старые события журнала ретрансляции, но в конечном итоге они закончатся.

Распространенные причины:

• Неверный хост или порт источника.
• Брандмауэр, группа безопасности или правило маршрутизации блокируют соединение.
• Неверный пароль пользователя репликации.
• Пользователю репликации разрешено подключаться с другого хоста, чем тот, который фактически использует реплика.
• Бинарное журналирование отключено на источнике.
• Источник удалил файл бинарного журнала, который запрашивала реплика.
• Изменились настройки TLS, и реплика больше не может аутентифицироваться.

Проверьте с хоста реплики:

mysql -h source-db.example.com -u repl_user -p

Если прямой вход не удается, репликация также не удастся. Проверьте учетную запись на источнике:

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

Учетной записи требуется привилегия REPLICATION SLAVE. Имя привилегии по-прежнему использует "SLAVE" в привилегиях MySQL.

Также проверьте, включено ли бинарное журналирование:

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

В новых версиях может быть доступна команда SHOW BINARY LOG STATUS. Суть та же: источник должен иметь бинарные журналы, и запрошенный файл все еще должен существовать.

Ошибка 1236: Отсутствующий или нечитаемый бинарный журнал

Last_IO_Errno: 1236 — одна из ошибок, которая обычно означает, что реплика запрашивает файл бинарного журнала или позицию, которую источник не может предоставить. Точное сообщение может различаться. Оно может сообщать, что первый файл журнала не найден, событие журнала не может быть прочитано или источник закрыл соединение во время чтения.

Наиболее распространенный операционный случай прост: реплика была отключена слишком долго, и источник удалил необходимые ей бинарные журналы.

Проверьте, какие журналы остались на источнике:

SHOW BINARY LOGS;

Затем сравните этот список с файлом, указанным в статусе реплики. Если реплике нужен mysql-bin.000120, а источник теперь начинается с mysql-bin.000140, реплика не сможет догнать по бинарным журналам.

У вас есть три реалистичных варианта:

• Восстановить или перестроить реплику из свежей резервной копии, взятой с источника.
• Использовать другую реплику, у которой все еще есть необходимые данные, в качестве источника клонирования, если ваш процесс это поддерживает.
• Если используется GTID и отсутствующие транзакции существуют в другом месте, перенастроить с действительного источника, который может их предоставить.

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

После восстановления увеличьте срок хранения бинарных журналов, если позволяет место на диске:

[mysqld]
binlog_expire_logs_seconds=604800

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

Если поток SQL остановлен

Когда Replica_SQL_Running равно No, реплика получила события, но не смогла применить одно из них. Это часто проблема согласованности данных, а не проблема соединения.

Прочитайте полное Last_SQL_Error. Оно обычно сообщает таблицу, ключ, неудачную операцию, а иногда и позицию журнала источника. Затем проверьте соответствующую строку как на источнике, так и на реплике, прежде чем что-либо менять.

Для неудачного события вокруг известной позиции бинарного журнала mysqlbinlog может показать событие:

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

Если бинарные журналы источника не находятся на локальном хосте, используйте удаленные опции или проверьте скопированный файл журнала. Будьте осторожны с событиями на основе строк: для их чтения могут потребоваться опции декодирования и метаданные таблицы.

Ошибка 1062: Дублирующаяся запись

Last_SQL_Errno: 1062 означает, что реплика попыталась вставить или обновить строку и наткнулась на уникальный ключ, который уже существует.

Типичные причины:

• Кто-то напрямую записал данные в реплику.
• Реплика была инициализирована из неверного снимка.
• Предыдущая ошибка репликации была пропущена.
• Неверные настройки автоинкремента в многопоточной или активно-активной архитектуре.
• Приложение по ошибке записывало данные на два сервера с возможностью записи.

Заманчивое исправление:

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

В старом синтаксисе используется STOP SLAVE и START SLAVE. Это может быть приемлемо для одноразовой реплики отчетности после подтверждения, что строка не важна. Это опасно для реплики, которая может быть повышена позже. Пропуск означает, что реплика больше не имеет той же истории транзакций, что и источник.

Более безопасный процесс:

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

Если реплика предназначена для аварийного переключения, перестройка часто чище, чем ручное исправление нескольких неизвестных различий.

Ошибка 1032: Невозможно найти запись

Last_SQL_Errno: 1032 обычно означает, что реплика попыталась обновить или удалить строку, которая не существует локально. Это зеркальное отражение многих проблем с дублирующимися ключами. У источника была строка; у реплики ее не было.

Распространенные причины:

• Строка была удалена вручную на реплике.
• Предыдущая транзакция была пропущена.
• Первоначальный дамп пропустил данные.
• Фильтры репликации исключили более ранние записи.

Не предполагайте, что отсутствующая строка безвредна. Если UPDATE не может найти строку, реплика уже отличается от источника. Сравните количество и выборочные данные вокруг затронутого ключа. Если таблица небольшая, перезагрузка таблицы может быть разумной. Если она большая или критическая, используйте инструмент проверки согласованности или перестройте реплику.

Проблемы с аутентификацией и привилегиями хоста

Очень распространенный сбой после смены пароля или сетевых изменений — ошибка ввода-вывода, которая выглядит как отказ в доступе:

Access denied for user 'repl_user'@'10.0.2.15'

Хост в ошибке — это тот, который видит MySQL. Он может не совпадать с ожидаемым именем хоста, особенно при использовании NAT, прокси или контейнерных сетей.

На источнике проверьте пользователей:

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

Если реплика подключается с 10.0.2.15, привилегия для 'repl_user'@'replica.internal' может не совпадать, если разрешение имен и привилегии не согласованы. Предпочитайте явные шаблоны хостов, соответствующие вашей сетевой архитектуре.

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

Проблемы с журналом ретрансляции

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

Сначала проверьте состояние диска и свободное место. Полный диск может вызвать несколько странных симптомов репликации:

df -h
iostat -xz 1

Если журнал ретрансляции поврежден, но на источнике все еще есть необходимые бинарные журналы, вы часто можете сбросить журналы ретрансляции и позволить реплике загрузить их снова. Точная команда зависит от версии и топологии. Не выполняйте команды сброса небрежно; убедитесь, что вы знаете файл и позицию журнала источника, которые уже были выполнены.

Во многих случаях такая проблема является признаком того, что на хосте реплики была основная проблема с хранилищем. Исправьте ее, прежде чем снова доверять реплике.

Отставание репликации — это не всегда сбой

Seconds_Behind_Source может быть высоким, пока оба потока работают. Это означает, что репликация жива, но отстает. Относитесь к отставанию иначе, чем к остановленному потоку.

Проверьте:

• Насыщен ли диск реплики?
• Генерирует ли источник всплеск записи?
• Конкурируют ли длинные чтения на реплике с потоком SQL?
• Меньше или медленнее ли реплика по сравнению с источником?
• Началась ли задача резервного копирования или создания снимка в то же время?

Если отставание уменьшается, реплика догоняет. Если отставание растет, снизьте нагрузку или добавьте ресурсы. Перезапуск отстающей реплики редко исправляет устойчивое узкое место ресурсов.

Фильтры и многопоточная репликация

Фильтры репликации могут затруднить чтение ошибок. Реплика может намеренно игнорировать некоторые базы данных или таблицы, но приложение все равно может ожидать, что связанные данные существуют. Если вы используете фильтры, проверьте их, прежде чем предполагать, что реплика повреждена:

SHOW REPLICA STATUS\G

Ищите поля, которые упоминают Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table или правила перезаписи. В старом выводе используются те же общие имена в SHOW SLAVE STATUS.

Фильтрация особенно рискованна при записях, затрагивающих несколько баз данных. Если транзакция обновляет app.orders и audit.order_events, но реплика фильтрует audit, результирующая копия может быть технически согласована с фильтром, но бесполезна для рабочего процесса, ожидающего строки аудита. Журналирование на основе операторов может сделать фильтры баз данных еще более неожиданными, потому что выбранная база данных по умолчанию может влиять на то, реплицируется ли событие.

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

SHOW REPLICA STATUS\G

В настройках на основе каналов вывод статуса включает имя канала. Исправьте неисправный канал, не сбрасывая здоровые каналы. Если два источника могут записывать перекрывающиеся ключи в одну и ту же таблицу, ошибки дублирования ключей часто являются проблемой проектирования, а не разовым сбоем репликации.

Избегайте скрытого расхождения данных

Худший сбой репликации — это тот, который говорит Yes, но все еще содержит неверные данные. Расхождение может произойти после пропущенных транзакций, прямой записи в реплики, неудачных импортов, плохих фильтров или ручных исправлений.

Для важных реплик планируйте проверки согласованности. Percona Toolkit pt-table-checksum обычно используется для этого, а pt-table-sync может помочь исправить различия в контролируемых ситуациях. Эти инструменты могут создавать нагрузку, поэтому сначала протестируйте их и запускайте с ограничениями, соответствующими вашей производственной среде.

Также защитите реплики от случайных записей:

[mysqld]
read_only=ON
super_read_only=ON

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

Быстрый контрольный список инцидентов

Используйте этот порядок при сбое репликации:

1. Сохраните вывод SHOW REPLICA STATUS\G.
2. Проверьте, остановлен ли поток ввода-вывода или поток SQL.
3. Прочитайте Last_IO_Error или Last_SQL_Error; не полагайтесь только на номер ошибки.
4. Проверьте журнал ошибок MySQL на наличие соответствующих временных меток.
5. Для сбоев ввода-вывода проверьте сеть, учетные данные, привилегии, TLS и доступность бинарных журналов.
6. Для сбоев SQL проверьте затронутую строку или таблицу как на источнике, так и на реплике.
7. Решите, следует ли исправить, пропустить с документированным риском, перезагрузить таблицу или перестроить реплику.
8. После восстановления выполните реальный тест записи и отслеживайте отставание.

Большинство сбоев репликации MySQL не решаются одной волшебной командой. Они решаются путем сохранения доказательств, определения того, какой поток вышел из строя, и выбора исправления, которое не оставит вас с репликой, которая работает, но не заслуживает доверия.