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

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

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

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

Немного теории..

MySQLDUMP это инструмент, который позволяет создавать резервные копии баз данных MySQL. На выхлопе мы получаем.sql файл с дампом базы данных. В данном файле содержится Sql код в виде текста, т.е. его всегда можно открыть текстовым редактором, чтобы посмотреть, отредактировать и т.д…
Восстановить такой дамп можно с помощью утилиты mysql через STDIN.

Кстати, есть еще такая утилита, как MySqlHotCopy, которую лучше всего использовать для создания горячих резервных копий, так как она ставит базу на блокировку и копирует файлы БД в нужное место. Но эта штука будет работать только если ее запускать на самом сервере, работает только с MyISAM и Archive-таблицами и больше подойдет для больших БД.
Восстановить данные можно путем копирования сохраненных файлов в каталог данных MySQL.

Но, в общем-то речь не о ней… может напишу о «горячей копии» в другой статье…

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

Начнем с самой распространенной команды создания дампа сайта site.ru в файл site.ru:

Mysqldump -uroot -h10.30.30.10 -p site_ru > site_ru.sql

Подробнее о ключах:
• -u (—user=…) это имя пользователя БД;
• -h (—host=…) это хост, на котором располагается сам сервер. Если сервер локальный, то данных параметр можно или не использовать или прописать туда localhost. Так же имейте в виду, что ip сервера после ключа нужно писать без пробела;
• -p (—password=…) это пароль пользователя. Если этот ключ не использовать, то подключение к БД будет возможно в том случае, если пароля нет. Так же никто не запрещает вписать пароль в команду, причем после ключа не должно быть пробела (например: -p1234567890, где 1234567890 это пароль);
• site_ru это имя БД на MySql сервере;
• site_ru.sql это файл для дампа.

Чтобы сделать резервную копию нескольких БД, можно воспользоваться ключом -B и указать несколько БД, вот пример:

Mysqldump -uroot -h10.30.30.10 -p site_ru site2_ru site3_ru > sites.sql

Если ситуация не дает времени на раздумья, и нужно делать резервную копию всех баз данных, то в данной ситуации можно воспользоваться ключом —all-databases , вот пример:

Mysqldump -uroot -h10.30.30.10 -p -A > all-db.sql

Кстати, есть нюанс.

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

Первый способ заключается в блокировке таблиц, т.е. можно воспользоваться параметром —lock-tables, вот пример:

Mysqldump -uroot -h10.30.30.10 -p --lock-tables site_ru > site_ru.sql

Но в момент создания резервной копии запросы клиентов будут подвешены.. а следовательно могут быть тайм-ауты.

Второй способ это использование ключа —flush-log при создании резервной копии. Этот ключ закроет старый лог действий и создаст новый. Если кто-то что-то запишет в процессе создания копии — это отразится в начале журнала и можно будет перенести это изменение в базу. Далее, чтобы наверняка, после окончания создании резервной копии, нужно выполнять команду mysqladmin -flush-logs и оставлять копию предпоследнего бинарного журнала.

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

Тут уже будет править утилита «mysql». Вот пример:

Mysql -uroot -h10.30.30.10 -p site_ru < site_ru.sql

Еще один способ, более педантичный:

Mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.6.35-1+deb.sury.org~xenial+0.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. mysql> use site_ru; mysql> source site_ru.sql;

Ну а если у вас БД сохранена в gz-архив, то можно скомбинировать команды mysql и zcat вот так:

Zcat site_ru.sql.gz | mysql -uroot -h10.30.30.10 -p site_ru

Еще варианты использования mysqldump

Например нам нужна база данных на dev зону, так сказать песочницу, а размер основной БД очень велик. Можно воспользоваться ключом —where=»true limit 150″, которому мы явно укажем выборку данных не более 150 записей. Вот пример:

Mysqldump -uroot -h10.30.30.10 -p --where="true limit 150" site_ru > site_ru.sql

Если нам нужна только структура без данных, то можно воспользоваться ключом —no-data, вот пример

Mysqldump -uroot -h10.30.30.10 -p --no-data site_ru > site_ru.sql

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

Mysqldump -u root -p testdb tablename > testdb_table_backup.sql

Если нам нужна копия триггеров, процедур и событий (встроенного планировщика), то вот пример:

Mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p site_ru | gzip > ~/database.sql.gz

Еще можно сразу создать заархивированный дамп базы. Сделать это можно вот так:

Mysqldump -uroot -p site_ru | gzip > /path/to/site_ru.sql.gz

И еще можно указать дату создания архива, вот так:

Mysqldump -uroot -p site_ru | gzip > `date +/path/to/site_ru.sql.%Y%m%d.%H%M%S.gz`

Ключи для использования mysqldump

Ниже будут приведены наиболее популярные ключи 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» будет заменена на 0x616263.
• —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.
• —events, -E — Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.
• —tz-utc — при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.
• —verbose, -v — Расширенный режим вывода. Вывод более детальной информации о работе программы.
• —version, -V — Вывести информацию о версии программы.
• —where=’where-condition’, -w ‘where-condition’ — Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.
• —xml, -X — Представляет дамп базы данных в виде XML.
• —first-slave, -x — Блокирует все таблицы во всех базах данных.
• —debug=…, -# — Отслеживать прохождение программы (для отладки).
• —help — Вывести справочную информацию и выйти из программы.

Приветствую вас, друзья! 🙂

Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL.

В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат.

Мы рассмотрим различные варианты выборки информации из : создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT запросов.

А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL.

В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов.

Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить.

А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об .

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

Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали.

На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов.

Создание дампа базы MySQL через консоль

Хочу в самом начале сделать небольшое уточнение.

Дамп базы — это файл с набором SQL команд, который при запуске позволяет создавать базы данных и таблицы, а также наполнять их информацией. Дамп нужен тем, кто хочет скачать базу данных MySQL с целью её копирования на другой сервер или в рамках существующего.

Также, если кто-то не в курсе, бэкап базы данных MySQL — это, по сути, её дамп, сделанный в определённый период времени, который позволяет восстановить структуру и данные базы при необходимости.

Экспорт данных — это просто извлечение информации из таблиц в текстовом виде для дальнейшей работы с текстовом или графическом редакторах.

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

Для создания дампа БД у MySQL есть встроенная утилита под названием mysqldump , которую нужно использовать за пределами командной строки MySQL в консоли сервера или другой оболочке.

Итак, для самого простого и распространённого варианта — экспорта данных конкретной БД в консоли MySQL для переноса её на другой сервер или внутреннего копирования нужно выполнить следующую команду:

Mysqldump -u имя_пользователя -p имя_базы_данных > путь_и_имя_файла_дампа

Данная утилита может создавать дампы базы MySQL только в виде файлов с SQL командами, поэтому какое бы вы расширение для своего файла не выбрали — в любом случае его содержимое будет одинаковым. И не забудьте перед экспортом информации из MySQL проверить права на запись у директории, в которой он будет располагаться, чтобы создание файла было возможно.

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

Mysqldump -u имя_пользователя -p --all-databases > путь_и_имя_файла_дампа

Для создания дампа лишь для нескольких конкретных баз данных вам понадобится вызов mysqldump со следующими параметрами:

Mysqldump -u имя_пользователя -p --databases имя_базы_данных1, имя_базы_данных2, ... > путь_и_имя_файла_дампа

В итоге, в каждом случае вы получите дамп базы MySQL, содержащий команды для создания структуры содержащихся таблиц (поля, их типы, индексы, ключи и т.д.), а также операции наполнения их данными.

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

О том, как делать бэкапы определённых таблиц MySQL и получать их данные в читаемом виде, речь пойдёт дальше.

Делаем дамп таблицы MySQL и экспорт данных

Для создания дампа определённых таблиц MySQL базы данных нам понадобится всё та же утилита mysqldump , вызываемая со следующими параметрами:

Mysqldump -u имя_пользователя -p имя_базы_данных имя_таблицы1, имя_таблицы2, ... > путь_и_имя_файла_дампа

Ещё при вызове mysqldump можно указывать требуемые таблицы в качестве значения параметра —tables , при использовании которого параметр —databases будет игнорироваться:

Mysqldump -u имя_пользователя -p --databases имя_базы_данных1, имя_базы_данных2 --tables имя_таблицы1, имя_таблицы2, ... > путь_и_имя_файла_дампа

Приведённый пример выведет на экран следующую ошибку:

Mysqldump: Got error: 1049: Unknown database "имя_базы_данных1," when selecting the database

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

