Как провести миграцию для PK с int на BigInt в MySql 5.7?

Question

[nnn Xion]

У меня есть таблица, в которой PK уже достиг 75% максимального значения типа int. Какие есть способы миграции на BigInt без блокировки таблицы и какие из них на ваш взгляд эффективнее?

Вот, что я нашел на данный момент:

* MySQL поддерживает онлайн миграции для первичных ключей (https://dev.mysql.com/doc/refman/5.7/en/innodb-onl...) но с какими-то ограничениями
* Утилиты для онлайн миграций: gh-ost и pt-online-schema-change

Answer 1

[mayton2019]

Можно попробовать почистить таблицу от исторических записей PK и появится свободное место снизу.
Допустим от ID=0 до 1 млрд. И потом потихоньку начать переиспользовать ключи снизу.
Но я не уверен что это работает для авто-инкрементных ключей особенно если уже много логики было
создано. В оракле проще. Есть отдельный объект SEQUENCE и его можно очень быстро пересоздать
или сделать циклическим. Тоесть от 4 млрд он пойдет снова с нуля.

Второй вариант - я-бы сделал копию ID INT с типом ID_BIG BIGINT. Это очень короткая блокировка. Надеюсь быстро.
Потом в фоновом режиме (транзакционном) пообновлял бы все до полного синхронизма. И потом
нужен регламент чтобы дропнуть ID и переделать все констрейнты быстро на ID_BIG.
Тут надо подготовить заране скрипты.

Comments

[nnn Xion]

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

За идеи спасибо. Будет над чем подумать

[mayton2019]

nnn Xion, пожалуйста. С другой стороны если у вас 75% заполнено то еще есть время
подумать когда остановить систему или сделать реплику чтобы за 15 минут сделать
это грязное дело.

Answer 2

[Rsa97]

Вариант 1:
1. Сделать полный бэкап базы.
2. Удалить все внешние ключи, ссылающиеся на изменяемое поле.
3. Изменить тип поля.
4. Изменить тип полей всех внешних ключей, ссылающихся на изменяемое поле.
5. Пересоздать все внешние ключи, ссылающиеся на изменяемое поле.

Вариант 2:
1. Сделать полный дамп базы.
2. Отредактировать дамп, изменив тип поля и всех внешних ключей, ссылающихся на него.
3. Восстановить базу из дампа.

В обоих вариантах надо останавливать работу с базой.

Comments

[nnn Xion]

эта таблица одна весит 450 гигов. Бекап всей базы с больше 1к таблиц...

[Rsa97]

nnn Xion, И? Лучше получить при сбое неисправную базу?

[nnn Xion]

на такие операции ресурсов не хватит

[IliaIT]

Хмм. тут недавно начал перегонять таймштамп в дататайм (мы же помним про переполнение через 10 лет, а патчик не факт что на 5.7 будет).
в итоге таблицу блочит, но достаточно просто сменить тип поля, остальное мускул сам доделывал. хотя было не быстро (небольшие таблицы до 1 гига), но можно же скрипт написать. ну и понятно что бекап он по любому нужен, ибо без него вообще страшно что то делать. В итоге всё получилось, никто не жаловался, но возможно всё от того что это не ключевое поле.

[Rsa97]

IliaIT, И ключевое поле конвертнётся автоматически, если на него нет внешних связей (FOREIGN KEY). Проблема именно в них.