Восстановление базы данных mysql InnoDB если остались только файлы frm и ibd

Случается так, что база данных(ibdata1, ib_logfile0) накрывается медным тазом, но к счастливейшему стечению обстоятельств остаются файлы frm и ibd и в настройках mysql выставлено innodb_file_per_table = 1, не стоит расстраиваться шанс восстановления данных мал, но он есть.

Успех зависит от того какой версии mysql вы использовали, если 5.6 и выше, то шансы растут, а если у вас еще есть структура таблиц, то там вообще шансы велики.

Для начала нам необходима структура базы данных, если у вас есть дамп то полдела сделано, нам нужна только структура. Для того что бы вынуть структуру из дампа есть такая утилита как awk и следующее выражение awk '/CREATE TABLE /,/ENGINE=/' dump.sql если конечно дамп сделан mysqldump то все получится, если другой утилитой, то придется подредактировать.

А теперь поговорим про ОС, где все эти манипуляции по восстановлению проводить, мой выбор пал на ubuntu 14.04, потому что там можно накатить любую версию mysql 5.5, 5.6, 5.7.

В общем ставим ubuntu, я ставил на VirtualBox с этого образа mini.iso.
Ставим openssh-server, apache, php, phpmyadmin
Если нужна версия mysql 5.7 нужно добавить репозиторий

deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.7

И ключ командой apt-key adv --keyserver pgp.mit.edu --recv-keys 5072E1F5

И устанавливаем mysql apt-get install mysql-server-5.X вместо X — версия.
После установки не забудьте добавить в конфиг mysql innodb_file_per_table = 1 и перезапустить сервер /etc/init.d/mysql restart

Ну а теперь самое интересное, если у вас есть структура базы данных пропускайте этот пункт.

Можно попробовать достать структуру бд из файлов frm, для этого заходим в каталог где лежат ваши файлы от бд и выполняем следующие команды:

ls -1 *.frm > ~/tables.sql
sed -i 's/^/create table /' ~/tables.sql
sed -i 's/.frm/ (id int) engine=innodb;/'  ~/tables.sql

В корне домашней директории, появится sql файл с созданием одноименных таблицы в бд.

Если у вас база данных без пароля для root, то выполняем следующие команды:

mysql -uroot -e "create database recover"
mysql -uroot recover < ~/tables.sql

Если с паролем то добавляем параметры -p

После создания одноименных таблиц, останавливаем mysql /etc/init.d/mysql stop переходим в каталог где живут файлы mysql, по дефолту это /var/lib/mysql/, там находим нашу бд recover и заменяем все файлы frm на файлы из бд которую восстанавливаем.

cd /var/lib/mysql/recover
rm -f *.frm
cp ~/dbre/*.frm .
chmod 660 *
chown mysql:mysql *

~/dbre/ — каталог где хранится бд которую восстанавливаем

Запускаем mysql /etc/init.d/mysql startИ смотрим получилось ли у нас что, для этого заходим в mysql

mysql -uroot
use recover;
show create table table_name;

Если покажет структуру таблицы, то ура, делаем dump mysqldump -uroot -d recover > ~/struct.sql если напишет recover.table_name does't exist то беда.

Можно попробовать поиграться с параметрами innodb_force_recovery=5, но почему то мне везло только с версий 5.5 mysql server'a и то как то попалась бд от wordpress'a с кодировкой utf8mb4, а в 5.5 естественно нет данной кодировки, и при запросе структуры таблицы выдавал ошибку Unknown collation '#246' Единственный вариант из всех что я видел это mysql workbench он кое как видит структуру, но и то без ключевых полей, а они необходимы, так что если вы примерно знаете структуру, то можно попробовать восстановить.

А теперь внимание, в mysql 5.6+ появилась возможность импорта ibd файлов, что значительно упрощает весь процесс восстановления, поэтому в любому случае ставим mysql 5.6 или 5.7

Есть утилита dbsake для получения структуры таблицы из FRM файла

В общем если у нас есть структура бд(как ее получить описал выше awk '/CREATE TABLE /,/ENGINE=/' dump.sql > ~/struct.sql), заливаем ее в нашу бд

mysql -uroot < ~/struct.sql

Теперь надо отключить ibd файлы у каждой таблицы, для этого нужно вновь провести некоторые манипуляции

cd /var/lib/mysql/recover
ls -1 *.frm > ~/tables.sql
sed -i 's/^/alter table /' ~/tables.sql
sed -i 's/.frm/ discard tablespace;/' ~/tables.sql

Создали sql файл, который массово отключит ibd для всех таблиц mysql -uroot recover < ~/tables.sql

Теперь самое интересное, копируем ibd файлы с базы которой восстанавливаем и подготавливаем sql файл импорта этих ibd файлов:

cd /var/lib/mysql/recover
cp ~/dbre/*.ibd .
chown mysql:mysql *
chmod 660 *
cp ~/tables.sql ~/tables2.sql
sed -i 's/discard/import/' ~/tables2.sql

Скопировали idb файлы и создали sql файл с импортом для всех таблиц, попробуем применить его mysql -uroot recover < ~/tables2.sql

Через phpmyadmin или mysql-workbench можно попробовать посмотреть, что мы там на восстанавливали и если все окей сделать нормальный dump mysqldump -uroot recover > recover.sqlУ меня так успешно восстановилось две базы данных, с другими двумя, они были версии mysql 5.5< поломались поля DATETIME

Еще есть утилита Percona data recovery tool for innodb, но тоже нужно знать структуру, как ей пользоваться описано в этой статье
Мне не понравилось решение с find для объединения файлов и я накатал свой небольшой скриптик, может кому понравится

#!/bin/bash

for dir in `ls -d */ -1 | sort -n`
do
echo ${dir%%/}
for file in `ls -1 $dir | sort -n`;do cat ${dir}/${file} >> data.dat; done;
done

Помещаем его в каталог pages-xxxxxxxx/FIL_PAGE_INDEX/ и запускаем, он объеденит все файлы из папок

Этой утилитой я тоже восстановил пару таблиц, в некоторых было все хорошо, в некоторых данные все же были повреждены.

Моя заметка скорее перевод вот этой, на авторство не претендую

И если кому вдруг интересно, я писал про восстановление БД wordpress, там структуру вытащить не получилось, поэтому я пошел более простым путем, я установил чистый wordpress и плагины, и сделал discard и import tablespace, и все отлично восстановилось.

Делайте бэкапы!