Резервное копирование всех баз данных Mysql одной командой в Debian

Данную статью поймёт не всякий, но профессионалам будет интересно! Всем, кому понравится данный материал, рекомендуется к прочтению моя статья про резервное копирование всех сайтов с консоли.

Виктор ТаранТех. Директор студии Клондайк

Резервное копирование, вещь лишняя и ненужная до того момента когда что-то….

В общем вы меня понимаете. В это время вы начинаете бегать по офису в лихорадочной попытке все же вспомнить — где вы могли в последний раз сохранить копию этого сайта, или его базу данных, и если оное оказывается, хваля свою память, судорожно начинаем заливать на сервер с точным убеждением что вот сейчас то вы точно доведете до ума свою backup систему, но завтра…

В общем изрядно потрепав себе нервы пришло время делать backup всего и вся.

А начнем мы с задач:

1. Обеспечить бесперебойную систему работы сайтов, включая моменты восстановления и резервного копирования.
2. Обеспечить себя backup на все случаи жизни! Ни один сайт не должен ни при каких обстоятельствах умереть совсем.
3. Все должно делаться, автоматически включая удаления старых backup, но не противоречить пункту 2.
4. Все backup должны быть индивидуальные для каждого сайта свой файл.
5. Удобная каталогизация.
6. Все файлы должны архивироваться (место все же не резиновое).
7. Backup должен работать исключительно на регулярных выражениях.
8. Несколько временных интервалов.
9. Логируем все, не дай бог что-то пойдет не так, а мы не в курсе.
10. Как можно легче, админ не программист;) Скрипт моя твоя не понимать, не особо полезен.

Приступим:

Имеется: Debian6×64, 2Tb, 32Gb, SSD 60Gb — 300 баз данных разных пользователей mysql + innodb. 5 Gb базы данных. Первое, что сразу захотелось сделать, это попросту скопировать /etc local/mysql/ и пришел на ум сразу изящный вариант.

Создаем папку для бэкапа: mkdir /var/backup/SQl_backup/

Создаем файл скрипта:

touch /root/backupsql.sh
vim /root/backupsql.sh

и вписываем вот этот текст.

#!/bin/sh
cd /var/backup/SQl_backup/
tar -cvjf /var/backup/SQl_backup/den_nedeli_`date +%u`.tar.bz2 /var/lib/mysql
>> /root/backupsql.sh

Меняем права.

chmod 700 /root/backupsql.sh

Вносим задачу в crontab

crontab –e

добавляем строчку, бэкап в 2 ночи

00 2 * * * /root/backupsql.sh

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

/var/backup/SQl_backup# ls -p
den_nedeli_1.tar.bz2
den_nedeli_5.tar.bz2
den_nedeli_2.tar.bz2
den_nedeli_6.tar.bz2
den_nedeli_3.tar.bz2
den_nedeli_7.tar.bz2
den_nedeli_4.tar.bz2

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

Так что нам придется делать полноценные бэкапы для каждого пользователя, с БД, архивировать их положенным образом, и складывать притом дампы делать mysqldump поскольку только его работа нам позволит не останавливать сервер и корректно делать выгрузки не опасаясь побить таблицы. Что в свою очередь отразится на весь mysql.

Порывшись в интернете, мы немного модернизируем нерабочий скрипт до вменяемого состояния.

for i in `mysql -uroot -pпароль -e'show databases;' | grep -v information_schema | grep -v Database`; do mysqldump -uroot -pпароль $i > /var/backup/mysql/$i/`date +%Y-%m-%d`-$i; gzip /var/backup/mysql/$i/`date +%Y-%m-%d`-$i;done >> /dev/null 2>> /var/log/sqlbackup.log

Теперь все работает правильно, но нам нужны backup адекватные. Так же требуется ограничить количество файлов, и структурировать максимально удобным образом. Мало того, у потоковой архивации есть неприятная плюшка, заключающаяся в том что `date +%Y-%m-%d` во время создания файла и архивации могут расходиться, и если мы добавим часы минуты и секунды то получим увлекательную штуку, все что за секунду попало в архив то ужалось, что больше секунды не попало под этот фильтр и не сжалось. Соответственно остается вероятность такого же в минутах часах, и теоретически днях. Что нас уже не устраивает, если жене давать столь подробнее название, то архиватор при попадание уже созданного файла просто останавливается и ждет ввода притом gzip не может по умолчанию нажимать ДА.

