Статьи

Как восстановить структуру таблицы из словаря InnoDB

Октябрь 30, 2018

Это сообщение от  Александра Кузьминского из MySQL Performance Blog.

Чтобы восстановить удаленную или поврежденную таблицу с помощью Percona Data Recovery Tool для InnoDB, вам нужны две вещи: носитель с записями (ibdata1, * .ibd, образ диска и т. Д.) И структура таблицы. Действительно, на странице InnoDB нет информации о структуре таблицы. Обычно мы либо восстанавливаем структуру из файлов .frm, либо берем ее из какой-то старой резервной копии.

Новый инструмент sys_parserможет восстановить структуру таблицы из словаря InnoDB.

В любом случае, зачем нам новый инструмент? Крайне важно иметь точное определение таблицы для обеспечения успешного восстановления. Даже незаметная разница, такая как NULL или NOT NULL, может сдвинуть все значения на байт и, таким образом, испортит результат. Вот почему я предпочитаю структуру из файлов .frm вместо взятых из резервных копий. Но в некоторых случаях даже .frm файлы не вариант:

  • Таблица была удалена, и innodb_file_per_table включен
  • Frm файл поврежден, обнулен, потерян или SHOW CREATE TABLE приводит к сбою MySQL

Существует еще один источник информации о структуре таблицы — словарь InnoDB. Давайте рассмотрим таблицы из словаря и посмотрим, что он может нам дать. Нам понадобится четыре из них:

  • SYS_TABLES
  • SYS_INDEXES
  • SYS_COLUMNS
  • SYS_FIELDS

SYS_TABLES
Здесь InnoDB хранит соответствие между понятными человеку именами таблиц и их внутренними идентификаторами.

