бинарный журнал обновлений mysql
Применимость: Linux, MySQL
Слова для поиска: бинарные логи, mysql-bin
Задача:
- Нужно средство восстановления данных в случае повреждения базы данных или нарушения ее целостности.
- Я обнаружил в /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.
Для предотвращения добавим еще и параметр -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
