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

Использование команды ALTER TABLE в облачных базах данных MySQL может привести к остановке кластера. Вместо 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

Укажите:

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

  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) — пользователь, под которым осуществляется подключение.