MySQL: Восстановление базы данных InnoDB из файлов .ibd

Дважды с проектом Hattrick Portal попадал в ситуацию «потери» файлов собственной базы MySQL (mysql) — СУБД переставало работать и рапортовало о том, что в первом случае таблицы innodb_index_stats, innodb_table_stats, slave_master_info потеряны, а во втором, что таблицы servers и plugin. Объединяет эти два случая одно — эти таблицы на движке InnoDB. Если раньше все таблицы системной БД mysql были на движке MyISAM, то постепенно MySQL переводит все в InnoDB. С чем связаны были проблемы выяснить не удалось, но нужно было срочно восстанавливать работу СУБД. Быстрое гугление показало один простой вариант — прибиваем все файлы данных, запускаем СУБД с пустыми каталогами данных (она создает их по умолчанию), затем восстанавливаем всё из бэкапа. Но восстановить из бэкапа 10 Гб данных — это долгий процесс. И причем ещё — нужные данные-то у меня все на месте и не пострадали!

Более подробный поиск в сети навел на решение — есть возможность восстановления таблицы из имеющегося файла .ibd, если у нас в СУБД используется опция innodb_file_per_table=1 (т.е. таблицы хранятся в отдельных табличных простраствах, а не в ibdata1. Таким образом можно, например, восстановить всю базу данных по сохранившимся файлам .ibd, без необходимости восстановления из бэкапа (тут, конечно, есть нюансы связанные с потерей данных, если до этого СУБД не корректно завершила работу и не сброшены все блоки в БД).

Для восстановления одной таблицы процесс следующий:

• переносим во временную папку файл .ibd, соответствующий таблице;
• если БД «видит» (SHOW TABLES) нашу таблицу, то удаляем её: DROP TABLE таблица;
• создаем таблицу точно такую же, как была до этого: CREATE TABLE table …;
• отключаем табличное пространство: ALTER TABLE таблица DISCARD TABLESPACE;
• соответствующий файл .ibd должен удалиться после удачного выполнения предыдущей команды и таблица размещается в общем табличном пространстве ibdata1;
• на его старое место копируем сохраненный во временной папке файл .ibd;
• импортируем табличное пространство: ALTER TABLE таблица IMPORT TABLESPACE;
• после применения последней команды таблица должна прочитаться со всеми данными, которые были в первом файле .ibd.

По аналогии, для восстановления всей базы данных таким образом соответственно нужно сначала перенести сохранившиеся файлы .ibd во временный каталог. Затем создать такую же структуру таблиц как была. Тут мы вспоминаем, что неплохо бы делать дампы бэкапов не только с данными, но и только со структурой — это сэкономит нам время в этом случае. Т.е. в команду вызова mysqldump нужно добавить опцию —no-data. Например, в моем случае:

@"mysqldump.exe" --host=localhost --port=3306 --user=пользователь --password=пароль --no-data --default-character-set=utf8 --log-error=базаданных.log --set-gtid-purged=OFF --single-transaction 
--add-drop-database --databases базаданных "базаданных.sql"

Если структуры под рукой нет, то вспоминаем про дамп БД, открываем его и ищем последовательно все операторы CREATE TABLE и копируем их содержимое в файл БД.sql. Поскольку файл бэкапа может быть огромным, то открываем его теми просмотровщиками, которые не пытаются полностью считать его в память, например, просмотровщик файлов в Total Commander имеет именно такое свойство. После восстановления структуры, мы готовим и запускаем скрипт вида:

ALTER TABLE таблица1 DISCARD TABLESPACE;
ALTER TABLE таблица2 DISCARD TABLESPACE;
...
ALTER TABLE таблицаN DISCARD TABLESPACE;

Потом возвращаем файлы .ibd обратно м запускаем процесс в обратную сторону скриптом вида:

ALTER TABLE таблица1 IMPORT TABLESPACE;
ALTER TABLE таблица2 IMPORT TABLESPACE;
...
ALTER TABLE таблицаN IMPORT TABLESPACE;

После этого проверяем доступность данных в таблицах, при необходимости выполняем CHECK TABLE таблица; для всех таблиц (хотя в InnoDB данный оператор весьма странно работает). Я на всякий случай выполняю скрипт оптимизации таблиц (см. мою статью MySQL: InnoDB и дефрагментация табличных пространств).

Если что-то пошло не так — у нас остаётся один вариант: восстанавливать БД из дампа. В предыдущей статье (MySQL: Бэкап базы данных и его сжатие) я как раз рассматривал создание сжатых дампов с помощью mydqldump и 7zip и восстановление из них.