Стратегия восстановления поврежденной таблицы в MySQL

Началось все с того, что в один прекрасный момент ядро прибило демона mysqld и mysql_safe автоматом его перезапустил и все бы хорошо, да только таблицы в БД использовались MyISAM. В итоге пришлось воспользоваться myisamcheck но это совсем другая история. В процессе проверки и починки индексов пострадала одна таблица и было принято решение восстанавливать из бекапов, хорошо, что раз в сутки делаются.

Исходные данные:

• имеем сервер БД с MySQL на борту;
• поврежденную таблицу логов(статистики) чего угодно, что постоянно заполняется и может например не использоваться какое-то время;
• суточный бекап;
• бинарные логи с последнего суточного(полного) бекапа.

Задача:

• сервер должен быть доступен для работы;
• новые данные должны попадать в таблицу;
• восстановить целостность данных.

Ожидаемый результат:
данные в поврежденной таблицы восстановлены без останова базы дынных;
таблица содержит все данные включая текущие.

Условные обозначения:

• DB-SRV — север с базой данных;
• ACME_DB — база данных в которой “потерянная” таблица;
• ACME_DB_RECOVERY — база данных для восстановления, таблицы или базы данных;
• ACME_DB_INCREMENTAL — база данных для восстановления из бинарного лога;
• FAIL_TABLE — поврежденная таблица подлежащая востановленияю;
• ACME_DB.FAIL_TABLE.BACKUPDATE.sql — файл с дампом поврежденной таблицы из последнего полного бекапа.

Для чего гордился такой огород. А все из-за размеров БД и недостатка ресурсов, на текущий момент используется по-табличный дамп, посему о целостности данных на момент бекапа речи не идет.

Вместо дисклаймера:

• Статья написана с целью ознакомить с еще одной стратегией восстановления данных при падении таблицы. Использовать все ASIS строго не рекомендуется. Ну и вы сами в ответе за все то, что происходит с вашими данными :)

План действий(восстановления):

Делаем копию поврежденной таблицы, чтобы текущие данные было куда складывать

mysql > CREATE TABLE FAIL_TABLE_NEW LIKE FAIL_TABLE;

Если таблица с полем AUTO_INCREMENT то выполняем еще изменения счетчика. Значение счетчика увеличиваем на сколько-нибудь, например на 1000 от текущего значения в таблице FAIL_TABLE.
mysql> ALTER TABLE FAIL_TABLE_NEW AUTO_INCREMENT = value;

mysql > RENAME FAIL_TABLE TO FAIL_TABLE_OLD, FAIL_TABLE_NEW TO FAIL_TABLE;

Создаем вспомогательные БД ACME_DB_RECOVERY и ACME_DB_INCREMENTAL

mysql > CREATE DATABASE ACME_DB_RECOVERY;
mysql > CREATE DATABASE ACME_DB_INCREMENTAL;

Для параноиков :) можно для каждой вспомогательной базы сделать по пользователю.

mysql > CREATE USER 'recovery'@’localhost' IDENTIFIED BY 'mypass';
mysql > CREATE USER 'increment'@’localhost' IDENTIFIED BY 'mypass';

mysql > GRANTSELECT,INSERT,UPDATE,DELETE,LOCK TABLES,SUPER,INDEX,CREATE ON ACME_DB_RECOVERY TO 'recovery'@'localhost';
mysql > GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,SUPER,INDEX,CREATE ON ACME_DB_INCREMENTAL TO 'increment'@'localhost';
mysql > FLUSH PRIVILEGES;

Восстанавливаем поврежденную таблицу из полного бекапа

$ mysql -u recovery -p -h DB-SRV ACME_DB_RECOVERY < ACME_DB.FAIL_TABLE.BACKUPDATE.sql

Дальше идем в datadir серевера и находим фалы вида server_hostname-bin.004324

Находим подходящий бинлог который начинается раньше чем данные в таблице из бекапа и бинлог с данными до момента повреждения, посмотреть что в бинарном логе можно командой mysqlbinlog -d ACME_DB .

Для того чтобы восстановиться из бинлога надо создать полную структуру таблиц ACME_DB

$ mysqldump --no-data -u ACME_USER -p ACME_DB -h DB-SRV | mysql -h DB-SRV -u increment -p ACME_DB_INCREMENTAL

И вводим данные из бинарных логов в порядке их создания.

$ mysqlbinlog -d ACME_DB <бинарный лог> | mysql -u increment -p ACME_DB_INCREMENTAL

Дальше нам надо соеденить данные из дампа и бинарных логов.

Определяем последние данные в таблице из бекапа, с этого “момена” мы будем добавлять данные из таблицы восстановленной из бинарных логов и первые данные из временной таблицы. Например вот такими вот запросами:

mysql > USE ACME_DB;

mysql > SELECT MIN(id) FROM FAIL_TABLE; # FIRST_ID
mysql > SELECT MIN(date) FROM FAIL_TABLE; # FISRT_DATE

mysql> USE ACME_DB_RECOVERY;

mysql > SELECT MAX(id) FROM FAIL_TABLE; # LAST_ID
mysql > SELECT MAX(date) FROM FAIL_TABLE; # LAST_DATE

mysql > INSERT INTO FAIL_TABLE (SELECT * FROM ACME_DB_INCREMENTAL.FAIL_TABLE WHERE ID > LAST_ID AND ID < FIRST_ID);
#или
mysql > INSERT INTO FAIL_TABLE (SELECT * FROM ACME_DB_INCREMENTAL.FAIL_TABLE WHERE DATE >= LAST_DATE AND DATE < FIRST_DATE);


Итак, данные востановили до момента падения, теперь дело за малым переносим данные, пример будет для таблицы с полем AUTO_INCREMENT, если такгово нет, место с установкой счетчика можно пропустить.

Смотрим какой сейчас счетчик в таблице ACME_DB.FAIL_TABLE, устанавливаем в таблицу ACME_RECOVERY.FAIL_TABLE счетчик на несколько больше, все зависит от того сколько записей у вас вставляется в базу, несколько тыс. должно хватить
mysql > USE ACME_RECOVERY;
mysql > ALTER TABLE FAIL_TABLE AUTO_INCREMENT = value;

mysql > USE ACME_DB;
mysql > RENAME TABLE FAIL_TABLE TO FAIL_TABLE_SMALL, ACME_RECOVERY.FAIL_TABLE TO ACME_DB.FAIL_TABLE;
mysql > INSERT INTO FAIL_TABLE (SELECT * FROM FAIL_TABLE_SMALL);

Удаляем все временные данные

mysql > DROP DATABASE ACME_DB_INCREMENTAL;
mysql > DROP DATABASE ACME_DB_RECOVERY;

update:
zhirafovod подсказал, что в бинлог одной базы могут попасть данные на изменения других, например update ACME2.TABLE…
Чтобы избежать изменений, следует обязательно выставлять привилегии для новых баз данных и специально созданных для них пользователей.