В общем, там еще остается куча нерешенных вещей, как ограничить количество по какому критерию и так далее. Обложившись маннами и здравым смыслом, в результате раздутый скрипт достигли 30 строчек, после чего пришлось их сокращать.

Что уже дало на выходе вот это

Создаем папку с форматом внимание ! …/2012-12-3/ в которую и сложим все файлы последнее это цифирное выражение дня недели! -%u и вуали, все строчки кода превратились в маленький скрипт, по дороге нам пришлось отказаться от потокового сжатия, его минусы оказались столь значительны что использование его оказалось невозможным, в частности слишком много, но выпазило по догори, и первая ошибка останавливала скрипит полость, хоть он у нас и легируется в файл, но веже недопустимая роскошь. Даже если один дамп не отработал, остальные обязаны корректно отработать. Плюс мы сделали отличную каталогизацию.

mkdir /var/backup/mysql/`date +%Y-%m-%u` >> /dev/null 2
for i in `mysql -uroot –pпарольr -e'show databases;' | grep -v information_schema | grep -v Database`; do mysqldump -uroot -pпароль $i > /var/backup/mysql/`date +%Y-%m-%u`/$i.sql;done >> /dev/null 2>> /var/log/sqlbackup.log

Но и от этого варианта пришлось отказаться, слишком трудно и много но. Нам нужно 100% работающий скрипт который невозможно повредить.

И так упрощаем, с этого места уже можно писать в консоль, а не просто читать.

vim /bin/sqlbackup
chmod 700 /bin/sqlbackup

и вставляем вот этот скрипт

#скрипт бэкапа баз данных
###########################
#Создаем папку для архивов. -p не ругается когда папка уже есь
test ! -d /var/backup/mysql/`date +%Y` && { mkdir -p /var/backup/mysql/`date +%Y` ; }
test ! -d /var/backup/mysql/last && { mkdir -p /var/backup/mysql/last ; }

#прячем от умных, и так не зайдут но все же.
chmod 700 /var/backup/mysql
chmod 700 /var/backup/mysql/last
# ********** -  это ваш пароль
# делаем сам дапм файлов sql, свежинькие файлы лежат всегда в ней, очень удобно не нужно заходить в архивы и искать там вчерашние базы, и логируется.
for i in `mysql -uroot -p********** -e'show databases;' | grep -v information_schema | grep -v performance_schema | grep -v Database`; do mysqldump -uroot -p********** $i > /var/backup/mysql/last/$i.sql;done >> /dev/null 2>> /var/log/sqlbackup.log

# Архивируем дамп, ну и логируем разумеется
cd /var/backup/mysql/
tar -zcvpf /var/backup/mysql/`date +%Y`/sqldump-`date +%Y-%m-%u`.tar.bz2 ./last >> /dev/nool >> /var/log/sqlbackup.log
# Конец скрипта

Выполняем с консоли:

sqlbackup

Для уменьшения нагрузки на рабочий сервер:

nice -19 sqlbackup

Добавляем в крон каждое утро 4.00

Crontab –e

Вставляем

00 4 * * * nice -19 sqlbackup >/dev/null 2 >> /var/log/cron.log

Теперь каждое утро в 4–00 будет делаться бэкап, по умолчанию он будет лежать в папке ласт, остальное будет сжато и лежать в папке соответствующего года.

В результате за год у вас будет сделано 12*7 бэкапов.

С виду достаточно много, но если перейти к цифрам то текстовые фалы очень хорошо ужимаются и все мои базы сжались до 400 мегабайт. Фактически отдать 84*400 всего то 40 Гб места, что мягко говоря против 2ТБ винтов копейки. Зато в замен у вас получаются бэкапы каждой последней недели каждого месяца. И последняя неделя свежая. Плюс в папке ласт лежит самый последний бэкап.

Можно усложнить скрипт для удаления старых бэкапов и обрезки их до состояния 1 срез в позапрошлом году. Но это уже по желанию. Фактически сейчас скрипт делает срез и архивит его. А дальше уже кроном делаем что вам надо. Как показала практика базы данных в архиве очен