Восстановить бд из дампа mysql. Дамп и восстановление базы данных MySQL

Утилита mysqldump служит для резервирования и восстановления резервных копий (часто называются "дампами") баз данных и таблиц MySQL. Утилита предназначена для командной строки и работает под операционными системами Windows, FreeBSD и некоторыми другими. Mysqldump является свободнораспространяемым ПО, не требующим обязательной оплаты.

Для локального копирования баз данных MySQL с типом таблиц ISAM и MyISAM лучше использовать утилиту, написанную на Perl: mysqlhotcopy . Работает она намного быстрее, чем mysqldump, так как копирует файлы баз данных *.frm, *.myd и *.myi целиком, предварительно заблокировав таблицы. Также, для корректной работы mysqlhotcopy необходимо наличие Unix-утилит: cp или scp. Для таблиц типа InnoDB mysqlhotcopy не подойдет, так как не все файлы таблиц InnoDB хранятся в стандартной директории БД MySQL.

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

Основной синтаксис mysqldump таков:

mysqldump [ OPTIONS] database [ tables]
mysqldump [ OPTIONS] -- databases [ OPTIONS] DB1 [ DB2 DB3... ]
mysqldump [ OPTIONS] -- all- databases [ OPTIONS]

Не указывая имена таблиц или используя параметры --databases или --all-databases, будет получен дамп всех баз данных находящихся в MySQL.

Записать дамп БД с удаленного сервера MySQL с адресом 127.0.0.2 на локальный компьютер в файл dump_db.sql:

mysqldump - uroot - h127.0.0.2 - p db_name > dump_db.sql

Восстановить базу MySQL db_name на удаленном сервере по адресу 127.0.0.2 с дампа, расположенного на локальном компьютере, в файле dump_db.sql:

mysql - uroot - h127.0.0.2 - p db_name < dump_db.sql

Записать дамп баз данных MySQL (database1, database2, database3), находящихся на удаленном сервере по адресу 127.0.0.2 в локальный файл dump_db.sql:

mysqldump - uroot - h127.0.0.2 - p - B database1 database2 database3 > dump_db.sql

Выборка не более 50 записей (напремер для тестирования не нужна полная копия БД):

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

mysqldump -- opt database | mysql -- host= remote- host - C database

Скопировать только структуру базы данных MySQL:

mysqldump - uroot - hh127.0.0.2 - p -- no- data my_dbname > my_db_structure.sql

Утилита mysqldump, используемая без опций --opt или --quick, перед тем, как сделать дамп результата выборки информации, запишет весь результат своей работы в память. Это может вызвать проблемы при получении дампа большой базы данных. Также следует учесть, что нет необходимости применять параметры --opt или -e, если предполагается использовать для получения дампа новую копию программы mysqldump, а затем воспроизводить его на очень старом MySQL-сервере.

Все параметры утилиты mysqldump можно просмотреть, запустив ее так:

mysqldump --help

Стандартный перечень параметров mysqldump с кратким описанием опубликован ниже:

--add-drop-database
Добавляет DROP DATABASE перед каждым оператором CREATE DATABASE.

--add-drop-table
Добавляет DROP TABLE перед каждым оператором CREATE TABLE.

--add-locks
Добавляет LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

--all-databases, -A
Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.

--allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.

--comments, -i
Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.

--compact
Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным --comments.

--compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.

--complete-insert, -c
Используется полная форма оператора INSERT (с именами столбцов).

--create-options
Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

--databases, -B
Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

--delayed
Использовать команду INSERT DELAYED при вставке строк.

--delete-master-logs
На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «--master-data».

--disable-keys, -K

Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.

--extended-insert, -e
Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

--flush-logs, -F
Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.

--force, -f
Продолжать даже если в процессе создания дампа произошла ошибка.

--hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263.

--ignore-table=db_name.tbl_name
Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «--ignore-table», указывая по одной таблице в каждом из параметров.

--insert-ignore
Добавляет ключевое слово IGNORE в оператор INSERT.

--lock-all-tables, -x
Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.

--lock-tables, -l
Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.

--no-autocommit
Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.

--no-create-db, -n
Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров --databases и --all-databases.

--no-data, -d
Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.

--opt
Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: --quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset. Начиная с MySQL 4.1, параметр --opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров --skip-opt

--order-by-primary
Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.

--port, -P
Номер TCP порта, используемого для подключения к хосту.

--protocol={TCP|SOCKET|PIPE|MEMORY}
Параметр позволяет задать протокол подключения к серверу.

--quick, -q
Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.

--quote-names, -Q
Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.

--replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.

--result-file=/path/to/file, -r /path/to/file
Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

--routines, -R
Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.

