Безопасные миграции MySQL: pt‑online‑schema‑change и gh‑ost без простоя

Зачем онлайн‑миграции и когда без них никак

Классический ALTER TABLE на перегруженной базе часто приводит к долгим блокировкам и простоям приложения. Даже если InnoDB поддерживает «online DDL», это не панацея: метаданные всё равно могут блокироваться, большие перекладки данных нагружают диск и репликацию, а на смешанной нагрузке (чтение/запись) легко поймать пики латентности. Онлайн‑миграции с «теневой» таблицей позволяют применять изменения постепенно, синхронизировать DML и выполнить короткий cut‑over без заметного простоя.

В этой статье сфокусируемся на двух де‑факто стандартах: pt-online-schema-change (Percona Toolkit) и gh-ost (GitHub). Оба инструмента надёжны на продакшене, но различаются методами синхронизации, требованиями к инфраструктуре и компромиссами по рискам.

Как это работает: два подхода

pt-online-schema-change: копия таблицы и триггеры

pt-online-schema-change (далее pt-osc) создаёт новую таблицу с целевой схемой, копирует данные партиями («чанками»), а изменения в исходной таблице ловит триггерами INSERT/UPDATE/DELETE. Когда копирование завершено, выполняется быстрый переезд имен: оригинальная таблица переименовывается в резервную, новая — подменяет оригинал. Плюсы — работает даже без реплик, достаточно прав на DDL и триггеры. Минусы — триггеры добавляют накладные расходы и совместимость с внешними ключами требует особой осторожности.

gh-ost: чтение binlog и «теневая» таблица

gh-ost строит «теневую» таблицу и наполняет её, считывая весь поток изменений из бинарного лога (обычно через реплику). DML синхронизируется по binlog, без триггеров на мастере. Это снижает накладные расходы на запись и лучше масштабируется, особенно при высокой write‑нагрузке. Но для gh-ost желательна реплика (или доступ к мастеру с правами на чтение binlog), корректный формат binlog и стабильная топология.

Предварительный чек‑лист нулевого простоя

• Первичный ключ: у таблицы должен быть стабильный монотонный PRIMARY KEY (для чанков и корректной репликации).
• Свободное место: запас на диске под копию таблицы и индексы (1.2–2.0 размера целевой таблицы в пике).
• Binlog: для gh-ost используйте формат ROW и проверьте стабильность репликации.
• Долгие транзакции: нет ли длительных BEGIN / больших batch‑операций, удерживающих версии строк и метаданные.
• Внешние ключи: оба инструмента ограничены. С FK — отдельная стратегия (см. раздел про камни).
• Триггеры и процедуры: учитывайте их влияние, особенно для pt-osc.
• Окно нагрузки: выбирайте период низкой write‑нагрузки, включайте троттлинг.
• Бэкап и план отката: горячая копия и подготовленные имена для быстрого возврата.
• Мониторинг: лаг репликации, QPS, InnoDB row lock time, IO‑латентность, CPU, сеть. Алерты на порогах.
• Совместимость версии: особенности вашей версии MySQL/MariaDB, поддержка ALGORITHM и LOCK.

Если поднимаете тестовую реплику или отдельную ноду под миграции, удобнее сделать это на VDS. После развёртывания не забудьте базовую защиту узла: SSH‑ключи, fail2ban, firewall — см. краткое руководство по безопасной настройке VDS с SSH и фаерволом.

Практика: pt-online-schema-change шаг за шагом

Сценарий: добавить колонку с дефолтом и индекс, не останавливая запись.

# Сухой прогон: проверка ограничений и плана
pt-online-schema-change --dry-run --alter "ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ADD INDEX idx_status(status)" --progress time,30 --statistics --max-load Threads_running=25 --critical-load Threads_running=64 --chunk-size 1000 --chunk-time 0.5 --check-alter --check-interval 2 --no-check-replication-filters D=mydb,t=mytable --execute

