Настройка mysqldump, проверка и мониторинг бэкапов mysql

Бэкап баз данных mysql является обязательной и регулярной процедурой при сопровождении информационной системы. Я расскажу, как делаю бэкап mysql с помощью mysqldump, а так же покажу, как сделать проверку и настроить мониторинг бэкапов баз данных. В качестве систем мониторинга будет выступать Zabbix.

Настройка mysqldump

Как я уже сказал, бэкап базы данных mysql я буду делать с помощью mysqldump. В общем случае выполнить его проще простого. Вот пример команды, которая сделает дамп базы данных:

# mysqldump --databases dbname -u'root' -p'password' > dbname.sql

Отдельно отмечу, что использовать выгрузку сырых данных из базы в виде текстового дампа имеет смысл для не очень больших баз. Думаю, что для баз размером до 10-15 Гб это можно делать. Сжатые дампы будут весить 500-1000 Мб. Если базы больше, лучше использовать другие способы. Например, бинарный бэкап с помощью Percona XtraBackup.

Мы выгрузили дамп базы dbname в отдельный файл. Дальше его можно сжать и отправить на сервер бэкапов. Но, как это обычно бывает, везде есть куча нюансов. Дефолтные настройки mysqldump очень часто не подходят. Например, наиболее распространенная проблема - блокировка таблиц во врем дампа с дефолтными настройками. Если в базу активно идёт запись, а тут вы запускаете mysqldump и блокируете таблицы, возникают проблемы с записью.

Все параметры mysqldump можно посмотреть в документации - https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html. Если вы не указываете никакие дополнительные ключи, то по умолчанию используется ключ --opt, который включает в себя следующие параметры:

• --add-drop-table - в дампе перед каждым созданием таблицы добавляется строка с её удалением. То есть при заливке дампа сначала удаляется таблица, потом создается пустая и в неё заливаются данные из дампа. И так для всех таблиц.
• --add-locks - в дампе в строке перед созданием таблицы ставится команда на ее блокировку, а после окончания заливки данных блокировка снимается. Это позволяет гарантировать успешную запись данных в таблицу при загрузке дампа.
• --create-options - в дамп добавляются команды на создание таблиц.
• --disable-keys - в дамп добавляются параметры, отключающие создание индексов рядом с каждой командой insert. Это позволяет ускорить загрузку дампа, а индексы создаются, когда все строки будут добавлены.
• --extended-insert - используется особый синтаксис multiple-row для команд insert.
• --lock-tables - блокировка таблиц перед созданием дампа. Этот параметр частенько мешает в движке innodb и его лучше не использовать (параметр, а не движок).
• --quick - ускоренный механизм получения строк таблицы.
• --set-charset - добавляет в дамп информацию о кодировках.

В целом, все дефолтные параметры можно считать полезными и удобными, кроме блокировки таблиц. Для дампа innodb баз, а это самый популярный движок хранения данных в mysql, рекомендуется не использовать lock-tables, а вместо этого включать механизм single-transaction. С этим параметром для обеспечения целостности данных в дампе используется не механизм блокировок, а учёт транзакций.

Я не буду сейчас подробно останавливаться на теории, так как все эти параметры очень хорошо описаны в документации. Можете сами зайти и почитать. Показываю свой набор ключей для создания дампов баз данных mysql с помощью mysqldump.

# mysqldump --add-drop-database --add-locks --create-options --disable-keys --extended-insert --single-transaction --quick --set-charset --routines --events --triggers --comments --quote-names --order-by-primary --hex-blob --databases dbname -u'root' -p'password' > dbname.sql

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

Проверка дампа mysql

То, что вы сделали дамп базы совсем не значит, что он завершился успешно. Он может быть прерван по какой-то причине. Причем, он год может выполняться успешно, а в какой-то момент начнет давать сбой в самом конце выгрузки. Если никак не отслеживаете ситуацию, можете это не заметить, а потом получить неконсистентную (битую) выгрузку.

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

-- MySQL dump 10.13 Distrib 5.7.33-36, for Linux (x86_64)

У вас может отличаться версия сервера, но начало будет одинаковое - двойное тире и слово Mysql. В конце дампа будет следующая строка:

-- Dump completed on 2021-07-11 20:21:06

Эти две строки я и буду проверять в скрипте, который будет делать дамп в автоматическом режиме. Результат проверки бэкапа я буду записывать в лог файл, а заодно и вывод утилиты mysqldump, чтобы в случае какой-то ошибки, я смог его проанализировать.

#!/bin/bash