--single-transaction
Параметр создает дамп в виде одной транзакции.

--skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.

--socket=/path/to/socket, -S /path/to/socket
Файл сокета для подсоединения к localhost.

--tab=/path/, -T /path/
При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров --fields-xxx и --lines-xxx.

--tables
Перекрывает действия параметра --databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

--triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр --skip-triggers.

--tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE="+00:00", который позволит обмениваться дампа в различных временных зонах.

--verbose, -v
Расширенный режим вывода. Вывод более детальной информации о работе программы.

--version, -V
Вывести информацию о версии программы.

--where="where-condition", -w "where-condition"
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны: "--where=user="test"" "-wuserid>1" "-wuserid

--xml, -X
Представляет дамп базы данных в виде XML.

--first-slave, -x
Блокирует все таблицы во всех базах данных.

--debug=..., -#
Отслеживать прохождение программы (для отладки).

--help
Выводится справка и завершается работа программы.

Mysqldump - документация и примеры.

Данная статья может оказаться полезной, если у вас есть веб-сайт, который использует систему управления контентом (Joomla, WordPress, OpenCart и т.д.), и вы решили перенести его на другой сервер . Для этого требуется не только перенос файлов сайта, но и перенос . Перед тем, как приступить к переносу веб-сайта, необходимо подобрать хороший и заказать его, чтобы получить доступ к новому хостинговому аккаунту. Мы, например, предлагаем отдельный , Joomla и PrestaShop. Если вы хотите перенести сайт на новое имя и вам нужно , не торопитесь. Наш план виртуального хостинга S4 предоставит вам домен и в подарок. Если для вашего сайта недостаточно виртуального хостинга, вы всегда можете заказать VPS или .

Для переноса базы данных необходимо сначала создать ее дамп, то есть разместить содержимое в отдельный sql-файл. Делается это в меню phpMyAdmin на хостинге, откуда вы переносите сайт. Зайдите в phpMyAdmin, выделите слева базу данных, которую необходимо перенести, и нажмите на кнопку «Экспорт » в верхнем меню.

Откроется диалоговое окно, в котором обязательно выберите тип базы SQL, после чего нажмите кнопку ОК . Дождитесь пока файл базы данных скачается на ваш компьютер.

Теперь этот файл необходимо корректно разместить на нашем хостинге. Для этого необходимо сначала создание базы данных MySQL в cPanel аккаунте, добавление к ней пользователя и назначение ему привилегии. Ниже по порядку расписано, как создать базу данных в MySQL.

Зайдите в cPanel и найдите раздел «Базы данных », выберите «».

Откроется новое окно. На шаге 1 введите имя базы. Обратите внимание на то, что имя БД всегда будет с префиксом, первая половина имени будет содержать в себе имя cPanel аккаунта.

После ввода имени нажмите на кнопку «Следующий шаг ».

На шаге 2 необходимо создать пользователя MySQL к базе и задать пароль для него. После ввода всех данных нажмите кнопку «Создать пользователя ».

При настройке привилегий созданного пользователя выберите опцию «ВСЕ ПРАВА » и переходите к следующему шагу.

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

Созданную БД вы теперь сможете найти в списке доступных баз MySQL в одноименном меню контрольной панели.

Теперь для восстановления базы данных MySQL импортируем в созданную базу содержимое сохраненного со старого хостинга файла с расширением .sql . Для этого на главной странице cPanel выберите пункт меню “phpMyAdmin ” в том же разделе “Базы данных ”. В открывшемся окне в меню слева выберите только что созданную БД и нажмите на вкладку «Импорт » в верхнем меню.

Нажмите кнопку «Выберите файл », в диалоговом окне выберите ранее сохраненный sql-файл и загрузите его на хостинг. Убедитесь, что выбран тип кодировки utf-8. После нажатия кнопки ОК дождитесь сообщения об успешном импорте базы данных.

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

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

А для чего собственно необходимо переносить базу данных?

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

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

Итак, что такое дамп базы данных разобрались. Остался вопрос как его использовать. Рассмотрим стандартную в веб-программировании задачу - перенос сайта, созданного на локальной машине на веб-хостинг. С переносом файлов описывать подробно не буду, остановлюсь на базах данных. Итак, имеем бд MySql и задачу перенести ее на веб-хостинг. Чтобы не изобретать велосипед будем использовать широко распространенную утилиту phpMyAdmin, которая как правило всегда установлена Вашим провайдером, да и в состав денвера она тоже входит. В конце-концов утилита еще и бесплатна и доступна для скачивания на официальном сайте. Работает она на веб-сервере, а написана на языке php, что позволяет пользоваться ею прямо в браузере. Т.е. тут проблем возникнуть не должно, если они все же возникли, пишите в комментарии - разберемся.

