Выполнение операций ALTER TABLE
Использование команды 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
необходимо подключиться к базе данных и установить утилиту. Это можно сделать несколькими способами:
- загрузить 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), в них указываются базы данных и таблицы.
Пример работы
Для добавления поля в sakila.actor
:
pt-online-schema-change h=<host>,P=6033,u=<user>,p=<password>,D=sakila,t=actor --alter "ADD COLUMN c1 INT" --execute --recursion-method=none
Для изменения 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
. Из-за ограничений MySQLpt-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
) — пользователь, под которым осуществляется подключение.