Использование утилиты mysqldump для резервирования MySQL

Существует программа mysqldump, позволяющая быстро и просто производить операции по созданию резервных копий баз MySQL. Также mysqldump дает возможность делать очень тонкие настройки для управления процессом создания резервных копий баз данных или отдельных таблиц. Можно сказать, что mysqldump - это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.

Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер. Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS. Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга. Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае - " > dump.txt ". Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.

Программа mysqldump создает сценарий восстановления Ваших данных. То есть, вывод mysqldump - это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария. Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись "1111", то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14
#
# Host: HOST DATABASE: DBNAME
#--------------------------------------------------------
# Server version 3.23.39-log
 
#
# TABLE STRUCTURE FOR TABLE 'test'
#
 
CREATE TABLE test (
 test2 INT(11) DEFAULT NULL
) TYPE=MyISAM;
 
#
# Dumping DATA FOR TABLE 'test2'
#
 
INSERT INTO test2 VALUES ('1111');

Таким образом, mysqldump "опишет" все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс - воссоздание базы из резервной копии.

Для восстановления будем пользоваться стандартной программой mysql, которая входит в комплект поставки MySQL наряду с mysqldump. Допустим, у нас имеется backup в файле dump.txt. Нам нужно восстановить его в рабочую базу. Например, мы случайно удалили нашу базу данных, а теперь пытаемся исправить эту незадачу. Делаем так :

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt

То есть, заставляем mysql-клиент соединиться с сервером и выполнить сценарий, который у нас имеется. После выполнения этой команды в Вашей базе появятся таблицы и данные из резервной копии. Учитывайте то, что данные будут просто восстанавливаться по сценарию из dump.txt. То есть, если таблицы, которые упоминаются в дампе базы, уже существуют и имеют другую структуру, тут явно возникнет ошибка. Просто посмотрите на сценарий и на рабочую базу и представьте, что Вы вручную выполняете команды из сценария. Если уверены, что все будет хорошо - смело восстанавливайте.

• –databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования –databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;
• –all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);
• Ключ –help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;
• –add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;
• –no-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;
• –result-file=… - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;
• –add-locks Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
• -e, –extended-insert Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
• -l, –lock-tables Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL, чтобы разрешить параллельные записи для MyISAM-таблиц.
• –single-transaction запустит транзакцию перед запуском. Вместо того, чтобы блокировать всю базу данных, это позволит mysqldump прочитать базу данных в текущем состоянии во время транзакции, создавая согласованный дамп данных.

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам.

Еще один очень полезный совет по использованию mysqldump в хостинговой ограниченной среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ –quick. Это решит проблему.

• -q, –quick Выводить дамп построчно на стандартный вывод stdout без буферизации запроса, для этого используется функция mysql_use_result().
• –opt То же, что и –quick –add-drop-table –add-locks –extended-insert –lock-tables. Должно дать наиболее быстрый дамп для чтения на MySQL-сервере.

Пример:

mysqldump --opt database > backup-file.sql

Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа - cron. Она позволяет запускать процессы в указанное пользователем время или с определенной периодичностью. Сразу оговоримся - cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время. Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME
	| gzip -c > `date "+%Y-%m-%d"`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz. Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали - date "+%Y-%m-%d". Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то, что испортилось, например.

Читайте также: Как бэкапить данные в MySQL

Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find ~/каталог-с-архивами -name "*.gz" -mtime +7, Вы будете удалять архивы, которые "старше" семи дней. Прочитайте документацию по find - она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина - это очень надежная штука. Однако, как говорится, "береженого Бог бережет". Старая как мир истина в определенных условиях может и Вам помочь. Используйте для копирования на другую машину команды ftp и scp.