Приступаем к созданию дампа базы данных. Открываем phpMyAdmin, выбираем нужную нам базу и нажимаем на вкладку "Export ". В результате вы должны получить что-то вроде этого:

Здесь нужно выбрать все таблицы (ну или те в которых нужно откатить изменения) и поставить radio button в значение SQL. В разделе Structure можно пометить первое поле "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT" для того чтобы перед импортом дампа в базе назначения удалились соответсвующие таблицы, если она переносится первый раз - признак можно не помечать. В том же окне ниже видим раздел "Save as file":

Собственно помечаем "Save as file" и пишем желаемое имя файла. Дамп базы данных можно заархивировать, но как правило он имеет не столь значительный размер, чтобы имело смысл сжимать файл. Нажимаем кнопку "GO", и получаем стандартное окно сохранения файла в браузере:
. Сохраненный файл и есть дамп базы данных. Если открыть его в том же блокноте - увидите в начале строки вида

PhpMyAdmin SQL Dump
-- version 3.2.3
-- http://www.phpmyadmin.net

Сам файл состоит из запросов на создание и вставку значений в таблицу, написанных на языке SQL. Тут абсолютно никакого шаманства, все очень даже очевидно. Теперь следующая задача - импорт дампа. С ней тоже ничего сложного. Заходите в панель управления хостингом, находите там phpMyAdmin, запускаете. Возможно придется указать логин/пароль - в зависимости от настроек хостера. Ну а дальше действия похожи - выбираем нужную бд, жмем вкладку "Import", видим такое окно:

В разделе "File to import" при помощи кнопки "Обзор" указываем файл дампа базы данных, ниже проставляем кодировку - в joomla по-умолчанию это utf-8. Ниже расположен раздел "Partial import" и поле "Number of records (queries) to skip from star" куда можно ввести числовое значение - количество строк, обрабатываемых за один проход, на тот случай если за время жизни скрипта вся база не успевает импортироваться. Значение по умолчанию "0" соответствует импорту всей базы за один запуск скрипта. Мне в моей практике не приходилось сталкиваться с тем что база не успевает залиться за один проход, хотя это не говорит что таких случаев не бывает. Значения остальных полей менять не требуется, просто нажимаем кнопку "GO". Вот собственно и все, дамп базы данных импортирован.

Ну и напоследок несколько уточнений.

1) Почему я описал способ импорта/экспорта всех таблиц а не бд целиком? Дело в том что наиболее часто приходится сталкиваться с процедурой импорта/экспорта при переносе данных с веб-сервера на локальную машину и наоборот. Но очень часто на локальной машине используется доступ по учетной записи "root" без пароля. На хостинге такого мы себе позволить не можем. А база данных как раз таки содержит в себе не только данные, но и "privileges" - права на доступ к ней определенных пользователей. Чтобы не приходилось каждый раз заводить/удалять пользователей и назначать им права доступа используется подход когда в дамп базы данных записывается только структура таблиц и данные в ней. В случае если создается просто бакап для того же сервера вполне можно сделать дамп с бд целиком.

2) Почему именно phpMyAdmin? Дело здесь совсем не в том что ему нет альтернативы - их как раз очень много, есть и более функциональные вещи и более простые. Просто phpMyAdmin входит в стандартный набор для большинства виртуальных хостингов, т.е. для его установки не придется делать абсолютно ничего. Т.е. мы просто открываем его и делаем дамп базы данных. Ну и конечно использование phpMyAdmin может быть полезным для массы задач - того же тестирования запросов, просмотра структуры таблиц, данных в них.

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

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

Разделы:

Установка mysqldump:

Что такое mysqldump?

MySQLdump – это серверное приложение, которое позволяет делать резервное копирование (далее дамп) баз данных и сохранять их в отдельном файле. При этом можно осуществлять гибкие настройки дампа: несколько или все базы данных, архивация в gzip, добавление команд lock, drop и многое другое. Также возможнен обратный импорт резервных копий БД. Осуществлять можно с помощью PHP, но это неприемлемо для больших проектов, которые имеют большой вес данных.

Эта программа очень полезна при реализации экспорта и импорта данных с БД. Она может быть стандартно установленной на вашем хостинге (точнее mysql сервере). Но для того, чтобы отточить мастерство работы с mysqldump и научится устанавливать, можно поставить ее на denwer. Что мы сейчас и сделаем.

Скачать mysqldump

Вы всегда можете скачать программу mysqldump у нас на сайте, при этом скачивание приложения совершенно бесплатно. Скачайте mysqldump по прямой ссылке ниже.