Важно: не используйте символы продолжения строки. Каждая команда — в одну строку. Для запуска без «сухого» режима уберите --dry-run. Основные флаги:

• --alter — целевой DDL.
• --chunk-size, --chunk-time — контроль размера копирования и длительности чанка.
• --max-load и --critical-load — авто‑троттлинг по статус‑метрикам.
• --progress, --statistics — прозрачность операции.
• --nocheck-unique-key-change, --check-alter — страховка от опасных изменений.

Запуск:

pt-online-schema-change --alter "ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ADD INDEX idx_status(status)" --progress time,30 --statistics --max-load Threads_running=25 --critical-load Threads_running=64 --chunk-size 1000 --chunk-time 0.5 D=mydb,t=mytable --execute

pt-osc создаст таблицу _mytable_new, добавит триггеры к mytable, скопирует данные чанками, затем выполнит короткий переезд имен с краткой блокировкой метаданных. При росте нагрузки инструмент будет дремать или уменьшать размер чанков.

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

Практика: gh-ост шаг за шагом

gh-ost подключается к мастеру или к реплике, читает binlog и синхронизирует «теневую» таблицу. Типичный сценарий — запуск через реплику, чтобы разгрузить прод‑мастер. Нужны права на чтение бинарных логов и DDL в целевой базе.

# Заполнение «теневой» таблицы без переключения (cut-over отложен флагом)
gh-ost --host=master.db.local --port=3306 --user=ghost --password=secret --database=mydb --table=mytable --alter="ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ADD INDEX idx_status(status)" --assume-rbr --max-load=Threads_running=25 --critical-load=Threads_running=64 --chunk-size=1000 --max-lag-millis=1500 --serve-socket-file=/tmp/gh-ost.sock --postpone-cut-over-flag-file=/tmp/ghost.cutover --debug --execute

Затем — полноценный запуск с переключением (или снимите флаг‑файл, если использовали отложенный cut‑over).

# Переключение по готовности (короткий cut-over)
gh-ost --host=master.db.local --port=3306 --user=ghost --password=secret --database=mydb --table=mytable --alter="ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ADD INDEX idx_status(status)" --assume-rbr --max-load=Threads_running=25 --critical-load=Threads_running=64 --chunk-size=1000 --max-lag-millis=1500 --cut-over=default --execute

• Нет триггеров — меньше накладных расходов на запись.
• Binlog — источник правды для DML; избегайте фильтров репликации, которые теряют события.
• Троттлинг — по лагу реплики, Threads_running и файлу‑флажку; безопасная пауза при росте нагрузки.
• Cut‑over — короткий метапереезд с минимальной блокировкой метаданных.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

Краеугольные камни и как их обходить

Внешние ключи (FK)

И pt-osc, и gh-ost плохо сочетаются с таблицами, участвующими во внешних ключах. Причина — финальный переезд имен и консистентность ссылок. Поведения различаются:

• pt-osc по умолчанию откажется, если видит внешние ключи. Режимы --alter-foreign-keys-method возможны, но чреваты долгими блокировками и требуют тестов.
• gh-ost также ограничен FK: либо временно убирать FK с отдельным планом, либо планировать короткое окно простоя для изменения ограничений.

Практический подход: если FK критичны, разнесите изменения на этапы — новые колонки/индексы без изменения FK, а затем ограничения в согласованное окно. Либо перенесите часть ссылочной целостности в приложение, если это укладывается в вашу политику.

Долгие транзакции и метаданные

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

AUTO_INCREMENT, ENUM, COLLATION

Изменение AUTO_INCREMENT и коллайшнов может привести к росту вторичных индексов и скачкам IO. Для ENUM предпочитайте миграцию в TINYINT/SMALLINT с мэппингом в приложении — проще индексировать и переносить между версиями.

Индексы и параллелизм

Построение больших вторичных индексов — самая дорогая часть миграций. Планируйте время, уменьшайте chunk-size, повышайте чувствительность троттлинга, давайте подсистеме хранения «дышать». При необходимости разбивайте изменения: сначала колонка, потом индекс.

