бинарный журнал обновлений mysql

Применимость: Linux, MySQL

Слова для поиска: бинарные логи, mysql-bin


Задача:

  1. Нужно средство восстановления данных в случае повреждения базы данных или нарушения ее целостности.
  2. Я обнаружил в /var/log/mysql множество огромных файлов с именами типа mysql-bin.000001, mysql-bin.000002. Как их удалить?

Решение:

Если данные на вашем сервере представляют некую ценность, то будет разумно обеспечить условия для возможности восстановления данных в случае их повреждения.

В этом случае резервное копирование нужно обязательно, но нередко и резервная копия может оказаться неработоспособной, неактуальной и т.п.

В MySQL предусмотрена возможность ведения бинарного журнала обновлений который содержит всю информацию об измененных данных с начала ведения журнала. Это может быть использовано для репликации данных между серверами или, в случае необходимости, для восстановления данных. Например, у вас периодичность резервного копирования один раз в неделю или в день, но если у вас есть бинарный журнал, то вы сможете восстановить состояние базы и на момент более поздний чем имеющаяся резервная копия.

Конфигурация

Активируем бинарные логи в секции [mysqld] файла /etc/my.cnf (/etc/mysql/my.cnf)

/etc/my.cnf

#включить журнал
log-bin=mysql-bin

# Хранить 7 дней.
expire_logs_days = 7

# Кэш бинарного лога
binlog-cache-size = 128K

# Синхронная запись в файл журнала. Замедляет работу, но опция необходима если ваш сервер не имеет источник бесперебойного питания или возможно некорректное завершение работы сервера по другим причинам.
sync-binlog = 1

# лимит размера лога "файла"
max-binlog-size = 350M

# максимальный размер кэша.
max-binlog-cache-size = 90M

После перезагрузки mysql вы увидите в /var/lib/mysql файл mysqld-bin.00001

Восстановление базы

Для работы с бинарными логами понадобится утилита mysqlbinlog. Утилита обрабатывает файлы журнала и выводит пригодный к использованию 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 -D -s -d NAME_BD -u USER_BD --start-datetime="2013-05-08 19:35:15" -t mysql-bin.000001 > my_new_bd.sql
  • -s запрещаем вывод дополнительной служебной информации.
  • -d база-данных имя.
  • -u пользователь базы-данных.
  • -D который запрещает ведение лога.
  • -t говорим, что обрабатывать и логи нужно.

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

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

Для предотвращения добавим еще и параметр -D, который запрещает ведение лога. Этот запрет будет доступен только если выполнять команду из под рута.

Теперь можно загрузить в базу восстановленные из журнала данные.

mysql -u root -p < my_new_bd.sql

Удаление файлов журнала

Если у вас проблемы с дисковым пространством и файлы логов надо будет срочно удалить…

Различные варианты удаления:

Удалить логи до mysql-bin.010

echo 'PURGE BINARY LOGS TO 'mysql-bin.010';' | mysql -u root -pPASSWORD

Удалить логи до 2008-04-02 22:46:26

echo 'PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';' | mysql -u root -pPASSWORD

Удалить логи старше 3-х дней

echo 'PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;' | mysql -u root -pPASSWORD

Удалить все логи

echo 'FLUSH LOGS;' | mysql -u root -pPASSWORD
echo 'RESET MASTER;' | mysql -u root -pPASSWORD

Если нужно изменить параметр срока хранения логов без перезагрузки сервера:

echo 'SET GLOBAL expire_logs_days=7;' | mysql -u root -pPASSWORD
echo 'RESET MASTER;' | mysql -u root -pPASSWORD

Смотрите также: