войти зарегистрироваться

MySQL whois

индекс
202,61

Восстановление базы MySQL из бинарных логов

Базы данных иногда пропадают. Человеческий фактор и всё-такое… Если вы не делали бекапов (а надо бы) или они уже устарели, не отчаивайтесь, — есть ещё возможность восстановить утерянную информацию.

Начиная с версии 4.1.3 MySQL записывает все запросы, которые приводят к изменению данных, в бинарный журнал обновлений (binary log). Но эта опция может оказаться и отключенной по умолчанию. На моей домашней машине, например, бинарные логи не велись после установки. Включил раскомментировав строку log_bin = /var/log/mysql/mysql-bin.log в конфигурационном файле (my.cnf). На сервере же велись изначально. Впрочем, там и параметр в настройках выглядит иначе: log-bin = mysql-bin. Ведутся логи и в том случае, если MySQL запущен с ключом --log-bin[=file_name].

Журнал обновлений обычно хранится в файлах вида mysql-bin.циферки. Если в настройках не задан полный путь, эти файлы будут размещены в директории, в которой MySQL хранит базы данных.

Возможно, для восстановления базы, отдельных таблиц или даже конкретных записей вам будут нужны не все файлы журнала. Смотрите на дату их изменения.

Для работы с бинарными логами понадобится утилита mysqlbinlog. Она идёт в комплекте с MySQL сервером. Утилита обрабатывает файлы журнала и выводит пригодный к использованию SQL код прямо в консоль. Вывод можно перенаправить в файл (mysqlbinlog [параметры] [лог_файлы] > файл.sql), непосредственно в MySQL (mysqlbinlog [параметры] | mysql [параметры]) или указать файл для вывода в параметрах утилиты. Например:

mysqlbinlog -s -d db_name -r out.sql mysql-bin.000012

В данном случае будет обработан файл mysql-bin.000012 (из текущей директории), вывод оправится в out.sql, будут выведены только команды, относящиеся к изменению базы с именем db_name. Параметром -s мы запретили вывод дополнительной служебной информации.

Ещё пример:

mysqlbinlog -s -d db_name -u user_name --start-datetime="2009-01-23 21:10:00" -t mysql-bin.000001 > out.sql

Здесь мы, кроме прочего, ограничиваемся выводом запросов, которые выполнялись пользователем user_name начиная с указанной даты. Параметр -t сообщает утилите, что нужно обрабатывать и логи, которые идут после файла mysql-bin.000001. Обратите внимание, что если вы будете перенаправлять вывод сразу в MySQL, то в журнал обновлений добавятся свежие записи и произойдёт зацикливание. Для предотвращения следует добавить ещё и параметр -D, который запрещает ведение лога. Запрет будет доступен только если выполнять команду из под рута.

Остальные параметры подсматриваем так же, как и для любой консольной программы:

mysqlbinlog --help

Вообще, перенаправление вывода прямо в мускул не рекомендуется. Кроме того, если использовать промежуточный SQL файлик, оттуда можно удалить злополучный DROP DATABASE, привёвший к потере данных. Файлик вам может понадобиться и в будущем.

Для восстановления базы из SQL файла, воспользуемся командой:

mysql -u user_name -p < out.sql

И не забываем делать бекапы:

mysqldump db_name > backup.sql

Или даже так:

mysqldump -u user_name --password -A > backup.sql

Полезные ссылки:
+55
23 января 2009, 22:48
Apostol

