Это сообщение от из MySQL Performance Blog.
Несколько лет назад Ив Трюдо и Александр Кузьминский писали сообщения о различных способах восстановления потерянных файлов .ibd:
- Подключение потерянных .ibd файлов
- Мелочи восстановления или как восстановить потерянный файл ibdata1
Сегодня я хочу показать вам, как это сделать проще и быстрее. В моем примере я восстановлю файл payment.ibd (таблица платежей) из Sakila DB на сервере с MySQL 5.5 (но с помощью MySQL 5.6 и песочницы ).
В моем случае ОС CentOS. Поэтому мне нужно было:
- установить mysqlsandbox (см. инструкции там)
- скачать последнюю версию Percona Server 5.6:
wget http://www.percona.com/redir/downloads/Percona-Server-5.6/LATEST/release-5.6.14-62.0/483/binary/linux/i686/Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Создать песочницу:
make_sandbox Percona-Server-5.6.14-rel62.0-483.Linux.i686.tar.gz
Попробуй это:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select @@version\G" *************************** 1. row *************************** @@version: 5.6.14-rel62.0
Оно работает!
Проверьте datadir и включена ли опция innodb_file_per_table (это требование):
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like 'datadir'" +---------------+---------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------+ | datadir | /home/mixa/sandboxes/msb_5_6_14/data/ | +---------------+---------------------------------------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "show variables like '%per_table'" +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+
Если он не включен, то вам нужно включить его
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "SET GLOBAL innodb_file_per_table=1"
Создать пустую таблицу платежей на песочнице 5.6
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 test < payment_table.sql
Payment_table.sql — файл с выпиской «SHOW CREATE TABLE» для таблицы платежей. Структура таблицы должна быть одинаковой.
cat payment_table.sql CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`) ) ENGINE=InnoDB
Стоп песочница
./sandboxes/msb_5_6_14/stop
Замените файл .ibd (в моем случае его правильная копия находится в моем домашнем каталоге)
cp ~/payment.ibd ~/sandboxes/msb_5_6_14/data/test/ -f
Убедитесь, что разрешения для .ibd файла в порядке
sudo chmod 660 ~/sandboxes/msb_5_6_14/data/test/payment.ibd sudo chown : ~/sandboxes/msb_5_6_14/data/test/payment.ibd
Начать песочницу
./sandboxes/msb_5_6_14/start
В настоящее время, если вы попытаетесь выбрать что-то из таблицы, вы получите ошибку:
Выбрать из таблицы
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment" ERROR 1146 (42S02) at line 1: Table 'test.payment' doesn't exist
Журнал ошибок
2013-11-02 14:36:34 b7eff990 InnoDB: Error: table 'test/payment' InnoDB: in InnoDB data dictionary has tablespace id 7, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name test/payment and id 10, though. Have InnoDB: you deleted or moved .ibd files? ... ... 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Failed to find tablespace for table '"test"."payment"' in the cache. Attempting to load the tablespace with space id 7. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: In file './test/payment.ibd', tablespace id and flags are 10 and 0, but in the InnoDB data dictionary they are 7 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. 2013-11-02 14:36:36 11640 [ERROR] InnoDB: Could not find a valid tablespace file for 'test/payment'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2013-11-02 14:36:36 a31a2b90 InnoDB: cannot calculate statistics for table "test"."payment" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
Как это исправить? В 5.6 управление табличным пространством очень улучшено, поэтому нужно только «ALTER TABLE .. DISCARD TABLESPACE» и «ALTER TABLE .. IMPORT TABLESPACE».
Пожалуйста, проверьте также ограничения: Ограничения копирования табличных пространств
Посмотрите на пример:
Отменить табличное пространство
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment discard tablespace; show warnings;" +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1812 | InnoDB: Tablespace is missing for table 'test/payment' | | Warning | 1812 | InnoDB: Tablespace is missing for table 'payment' | +---------+------+--------------------------------------------------------+
Импорт табличного пространства
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "alter table test.payment import tablespace; show warnings" +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/payment.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
Вот и все, данные восстановлены, таблица выплат доступна на 5.6 песочнице.
Теперь проверьте, есть ли данные в таблице платежей в песочнице:
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select count(*) from test.payment"
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
mysql -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 -e "select * from test.payment limit 1\G"
*************************** 1. row ***************************
payment_id: 1
customer_id: 1
staff_id: 1
rental_id: 76
amount: 2.99
payment_date: 0000-00-09 03:49:32
last_update: 2028-02-08 12:32:35
Существует.
Так что выкинь его из песочницы и восстанови на 5.5:
Дамп из 5.6
mysqldump -umsandbox -pmsandbox --host=127.0.0.1 --port=5614 --add-drop-table test payment > ~/payment_dump.sql
Восстановить до 5.5
mysql -u user -p < ~/payment_dump.sql
Проверьте, существуют ли данные на 5.5
mysql -u root -e "select * from test.payment limit 3;" +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | payment_id | customer_id | staff_id | rental_id | amount | payment_date | last_update | +------------+-------------+----------+-----------+--------+---------------------+---------------------+ | 1 | 1 | 1 | 76 | 2.99 | 0000-00-09 03:49:32 | 2028-02-08 12:32:35 | | 2 | 1 | 1 | 573 | 0.99 | 0000-00-09 03:49:32 | 0000-00-00 00:00:00 | | 3 | 1 | 1 | 1185 | 5.99 | 0000-00-09 03:49:37 | 0000-00-00 00:00:00 | +------------+-------------+----------+-----------+--------+---------------------+---------------------+
Во время моей работы с этим делом я попал в ситуацию, в которой оплата в дроп-таблице на 5.5 была невозможна, потому что payment.idb там не был корректным — поэтому сервер каждый раз падал, когда я пытался получить доступ к этой таблице. Для устранения этой проблемы:
— остановка сервера
— гт .ibd файл
— Запуск сервера
— удаление таблицы , как правило , с помощью DROP TABLE команды