Задача

Сделать резервную копию базы данных.

Решение

Резервная копия базы данных или дамп ("Dump", англ) - это слепок, образ базы данных на определенный момент времени записанный при помощи SQL-инструкций вставки/удаления данных. По сути это набор инструкций для СУБД как и куда она должна вставлять тот или иной текст.

Варианты

Чтобы в будущем можно было восстановить базу данных, которая была утрачена нужно сделать резервную копию базы данных (дамп). Для этого можно использовать:

• автоматизированные инструменты, которые предоставляет ваш хостер,
• Программа mysqldump (нужнен SSH-доступ)
• скрипт phpMyAdmin - полное управление базами данных
• скрипт Sypex Dumper Lite - импорт/экспорт больших баз данных.
• скрипт Staggered MySQL Dump Importer - создан для импорта больших объемов баз данных.
• Программа Navicat

Утилиты mysql

Если есть доступ к серверу по SSH, то создать дам базы можно с помощью mysqldump.

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

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

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

Немного расскажем о том, что же делает mysqldump.

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

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

Рассмотрим более тонкие настройки mysqldump:

Ключ --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-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

"Заставить" mysqldump писать данные сразу на диск, а не хранить их в памяти

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

Сделать дамп с указанием даты и времени и упаковать его в архив

Я использую такую команду:

В результате получаем сжатый файл с таким, например, именем: drupalcookbook_20.05.2008_15_23.gz

Использование phpMyAdmin для создания резервной копии базы данных

бесплатный скрипт phpMyAdmin, который позволяет управлять базой данных, а также создать дамп базы данных и скачать его

• Прежде чем устанавливать этот скрипт, выясните у вашего хостера - возможно он уже установлен. Если же нет, то вы можете установить phpMyAdmin самостоятельно.
• После этого открыть в браузере этот скрипт (адрес зависит от того, в какой папке был установлен скрипт).
• Выбрать нужную базу данных
• Нажать вкладку "Экспорт"
• Настроить параметры экспорта:
• Выбрать нужные таблицы. Если этого не сделать, то будут экспортироваться все таблицы
• Поставить галочку "послать файл"
• Выбрать метод сжатия
• Нажать кнопку "Выполнить"

Использование Sypex Dumper Lite для создания резервной копии базы данных

бесплатный скрипт Sypex Dumper Lite позволяет создавать резервные копии любых размеров и восстанавливать их.

"Sypex Dumper в отличии от многих подобных скриптов не загружает бекап-файл целиком в память, благодаря чему, ему безразличен размер базы данных и он одинаково быстро работает, как с маленькими, так и с большими объемами данных."

Установка

1. Распаковать скачанный zip-файл.
2. Закачать dumper.php в один из каталогов вашего сервера (доступный из web).
3. Установить для этого каталога CHMOD 777 (см. Права доступа к файлам).

Использование

1. Открыть в браузере URL вида: http://domain.com/dumper.php.
2. Ввести логин и пароль для вашей БД.
3. Создание резервной копии БД:
   1. Выберите базу данных в верхнем разделе главной страницы.
   2. Фильтр оставьте пустым (будут дампиться все таблицы выбранной БД), подробнее о фильтрах см. ниже.
   3. Выберите метод сжатия (bzip2 наиболее эффективный, но и самый медленный).
   4. Выберите степень сжатия (как показала практика, наиболее оптимальная — 7).
   5. Нажмите Применить.
   6. После окончания работы скрипта (станут активны кнопки Скачать файл и Вернуться), можно скачать файл по http (предварительно возможно понадобится настроить перехват расширений .sql, .gz и .bz2 в менеджеры загрузки) или скачать по FTP. Название файла состоит из названия базы данных, а также даты и времени создания дампа, для упрощения работы с файлами резервных копий.

Фильтры

В фильтре таблиц указываются специальные шаблоны по которым отбираются таблицы. В шаблонах можно использовать следующие специальные символы:

• символ * — означает любое количество символов
• символ ? — означает один любой символ
• символ ^ — означает исключение из списка таблицы или таблиц

Примеры:

• drupal_* - все таблицы начинающиеся с "drupal_"
• drupal_*, ^drupal_sessions - все таблицы начинающиеся с "drupal_", кроме "drupal_sessions"
• drupal_s*s, ^drupal_sessions - все таблицы начинающиеся с "drupal_s" и заканчивающиеся буквой "s", кроме "ib_sessions"
• ^*s - все таблицы, кроме таблиц заканчивающихся буквой "s"
• ^drupal_???? - все таблицы, кроме таблиц, которые начинаются с "drupal_" и содержат 4 символа после знака подчеркивания

скрипт Staggered MySQL Dump Importer

Предназначен для создания резервных копий больших баз данных.
Подробности: http://www.ozerov.de/bigdump.php

Программа Navicat

Адрес проекта: http://www.navicat.com
Есть 30-дневная версия и свободная версия (Lite Edition). Вот таблица различий: http://www.navicat.com/feature.html
Для Линукс версия для некоммерческого использования весит 20 MB!
Это продукт класса phpMyAdmin для управления базой данных.

Использованные материалы

• Вопросы СУБД MySQL
• Резервное копирование баз MySQL

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

• Как сделать бэкап БД?