#ключи mysqldump
OPTS="-v --add-drop-database --add-locks --create-options --disable-keys --extended-insert --single-transaction --quick --set-charset --routines --events --triggers --comments --quote-names --order-by-primary --hex-blob"
#файл дампа
FNAME="/mnt/backup/`date +%Y-%m-%d_%H-%M`_dbname.sql"
#лог результатов проверки дампа
LOG="/var/log/mysql/backup.log"
#лог вывода mysqldump
MLOG="/var/log/mysql"
#формат даты
DATA=`date +%Y-%m-%d_%H-%M`

#делаем дамп базы и записываем вывод в лог
/usr/bin/mysqldump ${OPTS} --databases dbname -u'root' -p'password' > ${FNAME} 2>> ${MLOG}/${DATA}-mysqldump.log
#сжимаем лог
/usr/bin/gzip ${MLOG}/${DATA}-mysqldump.log

#проверяем первую и последнюю строки дампа
BEGIN=`head -n 1 ${FNAME} | grep ^'-- MySQL dump' | wc -l`
END=`tail -n 1 ${FNAME} | grep ^'-- Dump completed' | wc -l`

#если обе строки верны, то пишем в лог ОК и сжимаем дамп
if [ "$BEGIN" == "1" ];then
if [ "$END" == "1" ];then
    echo `date +%F_%H-%M` ${FNAME} is OK >> $LOG
    /usr/bin/gzip ${FNAME}
else
    echo `date +%F_%H-%M` ${FNAME} is corrupted >> $LOG
    /usr/bin/rm ${FNAME}
fi
else
    echo `date +%F_%H-%M` ${FNAME} is corrupted >> $LOG
    /usr/bin/rm ${FNAME}
fi
#если дамп не проходит проверки, удаляем его и пишем в лог corrupted

#удаляем бэкапы и логи старше суток.
find /mnt/backup -type f -mmin +1440 -exec rm -rf {} \;
find /var/log/mysql/*mysqldump* -type f -mmin +1440 -exec rm -rf {} \;

Я скрипт прокомментировал, так что добавить особо нечего. Делаем дамп, проверяем первую и последнюю строки. Результат проверки пишем в лог файл, за которым далее будем наблюдать с помощью Zabbix.

Мониторинг бэкапов mysql

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

Мониторинг за валидацией бэкапов будем осуществлять с помощью Zabbix.

Если у вас еще нет своего сервера для мониторинга, то рекомендую материалы на эту тему. Для тех, кто предпочитает систему CentOS:

1. Установка CentOS 8.
2. Настройка CentOS 8.
3. Установка и настройка zabbix сервера.

То же самое на Debian 10, если предпочитаете его:

1. Установка Debian 10.
2. Базовая настройка Debian.
3. Установка и настройка zabbix на debian.

Мы настроили вывод результатов проверки архивов в лог файл /var/log/mysql/backup.log. Теперь сделаем так, чтобы Zabbix анализировал содержимое файла и слал оповещение, если там появится слово corrupted, что будет означать проблему с созданием дампа.

Для этого создаем новый шаблон и добавляем туда элемент данных.

Тут всё очень просто и стандартно. Далее делаем триггер.

Выражение проблемы:

{Backup mysql status:log[/var/log/mysql/backup.log].str(corrupted)}=1

Выражение восстановления:

{Backup mysql status:log[/var/log/mysql/backup.log].str(OK)}=1

Прикрепляем шаблон к хосту, где делаем бэкап. Не забудьте убедиться, что у zabbix-agent на хосте есть доступ на чтение этого лог файла. Таким образом, если проверка дампа не будет завершена успешно, сработает триггер. Он будет висеть активным до тех пор, пока не будет создан корректный бэкап базы mysql.

Вот так достаточно просто и быстро я решаю вопрос создания, проверки и оповещения о проблемах при создании дампов и бэкапов mysql баз.

Заключение

Решение задачи по бэкапу mysql баз, что я описал, не претендует на уникальность и 100% правильность. Это просто мой личный опыт. Никаких особых изысканий и поиска наилучшего решения не проводил. Просто сделал, как сделал, чем с вами и поделился. В моих задачах такой подход достаточен.

Еще раз напоминаю, что этот способ актуален для относительно небольших баз. Дампить объемные базы плохая идея, так как будет сильно проседать i/o дисков. Плюс тут нет возможности делать инкрементные бэкпы. Только полные, что, очевидно, не всегда удобно.

Для мониторинга бэкапов в целом, можете воспользоваться моей объемной статьей по теме - Мониторинг бэкапов с помощью zabbix.

Помогла статья? Подписывайся на telegram канал автора