Восстановление InnoDB MySQL

Linux Debian  /  Tweaks  /  Фриланс  




Как бы не убеждал нас Oracle и Mariadb о надежности InnoDB - это не так.

Поддержка транзакций и прочее страшные слова хороши до тех пор пока база данных не скрашилась (как вариант, не была дерзко скопирована в другое место). В этом случае наступает кризис, и останавливается работа.
Итак, описанный хак будет работать только в случае, если вы используете хранение каждой таблицы в отдельном файле (параметр innodb_file_per_table=1 в my.cnf). Если у вас такой настройки нет - сделайте ее, иначе будет поздно. Вы можете определить использование отдельного файла для таблицы, по присутствию множества .ibd файлов в каталоге базы данных.
Т.е., примерно в /var/lib/mysql/DATABASE_NAME/ у вас должны быть файлы с расширением FRM и IBD.
FRM - это структура таблицы. IBD - содержимое таблицы. Простое копирование не поможет, поскольку несмотря на то, что MySQL будет видеть названия таблиц, ни SELECT FROM ни DESC не получится.

Итак, допустим у вас есть все условия: бэкап с множеством IBD-файлов:
1) Удаляем файлы ibdata1 и логи в папке /var/lib/mysql (или в вашем каталоге с базами)
2) Перезапускаем MySQL. Он должен перезапуститься
3) Открываем консоль MySQL и заходим в него
4) Выполняем show databases; use <имя_вашей_базы>;show tables - вы должны увидеть таблицы.


Основной принцип восстановления таблиц из файлов следующий:
1) Создаем промежуточную базу данных
2) Восстанавливаем структуру каждой таблицы вашей поврежденной базы, при помощи утилиты
3) Создаем таблицы в промежуточной базе, на основе восстановленных из предыдущего пункта
4) Удаляем текущий контент таблицы, при помощи хитрой команды
5) Копируем физический файл с данными таблицы, из нашей поврежденной базы
6) Заставляем MySQL принять и перечитать новые данные.


Как это будет в командах.
1) cd /var/lib/mysql
2) rm ib_logfile0;rm ib_logfile1; rm ibdata1
3) service mysql restart
4) mysql -u'root' -p'ваш_пароль'
5) CREATE DATABASE swap;
6) quit
7) mysqlfrm --server=root:ваш_пароль@localhost:3306 --diagnostic <имя_файла.frm> > table.sql
8) mysql -u'root' -p'ваш_пароль' swap < table.sql
9) Предыдущие команды возможно вызовут ошибки, они незначительные, к примеру незакомментированное слово WARNING, но их придется устранить вручную
10) mysql -u'root' -p'ваш_пароль' swap
11) ALTER TABLE <имя_таблицы_которую мы создали пару пунктов назад> DISCARD TABLESPACE;
12) quit
13) cp <файл_таблицы.ibd из поврежденной базы> swap/ - попросту говоря, копируем восстанавливаемый файл IBD из папки старой базы, в папку с новой
14) mysql -u'root' -p'ваш_пароль' swap
15) ALTER TABLE <имя_таблицы_которую мы создали пару пунктов назад> IMPORT TABLESPACE;
16) Вуаля. service mysql restart


Для автоматизации рутинной работы, я написал скрипт
Код скрипта:

#!/bin/bash

pass='ваш_рутовый_пароль_на_mysql'

rm swap
db=$1
echo "================Restoring database $db======================="
cd $db
echo "1) Creating temp database _$db"
echo "drop database _$db;" | mysql -u'root' -p"$pass"
echo "create database _$db;" | mysql -u'root' -p"$pass"
echo "2) Re-creating table structure"
f=`ls | grep '.frm'`
cd ..
 for ii in $f;do
 tbl=`echo "$ii" | awk -F '.' {'print $1'}`
 echo " Creating structure for table $tbl"
 mysqlfrm --server=web:$pass@localhost:3306 --diagnostic $db/$ii | grep -v 'WARNING' | sed "s/$db/_$db/" > $db.$tbl.sql
 DONE=false
until $DONE ;do
read x || DONE=true
# Следующие строки нужны,чтобы заменять возможные апострофы в комментариях
xx=`echo $x | grep ' comment '`
if [[ "$xx" != "" ]];then
x=`echo $x | sed 's/"/\^/g' | sed "s/'/\"/" |  sed "s/',/\",/"`
                            fi
echo $x >> swap
done < $db.$tbl.sql
mv swap $db.$tbl.sql
mysql -u'root' -p"$pass" < $db.$tbl.sql
done
echo "Now will restart mysql..."
read a
# killall mysqld
# killall mysqld_safe
service mysql stop
/etc/init.d/mysql stop
service mysql start
echo "3) Discarding current table"
# cd ..
cd _$db
f=`ls | grep '.frm'`
for ii in $f;do
tbl=`echo "$ii" | awk -F '.' {'print $1'}`
printf " DISCARD _$db.$tbl..."
mv $tbl.ibd $tbl.bkp 
echo "USE _$db; ALTER TABLE $tbl DISCARD TABLESPACE;" | mysql -u'root' -p"$pass" 2> log
cp ../$db/$tbl.ibd $tbl.ibd

chmod 777 $tbl.ibd

echo "OK"

done
 f=`ls | grep '.ibd'`
 for ii in $f;do
 tbl=`echo "$ii" | awk -F '.' {'print $1'}`
 printf " IMPORT _$db.$tbl..."
 res=`echo "USE _$db; ALTER TABLE $tbl IMPORT TABLESPACE;" | mysql -u'root' -p"$pass" 2>err`
 err=`cat err | grep 'Data structure corruption'`
 # Если контент в IBD файле поврежден, мы его не восстановим, для этого придется оставить таблицу чистой, и снять с нее DISCARD
 if [[ "$err" != "" ]];then
 printf "Data corrupted, running restoration..."
 rm $tbl.ibd
 echo "USE _$db; ALTER TABLE $tbl DISCARD TABLESPACE;" | mysql -u'root' -p"$pass" 2> log
 mv $tbl.bkp $tbl.ibd
 echo "USE _$db; ALTER TABLE $tbl IMPORT TABLESPACE;" | mysql -u'root' -p"$pass" 2> log
 fi
 echo "OK"
 done
service mysql restart


Скрипт должен быть размещен в каталоге datadir (/var/lib/mysql)

Запускается он, находясь в каталоге, командой ./script.sh <имя_базы_данных>

Естественно, каталог с базой должен находиться здесь же. Скрипт создаст новую базу с таким же названием, но со знаком подчеркивания. Не забудьте так же поставить mysqlfrm (apt-get install mysql-utilities в Debian-based).


Не упрекайте меня пожалуйста за красоту программирования на bash, главная цель вовсе не она.

Надеюсь я вам помог.



Метки текста: InnoDB восстановление, MySQL ibd, InnoDB crash, InnoDB restore from ibd


http://minidevices.info/images/ava.png
http://minidevices.info/images/ava.png
2016-07-13 22:16
root 13 июля в 22:16  0 

700
2016-07-13 22:16


Комментировать



Опубликовать запись