MySQL: Бэкап базы данных и его сжатие

Продолжу описывать конкретные ситуации, с которыми доводилось сталкиваться при работе с MySQL, и эта статья про резервирование БД на сервере под управлением Windows для проекта Hattrick Portal. Не буду подробно останавливаться на всех существующих инструментах резервирования MySQL, просто вкратце обозначу мои причины выбора утилиты mysqldump:

• я использую движок MySQL InnoDB (а значит mysqlhotcopy не подходит);
• она бесплатна и входит в поставку СУБД («горячее» снятие снэпшотов для InnoDB я не видел в бесплатном варианте, да и нужно оно в основном для быстрого переноса данных);
• я снимаю бэкап всей БД (для отдельных таблиц есть операторы SELECT INTO OUTFILE и LOAD DATA INFILE);
• может работать при работающей СУБД;
• отставание бэкапа на день для меня не принципиально (т.е. инкрементные бэкапы не требуются);
• даёт вполне понятный и разбираемый текстовый файл, который может пригодиться не только для восстановления БД.

Минусы, конечно, тоже есть: самый главный — это блокировка таблиц на запись, что гарантирует нам с большими таблицами в БД задержки транзакций на обновление данных. Для того проекта, который я веду, это не так принципиально и это можно преодолеть только репликацией и снятием бэкапа уже с реплики (но это потребляет еще столько же места на диске, плюс дополнительная нагрузка на диск и СУБД и вообще желателен отдельный сервер для реплики). И инкрементные бэкапы с помощью этой утилиты тоже невозможно реализовать — хотя я для Windows бесплатного ПО и не видел, та же Percona XtraBackup только для Linux.

Я запускаю mysqldump со следующими параметрами:

@"mysqldump.exe" --host=localhost --port=3306 --user=пользователь --password=пароль --default-character-set=utf8 --log-error=базаданных.log --set-gtid-purged=OFF --single-transaction 
--add-drop-database --databases базаданных "базаданных.bak"

Все параметры вполне стандартные, кроме парочки:

• —single-transaction — создает дамп в виде одной транзакции.
• —set-gtid-purged=OFF — указывает то, что мы не используем репликацию на основе глобальных идентификаторов GTID.
• —default-character-set=utf8 — лучше на всякий случай указать, в какой кодировке у нас данные, чтобы потом не было мучительно больно.

Целую кучу параметров MySQL использует по умолчанию, например, с версии 4.1 по умолчанию выставлен параметр —opt для оптимизации скорости резервирования данных, поэтому набор ключей —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset уже не надо указывать.

Лично у меня сейчас данные занимают 10 Гб, а их бэкап (снимаемый mysqldymp) — 4 Гб. Жаль, что текстовый файл занимает прилично места и «из коробки» не жмется в архив, хотя для текста это было бы логично. Для обычного виртуального сервера в хостинге любой гигабайт дискового пространства дорог (в денежном выражении). Поэтому когда приближается лимит на дисковое пространство, при превышении которого цена за хостинг начинает расти, начинаешь ценить эти гигабайты свободного места. И становится жаль что бэкап БД занимает столько нужного места, да еще чтобы снять новый бэкап — тоже нужно столько же. Я уж не говорю, что иногда хочется иметь не одну последнюю резервную копию, а хотя бы еще одну-две запасные с разницей в день и неделю.

Тут нам на помощь приходит прекрасный оператор конвейера |, используемый и в *nix и в Windows. Поэтому мы дописываем в конце команды создания бэкапа подобную конструкцию (для использования 7-zip):

| "7z.exe" a -tgzip -si"базаданных.bak" "базаданных.gzip"

и получаем, что бэкап сжимается «на лету» и сразу образует заархивированный файл. В моем случае имеем вместо 4 Гб примерно 1,3 Гб — экономия в 3 раза. Что дает нам возможность либо иметь меньше свободного дискового пространства, либо можно хранить до 3 бэкапов вместо одного. Итоговый скрипт backup_db.bat получения сжатого бэкапа базы данных:

@"mysqldump.exe" --host=localhost --port=3306 --user=%1 --password=%2 --default-character-set=utf8 --log-error=%3.log --set-gtid-purged=OFF --single-transaction --add-drop-database --databases %3 "%3.bak" | "7z.exe" a -t7z -si"%3.bak" "%3.7z"

и запуск её:

backup_db.bat пользователь пароль имябазыданных

Соответственно восстановление из сжатого бэкапа:

@"7z.exe" e -so "имябазыданных.7z" | "mysql.exe" --user=пользователь --password=пароль --database=имябазыданных

Ну и маленькая подсказка: если после —password не дописывать =пароль, программа предложит его ввести и это работает даже с предыдущей командой восстановления БД из сжатого бэкапа. Я не люблю прописывать пароли в скриптах и вам не советую. Уж если надо вызывать скрипт (например, из планировщика заданий, то лучше уж пароль передавать как параметр). Т.е. скрипт restore_db.bat вполне может быть вида:

@"7z.exe" e -so "%3.7z" | "mysql.exe" --user=%1 --password=%2 --database=%3

и вызываться из планировщика или просто из командной строки:

restore_db.bat пользователь пароль имябазыданных