Правильное резервное копирование баз MySQL/MariaDB

Всем привет сегодня расскажу о том как с моей точки зрения делается правильное резервное копирование баз MySQL/MariaDB. Для создания дампов MySQL/MariaDB обычно используют утилиту mysqldump с указанием в ней имени базы данных - это правильно, это работает. Но когда нужна только одна таблица, а дампа базы занимает в не запакованном виде 50Гб, чтобы добраться до заветной таблицы придётся очень сильно подождать. Решая в очередной раз подобную задачу решено было делать дамп потаблично. Как результат был написан такой скрипт.

Адрес хоста с которого нужно лить бэкап
DBHOST='127.0.0.1'
Имя пользователя от имени которого будет выполняться бэкап MySQL/MariaDB
DBUSER='root'
Пароль этого порльзователя
DBPASS=''
Номер порта
DBPORT=
Имя базы данных
DBNAME=
Папка для размещения бэкапов
BKPPATH='/data/backup/'
Папка для постоянного хранения бэкапов
STORAGE='/mnt/backup/'
Временная папка для бэкапов
TMPPATH='/tmp/'$DBNAME'/'
Список таблиц базы данных
TABLES=$TMPPATH'tables-list.txt'
Имя хоста на котором выполняется скрипт
HOSTNAME=`/bin/hostname`
Имя хоста и имя базы данных
HOSTDBNAME=$HOSTNAME$'_'$DBNAME
Ящик с которого отправляем сообщения о выполнении дампа базы данных
SENDER='senders-email@your-mail-domain.com'
Получатель сообщений о статусе выполнения дампа базы данных
RECIPIENT='recipient-of-message@your-mail-domain.com'
Если подключена услуга mail2sms то отправляем письма на свой телефон
PHONES='123456789@sms.beemail.ru'
Сообщения
Тема сообщения - всё прошло хорошо
SUBJECT_OK='Backup '$HOSTDBNAME' OK!'
Тема сообщения - что-то пошло не так как надо, за деталями посмотрите лог отправленный по почте.
SUBJECT_FAIL='Backup '$HOSTDBNAME' FAIL! Check your e-mail for detail!'
Тело письма - бэкап выполнился хорошо, если нужны детали смотрите вложения
MESSAGE_OK='Backup '$HOSTDBNAME' successfully. See attachment for detail.'
Тело письма - что-то пошло не так как запланировано, надо смотреть лог работы скрипта отправленный по почте
MESSAGE_FAIL='Some errors was corrupted with '$HOSTDBNAME' backup. See attachement for detail!'
Утилита для отправки письма с заранее заданными параметрами
SENDEMAIL='/usr/local/bin/sendEmail -q -s -f '$SENDER
Имя архива с дампом
PACK=$BKPPATH$HOSTDBNAME'-'`/bin/date +%Y-%m-%d`'.7z'
Папка для бэкапа
DST=$STORAGE`basename $PACK`
Имя файла журнала
LOGFILE='/var/log/backup_'`date +%F`'_'$DBNAME'.log'

Записываем в переменные в лог
echo -e `date +%F%t%T%t`'Start backup database '$HOSTNAME >> $LOGFILE
echo -e '' >> $LOGFILE
echo -e `date +%F%t%T%t`'Database host:\t\t'$DBNAME >> $LOGFILE
echo -e `date +%F%t%T%t`'Database port:\t\t'$DBPORT >> $LOGFILE
echo -e `date +%F%t%T%t`'Database name:\t\t'$DBNAME >> $LOGFILE
echo -e `date +%F%t%T%t`'Database user:\t\t'$DBUSER >> $LOGFILE
echo -e '' >> $LOGFILE
echo -e `date +%F%t%T%t`'Backup folder:\t\t'$BKPPATH >> $LOGFILE
echo -e `date +%F%t%T%t`'Remote folder:\t\t'$STORAGE >> $LOGFILE
echo -e `date +%F%t%T%t`'Tempotary folder:\t\t'$TMPPATH >> $LOGFILE
echo -e `date +%F%t%T%t`'Database archive name:\t'$PACK >> $LOGFILE
echo -e '' >> $LOGFILE
echo -e `date +%F%t%T%t`'List of tables in database:\t'$TABLES >> $LOGFILE
echo -e `date +%F%t%T%t`'Create temp folder '$TMPPATH >> $LOGFILE
/bin/mkdir -p $TMPPATH

Получаем список таблиц из базы данных и записываем в файл
echo -e `date +%F%t%T%t`'Start getting tables in the database' >> $LOGFILE
/usr/bin/mysql -u$DBUSER -p$DBPASS -h$DBHOST -P$DBPORT -e 'use '$DBNAME'; show tables;' | /usr/bin/tail -n +2 > $TABLES
echo -e `date +%F%t%T%t`'List of tables from the database obtained' >> $LOGFILE
Начинаем делать потабличный дамп базы данных
echo -e `date +%F%t%T%t`'Start backup database '$DBNAME' per tables' >> $LOGFILE
for table in `/bin/cat $TABLES`
do
echo -e `date +%F%t%T%t`'Start backup table '$table >> $LOGFILE
/usr/bin/mysqldump -u$DBUSER -p$DBPASS -h$DBHOST -P$DBPORT --routines --disable-keys --add-drop-table $DBNAME $table -r $TMPPATH$DBNAME'.'$table'.sql'
echo -e `date +%F%t%T%t`'Finish backup table' >> $LOGFILE
done
echo -e `date +%F%t%T%t`'Finish backup database per table' >> $LOGFILE

Начинаем упаковывать дамп базы в архив
echo -e `date +%F%t%T%t`'Start packing database '$DBNAME >> $LOGFILE
/usr/bin/7z a -mx=5 $PACK $TMPPATH | /bin/grep 'Everything is Ok' > /dev/null 2>&1
echo -e `date +%F%t%T%t`'Finish packing database' >> $LOGFILE
RESULT=$?
После окончания архивации начинаем перемещать бэкап в папку постоянного хранения.
echo -e `date +%F%t%T%t`'Start to transfer the archive '$PACK' to the repository '$DST  >> $LOGFILE
if [[ $RESULT -eq 0 ]]; then
mv $PACK $STORAGE
echo -e `date +%F%t%T%t`'Transfer the archive to store completed' >> $LOGFILE
После перемещения проверям архив на целостность
echo -e `date +%F%t%T%t`'Start testing archive correctly after moving the archive' >> $LOGFILE
7z t $DST | grep 'Everything is Ok' > /dev/null 2>&1
echo -e `date +%F%t%T%t`'Closing testing archive' >> $LOGFILE
RESULT=$?
Если тестирование успешно завершено то отсылаем соответственное сообщение
if [[ $RESULT -eq 0 ]]; then
#$sendemail -f $SENDER -t $PHONES -m $SUBJECT_OK
$sendemail -f $SENDER -t $RECIPIENT -u $SUBJECT_OK  -m $MESSAGE_OK -a $TABLES -a $LOGFILE
Если не успешно, то тоже шлём сообщение
else
$sendemail -f $SENDER -t $PHONES -m $SUBJECT_FAIL
$sendemail -f $SENDER -t $RECIPIENT -u $SUBJECT_FAIL  -m $MESSAGE_FAIL -a $TABLES -$LOGFILE
fi
Удаляем временную папку
rm -rf $TMPPATH
fi

Скачать готовый скрипт

Материал сайта pyatilistnik.org