Дамп и восстановление базы данных 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. Рассмотрим сразу же пример.

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

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 по умолчанию изменяет значения переменных только в контексте текущей сессии.

Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как 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 {} \;

Тем самым Вы будете удалять архивы, которые "старше" семи дней.

./mysql -u -p H < db_dump-file

2018-12-04T00:00Z

2018-12-11T00:00Z

Когда мы создаем файл дампа с mysqldump , то он содержит большой скрипт SQL для воссоздания содержимого базы данных. Поэтому мы восстанавливаем его с помощью запуска клиентской командной строки MySQL:

Mysql -uroot -p

(где root - наше имя администратора для MySQL), и после подключения к базе данных нам нужны команды для создания базы данных и чтения файла:

Create database new_db; use new_db; \. dumpfile.sql

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

2018-12-18T00:00Z

Вам просто нужно запустить это:

mysql -p -u < db_backup.dump

Если дамп содержит несколько баз данных, вы должны опустить имя базы данных:

mysql -p -u < db_backup.dump

Чтобы запустить эти команды, откройте командную строку (в Windows) и cd в каталог, в котором mysql.exe исполняемый файл mysql.exe (возможно, вам придется немного поразмыслить над ним, это будет зависеть от того, как вы установили mysql, то есть автономный или как часть пакета, такого как WAMP). Когда вы находитесь в этом каталоге, вы должны просто ввести команду.

2018-12-25T00:00Z

Это должно быть так просто, как запустить это:

Mysql -u -p < db_backup.dump

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

USE ;

Если это дамп многих баз данных, операторы использования уже там.

Чтобы запустить эти команды, откройте командную строку (в Windows) и cd в каталог, в котором mysql.exe исполняемый файл mysql.exe (возможно, вам придется немного поразмыслить над ним, это будет зависеть от того, как вы установили mysql, то есть автономный или как часть пакета, такого как WAMP). Как только вы попадете в этот каталог, вы сможете просто набрать команду, как я ее выше.

2019-01-01T00:00Z

Команда One-liner для восстановления сгенерированного SQL из mysqldump

Mysql -u -pE "source

2019-01-08T00:00Z

Используя файл дампа 200 Мбайт, созданный в Linux для восстановления в Windows с mysql 5.5, у меня был больше успеха с

Source file.sql

подход из командной строки mysql, чем с помощью

Mysql < file.sql

подход в командной строке, вызвавший некоторую ошибку 2006 «сервер ушел» (на окна)

Как ни странно, служба, созданная во время (mysql) install, относится к файлу my.ini, которого не было. Я скопировал «большой» пример файла в my.ini, который я уже модифицировал с рекомендуемым увеличением.

Мои ценности

Max_allowed_packet = 64M interactive_timeout = 250 wait_timeout = 250

2019-01-15T00:00Z

В качестве конкретного примера предыдущего ответа:

Мне нужно было восстановить резервную копию, чтобы я мог импортировать / перенести ее в SQL Server. Я установил только MySql, но не зарегистрировал его как услугу или не добавил на свой путь, поскольку мне не нужно его запускать.

Я использовал Windows Explorer, чтобы поместить мой файл дампа в C: \ code \ dump.sql. Затем откройте MySql из пункта меню «Пуск». Создал БД, затем выполнил команду источника с полным путем следующим образом:

Mysql> create database temp mysql> use temp mysql> source c:\code\dump.sql

Странно, но в интернете найти эту информацию довольно непросто. Куча левых всяких рекомендаций. А на самом деле всё просто. Восстановить дамп можно командой:

# 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 градусов.