Восстановление базы 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
Читайте также:
  • Хранимые процедуры в MySQL
    Что для этого нужно? Установите MySQL сервер версии 5 или выше (dev.mysql.com/downloads). Процедуры можно создавать как запросы, например через командную строку MySQL, но для удобства советую скачать MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет включает в себя три программы - My...
  • Представления (VIEW) в MySQL
    Представления иногда называют "виртуальными таблицами". Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит...
  • Библиотека отладчика MySQL
    Автор зачем-то приводит заголовок файла dbug.c из исходников пакета (вместе со всеми комментариями к нему). Приведу его и я, поскольку это может быть продиктовано какими-либо юридическими проблемами с копирайтом. Поскольку я не юрист, привожу этот заголовок без какого-либо перевода (во избежание оши...
  • Интерфейс MySQL с perl (MySQL perl API)
    Простой интерфейс с perl СУБД MySQL Этот интерфейс представляет собой первый способ обратиться к базе данных MySQL из программы на perl. Коротко о главном:   use Mysql;  $dbh = Mysql->Connect;  $dbh = Mysql->Connect($host);  $dbh = Mysql->Connect($host,$database);&n...
  • Администрирование пакета MySQL
    Предоставление доступа к базам данных СУБД MySQL использует специальную базу данных для предоставления прав доступа к своим базам данных. Эти права могут базироваться на именах серверов и/или пользователей и предоставляться для одной или нескольких баз данных Пользовательские аккаунты могут быть...