Вчера ночью мой сервер MySQL упал, и теперь у меня есть несколько баз данных, данные которых я не могу восстановить.

Согласно журналу ошибок, повреждения в пространствах таблиц произошли довольно давно (несколько месяцев назад), но никто этого не заметил, и вчера, когда MySQL упал (по совершенно другой причине), он просто не смог запуститься с следующей ошибкой:

2015-06-09 23:09:23 14642 [Note] InnoDB: Starting crash recovery.
2015-06-09 23:09:23 14642 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-06-09 23:09:24 14642 [ERROR] InnoDB: Tried to read 16384 bytes at offset 0. Was only able to read 0.
2015-06-09 23:09:24 7f648369a7e0  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2015-06-09 23:09:24 14642 [ERROR] InnoDB: File (unknown): 'read' returned OS error 71. Cannot continue operation

Когда база данных была запущена с установленным innodb-force-recovery на 6, сервер запустился, с множеством таких сообщений в журнале ошибок:

2015-06-09 23:16:50 16659 [ERROR] InnoDB: Failed to find tablespace for table '"db1234"."tbl456"' in the cache. Attempting to load the tablespace with space id 275772.

Мне удалось сделать дамп большинства баз данных и импортировать их в чистый каталог данных. Но некоторые базы данных имеют поврежденные пространства таблиц, и я не могу сделать дамп (или даже открыть любую таблицу в них).

Теперь у меня есть несколько (около ста) баз данных, которые не могут быть загружены в работающий сервер MySQL. У меня есть их файлы данных (как .frm, так и .ibd файлы), но они каким-то образом повреждены, и MySQL не может их открыть:

2015-06-10 18:37:18 3965 [Warning] InnoDB: Cannot open table db123/tbl456 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

Я пытался скопировать файлы данных в другой экземпляр MySQL, но без успеха, я даже пытался создать новые .frm файлы (но я не знаю структуру таблиц), остановить сервер, заменить их на правильные, запустить сервер и открыть их – снова без успеха.

К сожалению, у меня нет резервной копии этих баз данных, потому что они были повреждены давно, и резервные дампы не смогли сохранить никаких данных.

Теперь у меня есть данные в файлах данных InnoDB, но я не могу загрузить их в работающий сервер. Есть ли способ сохранить их?

Я нашел этот инструмент: https://www.percona.com/software/mysql-innodb-data-recovery-tools, но я не уверен, что смогу им воспользоваться, он довольно сложный, и я не вижу, как восстановить сто баз данных, структура которых мне неизвестна…

Сервер использует MySQL 5.6.17, скомпилированный из исходного кода.

Как автор набора инструментов восстановления данных Percona, я рекомендую вам использовать набор инструментов восстановления данных TwinDB. Разработка набора инструментов Percona не ведется уже два года, и все новые функции переходят в набор инструментов TwinDB. Ваш случай описан в посте Восстановление поврежденной базы данных MySQL

Чтобы восстановить структуру таблицы, вы можете использовать инструмент mysqlfrm или восстановить структуру из словаря InnoDB

Я несколько дней боролся с поврежденной базой данных MySQL/MariaDB и наконец-то нашел что-то, что работает, основываясь на этом руководстве. В моем случае я видел такие ошибки:

#1932 - Table 'phpmyadmin.pma__recent' doesn't exist in engine
ERROR 1030 (HY000): Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
ERROR 1146 (42S02): Table 'my_database.my_table' doesn't exist
ERROR 1813 (HY000): Tablespace for table '`my_database`.`my_table`' exists. Please DISCARD the tablespace before IMPORT

Отказ от ответственности: Решение, описанное ниже, кажется мне довольно рискованным, но, к сожалению, у меня не было актуальных резервных копий SQL дампов (в будущем я постараюсь делать это чаще и рекомендую вам тоже!). Я вижу это больше как “последнее средство”. Если у вас есть недавний SQL дамп, возможно, стоит использовать его вместо этого.

Предположения:

• У вас есть доступ root через sudo.
• Ваш сервер MySQL работает.
• Ваш каталог базы данных MySQL/MariaDB находится по адресу /var/lib/mysql.
• Ваше имя пользователя MySQL – mysql.
• Вы сделали резервную копию ВСЕХ файлов в каталоге вашей базы данных MySQL на случай, если вы что-то сделаете не так. (Мы будем удалять таблицы в следующем!)
• У вас есть временный рабочий каталог, в который пользователь mysql может копировать файлы (в следующем я буду использовать /home/mysql/tmp_bck).
• Вы установили innodb_force_recovery=0 в вашей конфигурации MySQL (/etc/mysql/my.cnf) или он вообще не установлен/не изменен (в противном случае вы не сможете записывать).
• У вас установлен инструмент mysqlfrm. Если его у вас ещё нет, установите его с помощью sudo apt-get install mysql-utilities.

Убедитесь, что вышеперечисленные условия выполнены, прежде чем продолжить.

Для каждой базы данных, которую мы хотим восстановить, создайте временный рабочий каталог. Предположим, что база данных называется my_database, тогда выполните:

sudo -u mysql mkdir /home/mysql/tmp_bck/my_database

Для каждой таблицы (например, my_table) в этой базе данных, которую мы хотим восстановить:

1. В вашей консоли bash/SSH создайте временную резервную копию структуры/формата базы данных (.frm) и данных (.ibd). Затем используйте mysqlfrm, чтобы войти в ваш сервер MySQL (здесь: с пользователем “root”, паролем “my_password” (замените), и портом 3306 (по умолчанию)) и создать временный сервер-заглушку на другом порту (здесь: 3310).

sudo -u mysql cp /var/lib/mysql/my_database/my_table.frm /home/mysql/tmp_bck/my_database/my_table.frm
sudo -u mysql cp /var/lib/mysql/my_database/my_table.ibd /home/mysql/tmp_bck/my_database/my_table.ibd
cd /var/lib/mysql/my_database
sudo -u mysql mysqlfrm --server=root:my_password@localhost:3306 my_table.frm --port=3310

mysqlfrm даст вам команду CREATE TABLE, например:

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  KEY `autoload` (`autoload`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Скопируйте это, добавьте ; в конце, и сохраните это для шага 4!

2. В командной строке MySQL (откройте окно bash/SSH и запустите mysql -u root -p) выберите вашу базу данных и удалите/удалите таблицу. Это приведет к потере данных, если вы не сделали резервную копию файлов .frm/.ibd перед началом или если вышеуказанное копирование не удалось!:

USE my_database;
DROP TABLE my_table;

3. Вернитесь в вашу обычную оболочку bash, остановите сервер, удалите все оставшиеся файлы .frm/.ibd (как минимум, файл .ibd по-прежнему был в моем случае) и перезапустите сервер:

sudo systemctl stop mariadb
sudo -u mysql rm /var/lib/mysql/my_database/my_table.frm
sudo -u mysql rm /var/lib/mysql/my_database/my_table.ibd
sudo systemctl start mariadb

4. В командной строке MySQL создайте таблицу с помощью команды CREATE TABLE из шага 1, например:

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
   KEY `autoload` (`autoload`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Затем удалите пространство таблиц данных .ibd с помощью:

ALTER TABLE my_table DISCARD TABLESPACE;

5. Вернитесь в вашу обычную оболочку bash, скопируйте файл .ibd из вашей временной резервной копии:

sudo -u mysql cp -f /home/mysql/tmp_bck/my_database/my_table.ibd /var/lib/mysql/my_database/my_table.ibd
sudo chmod 660 /var/lib/mysql/my_database/my_table.ibd

6. Наконец, в MySQL импортируйте .ibd файл, который только что был скопирован из резервной копии:

ALTER TABLE my_table IMPORT TABLESPACE;

Готово! Убедитесь, что это успешно (например, используя phpmyadmin, вы не должны увидеть сообщение об ошибке, а вместо этого увидеть таблицу с данными, или хотя бы пустую таблицу без сообщения об ошибке). Повторите шаги выше для каждой базы данных/таблицы. Удачи!