Хорошо, дамп таблиц MySQL базы данных мы получили. Его можно использовать для их восстановления или копирования вместе со структурой.

А что, если нужно получить просто хранимую в них информацию и, желательно, в читаемом виде, чтобы можно было её отправить менеджеру и просмотреть в обычном текстовом или табличном редакторе? У MySQL есть средства и для этого.

Достичь задуманного нам поможет вариант вызова утилиты mysql из консоли с определёнными параметрами:

Mysql -u имя_пользователя -p имя_базы_данных -e "SELECT * FROM имя_таблицы"

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

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

Mysql -u имя_пользователя -p -e "SELECT * FROM имя_таблицы" > путь_и_имя_файла

Благодаря данным конструкциям мы можем не только получить данные, хранящиеся во всех полях таблицы, но и в конкретных. Для этого достаточно вместо символа wildcards (*) прописать через запятую требуемые.

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

Если же захотите экспортировать данные из MySQL базы в xls или csv формате, чтобы полученный файл корректно отображался в табличных редакторах, то о том, как это сделать будет рассказано немного позже 🙂

Создание бэкапов и вывод данных из MySQL базы с помощью запросов

О том, как сделать дамп базы данных MySQL — одной и нескольких, а также отдельных их таблиц, мы поговорили. Но иногда на практике бывают случаи, когда нужно экспортировать набор данных, не ограниченный одной таблицей. Либо нужно выбрать лишь некоторые данные из таблицы.

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

Для бэкапа нам понадобится всё та же утилита mysqldump , которую нужно будет вызвать в таком виде:

Mysqldump -u имя_пользователя -p имя_базы_данных имя_таблицы --where "уточняющий_запрос" > путь_и_имя_файла_дампа

В результате мы получим файл с SQL командами для создания таблицы со всей его структурой, которая после создания будет наполняться информацией, выбираемой с помощью уточняющего запроса.

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

Mysql -u имя_пользователя -p -e "SELECT * FROM имя_таблицы WHERE уточняющий_запрос" > путь_и_имя_файла

Как вы понимаете, помимо различных уточнений, указываемых в запросе с помощью директивы WHERE , можно использовать и прочие SQL конструкции: JOIN , UNION и т.д.

Статистику собрать получится какую угодно 🙂

То же самое действие возможно произвести также при работе в командной строке MySQL с помощью следующей команды:

SELECT * FROM таблица_базы_данных WHERE уточняющий_запрос INTO OUTFILE "путь_и_имя_файла";

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

Если перечисленное — ваш случай, то с полным списком параметров и вариантов вызова данной команды вы можете ознакомиться здесь — https://dev.mysql.com/doc/refman/5.7/en/select-into.html

В завершение своего краткого экскурса по mysqldump хочу привести вариант вызова команды со списком параметров для создания оптимизированного дампа базы MySQL и таблиц, восстановление БД и отдельных таблиц из которого будет занимать меньше времени, чем при обычном вызове:

Mysqldump -u имя_пользователя -h хост_или_IP_сервера_MySQL -p --no-autocommit --opt имя_базы_данных > путь_и_имя_файла_дампа;

Ради эксперимента я использовал данный вариант для того, чтобы сделать дамп базы MySQL размером в 143 Мб. Последующее восстановление заняло 59 секунд времени против 1 минуты и 3 секунд, когда БД восстанавливалась из дампа, сделанного вызовом mysqldump без специальных параметров.

Согласен, что это мелочь. Но это только в случае данного объёма данных. Если использовать данную методику при создании дампа размером более 1Гб, то разница будет более существенной.

Если вы столкнётесь с такой ситуацией, то не забудьте ещё предварительно запаковать дамп базы MySQL в архив. Лучше всего tar.gz. Тогда восстановление займёт ещё меньше времени.

Экспорт данных из MySQL в Excel и csv файлы

Я не зря объединил информацию о выводе информации из MySQL в эти два формата в одном блоке, т.к. они очень похожи, используются примерно одинаково (для структурирования информации в виде таблиц) и команды для экспорта будут вызываться одни и те же.

Как известно, единственным существенным различием между данными форматами является то, что расширение xls и xlsx имеют файлы, создаваемые в программе Microsoft Office Excel, которая работает только под Windows, а csv файлы являются более универсальными и операции с ними возможны во многих редакторах.