комментарии (27)

  • Спасибо за инфу!
    Желаю всем, чтобы не пригодилась!
    • Пожалуйста!
      Мне вчера ночью как раз и пригодилось бы. Завалил по-неосторожности данные, которые месяц собирал. Решение проблемы не сразу нашёл.
      • и у тебя совершенно случайно включены бинлоги? :-)
        имхо — если бинлоги включены, значит человек знает, зачем они нужны и как с ними работать.
        • Эм… Я их на сервере не включал. Получается, что случайно. :)
          На домашней машине проверил — не включены по умолчанию. Сейчас в заметку добавлю. Спасибо!
          • хех, везунчик :-) но, как ты заметил в статье, одних бинлогов мало — нужен ещё и дамп :-)
            или ты дважды везунчик, что у тебя и бинлоги, и дамп, сделанный от начала их ведения (или уже во время)
            • Если не трижды. Оказывается, ещё и сбрасываться логи могут при достижении определённых размеров или спустя какое-то время. В настройках задаётся.

              Мне дамп не понадобился (его и не было) все изменения базы попали в логи.
              • в текущих стабильных версиях в бинлог не попадают изменения. туда — попадают запросы, которые затрагивают > 1 записи.
                если там были UPDATE/DELETE, то я не верю, что по бинлогам можно что-то получить без дампа.
                • В лог попадают все запросы, которые изменяют данные, а не просто затрагивают. ;) И, наверное, всё-таки изменяют > 0 записей.

                  У меня были UPDATE и DELETE, но перед ними в логи попали и все CREATE с INSERT'ами. Поэтому всё без дампа и обошлось.
                  • упс, потерялось = (>= 1)

                    кстати
                    «The binary log contains all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows).»

                    т.е. даже не обязательно данные изменять :-)
                • Если бинлог ведется с момента создания базы — то все изменеия будут в нем, и дамп не потребуется.
  • Угу, только надо не забывать про следующее:
    1. Восстановление из бинлогов возможно только тогда, когда они велись с самого начала. Либо если есть слепок первоначального состояния базы данных, после которого начали вестись логи.
    2. На нагруженных серверах бинлоги быстро убивают пространство на диске, поэтому их хранят не все, а некоторое количество последних. Предыдущие же либо пакуются, либо вовсе удаляются. Понятно, что в последнем случае восстановить ничего не удастся.
    3. Еще одно назначение бинлогов — репликация. Если есть реплика, безусловно легче восстановить данные с нее, чем из бинлогов.
    • И ещё вопрос снижения производительности при включённых логах — ведь ничего бесплатно не бывает
      • Разумеется, но это уже вопрос из другой темы, в данном топике считается, что логи ведутся априори, так что дабы не разводнять, я этого касаться не стал )
      • Где-то, кажется в доках мускула, видел цифру в 1% снижения производительности. Насколько это близко к реальности не знаю.
    • Благодарю за комментарий.
      То есть, после удаления старых логов нужно обязательно слепок делать? Расскажите, пожалуйста подробнее, можно ссылкой на описание этой процедуры.

      Кстати, я восстанавливал удалённую базу по одному последнему лог-файлу. Причём, пробовал и копировать его в отдельную папку, где более старых логов не было. Всё прекрасно восстановилось. Как-то можете это прокомментировать?
      • 1. Зависит от многих факторов, например есть ли реплика? Если есть, то дамп надо делать на ней, это не потребует залочки мастера. Если нет, основной алгоритм такой — лочим базу, делаем дамп, делаем purge bunary logs, разлочиваем базу. Имеем — свежий слепок БД и бинлоги начавшиеся с момента бэкапа.

        2. Вероятно все ваши операции с вашей удаленной базой уложились как раз в один бинлог-файл, потому и смогли восстановить.
        • Спасибо большое. Пригодится.
        • Подскажите, пожалуйста, а что делать в такой ситуации:
          * сохраняем бинлоги, они жрут много места, удаляем логи старше 10 дней
          * делаем бэкап в полночь каждый день
          * произошёл сбой днём и mysql-сервер встал
          * т.е. есть дамп и бинлог, но бинлог не начат в момент бэкапа
          • Можно использовать бэкап + записи в бинлоге после создания бекапа. Утилита mysqlbinlog может выбирать записи в бинлоге начиная с определенного времени.

            mysqlbinlog --start-datetime=«2005-12-25 11:25:56» binlog.000003
          • Раз в 10 дней:
            — Лочите все таблицы
            — Делаете дамп
            — Выполняете PURGE BINARY LOGS
            — Разлочиваете таблицы
            — Удаляете старые логи

            При сбое в любой момент можете восстановить все данные. Каждый день дампы делать нет смысла, если используете бинарный журнал.
            • Пардон, удалять старые логи и не нужно будет. Это происходит автоматически при выполнении PURGE LOGS.
  • поставил в закладки, спасибо
  • Еще важно помнить, что по умолчанию MySQL не синхронизирует бинарный лог с диском для каждой операции. Т.е. если машина упала, то с большой долей вероятности у вас будут разные данные в базе и в логах (бинарных). Проблему можно решить установкой параметра sync_binlog=1. Это значить, что MySQL будет синхронизировать лог с диском для каждой операции (если поставить 5, то для каждые 5 операций). Естественно, это отразиться на производительности, но это уже другая история.

  • Ё-маё. С каких пор изложение мана своими словами стало называться статьей? Если уж затронули тему восстановления БД, так сделали бы обзор ВСЕХ способов, их плюсы и минусы, в каких условиях приемлимы и т.д. А то одни вершки, которые новичкам только мозг запудрят. Вот, например, почему в «статье» о бинарных логах MySQL не упоминается о BLACKHOLE? Да потому что автор не затруднил себя детальным изучением вопроса и скорее всего вообще о BH не слышал.
    • Пост писал в первую очередь в таком виде, в каком он мне самому бы пригодился в случае необходимости. Для более подробного изучения изложенного есть ссылки в конце поста.

      Вы бы вместо того, чтоб возмущаться, взяли бы, да написали обзор всех методов. А мы бы вам были очень благодарны.
  • Не согласен в вами. Хабр — это сообщество, поэтому, если пишите статью — пишите для всех. А «заметки для себя» лучше писать в блокноте. Инет итак переполнен подобными отрывочными, однобокими заметками. В гугле они находятся за одну секунду по 20 штук на страницу выдачи. Давайте уже как-то повышать качество материалов на хабре.

    Я не считаю у меня достаточно знаний для толковой статьи посвященной бэкапу БД. Поэтому и не спешу пересказывать на хабре только что прочитанные маны.

    Простите за суровое отношение, ваш материал (хоть я и не в восторге от него) лучше многих, что в последнее время публикуют «авторы». Но, повторюсь еще раз, вам есть куда расти.
    • само собой, это был ответ автору топика.
Только авторизованные пользователи могут оставлять комментарии. Авторизуйтесь, пожалуйста.