Автоматический бэкап базы: mysqldump и cron

Набор команд для создания автоматического бекапа базы данных MySql посредством утилиты mysqldump по расписанию в планировщике задач cron. 

Бекап будет создаваться из под созданного для это пользователя. Пароль к базе сохраним в отдельном файле с ограниченными правами доступа. 

Сам бэкап будет архивироваться. В имени файла бекапа сохраним время его создания. 

Логинимся в базу:

mysql -u root -p

Создаем и даем права пользователю для бекапа, с возможностью логинится только с локального хоста: 

CREATE USER 'имя_пользователя_для_бекапа'@'localhost' IDENTIFIED BY 'тут_пароль';

GRANT LOCK TABLES, SELECT ON имя_базы.* TO 'имя_пользователя_для_бекапа'@'localhost';

FLUSH PRIVILEGES;

quit

Проверим на всякий случай, что все ок, залогинимся, посмотрим список таблиц:

mysql -u имя_пользователя_для_бекапа -p имя_базы

show tables;

quit

Переходим в домашнюю папку /root и создадим файл в котором сохраним пароль к базе:

cd ~/

nano .my.cnf

В файле пишем:

[mysqldump]
user = имя_пользователя_для_бекапа
password = "тут_пароль"

Сохраняем через ctrl+x и y.

Ограничиваем права на доступ к файлу с паролем:

chmod 600 .my.cnf

sudo chown $USER:nogroup .my.cnf

создадим папку с бекапом:

mkdir backup

Тестируем создание бекапа из консоли и смотрим что он появился в папке:

mysqldump --defaults-extra-file=/home/user/.my.cnf имя_базы | gzip > `date +/home/user/backup/backupdb_имя_базы_%Y%m%d.%H%M%S.sql.gz`

ls -la backup

Создаем задачку по расписанию, лучше укажите время создания бекапа через несколько минут от текущего времени, чтобы убедится что он создался. Потом можно подправить на желаемое. Задача будет выполнятся в 4:17 каждый день:

crontab -e

17 4 * * * mysqldump --defaults-extra-file=/home/user/.my.cnf имя_базы | gzip > `date +/home/user/backup/backupdb_имя_базы_\%Y\%m\%d.\%H\%M\%S.sql.gz`

В заключение рекомендую создать тестовую базу и восстановить в нее базу из бекапа. Распаковываем и восстанавливаем

zcat backup/имя_файл.sql.gz | mysql -uroot -p имя_тестовой_базы

Помним админы делятся на три типа:

1. кто не делает бэкапы
2. тех кто уже делает
3. тех кто проверяет корректность и скорость восстановления

Далее можно дополнительно настроить копирование бекапов к себе локально, например буду к себе копировать на windows:

scp -i D:\scp\ssh_key.key -r user@ip_or_host:/home/user/backup/. D:\Backups\mysql\

Полезные ссылки:

• elims.org.ua - Linux: заметки