Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.
mysqldump - утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
mysqldump -u root -p -f name_database >
Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt
Файл можно не создавать, MySQL создаст его сам.
mysql -u root -p -f name_database < C:\mydb_backup_name_database.txt
этой командой мы импортируем данные бэкапа из файла C:\mydb_backup_name_database.txt
Примечание: -f, --force - опция, которая указывает продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка.
Чтобы пароль не запрашивался, нужно писать его сразу после -p, то есть без пробелов. Если пароль Pwd , то пример выгладит так:
mysqldump -u root -pPwd -f name_database > C:\mydb_backup_name_database.txt
Если вы часто используете данную команду, то лучше сделать отдельного пользователя с необходимыми правами, дабы поменьше светить пароль root-а
Рассмотрим более тонкие настройки 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 в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick . Это решит проблему.
Приведем еще пару полезных примеров:
mysqldump -u root -pPwd -f --default-character-set=cp1251 DBNAME | gzip -c > filename.txt .gz
распаковывать такой архив можно командой:
gunzip filename.txt .gz
Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:
mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz
а если приходится делать дампы с разными кодировками, то удобно пользоваться переменными:
set DBCHARACTER = utf8
set DBNAME = breach
mysqldump -u root -pPwd -f --default-character-set=$DBCHARACTER $DBNAME | bzip2 -c > sql.$DBNAME .`date "+%Y-%m-%d"`.bz2
Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически
find ~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} \;
Тем самым Вы будете удалять архивы, которые "старше" семи дней.
Наверяка вам не раз приходилось импортировать дампы базы данных. И не смотря на всю тривиальность этой задачи, часто возникают вопросы, а иногда и проблемы связанные с этим. Ниже приведен весь код, который необходим, чтобы быстро применить дамп базы из командной строки.
SET NAMES utf8; DROP DATABASE `mydb`; CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; USE `mydb`; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0; SOURCE /path/to/db_dump.sql; COMMIT; SET autocommit=1; SET foreign_key_checks=1; SET unique_checks=1;
Эти команды позволят вам быстро импортировать дамп базы данных и избежать проблем. Причем некоторые из этих проблем не решают даже распространенные mysql-клиенты, такие как phpMyAdmin и другие.
Попробуем разобрать данный код по порядку.
SET NAMES utf8; говорит, что обмен данными будет происходить в кодировке UTF-8 и именно в ней находится исходный файл дампа базы и именно её будет иметь конечная база данных.
DROP DATABASE `mydb`; и CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8; выполняют уничтожение базы данных и её последующее создание с кодировкой по умолчанию UTF-8. Понятное дело, что это сотрет все таблицы и данные в них, но это и даст нам уверенность в том, что полученная БД будет полностью соответствовать данным в файле дампа.
USE `mydb`; указывает, что надо использовать именно эту базу данных для всех команд из файла дампа (проверьте файл дампа на наличие этой директивы с указанием другой базы, дабы избежать конфуза).
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; отключает увеличение счетчика авто-инкремента при записи в поле авто-инкремента значения 0. По умолчанию, при записи в такое поле значения NULL или 0 оно автоматически инкрементируется. И хотя иметь значение 0 в авто-инкрементном поле - не лучшая практика, иногда все-таки случается что в дампе есть такие строки (я встретился с подобным в дампе от magento). Стоит также заменить, что mysqldump с некоторых пор включает данную директиву по умолчанию в своих дампах.
SET autocommit=0; перед заливкой дампа и COMMIT; после него - ускоряют выполнение INSERT"ов для InnoDB таблиц, поскольку при включенном режиме авто-коммита логи сохраняются на диск при каждом INSERT"е. Может помочь при накатывании больших дампов.
SET foreign_key_checks=0; наиболее очевидная опция, без которой большая часть дампов вообще не могла бы быть применена. Отключает проверки внешних ключей, что позволяет записывать данные в любом порядке и в том числе с циклическими ссылками по внешним ключам. Эта опция также ускоряет импорт дампа.
SET unique_checks=0; отключает проверки на уникальность для вторичных ключей, что опять таки может ускорить процесс импорта.
Ну и наконец, основная команда, ради которой все это было задумано - SOURCE /path/to/db_dump.sql; непосредственно импортирует дамп базы mysql из указанного файла. Путь до файла может быть абсолютными, либо относительным от той директории, откуда вы зашли в консоль mysql.
А после выполнения импорта - восстанавливаем значение флагов, хотя этого можно и не делать, ибо команда SET в mysql по умолчанию изменяет значения переменных только в контексте текущей сессии.
Странно, но в интернете найти эту информацию довольно непросто. Куча левых всяких рекомендаций. А на самом деле всё просто. Восстановить дамп можно командой:
# mysql -u -p < mysql_dump.sql.
Вместо подставьте имя пользователя, я лично восстановление делаю от рута. А в mysql_dump,sql, собственно, дамп сделанный mysqldump утилитой. Опция -p указывает на авторизацию с паролем, после нажатия ентера у вас спросят пароль. Вот и всё, собственно. Сделать дамп можно командой:
# mysqldump -u root -p -f database_name > file_with_dump.sql
Здесь параметр -f database_name задаёт имя базы, которую сохраняем. Если надо всё — заменяем на —all-databases. Эта опция укажет сохранять все базы на сервере. Чтобы не вводить каждый раз пароль, можно указать его прямо в команде (это надо, если вы делаете дамп по крону из скрипта), впрочем, с точки зрения безопасности тут возникают вопросы, особенно если пароль рутовый для мускуля. Вот если бы мускуль понимал хотя бы md5 пароли… Впрочем не факт, что не понимает, просто может я не знаю:)
Автор
Алекс Разгибалов
Сумасшедший мужчина, неопределённого возраста, наслаждающийся манией преследования. Паталогически недоверчив, эгоистичен, авторитарен. Вторичные диагнозы - программист и поц. Владеет английским языком на уровне около хренового разговорного. Также знаком с некоторыми другими языками. Интересуется всем и вся, за счёт чего в любой области знания являются поверхностными, неглубокими. Характер невыдержанный. Крепость - 55 градусов.
Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty. Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd ‘. Ниже приведены примеры о том, как создавать дампы базы данных MySQL и затем восстанавливать их при необходимости, например для Вашего сайта, интернет-магазина или какого либо другого проекта.
Создание дампа базы данных MySQL
Для того, чтоб выполнять данные команды, подключитесь удаленно к Вашему серверу через SSH используя одну из перечисленных выше программ. После подключения и авторизации к серверу/хостингу, Вы можете вводить приведенные ниже команды.
# Бекап одной базы данных в файл dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # На windows дамп лучше всего создавать немного другой командой, которая предотвращает # случайное затирание строк дампа из за конвертации символов перевода строки "\r\n" в "\n" mysqldump -uroot -p your_base -r dump_file_utf8.sql # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных # (указываем наименования таблиц через пробел после названия базы данных) mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql # Если нужно создать бекап только структуры базы данных без самих данных mysqldump -uroot -p --no-data your_base > dump_file.sql # Бекап всех баз данных в файл текущая_дата.gz mysqldump -uroot -p --all_databases | gzip -c > "date "+%Y-%m-%d"".gz # Бекап, где для каждой записи создается отдельный INSERT # и с явным указанием кодировки базы данных UTF-8 mysqldump -uroot -p --default-character-set=utf8 your_base --extended-insert=FALSE | gzip -c > "date "+%Y-%m-%d"".gz
# Бекап одной базы данных в файл dump_file.sql mysqldump - uroot - p your_base > dump_file . sql # На windows дамп лучше всего создавать немного другой командой, которая предотвращает # случайное затирание строк дампа из за конвертации символов перевода строки "\r\n" в "\n" mysqldump - uroot - p your_base - r dump_file_utf8 . sql # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных # (указываем наименования таблиц через пробел после названия базы данных) mysqldump - uroot - p your_base TABLE1 TABLE2 TABLE3 > dump_file . sql # Если нужно создать бекап только структуры базы данных без самих данных mysqldump - uroot - p -- no - data your_base > dump_file . sql # Бекап всех баз данных в файл текущая_дата.gz mysqldump - uroot - p -- all_databases | gzip - c > "date "+%Y-%m-%d"" . gz # Бекап, где для каждой записи создается отдельный INSERT # и с явным указанием кодировки базы данных UTF-8 mysqldump - uroot - p -- default - character - set = utf8 your_base -- extended - insert = FALSE | gzip - c > "date "+%Y-%m-%d"" . gz |
В приведенном выше примере, для создания бекапа используется утилита mysqldump , которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:
- -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
- -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
- your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
- > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
- dump_file.sql
– это название Вашего файла.slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:
"date "+%Y-%m-%d""
"date "+%Y-%m-%d""
после этой строки в примере указывается расширение файла ‘.gz ‘. В результате будет создан файл вида ‘2014-11-15.gz ‘.Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде # , где root@dvs это логин и имя сервера, то файл будет создан в директории /home . Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql .
- Во втором примере, вместо оператора ‘> ‘ используется оператор ‘| ‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c ‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz ‘, о чем сообщает оператор ‘> ‘.
- Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
- Параметры –default-character-set=utf8 и –extended-insert=FALSE . Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251 . Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.
Восстановление базы данных из файла дампа MySQL
Теперь рассмотрим с Вами обратный процесс восстановления базы данных из файла дампа. Данное действие выполняется при помощи программы mysql. Рассмотрим сразу же пример.
