Брайн Лай. Репликация MySQL - пропущенные GTID и как их исправить

Это перевод статьи: Brian Lie. MySQL replication - Skipped GTID and how to fix it

Содержание

  1. Содержание
  2. Введение
  3. Первоначальная причина
  4. Почему нельзя оставлять пропущенные GTID
  5. Дополнительная информация - почему сложилась такая ситуация
  6. Исправление пропущенных GTID: Вставка пустых транзакций

Введение

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

Однажды при запуске

SHOW SLAVE STATUS\G

на ведомом сервере в поле "Executed_Gtid_set" отображаются пропущенные глобальные идентификаторы транзакций (GTID):

mysql> SHOW SLAVE STATUS\G
...
Retrieved_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:40873-44214
Executed_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:1-40872:40874-44210:44212-44214
...

По приведённой выше информации, 8e731569-c5f5-11ea-9329-42010a282334 - это универсальный уникальный идентификатор (UID) ведущего сервера на ведомом сервере.

В поле "Executed_Gtid_Set" можно заметить отсутствие номеров 40873 и 44211. Это пропущенные GTID.

Первоначальная причина

Используется опция

slave-skip-errors = 1396,1133

для пропуска двух ошибок, которые часто приводят к остановке репликации с ведущего сервера на ведомый:

  • Ошибка с кодом 1396 - Operation ALTER USER failed for <пользователь MySQL>: изменение пользователя MySQL на ведущем сервере.
  • Ошибка с кодом 1133 - Can’t find any matching row in the user table: тоже изменение пользователя MySQL на ведущем сервере.

Опция slave-skip-errors позволяет сохранить исправность репликации на ведомом сервере, несмотря на появление на ведущем сервере выражений для изменения его внутренних пользователей, но ценой того, что события по изменению пользователей просто не применяются (пропускаются) и записываются на ведомом сервере в поле "множество выполненных GTID" как пропущенные GTID.

Почему нельзя оставлять пропущенные GTID

Ещё один пример "SHOW SLAVE STATUS" с пропущенными GTID:

mysql> show slave status\G 
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: c3505d05-c33d-11e9-84c7-42010a2800e3:296887615-474297615
Executed_Gtid_Set: c3505d05-c33d-11e9-84c7-42010a2800e3:1-296889515:296889517-439227589:439227591-474297615
Auto_Position: 1 
...

Из указанного выше примера можно увидеть, что поток ввода-вывода (Slave_IO_Running) и поток SQL (Slave_SQL_Running) работают исправно.

Но при перезапуске виртуальной машины или если пользователь перезапустит сервис вручную командой "sudo systemctl restart mysql", репликация на ведомом сервере сломается.

mysql> show slave status\G
...
Slave_IO_Running: No
Slave_SQL_Running: Yes
...
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
Last_SQL_Errno: 0
Last_SQL_Error:
...
Retrieved_Gtid_Set:
Executed_Gtid_Set: c3505d05-c33d-11e9-84c7-42010a2800e3:1-296889515:296889517-439227589:439227591-474297615
Auto_Position: 1
...

Это происходит потому, что после запуска MySQL на ведомом сервере поток ввода-вывода связывается с ведущим сервером, получает информацию о выполненном множестве GTID на ведущем сервере и сравнивает его с собственным выполненным множеством GTID (которое доступно через глобальную переменную gtid_executed, доступную только для чтения). Поток ввода-вывода запросит у ведущего сервера результат сравнения последовательностей GTID.

Пропущенные GTID не вызывают проблем, если они есть на ведущем сервере в текущих журналах репликации, но если в журнале репликации GTID уже удалён и событие, связанное с ним больше нельзя получить, то поток ввода-вывода возвращает ошибку 1236.

В примере выше ошибка вызвана событием, связанным с GTID с номером последовательности 296889516 (в полном виде - c3505d05-c33d-11e9–84c7–42010a2800e3:296889516), которое больше не существует на ведущем сервере. Журнал репликации с этим событием на ведущем сервере был очищен (устарел).