Как установить mysqldump?

Устанавливать будем на локальный сервер Denwer. Установить приложение легко и просто, для этого следуйте ниже предоставленным инструкциям и скриншотам.

1.Копируем файл mysqldump.exe в папку с денвером:

D:\WebServers\usr\local\mysql5\bin\
При этом у вас может быть немного другое название папки mysql5, например mysql-5.1 или немного видоизмененное. Поэтому, для большей ясности, ниже предоставлен скриншот:

2.Запускаем денвер

Наверняка вы и сами знаете как запустить Denwer.

3.Запускаем консоль:

Пуск->Выполнить-> cmd. exe или в ОС Windows 7: Пуск->Поиск->Вводим cmd. exe-> Enter , как показано на скриншоте:

4.Тестируем:

С помощью команд в консоле, переходим на виртуальных диск денвера (у меня W:\) и в папку с приложением mysqldump. Для подтверждения выполнения команды жмем Enter.

Вводим команды:

W: - заходим на виртуальный диск денвера

cd usr\ local\ mysql5\ bin – заходим в папку с приложением

mysqldump -uroot имя_вашей_бд>имя_файла.sql – тестируем, делаем дамп произвольной базы данных в файл, который сохранится в папку bin.

У меня установка выполнена успешно, надеюсь у вас тоже. В папке bin находим файл резервной копии БД. Чтобы научится более широко использовать программу, читайте следующие разделы статьи.

Начало работы: экспорт и импорт БД

Экспорт базы данных

Мы уже установили приложение и научились пользоваться консолем. Тестовый дамп мы уже делали. Теперь сделаем простой дамп базы данных в нужный нам каталог. Для этого я создал ранее используемую БД под именем “test”. Она находится на локальном сервере denwer. Ниже предоставлены пошаговые команды консоля для дампа бд test в нужную папку и нужный файл.

W: cdusr\local\mysql5\bin mysqldump -uroot test>D:\test\easydump.sql

На скриншоте ниже показан дамповый файл в папке тест:

Экспорт выполнен успешно. Теперь попробуем импортировать этот файл обратно на наш сервер.

Импорт базы данных

Для импорта БД, очищаем БД в phpmyadmin, и пользуемся следующей командой в cmd.exe:

Mysql -uroot test

Важное замечание: если при экспорте мы использовали mysqldump… , то при импорте нужно начинать команду с mysql . В этом примере заключается базовое использование приложения mysqldump, для создания резервных копий (бэкапа) баз данных. Еще более команд и примеров, вы найдете в разделе и статье .

MySQLdump примеры

Ниже предоставлены наиболее используемые примеры использования mysqldump. С помощью которых можно не только делать бэкап, но и добавлять некоторые параметры резервного копирования: сжатие с помощью gzip, добавление даты бэкапа, делать дамп только нескольких таблиц или структуры БД, использовать гибкие настройки. Эти параметры позволяют увеличить скорость выполнения дампа и экономно использовать место дискового пространства.

Создание дампа

mysqldump –uUSER -h82.82.82.82 -pPASSWORD DATABASE > /path/to/file/dump.sql

-u или --user=... - имя пользователя

-h или --host=... - удаленный хост (для локального хоста можно опустить этот параметр)

-p или --password - запросить пароль

database - имя экспортируемой базы данных

/path/to/file/dump.sql - путь и файл для дампа

Делаем дамп нескольких баз данных, для этого используем атрибут --databases или сокращенно –B, смотрите на примере ниже:

Mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

Если вы желаете создать дамп всех баз данных, необходимо использовать параметр –all-databases или –А в сокращенном виде, смотрим пример:

Mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Создаем структуру базы без данных

Для этого необходимо использовать параметр --no-data как показано на примере ниже:

Mysqldump --no-data - uUSER -pPASSWORD DATABASE > /path/to/file/schema.sql

Создаем дамп только одной или нескольких таблиц БД

mysqldump -uUSER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > dump.sql

Создаем дамп и архивируем его в gzip

mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создаем дамп с указанием даты в имени файла

mysqldump -uUSER -pPASSWORD DATABASE | gzip > `date +dump.sql.%Y%m%d.%H%M%S.gz`

Используем дополнительные атрибуты

mysqldump -Q -c -e -uUSER -pPASSWORD DATABASE > /path/to/file/dump.sql

Как уже упоминалось раньше, эти атрибуты уменьшают итоговый размер файла и ускоряют процесс резервного копирования. А точнее:

-Q оборачивает имена обратными кавычками

-c делает полную вставку, включая имена колонок

-e делает расширенную вставку.