Использование ALTER TABLE в MySQL sync

Последнее изменение: 26 февраля 2024

Использование ALTER TABLE в MySQL sync

Использование команды ALTER TABLE в облачных базах данных MySQL sync может привести к остановке кластера. Вместо ALTER TABLE мы рекомендуем использовать утилиту pt-online-schema-change от Percona.

Утилита pt-online-schema-change изменяет структуру таблицы как ALTER TABLE, но при этом не блокирует операции чтения и записи.

Подробнее об использовании pt-online-schema-change в официальной документации.

к сведению

Используйте утилиту только при наличии резервной копии.

Описание утилиты pt-online-schema-change⁠

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

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

После завершения копирования данных в новую таблицу pt-online-schema-change использует операцию RENAME TABLE и одновременно переименовывает оригинальную и новую таблицу. После по умолчанию оригинальная таблица удаляется.

Утилита создает триггеры в оригинальной таблице для обновления данных в новой — любые изменения в оригинальной таблице при копировании будут отражены в новой. Если какие-либо триггеры уже определены в таблице, утилита не будет работать.

Риски использования⁠

Внешние ключи (constraints) служат для связывания таблиц, подробнее в официальной документации MySQL.

Использование внешних ключей усложняет работу утилиты и представляет собой дополнительный риск. Когда внешние ключи ссылаются на таблицу, атомарное переименование оригинальной и новой таблицы не работает. Утилита pt-online-schema-change должна обновить внешние ключи для отсылки к новой таблице после внесения изменений в схему. Проблема решается с помощью параметра --alter-foreign-keys-method.

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

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

Установить pt-online-schema-change⁠

1. Подключитесь к базе данных.

2. Установите утилиту pt-online-schema-change одним из способов:

   • загрузите Percona Toolkit с официального сайта;

   • загрузите Percona Toolkit через командную строку с помощью команд:

     wget percona.com/get/percona-toolkit.tar.gz
     wget percona.com/get/percona-toolkit.rpm
     wget percona.com/get/percona-toolkit.deb

   • установите только утилиту:

     wget percona.com/get/pt-online-schema-change

Использование утилиты⁠

Синтаксис утилиты:

pt-online-schema-change [OPTIONS] DSN

Укажите:

• [OPTIONS] — параметры утилиты;
• DSN — параметры DSN (Data Source Name), в них указываются базы данных и таблицы.

Пример работы⁠

1. Добавьте поле в sakila.actor:

   pt-online-schema-change \
       h=<host>,P=6033,u=<user_name>,p=<password>,D=sakila,t=actor \
       --alter "ADD COLUMN c1 INT" \
       --execute \
       --recursion-method=none

   Укажите:

   • <host> — DNS- или IP-адрес ноды;
   • <user_name> — имя пользователя базы данных;
   • <password> — пароль пользователя базы данных.

2. Измените ENGINE с sakila.actor на InnoDB (чтобы выполнялся неблокирующий запрос OPTIMIZE TABLE):

   pt-online-schema-change \
       h=<host>,P=6033,u=<user>,p=<password>,D=sakila,t=actor \
       --alter "ENGINE=InnoDB" \
       --execute \
       --recursion-method=none

Параметры pt-online-schema-change⁠

Описание основных параметров утилиты pt-online-schema-change:

--alter⁠

Изменение структуры таблицы без ключевых слов ALTER TABLE. Несколько изменений разделяются запятой. Подробнее в официальной документации MySQL.

Ограничения, которые могут привести к сбоям в работе утилиты:

• в большинстве случаев в таблице должны быть PRIMARY KEY или UNIQUE INDEX. Это необходимо, потому что утилита создает триггер DELETE для поддержания новой таблицы в актуальном состоянии в процессе изменения;
• нельзя использовать RENAME для переименования таблиц;
• столбцы нельзя переименовать, иначе данные не будут скопированы из оригинальной таблицы в новую;
• создавать столбцы без указания значения DEFAULT и при этом с указанием NOT NULL нельзя, необходимо явно задавать значение DEFAULT;
• для операций вида DROP FOREIGN KEY нужно указывать _constraint_name вместо constraint_name. Из-за ограничений MySQL pt-online-schema-change при создании новой таблицы добавляет нижнее подчеркивание в имена внешних ключей.

--ask-pass⁠

Запрос пароля при подключении к MySQL.

--dry-run⁠

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

Использование --dry-run и --execute вместе невозможно.

--execute⁠

Без указания этой опции будут выполнены только предварительные проверки, изменения в структуру таблицы не будут внесены.

Использование --dry-run и --execute вместе невозможно.

--recursion-method⁠

Метод для обнаружения реплик. Подробнее о параметре в официальной документации Percona Toolkit.

Параметры DSN⁠

Каждый параметр записывается так:

параметр=значение

Пример:

p=<password>

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

Список параметров:

• A (charset) — набор символов по умолчанию;
• D (database) — имя базы данных для оригинальной и новой таблицы;
• F (mysql_read_default_file) — использовать опции по умолчанию из указанного файла;
• h (host) — адрес хоста;
• p (password) — пароль для подключения. Если пароль содержит запятые, их необходимо экранировать обратным слэшем или заключить всё значение в кавычки;
• P (port) — порт, используемый для подключения;
• S (mysql_socket) — сокет-файл, используемый для подключения;
• t (table) — таблица, в структуру которой будут внесены изменения;
• u (user) — пользователь, под которым осуществляется подключение.