centos

Добавление индекса на мастер MySQL без блокировки

• 12.01.2019

Решение подсмотрел в статье на хайлоаде

pt-online-schema-change

Инструмент pt-online-schema-change решает проблему блокировок таким образом:

1. Создает таблицу с такой же структурой, как и у рабочей.
2. Создает индексы на новой таблице (а она пустая, значит все быстро).
3. Копирует данные из рабочей таблицы в новую (это долго, однако никаких блокировок).
4. Создает триггеры для синхронизации данных между рабочей и новой таблицами.
5. Заменяет рабочую таблицу на новую и удаляет старую.

Установить репозиторий Percona и percona-tools

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
yum install percona-tools -y

Сначала запустить тест

pt-online-schema-change --alter "add index some_idx (some_field)" D=test_db,t=test_tbl,h=127.0.0.1 --user root --password pwd

ОСТОРОЖНО *ЛЯ! ДЕЛАЙ БЭКАПЫ!

А теперь стартуем по-настоящему:

pt-online-schema-change --alter "add index some_idx (some_field)" D=test_db,t=test_tbl,h=127.0.0.1 --user root --password pwd --execute

Я не трогал slave на время шаманства с мастером, но если критично отставание то есть опция --max-lag, которая заставляет присматривать за slave-ом чтобы он сильно не отстал.

1> Cannot connect to h=5.123.456.78,p=...,u=root
No slaves found.  See --recursion-method if host mydb.ru has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `mydb`.`user`...
Creating new table...
Created new table mydb._user_new OK.
Altering new table...
Altered `mydb`.`_user_new` OK.
2019-01-12T16:32:51 Creating triggers...
2019-01-12T16:32:51 Created triggers OK.
2019-01-12T16:32:51 Copying approximately 3648600 rows...
Copying `mydb`.`user`:   4% 10:29 remain
Copying `mydb`.`user`:  10% 08:35 remain
Copying `mydb`.`user`:  15% 08:23 remain
...
Copying `mydb`.`user`:  90% 01:21 remain
Copying `mydb`.`user`:  93% 00:59 remain
Copying `mydb`.`user`:  95% 00:35 remain
Copying `mydb`.`user`:  98% 00:13 remain
2019-01-12T16:49:27 Copied rows OK.
2019-01-12T16:49:28 Swapping tables...
2019-01-12T16:50:09 Swapped original and new tables OK.
2019-01-12T16:50:09 Dropping old table...
2019-01-12T16:50:14 Dropped old table `mydb`.`_user_old` OK.
2019-01-12T16:50:15 Dropping triggers...
2019-01-12T16:50:15 Dropped triggers OK.
Successfully altered `mydb`.`user`.

PS: Тулза руганулась что innodb_lock_wait_timeout только для чтения

Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"].  The current value for innodb_lock_wait_timeout is 50.  If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.

Добавил в параметры вызова —set-vars innodb_lock_wait_timeout=50 и всё поехало.