MySQL-дамп: три дня данных в мусорную корзину

Позвонили в среду утром. Хостер накосячил с железом, восстановили из снапшота двухдневной давности. Файлы сайта вернулись, сайт работает. Но каталог пуст. Точнее - каталог на месте, а три дня заказов и правок пропали. Потому что бэкап делался rsync-ом по файлам, а база MySQL там была вообще не при делах.

Это не первый раз, когда мы видим эту историю. Чем больше сайтов переезжает с «всё в файлах» на базу данных, тем чаще оказывается, что люди продолжают бэкапить так, как привыкли - копируют директории. Файлы сайта приходят. База нет.

Почему обычный бэкап файлов базу не спасёт

MySQL хранит данные в файлах - это правда. В директории /var/lib/mysql/ лежат .frm, .MYD, .MYI и прочее. Но копировать их напрямую, пока MySQL запущен, - плохая идея. База может быть в середине записи, буферы не сброшены, файлы несогласованы между собой. Восстановишь такое - получишь повреждённые таблицы вместо данных.

Для базы нужен дамп - снимок, который MySQL делает сам, в согласованном состоянии.

Как делается дамп

Инструмент называется mysqldump, поставляется вместе с MySQL. Базовое использование:

mysqldump -u root -p имя_базы > /backup/mysite-$(date +%Y%m%d).sql

Получается .sql-файл с полным набором CREATE TABLE и INSERT на все данные. Его можно положить куда угодно, передать, заархивировать. Восстанавливается так:

mysql -u root -p имя_базы < /backup/mysite-20030528.sql

Если нужно бэкапить несколько баз сразу:

mysqldump -u root -p --all-databases > /backup/all-$(date +%Y%m%d).sql

Для каталога с несколькими таблицами InnoDB (если успели перейти) добавляем --single-transaction - это позволяет сделать согласованный снимок без полной блокировки таблиц:

mysqldump -u root -p --single-transaction имя_базы > /backup/mysite-$(date +%Y%m%d).sql

Для MyISAM (стандарт в MySQL 3.x-4.x) этот ключ не нужен - там блокировка происходит по-другому, и без неё обойтись не получится.

Расписание - простой cron

Дамп руками - не бэкап. Бэкап - это то, что происходит без участия человека. Добавляем в crontab:

0 3 * * * mysqldump -u backup_user -pпароль имя_базы | gzip > /backup/mysite-$(date +\%Y\%m\%d).sql.gz

Три ночи - стандартное время, когда на базе минимальная нагрузка. Отдельный пользователь backup_user с правами только на SELECT и LOCK TABLES - не под root гонять такое.

Пароль в crontab выглядит не очень, но альтернатива - файл ~/.my.cnf с секцией [mysqldump], куда кладётся пользователь и пароль. Cron подхватит автоматически.

Ротация - чтобы диск не кончался

Дамп среднего каталога - мегабайты, gzip сжимает раз в пять. Но если просто накапливать файлы, через несколько месяцев диск набьётся. Ротация проще, чем кажется:

find /backup -name "mysite-*.sql.gz" -mtime +7 -delete

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

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

Что вышло у клиента

Три дня заказов восстановить не получилось. Часть клиент вспомнил из почты, часть просто потеряли. После этого поставили cron на дамп каждую ночь, rsync дампов на офисный сервер раз в сутки утром. С тех пор проблем не было.

Разница между файловым бэкапом и бэкапом базы - это не техническая тонкость. Это разные вещи, которые решают разные задачи. Бэкап файлов возвращает код и шаблоны. Бэкап базы возвращает данные. Для современного каталога, CRM или любого другого приложения на MySQL важны именно данные - код всегда можно перезалить из архива или с другой машины.

В декабре писали про ленточную ротацию - принцип тот же: несколько поколений, хранение в разных местах. Только вместо ленты - sql.gz-файлы и cron.