Изменение структуры таблиц в кластере MySQL sync с репликами

Чтобы изменить структуру таблицы в кластере MySQL sync с репликами, рекомендуем использовать утилиту pt-online-schema-change из пакета Percona Toolkit. Эта утилита меняет структуру таблицы без блокировки операций чтения и записи.

Мы не рекомендуем использовать стандартную команду ALTER TABLE в кластерах MySQL sync с репликами. Она может вызвать ограничения в работе кластера:

таблица в мастер-ноде будет недоступна на время изменения структуры;
реплики могут отставать от мастер-ноды, возможна остановка репликации.

Принцип работы pt-online-schema-change

С требованиями, ограничениями и известными ошибками при использовании утилиты можно ознакомиться в инструкции pt-online-schema-change документации Percona Toolkit.

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

Утилита pt-online-schema-change меняет структуру не в оригинальной таблице, а в ее копии. Копия становится новой версией таблицы. Из-за этого оригинальная таблица не блокируется, а операции чтения и записи в ней остаются доступными.

При запуске утилита создает пустую копию оригинальной таблицы, изменяет ее структуру и затем построчно копирует данные из оригинальной таблицы в новую. Для безопасности данных утилита по умолчанию не применяет изменения в таблице, если вы не укажете параметр --execute. Подробнее о параметре в подразделе Основные параметры для изменения таблицы в кластере MySQL sync.

Для синхронизации данных между таблицами утилита создает триггеры — это позволяет при любых изменениях данных в оригинальной таблице автоматически подтягивать их в новую. Если до запуска утилиты в оригинальной таблице уже были определены какие-либо триггеры, утилита работать не будет. Подробнее о триггерах в инструкции Using Triggers документации MySQL.

Когда все данные скопированы, утилита одновременно переименовывает оригинальную и новую таблицы. Затем заменяет оригинальную таблицу на новую и удаляет оригинальную таблицу.

Особенности работы утилиты при использовании внешних ключей

Внешние ключи в MySQL позволяют ссылаться на данные в других таблицах, поддерживая связи между ними. Подробнее о внешних ключах в инструкции Using Foreign Keys документации MySQL.

Если на изменяемую таблицу ссылаются внешние ключи:

утилита не сможет изменить таблицу без параметра --alter-foreign-keys-method. Он необходим, чтобы внешние ключи корректно указывали на новую таблицу. Подробнее о параметре в подразделе Основные параметры для изменения таблицы в кластере MySQL sync;
имена внешних ключей в новой таблице будут изменены — например, constraint_name станет _constraint_name. MySQL не позволяет использовать одинаковые имена ключей в одной базе данных. Утилита добавляет символ _, чтобы избежать конфликта имен.

Изменить структуру таблицы

Установите пакет Percona Toolkit.
Убедитесь, что есть бэкап кластера и он работает. Бэкап позволит восстановить кластер в случае потери данных из-за возможного сбоя утилиты.
Если в таблице уже есть какие-либо другие триггеры, удалите их. Вы можете сохранить определения триггеров перед удалением и восстановить триггеры после изменения таблицы. Подробнее о триггерах в инструкции Using Triggers документации MySQL.
Откройте CLI.
Измените структуру таблицы:
```
pt-online-schema-change \
  h=<host>,P=<port>,u=<database_user_name>,p=<password>,D=<database_name>,t=<table> \
  --alter "<command>" \
  --recursion-method=none \
  --execute \
  <options>
```
Укажите:
- параметры DSN для подключения к кластеру:
  - <host> — DNS- или IP-адрес ноды;
  - <port> — порт для подключения;
  - <database_user_name> — имя пользователя базы данных;
  - <password> — пароль пользователя базы данных;
  - <database_name> — имя базы данных;
  - <table> — имя таблицы;
- параметры для изменения таблицы:
  - <command> — команда для изменения таблицы;
  - --recursion-method=none — параметр для отключения поиска реплик;
  - --execute — параметр для применения изменений в таблице;
  - опционально: <options> — дополнительные параметры для изменения таблицы.

Основные параметры DSN для подключения к кластеру MySQL sync

Параметры DSN (Data Source Name) — параметры для подключения к кластеру.

Формат записи параметров:

каждый параметр указывается в виде параметр=значение — например p=password;
параметры чувствительны к регистру. Например, P и p — разные параметры;
до и после = не должно быть пробелов. Если значение содержит пробелы — заключите его в кавычки;
параметры разделяются запятыми.

