Восстановление дампа сделанного mysqldump. Делаем дамп базы MySQL и экспорт данных в консоли

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

./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

Большую часть времени, вы можете легко управлять своей базы данных с веб-инструментов, например через PHPMyAdmin. К сожалению, бывают случаи, когда необходимо восстановить большое количество данных. Это не может быть достигнуто через PHPMyAdmin из-за пределов РНР (либо максимальное время работы скрипта не позволяет или невозможно загрузить большой архив с БД).

Использование MySQL с командной строки

Если вы используете MySQL на Linux или MS Windows, вы можете использовать командную строку для выполнения большинства задач. Если вы используете MS Windows, убедитесь, что путь к исполняемым файлам командной строки входит в системном пути. В противном случае, вам придется ввести полный путь к каждой команды.

Как сделать резервную копию базы данных MySQL

MySQL предоставляет утилиту под названием «mysqldump.» В принципе, этот инструмент создает плоский файл, содержащий инструкции MySQL, чтобы восстановить базу данных. Вот несколько примеров использования mysqldump:

Создание простого резервной копии базы данных

# mysqldump -u username -p DB_name > your_file.sql

Это создает файл, содержащий все операторы MySQL для создания таблиц и восстановление данных в существующую базу данных. Если целевая база данных содержит таблицы с одинаковыми именами, они будут перезаписаны. Если вы хотите существующие таблицы, которые будут отброшены и заново использованы, то используйте опцию » add-drop-table»:

# mysqldump --add-drop-table -u username -p DB_name > your_file.sql

Кроме того, вы можете выбрать это в качестве альтернативы:

# mysqldump --add-drop-databases -u username -p DB_name > your_file.sql

Резервное копирование нескольких баз данных

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

# mysqldump -u username -p --databases your_database1 your_database2 your_database3 > your_file.sql

Создание резервной копии всех баз данных может быть достигнуто с помощью опции все-базы данных:

# mysqldump -u username -p --all-databases > your_file.sql

Резервное копирование таблиц InnoDB

Если в базе данных есть столбцы InnoDB, вам нужно будет отключить ссылочную целостность при восстановлении данных. К сожалению, это не может быть сделано с помощью утилиты mysqldump. Для этого нужно открыть сам файл и добавить:

SET FOREIGN_KEY_CHECKS = 0;
… И добавить следующее в конец файла:
SET FOREIGN_KEY_CHECKS = 1;

Сжатие MySQL Dump файлов

Если вы используете mysqldump в оболочке Linux, вы можете сжать через Gzip, чтобы сжать файл дампа (при условии, что вы установили gzip):

# mysqldump -u username -p your_database_name | gzip -c your_file.sql.gz

Восстановление из резервной копии MySQL

Чтобы восстановить резервную копию, созданную с mysqldump, вам нужно будет использовать команду MySQL. Если ваш файл дампа MySQL не содержит «Создать базу данных» о, вы можете использовать следующую команду:

# mysql -u username -p your_database_name < your_file.sql

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

# mysql -u username -p < your_file.sql

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

От автора: пришел в гости к другу, а у его жены истерика, попугай орет: «Май-эс-кью-эль дамп». Сам друг лежит с головной болью и маниакальным желанием узнать, как происходит в MySQL восстановление из дампа. Чтобы спасти головы остальных «несведущих», нервы их жен и голосовые связки попугаев, рассмотрим эту тему подробнее.

Опасность «All inclusive»

Мой друг, как и многие из новичков, для изучения азов разработки использует программные пакеты «Все включено». Самым распространенным в Рунете является джентльменский набор «Денвер». И своей «комплексностью» (в его состав входит локальный сервер, оболочка для работы с СУБД, сервер MySQL) он доводит этих самых «джентльменов» до умопомрачения.

В состав «Денвера» входят лишь несколько стандартных утилит для работы с MySQL. А так как все мы современные люди, то за недостающей информацией (для понимания сущности чего-либо) отправляемся в интернет. Но в материалах, опубликованных в Сети, ничего не говорится, что для выполнения отдельных наборов команд требуется наличие в стандартном пакете MySQL специализированных утилит. Например, чтобы создать дамп базы данных MySQL, нужна программа MySQLDump.

Поэтому, если для обучения и тестирования кода вы также используете Denwer, и описываемые в этом материале запросы не будут выполняться в командной строке, то перейдите по адресу: D:\Webserver\usr\local\mysql-5.5\bin

И посмотрите, какие утилиты для работы с сервером СУБД имеются «на борту» клиентской машины. Повторюсь еще раз, нам сегодня потребуется программка MySQLDump.

Работа с MySQLDump

Данная утилита представляет собой обычный «экзешник», который после скачивания следует разместить в папку bin (путь к ней указан в первом разделе материала). Это маленькое приложение позволяет решать «глобальные» задачи – осуществлять в MySQL восстановление из дампа, создавать бэкапы и настраивать параметры резервных копий.

Утилита не имеет собственного интерфейса, поэтому работа с ней происходит через стандартную CMD, входящую в стандартный набор программ Windows (запуск через меню «Пуск»). А теперь пошагово:

Сначала переходим в виртуальный диск (создается после запуска входящего в состав «Денвера» Apache).

Если вы не используете набор для «джентльменов», то это не значит, что вы не джентльмен. Просто вам нужно пропустить этот шаг инструкции.

Затем с помощью команды cd (change directory) указываем путь к папке, где установлен MySQL. В «Денвере» эта директория находится здесь: Z:\usr\local\mysql-5.5\bin

При отдельной инсталляции системы управления БД эта папка располагается: C:\Program Files…

Поскольку мы уже находимся на нужном (виртуальном) диске, то нам осталось указать лишь часть пути после команды cd:

Проверим работоспособность приложения, и создадим MySQL дамп базы, которая находится на нашем сервере. Например, БД «wordpress». Для этого будем использовать одноименную с утилитой команду mysql dump. Вот ее синтаксис:

mysqldump -u username -p password name_database > backup- database.sql

mysqldump - u username - p password name_database > backup - database . sql

Пример использования команды:

mysqldump -uroot wordpress>Z:\home\localhost\dump\wp_copy.sql

mysqldump - uroot wordpress > Z : \ home \ localhost \ dump \ wp_copy . sql

В результате мы получили дамп базы данных сайта WordPress:

Что такое дамп?

Если ничего не знаете о дампе базы данных MySQL, и что это такое, с трудом догадываетесь, то данный раздел для вас. Дамп представляет собой одну из разновидностей резервных копий баз данных. Точнее, файлы копий БД (в зависимости от типа и средства создания), могут содержать строки данных определенного типа, а также включать в себя SQL-запросы для воссоздания утраченной информации, ее структуры и значений параметров.

Чтобы не быть голословными, откроем созданную нами копию wp_copy с помощью стандартного «Блокнота». После недолгого изучения его содержимого мы увидим, что оно почти целиком состоит из запросов, с помощью которых можно воссоздать структуру таблиц БД и их содержимое.

Тестовое восстановление

Если руки чешутся, а мыло не помогает, то проведем «тестовый заезд» утилиты. Для начала через phpMyAdmin (чтоб было быстрее) создадим тестовую БД «Auto» с двумя таблицами. Одну назовем «car», а другую «color». Добавьте в них по несколько строчек данных для наглядности.