Восстановление базы 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

    Полезные ссылки:
    Поделиться публикацией

    Комментарии 28

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

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

                    У меня были UPDATE и DELETE, но перед ними в логи попали и все CREATE с INSERT'ами. Поэтому всё без дампа и обошлось.
                      0
                      упс, потерялось = (>= 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
                      Если бинлог ведется с момента создания базы — то все изменеия будут в нем, и дамп не потребуется.
          +3
          Угу, только надо не забывать про следующее:
          1. Восстановление из бинлогов возможно только тогда, когда они велись с самого начала. Либо если есть слепок первоначального состояния базы данных, после которого начали вестись логи.
          2. На нагруженных серверах бинлоги быстро убивают пространство на диске, поэтому их хранят не все, а некоторое количество последних. Предыдущие же либо пакуются, либо вовсе удаляются. Понятно, что в последнем случае восстановить ничего не удастся.
          3. Еще одно назначение бинлогов — репликация. Если есть реплика, безусловно легче восстановить данные с нее, чем из бинлогов.
            +1
            И ещё вопрос снижения производительности при включённых логах — ведь ничего бесплатно не бывает
              0
              Разумеется, но это уже вопрос из другой темы, в данном топике считается, что логи ведутся априори, так что дабы не разводнять, я этого касаться не стал )
                0
                Где-то, кажется в доках мускула, видел цифру в 1% снижения производительности. Насколько это близко к реальности не знаю.
                0
                Благодарю за комментарий.
                То есть, после удаления старых логов нужно обязательно слепок делать? Расскажите, пожалуйста подробнее, можно ссылкой на описание этой процедуры.

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

                  2. Вероятно все ваши операции с вашей удаленной базой уложились как раз в один бинлог-файл, потому и смогли восстановить.
                    0
                    Спасибо большое. Пригодится.
                    • НЛО прилетело и опубликовало эту надпись здесь
                        +1
                        Можно использовать бэкап + записи в бинлоге после создания бекапа. Утилита mysqlbinlog может выбирать записи в бинлоге начиная с определенного времени.

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

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

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

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

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

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

                            Вообщем, настроил сервер как полагается, логи ведутся, всё ок.

                            Намеренно удаляю 15 записей в таблице…
                            Вытаскиваю из бинлога sql… и вижу там свой delete запрос… отлично, но чем это мне поможет? мне же важны были именно удалённые данные…

                            Попытался сделать insert в очищенную таблицу, и он в логах вообще не зафиксировался…

                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                            Самое читаемое