Мы указали основные параметры DSN для подключения к кластеру MySQL sync. Полный список параметров указан в разделе DSN OPTIONS инструкции pt-online-schema-change документации Percona Toolkit.

`D` (`database`)	Имя базы данных
`h` (`host`)	DNS- или IP-адрес ноды
`p` (`password`)	Пароль пользователя базы данных. Если пароль содержит запятые, экранируйте их обратным слешем или заключите всё значение в кавычки
`P` (`port`)	Порт для подключения к кластеру
`t` (`table`)	Имя таблицы
`u` (`user`)	Имя пользователя базы данных

Основные параметры для изменения таблицы в кластере MySQL sync

Мы указали основные параметры pt-online-schema-change для изменения таблицы в кластере MySQL sync. Полный список параметров pt-online-schema-change и их описание указаны в разделе OPTIONS инструкции pt-online-schema-change документации Percona Toolkit.

--alter	Изменяет структуру таблицы, используя синтаксис `ALTER TABLE`, но без ключевых слов `ALTER TABLE`. Чтобы внести несколько изменений в таблицу, укажите их через запятую. Подробнее о синтаксисе `ALTER TABLE` в инструкции ALTER TABLE Statement документации MySQL. Для параметра установлены ограничения. При их несоблюдении в работе утилиты могут быть сбои: таблица должна иметь `PRIMARY KEY` или `UNIQUE INDEX`, чтобы триггер `DELETE` корректно синхронизировал новую таблицу с оригинальной; в `DROP FOREIGN KEY constraint_name` указывайте `_constraint_name` вместо реального имени `constraint_name`. Это необходимо, потому что утилита добавляет символ `_` в имена внешних ключей, чтобы избежать конфликта имен при копировании таблицы; не используйте `RENAME` для переименования таблиц; не переименовывайте столбцы, иначе данные не будут скопированы; не добавляйте столбцы с `NOT NULL` без явного значения по умолчанию
--alter-foreign-keys-method	Определяет, как утилита обрабатывает внешние ключи
--dry-run	Проверяет возможность изменения таблицы, но не вносит реальных изменений в структуру и данные. Рекомендуем использовать этот параметр, чтобы безопасно протестировать процесс изменения таблицы перед запуском утилиты с параметром `--execute`. Использование параметров `--dry-run` и `--execute` вместе невозможно
--execute	Применяет изменения к таблице. Без этого параметра утилита только проверяет возможность безопасного изменения таблицы и завершает работу. Использование параметров `--dry-run` и `--execute` вместе невозможно
--recursion-method	Определяет метод поиска реплик в кластере. Параметр обязателен для работы утилиты в кластерах MySQL Sync. Указывайте только значение `--recursion-method=none`. Использование других значений или отсутствие параметра могут привести к сбою в работе утилиты

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

Добавить новый столбец в таблицу

pt-online-schema-change \
    h=host,P=6033,u=database_user_name,p=password,D=database_name,t=table \
    --alter "ADD COLUMN new_column INT" \
    --execute \
    --recursion-method=none

Здесь:

h=host — DNS- или IP-адрес ноды;
P=6033 — порт для подключения;
u=database_user_name — имя пользователя базы данных;
p=password — пароль пользователя базы данных;
D=database_name — имя базы данных;
t=table — имя таблицы;
--alter "ADD COLUMN new_column INT" — команда для добавления столбца в таблицу;
--execute — параметр для применения изменений в таблице;
--recursion-method=none — параметр для отключения поиска реплик.

Изменить механизм хранения данных в таблице на InnoDB

pt-online-schema-change \
    h=host,P=6033,u=database_user_name,p=password,D=database_name,t=table \
    --alter "ENGINE=InnoDB" \
    --execute \
    --recursion-method=none

Здесь:

h=host — DNS- или IP-адрес ноды;
P=6033 — порт для подключения;
u=database_user_name — имя пользователя базы данных;
p=password — пароль пользователя базы данных;
D=database_name — имя базы данных;
t=table — имя таблицы;
--alter "ENGINE=InnoDB" — команда для изменения механизма хранения данных в таблице;
--execute — параметр для применения изменений в таблице;
--recursion-method=none — параметр для отключения поиска реплик.