Дополнительная информация - почему сложилась такая ситуация

  • Для настройки конвейера данных нужно сначала снять резервную копию базы данных (разово), а затем отслеживать изменения базы данных в реальном времени (непрерывно).
  • Резервное копирование базы данных с ведущего сервера оказывает значительное влияние на производительность. Поэтому стоит читать реплику.
  • Но журналы репликации CloudSQL MySQL (где можно отслеживать поток изменений базы данных в реальном времени) можно включить только на ведущем сервере https://cloud.google.com/sql/docs/mysql/replication/create-replica → "Примечание: Все упоминания журнала репликации в этом документе подразумевают журнал на ведущем сервере. CloudSQL не поддерживает журналы репликации на ведомых серверах". Поэтому создадим внешнюю реплику для чтения.
  • Google запрещает пользователям CloudSQL делать резервную копию (экспорт) системной схемы mysql https://cloud.google.com/sql/docs/mysql/import-export/exporting#export-mysqldump → "Нужно явным образом указать список баз данных для экспорта и в этом списке не должно быть системной базы данных mysql." Поэтому на внешней реплике не будет набора пользователей с ведущего сервера.
  • Выражения DDL в MySQL для управления пользователями (например, выражения ALTER USER) записываются в журнал репликации с собственным GTID и отправляются на все ведомые узлы. Вот почему внешние реплики для чтения говорят "О, нет ☹️", когда на ведущем сервере в CloudSQL изменяется пользователь. Реплика принимает с ведущего сервера, например, выражение "ALTER USER x", но не может найти пользователя x в своей внутренней базе данных и останавливает репликацию из-за ошибки.
  • Первым делом, чтобы предотвратить остановку внешней реплики при обновлении пользователя на ведущем сервере CloudSQL, добавим в конфигурацию MySQL опцию slave-skip-errors = 1396,1133 для пропуска двух самых часто встречающихся ошибок, приводящих к поломке репликации:
    • Ошибка с кодом 1396 - Operation ALTER USER failed for <пользователь MySQL>: изменение пользователя MySQL на ведущем сервере.
    • Ошибка с кодом 1133 - Can’t find any matching row in the user table: тоже изменение пользователя MySQL на ведущем сервере.
  • Настройка опции slave-skip-errors предохраняет репликацию на ведомом сервере от поломок из-за выражений, изменяющих внутренних пользователей на ведущем сервере. Но это происходит за счёт того, что события по изменению пользователей просто не применяются (пропускаются) на ведомом сервере и записываются на ведомом сервере в "выполненный набор GTID" как пропущенные GTID.

Исправление пропущенных GTID: Вставка пустых транзакций

Напоминание: это мем 2017 - начала 2020

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

Одну пустую транзакцию можно создать следующим образом (с помощью клиента MySQL в терминале):

begin; 
commit;

Но эта пустая транзакция свяжется с переменной gtid_next из MySQL, а без временного изменения gtid_next во множестве GITD на ведущем сервере эта пустая транзакция будет зафиксирована только на ведомом сервере в его собственном множестве GTID (отдельном от множества на ведущем сервере) и мы не сможем решить эту проблему.

Прежде всего нужно удалить опцию slave-skip-errors на ведомом сервере в конфигурации my.cnf и выполнить команду sudo systemctl restart mysql для перезапуска ведомого сервера.

Затем нужно убедиться, что события с ведущего сервера не достигнут ведомого сервера, когда значение gtid_next будет временно изменено (по умолчанию это значение AUTOMATIC, а его изменение при запущенных потоках репликации на ведомом сервере приведёт к поломке репликации). Мы можем (временно) защитить ведомый сервер от обновлений с ведущего сервера, запустив:

mysql> STOP SLAVE;

Теперь можно поменять gtid_next. Для того, чтобы узнать значение для gtid_next, сначала запустим SHOW SLAVE STATUS и узнаем пропущенные GTID из "Executed_Gtid_Set". Из предыдущего примера:

mysql> SHOW SLAVE STATUS\G 
...         
Retrieved_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:40873-45071         
Executed_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:1-40872:40874-44210:44212-44214 
...

Имеется два пропущенных GTID:

  • 8e731569-c5f5–11ea-9329–42010a282334:40873
  • 8e731569-c5f5–11ea-9329–42010a282334:44211

Для начала обработаем первую. После команды STOP SLAVE выполним:

mysql> SET gtid_next='8e731569-c5f5-11ea-9329-42010a282334:40873';
Query OK, 0 rows affected (0.00 sec)

Теперь внедрим пустую транзакцию:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Вернём gtid_next значение по умолчанию (AUTOMATIC, регистр не важен):

mysql> SET gtid_next='automatic';
Query OK, 0 rows affected (0.00 sec)

Теперь, если снова запустить SHOW SLAVE STATUS:

Retrieved_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:40873-45071
Executed_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:1-44210:44212-45062

можно заметить, что последовательность GTID 40873 в "Executed_Gtid_Set" больше не пропущена.

После повторения действий со значением следующей пропущенной последовательности GTID 44211 получим непрерывную последовательность в поле "Executed_Gtid_Set" на ведомом сервере

Retrieved_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:40873-45071
Executed_Gtid_Set: 8e731569-c5f5-11ea-9329-42010a282334:1-45062

Теперь, когда больше нет пропущенных GTID, перезапуск сервиса MySQL не вызовет поломку репликации на ведомом сервере.

Снова включаем репликацию:

mysql> START SLAVE; 
Query OK, 0 rows affected (0.00 sec)