Восстанавливаем поврежденные таблицы Innodb

Предположим, вы работаете с MySQL таблицами Innodb, и в один прекрасный не самый хороший момент подводит глючное железо, драйвер, бажит ядро, отключается электричество или случается одна из редких ошибок в среде MySQL. На выходе получаем повреждение некоторых страниц в табличной области Innodb.

В одной из предыдущих статей, в комментариях, нас спрашивали, что можно сделать в такой ситуации. Мы постараемся ответить максимально лаконично и по делу.

Так вот, сейчас речь о ситуации вроде этой:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

Что делать для восстановления таблицы? В принципе, типов повреждений может быть несколько, ниже мы рассмотрим один из наиболее распространенных моментов. А именно — когда повреждена страница в кластеризованном первичном ключе.

В примере рассматривается файл test.idb, где заменено несколько байтов, так что повреждение достаточно умеренное.

При этом операция CHECK TABLE в INNODB практически бесполезна. Для текущего поврежденного файла мы получаем:

mysql> check table test;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> check table test;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.69 sec)

Первый запуск — проверка таблицы в обычном режиме, в этом случае innodb просто падает, если есть ошибка в контрольной сумме (даже, если мы выполняем CHECK). Во втором случае запускаем innodb_force_recovery=1. И даже здесь мы получаем в логах запись о несовпадении контрольной суммы, при этом CHECK TABLE говорит нам, что с таблицей все ок. Как видим, CHECK TABLE доверять можно далеко не всегда.

В примере «повреждение» совсем небольшое, поэтому, если запускаем innodb_force_recovery=1, получаем следующее:

mysql> CREATE TABLE `test2` (
    ->   `c` char(255) DEFAULT NULL,
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2 select * from test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376  Duplicates: 0  Warnings: 0

Теперь мы получили все данные в таблице MyISAM, так что все, что остается сделать — дропнуть старую таблицу, и конвертировать новую в innodb после рестарта без опции innodb_force_recovery. Если старая таблица будет нужна в дальнейшем, ее можно просто переименовать. Вторая альтернатива — сделать дамп с MySQLDump и загрузить таблицу обратно. В принципе, это почти одно и то же. MyISAM используется по причине, описанной ниже.

Почему бы просто не воспользоваться OPTIMIZE TABLE? Все потому, что работа в режимер innodb_force_recovery проводится в режиме чтения для операций с данными, поэтому нельзя вставлять или стирать данные (при этом можно создавать или удалять таблицы Innodb):

mysql> optimize table test;
+-----------+----------+----------+----------------------------------+
| Table     | Op       | Msg_type | Msg_text                         |
+-----------+----------+----------+----------------------------------+
| test.test | optimize | error    | Got error -1 from storage engine |
| test.test | optimize | status   | Operation failed                 |
+-----------+----------+----------+----------------------------------+
2 rows in set, 2 warnings (0.09 sec)

Это было просто, правда?

После этого можно пойти еще дальше, и отредактировать наш файл test.ibd, полностью удалив один из заголовков страницы. Теперь CHECK TABLE будет падать даже при использовании innodb_force_recovery=1

080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even

Если мы видим нечто подобное, то innodb_force_recovery нам не поможет, поскольку работать с этим можно тлько в случаях повреждения данных в различных системных местах. Но в нашем случае это не помогает.

Получаем такую ошибку:

mysql> insert into test2 select * from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Попытки использования автоматических процессов восстановления поврежденных данных не приводят к положительному результату. Поэтому стоит использовать серию команд с LIMIT для режима ручного восстановления:

mysql> insert ignore into test2 select * from test limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> insert ignore into test2 select * from test limit 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20  Duplicates: 10  Warnings: 0
mysql> insert ignore into test2 select * from test limit 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100  Duplicates: 20  Warnings: 0
mysql> insert ignore into test2 select * from test limit 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200  Duplicates: 100  Warnings: 0
mysql> insert ignore into test2 select * from test limit 300;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Здесь строки из таблицы переводятся в новую таблицу, пока мы не добираемся до строки, которая и вызывает падение MySQL. Мы можем ожидать этого в строке между 200 и 300, так что стоит использовать серию схожих действий для решения проблемы.

Теперь мы обнаружили поврежденные данные в таблице, при этом стоит использовать max PK, и проверить иные значения:

mysql> select max(id) from test2;
+---------+
| max(id) |
+---------+
|     220 |
+---------+
1 row in set (0.00 sec)
mysql> insert ignore into test2 select * from test where id>250;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> insert ignore into test2 select * from test where id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140  Duplicates: 0  Warnings: 0

Так, мы пробуем пропустить 30 строк, но это оказывается недостаточным. Пропускаем 80 строк, и теперь все хорошо. Используя «двоичный поиск» мы можем понять, сколько строк нужно пропустить, для восстановления максимального количества поврежденных данных. Размер строки при этом может помочь. Так, у нас есть 280 байт на строку, поэтому мы получаем около 50 строк на страницу. И здесь 30 строк недостаточно — если таблица страниц повреждена, нужно пропустить минимум всю страницу. Если повреждена страница на более высоком уровне BTREE, нужно пропустить больше страниц, для использования этого метода восстановления.

В некоторых случаях, например, когда повреждена root page для кластеризованного индекса, этот метод не будет нормально работать. В этом случае стоит использовать Innodb Recovery Toolkit.

P.S. Принимаем заявки на статьи :)