Стратегия восстановления поврежденной таблицы в 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…
Чтобы избежать изменений, следует обязательно выставлять привилегии для новых баз данных и специально созданных для них пользователей.
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 9

    +1
    Спасибо за методику. Не дай бог воспользоваться, но береженого Бог бережет.
      +2
      Честно говоря это все костыли которые приходится использовать за не имением нормальных возможностей.
      Хотя пусть лучше так чем совсем без ничего.
      +2
      Люблю такие статьи, спасибо.
      Обычно к ним комментариев мало, но все добавляют в избранное.
      Как гайд на будущее, если его случится.
      Зато спокоен, знаешь, что на Хабре лежит и искать долго не придется.
        0
        Хорошо, что с пользой… Лучше больше пользы чем комментариев.
        0
        Интересно конечно. Но, позволь расскажу другую историю (может пригодится) у меня используется просто тупой метод копирования (данных не очень много проект не особо крупный). В момент разработки системы, чтобы сэкономить время таблицы myisam тупо копируются в файловое хранилище причем не за один день, а за целый месяц, вернее в имени каждого архива имеется дата. В случае существования такого архива он перезаписывается. Поскольку таблицы myisam, а именно файлы .MYD, .frm и .MYI можно спокойно копировать сохраняя их целостность, соответственно точно так же и восстанавливать, они копируются, архивируются и лежат ждут своей участи. Может так больше места тратится, но быстрее и проще.
        P.S. С таблицами innoDB так к сожалению не прокатит.
          0
          Копировать хорошо если БД не занимает 50Gb… НУ а таблиц innoDB есть xtrabackup, вроде как сохраняет целостность…
          +1
          1. У вас присутствует одна неточность, которая может серьёзно подпортить вам жизнь. В бинлог попадают записи как
          use ACME;
          update/insert blah blah
          так и
          USE DB2;
          update ACME.tablename

          mysqlbinlog -d ACME_DB <бинарный лог> | mysql -u increment -p ACME_DB_INCREMENTAL
          выведет конструкции с дефолтной базой только, пропустив
          update ACME.tablename;

          В официальной доке по mysqlbinlog пример расписан
          dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html

          2. Для целостного бэкапа рекоменду поднимать реплику mysql, с которой легко можно сделать целостный бэкап остановив репликацию.
            0
            тут я с вами согласен, на 100%, только считаю, что использовать «внешние» базы данных в запросах не есть хорошо.
              0
              Обновил немного пост, спасибо.

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

            Only users with full accounts can post comments. Log in, please.