Это не значит, что xls нигде, кроме Microsoft Office Excel, не откроется. Тот же OpenOffice подтверждает обратное.

Но для такой возможности данная поддержка должна присутствовать в программном продукте. csv же файлы доступны для чтения даже в обыкновенном текстовом редакторе типа «Блокнот», только такая форма будет не совсем читабельной.

Начну с того, что экспортировать в xls или csv можно только результаты SQL запросов, работать с которыми мы с вами научились ранее, т.к. всю базу данных в один файл будет невозможно вывести за одну операцию.

Во-первых, это не оптимально, т.к. такой файл вряд ли откроется при больших объёмах хранящейся в БД информации. А, во-вторых, непонятно, как разбивать внутри файла информацию по таблицам и полям.

Нет, сделать это, конечно, возможно, но вряд ли одной командой и вообще вряд ли кто-то будет таким заниматься в консоли. Думаю, что для данных целей понадобится специальное ПО или, как минимум, скрипт.

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

Итак, если мы говорим о том, как сделать экспорт данных из MySQL в xls и csv, то сделать это можно прямо в консоли сервера через утилиту mysql либо в , работой с которой я знакомил вас в предыдущей своей статье.

Начнём по порядку.

Экспортировать данные из MySQL базы данных в csv и xls форматы прямо в консоли сервера можно следующими командами.

На Linux системах:

Mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > путь_и_имя_файла.csv

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

На Windows :

Экспорт данных из MySQL таблиц в csv приведённой выше командой в данном случае, к сожалению, не удастся, т.к. у Windows, в отличие от Linux, нет встроенной консольной команды для работы с потоками, какой является sed в Linux.

Установить её, конечно, можно, но слишком много хлопот. Ещё, как вариант, можете использовать CygWin — эмулятор консоли Linux для Windows систем.

Хорошо, если она у вас уже установлена. В противном случае экспорт данных из БД MySQL выбранным способом принесёт нам слишком много хлопот.

Зато извлечение информации в xls файл прост как 5 копеек 🙂 Запустить его очень просто следующим способом, который я опробовал лично:

Mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" > путь_и_имя_файла.xls

Открылся данный файл в Microsoft Office Excel вообще без всяких проблем. Единственное, при открытии на экран вывелось сообщение с предупреждением о том, что действительный формат открываемого файла отличается от указываемого его расширения.

Но при подтверждении действия документ открылся без трудностей — вся информация была разбита по ячейкам в том виде, в каком она хранилась в самой таблице.

Не знаю, возможно, при произведении каких-либо специфических действий в Microsoft Office Excel и возникнут проблемы в дальнейшем, я так глубоко не копался. При обычном просмотре данных, по крайней мере, я ничего необычного не встретил.

Если у вас в процессе использования экспортированного из MySQL xls файла возникнут какие-то проблемы либо в данной программе, либо в других, то дайте мне знать, пожалуйста, в комментариях.

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

OpenOffice, кстати, всё равно 🙂 Он автоматически разграничил информацию, полученную способом, которым мы экспортировали содержимое базы MySQL в xls. Не знаю, как он это делает — но рекомендую пользоваться 🙂

Ну, а тот же самый Microsoft Office Excel отобразил всю информацию, соответствующую одной записи в таблице, записав её в одну ячейку без каких-либо разделителей. Думаю, что подобным образом поступят и другие редакторы таблиц.

Поэтому при экспорте базы данных MySQL в csv файлы нужно делать это, разделяя информацию специальными символами, воспринимаемыми редакторами.

И тут я плавно подошёл ко второму способу экспорта MySQL данных в csv и xls, который заключается в использовании командной строки MySQL.

Итак, для того, чтобы экспортировать данные MySQL в csv файл данным способом, нам нужна следующая команда:

SELECT * FROM таблица_базы_данных INTO OUTFILE "путь_и_имя_файла.csv" FIELDS TERMINATED BY "," ENCLOSED BY """ LINES TERMINATED BY "\n";

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

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

SELECT * FROM таблица_базы_данных INTO OUTFILE "путь_и_имя_файла.xls";

Однако, на практике не всё так просто, как я описал. Во время выполнения команды вы можете столкнутся со следующей ошибкой в консоли, препятствующей выполнению экспорта:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Она вызвана тем, что ваш MySQL сервер был запущен с опцией —secure-file-priv . Лично я столкнулся с данной проблемой из-за того, что для работы в консоли пользуюсь дистрибутивом MySQL, входящим в комплект WAMP OpenServer, который, в свою очередь запускает MySQL сервер данным образом.

Здесь есть два способа решения проблемы:

• Изменить параметры запуска MySQL сервера
• Изменить путь к конечному файлу экспорта MySQL

Первый способ мне показался слишком сложным, т.к. пришлось бы копаться в конфигурации OpenServer, которая не мною была написана со всеми вытекающими из этого обстоятельствами 🙂 Поэтому я решил пойти вторым путём. Если вы столкнулись с подобной проблемой — повторяйте за мной.

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

SHOW VARIABLES LIKE "secure_file_priv"; SELECT @@GLOBAL.secure_file_priv;

Результатом выполнения обеих будет значение глобальной переменной MySQL secure_file_priv , которая содержит путь к каталогу, через который могут осуществляться операции экспорта и импорта данных MySQL (в будущем ссылка на статью об импорте данных).

Т.е. при использовании команд LOAD DATA и SELECT … INTO OUTFILE экспортируемые и импортируемые файлы могут располагаться только внутри данного каталога.

В моём случае у данной переменной было вообще установлено значение NULL , т.к. я, как уже говорил, пользуюсь для работы в консоли утилитами MySQL из дистрибутива, входящего в OpenServer. Данное значение указывало на то, что операции экспорта данных MySQL и импорта с помощью указанных команд были вообще закрыты.

Как потом выяснилось, это распространённая ситуация в случае использования коробочных WAMP и MAMP серверов.

К сожалению, привычными методами изменения значений глобальных переменных MySQL в моём случае воспользоваться не удалось:

SET имя_переменной = значение;

В результате я увидел в консоли лишь следующую ошибку:

ERROR 1238 (HY000) at line 1: Variable "secure_file_priv" is a read only variable.

В итоге, чтобы изменить значение переменной secure_file_priv и открыть операции экспорта и импорта, мне потребовалось зайти в файл конфигурации MySQL mysql.ini, который расположен в корневой директории дистрибутива MySQL, или к нему можно получить доступ иным способом, если MySQL входит в комплект вашего WAMP/LAMP/MAMP сборки сервера.

Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.

В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:

Secure-file-priv = "%dprogdir%\\userdata\\temp"

Если у вас её не будет, то пропишите её с нуля в секции (по крайней мере, у меня она располагалась там).

Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из MySQL и их импорте обратно файлы у меня теперь будут храниться в каталоге c:\openserver\userdata\temp\ .

После изменения конфига (любого, кстати), не забывайте перезапускать ваш сервер либо отдельную службу, настройки которой вы правили, если есть такая возможность, чтобы изменения вступили в силу!

Для уверенности, после перезапуска MySQL сервера ещё раз выводим на экран переменную secure_file_priv и копируем её значение в буфер обмена.

А теперь нам нужно вызвать команду, что и в начале, только перед названием файла, в который будет сохраняться информация из БД MySQL, прописать путь, хранимый в изменяемой нами переменной в следующем виде:

SELECT * FROM таблица_базы_данных INTO OUTFILE "значение_secure_file_priv\имя_файла.csv";

После этого экспорт данных из MySQL в моём случае заработал.

Важный момент! Если вы работаете с MySQL под Windows, то не забывайте при указании пути к файлу поменять «\» на «/», иначе ошибка с —secure-file-priv всё равно продолжит выводиться.

На этом статья о том, как сделать дамп базы MySQL и её таблиц, а также сделать вывод данных из MySQL таблиц в различные форматы, подходит к концу. Пишите в комментариях свои отзывы и делитесь со всеми своими вариантами скриптов, которыми вы пользуетесь на практике чаще всего.

Если статья вам понравилась — вы можете поблагодарить автора репостом статьи в социальные сети или финансово с помощью формы ниже, чтобы была возможность элементарной оплаты хостинга.

Всем удачи и до новых встреч! 🙂

P.S. : если вам нужен сайт либо необходимо внести правки на существующий, но для этого нет времени и желания, могу предложить свои услуги.

Более 5 лет опыта профессиональной разработки сайтов. Работа с PHP