Нагрузка и троттлинг

Оба инструмента умеют «слушать» базу и бережно дозировать копирование.

• pt-osc: --max-load и --critical-load по статус‑метрикам, --chunk-time для ограничения длительности чанка, --set-vars для параметров сеанса (например, innodb_lock_wait_timeout).
• gh-ost: --max-lag-millis, --max-load/--critical-load, троттлинг по файлу‑флажку и лагу реплики.

Золотое правило: сначала найдите параметры, при которых система стабильно переваривает 5–10% от пика. Пусть миграция идёт дольше, но без рывков и инцидентов.

Откат и аварийный план

Благодаря «теневым» таблицам откат чаще всего сводится к обратному переименованию. Действуйте быстро и последовательно:

• Договорённые имена резервных таблиц (например, mytable_old) для мгновенного возврата.
• Чёткие пороги отказа: лаг репликации, 95‑й перцентиль латентности, p99. Не геройствуйте.
• Горячий бэкап на случай логической ошибки в DDL.

Пример ручного возврата после cut‑over (если инструмент уже завершил переименование):

RENAME TABLE mytable TO mytable_bad, mytable_old TO mytable;
-- После стабилизации: перенесите mytable_bad в песочницу и проанализируйте расхождения

Когда что выбирать

• Нет реплик, ограниченные права, простая топология — чаще проще применить pt-osc.
• Высокая write‑нагрузка, дорогие триггеры, важна низкая накладная на DML — gh-ost предпочтительнее.
• Сложные FK — планируйте отдельную стратегию, возможно с коротким окном простоя.
• Старые версии MySQL/MariaDB — проверьте совместимость и известные баги вашей версии.
• Ограничения доступа — для gh-ost могут потребоваться права на binlog/репликацию.

Чек‑лист перед запуском в проде

• Проверить схему: PK, кандидаты в индексы, отсутствие ловушек (например, TEXT без длины индекса).
• Замерить нагрузку: QPS, долю write, средний размер строки и индексов; оценить время миграции.
• Отключить тяжёлые фоновые задачи: отчёты, импорты/экспорты.
• Настроить троттлинг, протестировать на staging с копией данных или на реплике.
• Включить алерты по лагу репликации, росту Threads_running, ошибкам в error.log.
• Подготовить команды отката, проверить свободное место и свежесть бэкапа.

FAQ: частые вопросы

Нужен ли online DDL, если есть pt-osc/gh-ost?

Да. Если ваша версия поддерживает ALGORITHM=INPLACE и LOCK=NONE для конкретного DDL, иногда достаточно нативного ALTER. Но на больших таблицах «теневые» миграции дают больше контроля.

Можно ли менять тип первичного ключа без простоя?

Это одна из самых сложных миграций. Инструменты способны перекопировать данные в новую таблицу с иной схемой PK, но риски велики, а cut‑over может занять больше времени. Разбивайте на этапы и тестируйте.

Можно ли мигрировать таблицы без PK?

Плохо. И pt-osc, и gh-ost ожидают разумный порядок строк. Добавьте технический BIGINT PK перед основной миграцией.

Как оценить время?

Замерьте скорость чанка на реплике или staging с аналогичными ресурсами, умножьте на объём, учтите паузы троттлинга и построение индексов. Заложите запас в 2–3 раза.

А если миграция «застряла»?

Смотрите метрики: лаг репликации, блокировки, медленные запросы. Уменьшите chunk-size, приглушите фоновые джобы, временно увеличьте IOPS. При необходимости поставьте на паузу и продолжите позже.

Итоги

Онлайн‑миграции MySQL без простоя реальны при дисциплине: подготовка, измерения, троттлинг и план отката. pt-online-schema-change — универсальный «рабочий осёл» для одиночного инстанса; gh-ost раскрывается в связке с репликами и высокой write‑нагрузкой. Тестируйте на копии данных, а при переносе приложений посмотрите и общие приёмы — например, переезд сайта без простоя.