CREATE TABLE `SYS_TABLES` (
  `NAME` varchar(255) NOT NULL DEFAULT '',
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `N_COLS` int(10) DEFAULT NULL,
  `TYPE` int(10) unsigned DEFAULT NULL,
  `MIX_ID` bigint(20) unsigned DEFAULT NULL,
  `MIX_LEN` int(10) unsigned DEFAULT NULL,
  `CLUSTER_NAME` varchar(255) DEFAULT NULL,
  `SPACE` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

NAMEявляется удобочитаемым именем таблицы в форме имя_базы_данных / имя_таблицы, например sakila/actor. IDэто идентификатор таблицы Нам понадобится идентификатор таблицы, чтобы найти индексы таблицы.

mysql> select * from SYS_TABLES WHERE NAME='sakila/actor';
+--------------+-----+--------+------+--------+---------+--------------+-------+
| NAME         | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+--------------+-----+--------+------+--------+---------+--------------+-------+
| sakila/actor | 741 |      4 |    1 |      0 |       0 |              |   738 |
+--------------+-----+--------+------+--------+---------+--------------+-------+

SYS_INDEXES В
этой таблице перечислены все индексы, которые есть в таблице, как вторичные, так и первичные.

CREATE TABLE `SYS_INDEXES` (
  `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(120) DEFAULT NULL,
  `N_FIELDS` int(10) unsigned DEFAULT NULL,
  `TYPE` int(10) unsigned DEFAULT NULL,
  `SPACE` int(10) unsigned DEFAULT NULL,
  `PAGE_NO` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TABLE_ID`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Итак, TABLE_IDнаш table_id. IDвот идентификатор индекса. Нам нужно найти страницы InnoDB, которые относятся к индексу таблицы. Который из? Таблица может иметь много вторичных индексов, но только в первичном индексе мы можем найти все поля. Он должен существовать для любой таблицы InnoDB. Если явно определено, NAMEэто ПЕРВИЧНО. Если первичный ключ не определен, InnoDB будет использовать уникальный вторичный индекс в качестве первичного. Если нет уникального индекса, InnoDB создаст его неявно. Его имя будет GEN_CLUST_INDEX.
Не имеет значения, как создается первичный индекс, он будет минимальным IDсреди индексов таблицы.

mysql> select * from SYS_INDEXES WHERE TABLE_ID=741;
+----------+------+---------------------+----------+------+-------+---------+
| TABLE_ID | ID   | NAME                | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+------+---------------------+----------+------+-------+---------+
|      741 | 1679 | PRIMARY             |        1 |    3 |   738 |       3 |
|      741 | 1680 | idx_actor_last_name |        1 |    0 |   738 |       4 |
+----------+------+---------------------+----------+------+-------+---------+

SYS_COLUMNS
Таблица SYS_COLUMNSхранит имена полей и информацию о типе таблицы.

CREATE TABLE `SYS_COLUMNS` (
  `TABLE_ID` bigint(20) unsigned NOT NULL,
  `POS` int(10) unsigned NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `MTYPE` int(10) unsigned DEFAULT NULL,
  `PRTYPE` int(10) unsigned DEFAULT NULL,
  `LEN` int(10) unsigned DEFAULT NULL,
  `PREC` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TABLE_ID`,`POS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Вот TABLE_IDизвестный идентификатор таблицы, POS— позиция поля в таблице. NAMEэто имя поля, MTYPEи PRTYPEхранить информацию о типе поля, свойство , кодирующий, NULL / NOT NULL и т.д.
LENэто максимальное число байт , поле использует для хранения значения. Я не уверен, для чего PRECиспользуется. Это звучит как сокращение от «точности», но, по крайней мере, для типа DECIMAL, где это имеет смысл, оно все равно равно нулю. Если вы знаете, как InnoDB использует, PRECпожалуйста, дайте мне знать.

mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741;
+----------+-----+-------------+-------+---------+------+------+
| TABLE_ID | POS | NAME        | MTYPE | PRTYPE  | LEN  | PREC |
+----------+-----+-------------+-------+---------+------+------+
|      741 |   0 | actor_id    |     6 |    1794 |    2 |    0 |
|      741 |   1 | first_name  |    12 | 2162959 |  135 |    0 |
|      741 |   2 | last_name   |    12 | 2162959 |  135 |    0 |
|      741 |   3 | last_update |     6 |    1799 |    4 |    0 |
+----------+-----+-------------+-------+---------+------+------+

Итак, мы знаем все поля таблицы, мы можем получить тип. Достаточно ли для восстановления? Нет.

SYS_FIELDS
Нам нужно знать, какие поля образуют первичный ключ. Дело в том, что независимо от того, в какой позиции поля первичного ключа определены внутри CREATE TABLEоператора, они всегда идут первыми в записи. Второй вопрос, который мы должны учитывать, это внутренние поля DB_TRX_IDи DB_ROLL_PTR. Эти два поля всегда находятся между полями первичного ключа и остальными полями.
SYS_FIELDSперечисляет поля всех индексов, включая первичные.

CREATE TABLE `SYS_FIELDS` (
  `INDEX_ID` bigint(20) unsigned NOT NULL,
  `POS` int(10) unsigned NOT NULL,
  `COL_NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`INDEX_ID`,`POS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Имена полей объясняют их содержание.
Идентификатор индекса в нашем примере — 1679:

mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679;
+----------+-----+----------+
| INDEX_ID | POS | COL_NAME |
+----------+-----+----------+
|     1679 |   0 | actor_id |
+----------+-----+----------+

Это означает, что первичным ключом таблицы является одно поле actor_id.

Теперь у нас есть вся необходимая информация для генерации оператора CREATE TABLE.

0. Загрузите последнюю версию инструмента восстановления

bzr branch lp:percona-data-recovery-tool-for-innodb

1. Скомпилируйте словарь парсеров

make dict_parsers

2. Разделить ibdata1 с page_parser

./page_parser -f /var/lib/mysql/ibdata1

3. Восстановите SYS_TABLES, SYS_INDEXES, SYS_COLUMNS и SYS_FIELDS из индексов 0-1, 0-3, 0-2 и 0-4 соответственно.

./bin/constraints_parser.SYS_FIELDS -4f pages-ibdata1/FIL_PAGE_INDEX/<index_id>

4. Загрузите дампы таблиц словаря на какой-нибудь сервер MySQL. Использование LOAD DATA INFILE constraints_parserгенерирует

mysql>LOAD DATA INFILE '/path/to/SYS_FIELDS' REPLACE INTO TABLE `SYS_FIELDS` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_FIELDS\t' (`INDEX_ID`, `POS`, `COL_NAME`);

5. Теперь все готово для генерации CREATE TABLEоператора для таблицы:

./sys_parser -u root sakila/actor
CREATE TABLE `actor`(
        `actor_id` SMALLINT UNSIGNED NOT NULL,
        `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
        `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
        `last_update` TIMESTAMP NOT NULL,
        PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB;

В конце есть две заметки:

  • Созданная структура отличается от исходной структуры таблицы, но она достаточно хороша для восстановления.
  • Поля DECIMAL поддерживаются не полностью. Если поле имеет тип DECIMAL (5,2), sys_parser сгенерирует DECIMAL (5,0). Это должно быть исправлено вручную

37
Share: