Сделайте дамп бд из файла. Дамп и восстановление базы данных MySQL

Данная статья может оказаться полезной, если у вас есть веб-сайт, который использует систему управления контентом (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. После нажатия кнопки ОК дождитесь сообщения об успешном импорте базы данных.

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

Данная утилита позволяет получить дамп (``моментальный снимок"") содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Если же резервная копия создается на сервере, то вместо описываемой утилиты следует использовать mysqlhotcopy . См.раздел See section 4.8.6 mysqlhotcopy , Копирование баз данных и таблиц MySQL .

Shell> mysqldump database или mysqldump --databases DB1 или mysqldump --all-databases

Если не указывать имена таблиц или использовать параметры --databases или --all-databases , то будет получен дамп базы данных в целом (соответственно - всех баз данных).

Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump , можно получить, выполнив команду mysqldump --help .

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

Учтите, что не следует применять параметры --opt или -e , если вы собираетесь использовать для получения дампа новую копию программы mysqldump , а затем воспроизводить его на очень старом MySQL-сервере.

Утилита mysqldump поддерживает следующие опции:

Add-locks Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). --add-drop-table Добавить команду DROP TABLE перед каждой командой CREATE TABLE . -A, --all-databases Произвести дамп всех баз данных. Аналогично опции --databases с указанием всех баз данных. -a, --all Включить все опции создания объектов, специфичные для MySQL. --allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. -c, --complete-insert Использовать полные команды INSERT (с именами столбцов). -C, --compress Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие. -B, --databases Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных. --delayed Использовать команду INSERT DELAYED при вставке строк. -e, --extended-insert Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). -#, --debug[=option_string] Отслеживать прохождение программы (для отладки). --help Вывести справочную информацию и выйти из программы. --fields-terminated-by=... --fields-enclosed-by=... --fields-optionally-enclosed-by=... --fields-escaped-by=... --lines-terminated-by=... Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE . См. раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE . -F, --flush-logs Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. -f, --force, Продолжать даже при получении ошибки SQL при выполнении дампа таблицы. -h, --host=.. Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию - localhost . -l, --lock-tables. Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL , чтобы разрешить параллельные записи для MyISAM -таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция --lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях. -K, --disable-keys Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных. -n, --no-create-db В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций --databases или --all-databases . -t, --no-create-info Не записывать информацию о создании таблицы (команда CREATE TABLE). -d, --no-data Не записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы! --opt То же, что и --quick --add-drop-table --add-locks --extended-insert --lock-tables . Должно дать наиболее быстрый дамп для чтения на MySQL-сервере. -pyour_pass, --password[=your_pass] Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль. -P port_num, --port=port_num Номер порта TCP/IP, используемого для подключения к хосту (применяется при подсоединении к хостам, отличным от localhost , для которого используются сокеты Unix). -q, --quick Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result() . -Q, --quote-names Взять в кавычки имена таблиц и столбцов без символов ``" . -r, --result-file=... Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки "\n" в последовательность "\n\r" (новая строка + возврат каретки). --single-transaction Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB -таблицамии и уровнем изоляции транзакций READ_COMMITTED , так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM - или HEAP -таблицы при использовании данной опции могут все же изменить свое состояние. Опция --single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции --lock-tables , так как команда LOCK TABLES прерывает выполнение предыдущей транзакции. -S /path/to/socket, --socket=/path/to/socket Файл сокета для подсоединения к localhost (значение хоста по умолчанию). --tables Перекрывает параметр --databases (-B). -T, --tab=path-to-some-directory Для каждой заданной таблицы создает файл a `table_name.sql" , содержащий SQL CREATE команды для создания таблицы, и файл `table_name.txt" с данными таблицы. Файл `.txt" имеет формат в соответствии с параметрами --fields-xxx и --lines--xxx . Примечание : Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld , причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу. -u user_name, --user=user_name Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix. -O var=option, --set-variable var=option Установить значения переменных. Доступные для использования переменные перечислены ниже. -v, --verbose Расширенный режим вывода. Вывод более детальной информации о работе программы. -V, --version Вывести информацию о версии и выйти из программы. -w, --where="where-condition" Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. "--where=user="jimf"" "-wuserid>1" "-wuserid -X, --xml Представляет дамп базы данных в виде XML. -x, --first-slave Блокирует все таблицы во всех базах данных. -O net_buffer_length=#, where # Чаще всего утилита mysqldump используется для получения резервной копии всех баз данных. See section 4.4.1 Резервное копирование баз данных . mysqldump --opt database > backup-file.sql

Mysql database

Mysql -e "source /patch-to-backup/backup-file.sql" database

Данная утилита достаточно часто используется и для переноса информации из базы данных на другой MySQL-сервер:

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

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

Mysqldump --databases database1 > my_databases.sql

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

Mysqldump --all-databases > all_databases.sql

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

В приведенном выше примере, для создания бекапа используется утилита 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. Рассмотрим сразу же пример.

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

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