Как устранить неисправность InnoDB в базе данных MySQL

Источник: http://kb.odin.com/ru/6586

Проблема

1. MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
2. При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):

   mysqlcheck -uadmin -p****** db_example
   db_example.BackupTasks
   error : Can't find file: 'BackupTasks.MYD' (errno: 2)
   

3. Невозможно выполнить запрос таблицы с оператором «SELECT»:

   mysql> select * from db_example.misc;
   ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
   

4. Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.

   mysql> repair table misc;
   +-------------------------+--------+----------+---------------------------------------------------------+
   | Table | Op | Msg_type | Msg_text |
   +-------------------------+--------+----------+---------------------------------------------------------+
   | psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
   +-------------------------+--------+----------+---------------------------------------------------------+
   

Причина

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 4

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

/etc/init.d/mysqld start

Создайте дамп всех баз данных:

mysqldump -uadmin -p****** -A > /root/dumpall.sql

Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

/etc/init.d/mysqld stop
rm -rf /var/lib/mysql/*

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

/etc/init.d/mysqld start

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:

mysql -uadmin -p****** > dumpall.sql

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Попробуйте создать копию:

CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>

Или при попытке сделать дамп через mysqldump

[red@hellsrv ~]$ mysqldump -uroot -p databasename > databasename.sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES
[red@hellsrv ~]$

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

service mysqld stop
cp -R /var/lib/mysql/databasename /home/USERNAME/backup

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

• узнать структуру таблиц
• иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

• файл .frm хранит в себе структуру таблицы;
• файл .ibd собственно данные

План восстановления:

• выяснить структуру поврежденной таблицы;
• создать новую базу;
• создать в новой базе таблицу нужной структуры;
• скопировать данные в новую таблицу из старой;
• если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

mysqlfrm --diagnostic table1.frm
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Также желательно узнать кодировку старой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251                     | cp1251_general_ci      |
+----------------------------+------------------------+
1 ROW IN SET (0.00 sec)

Создаем новую базу:

mysql> CREATE DATABASE helldb CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

mysql> USE databasename;
mysql> CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Далее копируем данные:

• Очищаем автоматически созданный файл

  mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
  Query OK, 0 ROWS affected (0.04 sec)

• Копируем файл с данными с поврежденной таблицы

  cp /home/USERNAME/tables1.ibd tables1.ibd
  chown mysql:mysql tables1.ibd

• Импортируем данные

  mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
  Query OK, 0 ROWS affected, 1 warning (0.50 sec)

• Проверяем корректность чтения данных

  mysql> SELECT * FROM tables1 LIMIT 10;
  +-----+-----------+
  | id  | title     |
  +-----+-----------+
  |  1  | Title 1   |
  |  2  | Title 2   |
  |  3  | Title 3   |
  |  4  | Title 4   |
  +-----+-----------+
  4 ROWS IN SET (0.00 sec)

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.