Что такое dump базы данных

Дамп базы данных

Что такое дамп базы данных

Web-сайты состоят из файлов и базы данных. Именно в базе данных хранится вся часто изменяемая информация сайта:

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

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

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

Работа с дампом базы данных MySQL

Как индустриальный стандарт MySQL поддерживается у всех провайдеров хостинга. Популярные CMS WordPress, Joomla, Bitrix используют в качестве хранилища информации именно MySQL.

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

Чтобы получить дамп базы данных MySQL на VPS/VDS и выделенном сервере, можно воспользоваться командной строкой сервера или специальным web-интерфейсом.

Использование функций панели управления

На примере провайдера Reg.ru и панели ISPManager разберем, как сделать дамп базы данных.

1. Зайдите в меню “Базы данных”.
2. Нажмите на кнопку “Скачать”, чтобы скачать дамп базы данных на локальный компьютер.

3. Чтобы импортировать дамп базы данных с локального компьютера на сервер, перейдите в раздел “Закачать” и нажмите на кнопку “Выберите файл”. Затем выберите файл с дампом на компьютере и запустите процесс закачки файла на сервер нажатием кнопки “Ok”.

Также имеется пункт меню “Локальный дамп”, который позволяет осуществить процедуру импорта дампа базы данных из предварительного закачанного на сервер файла. Достаточно просто найти и выбрать предварительно загруженный файл с дампом в одном из каталогов сайта:

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

Использование web-интерфейса PHPMyAdmin

Практически у всех провайдеров виртуального хостинга в панель управления хостингом включена утилита PHPMyAdmin. Она используется в качестве удобного web-интерфейса для работы с базой данных MySQL. Например, у провайдера Beget ссылка на запуск phpMyAdmin располагается в разделе управления базами данных:

В числе прочих функций в PHPMyAdmin встроен механизм получения дампа базы данных. Для этого следует воспользоваться пунктом меню “Экспорт”:

При просмотре этого файла в блокноте видно, что он состоит из отдельных команд SQL

Обратной операцией по отношению к экспорту дампа базы данных является восстановление данных из этого дампа. Для этого в PHPMyAdmin нужно воспользоваться меню “Импорт”:

Кнопкой “Choose file” выбирается нужный файл на локальном компьютере, после нажатия кнопки “Вперед” происходит процедура импорта.

Таким образом, web-интерфейс работы с базой данных PHPMyAdmin можно применять как для создания дампа базы данных, так и для его импорта. PHPMyAdmin возможно установить и на свой виртуальный или выделенный сервер вместе с другим программным обеспечением. Кроме того, такие популярные панели управления хостингом, как VestaCP или ISPManager, также предоставляют этот web-интерфейс.

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

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

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

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

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

Синтаксис команды для создания дампа:

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

Пример: после запуска процедуры создания дампа командой mysqldump, с помощью команды “ls” проверили, что в папке сайта появился файл dump.sql с дампом базы данных.

Для импорта дампа базы данных нужно будет воспользоваться командой “mysql” с синтаксисом:

Пример: после использования команды pg_dump дамп базы данных сохранен в файл dump.sql

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

Источник

Что такое dump базы данных

Reg.ru: домены и хостинг

Крупнейший регистратор и хостинг-провайдер в России.

Более 2 миллионов доменных имен на обслуживании.

Продвижение, почта для домена, решения для бизнеса.

Более 700 тыс. клиентов по всему миру уже сделали свой выбор.

Бесплатный Курс «Практика HTML5 и CSS3»

Освойте бесплатно пошаговый видеокурс

по основам адаптивной верстки

на HTML5 и CSS3 с полного нуля.

Фреймворк Bootstrap: быстрая адаптивная вёрстка

Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.

Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.

Верстайте на заказ и получайте деньги.

Что нужно знать для создания PHP-сайтов?

Ответ здесь. Только самое важное и полезное для начинающего веб-разработчика.

Узнайте, как создавать качественные сайты на PHP всего за 2 часа и 27 минут!

Создайте свой сайт за 3 часа и 30 минут.

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

Вам останется лишь наполнить его нужной информацией и изменить дизайн (по желанию).

Изучите основы HTML и CSS менее чем за 4 часа.

После просмотра данного видеокурса Вы перестанете с ужасом смотреть на HTML-код и будете понимать, как он работает.

Вы сможете создать свои первые HTML-страницы и придать им нужный вид с помощью CSS.

Бесплатный курс «Сайт на WordPress»

Хотите освоить CMS WordPress?

Получите уроки по дизайну и верстке сайта на WordPress.

Научитесь работать с темами и нарезать макет.

Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!

Хотите изучить JavaScript, но не знаете, как подступиться?

После прохождения видеокурса Вы освоите базовые моменты работы с JavaScript.

Развеются мифы о сложности работы с этим языком, и Вы будете готовы изучать JavaScript на более серьезном уровне.

*Наведите курсор мыши для приостановки прокрутки.

Как сделать дамп базы данных?

Размер: 14,0 Мб.

Длительность: 07 мин. 21 сек.

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

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

Итак, что такое дамп базы данных?

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

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

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

В видео ниже мы рассмотрим наиболее важные моменты, касающиеся работы с дампом базы данных.

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

Под видео вы найдете сокращенную текстовую версию для этого видеоурока.

Краткий обзор урока (все подробности смотрите в видео):

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

В случае, если вы работаете с набором «Денвер», вы можете получить к ней доступ, набрав в адресной строке браузера путь:

Теперь вам нужно выбрать ту базу, дамп которой вы хотите создать, например menu. Кликаем по имени базы и оказываемся на странице с ее структурой.

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

Следующим шагом ставим галочку в поле Save as file (Сохранить как файл) и нажимаем кнопку Go в правом нижнем углу экрана.

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

Все! На этом ваш дамп базы готов. Теперь давайте научимся его использовать.

Если вы откроете этот файл любым текстовым редактором (например, программой Notepad++), то увидите картину, близкую к этой:

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

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

После создания базы (например, с именем, menu2), заходим в нее и выбираем вверху опцию import.

Далее кликаем по кнопке Обзор и выбираем дамп, который мы хотим импортировать:

Когда путь указан, нажимаем кнопку Go в правом нижнем углу.

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

Таким образом можно быстро дублировать содержимое баз данных.

Кроме того, есть еще один вариант импорта дампа базы. Давайте рассмотрим и его.

Пусть у нас есть еще одна пустая база данных (например, menu3). Заходим в нее и выбираем теперь сверху пункт SQL:

Теперь в поле, которое вы видите, нужно вставить все содержимое файла дампа базы. Просто скопируйте все его содержимое и вставьте в поле для выполнения SQL-запроса, после чего нажмите кнопку Go в правом нижнем углу:

Вот и все. Мы добились точно такого же эффекта, как и в предыдущем случае. Теперь база данных menu3 имеет точно такое же содержимое и структуру, как исходная база menu. В итоге все три базы (menu, menu2 и menu3) у нас абсолютно одинаковы и отличаются только названиями.

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

Удачи вам и успехов!

P.S. Присмотритесь к премиум-урокам по различным аспектам сайтостроения, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля. Все это поможет вам быстрее и проще освоить JavaScript, PHP и ряд других технологий веб-разработки:

Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!

Источник

Как сделать дамп базы MySQL / MariaDB

В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.

Синтаксис и базовая команда

Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:

Пример базовой команды для резервирования базы:

* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.

Базовые параметры команды mysqldump:

* полный перечень параметров смотрите в официальном руководстве.

Примеры создания дампа MySQL

1. С последующим архивированием

Или с подробным выводом информации на экран (дольше по времени):

2. Для одновременно нескольких баз

3. Для всех баз одной командой

4. Резервирование только структуры базы

5. Создание копии определенной таблицы

Для этого после базы через пробел перечисляем названия таблиц

6. Резервирование прав доступа на СУБД

Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.

* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;

7. Проигнорировать определенную таблицу

Выполняется при помощи ключа ignore-table:

Скрипт для резервного копирования

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

Резервное копирование в phpMyAdmin

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

В верхней части меню кликаем по Экспорт:

В разделе «Способ экспорта» ставим переключатель в положение Обычный:

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

Выбираем компрессию, например, zip:

И в нижней части окна нажимаем OK.

Начнется загрузка файла с резервной копией на компьютер.

Возможные проблемы

Incorrect key file for table

Ошибка появляется во время выполнения резервного копирования. Более полный текст:

mysqldump: Error 1034: Incorrect key file for table ‘

‘; try to repair it when dumping table `

` at row: xxxxxx

Причина: причин может быть несколько:

Решение: в зависимости от причины, решений будет несколько.

1. Проще всего сначала проверить место на диске. В конфигурационном файле СУБД (как правило, /etc/my.cnf) можно найти опцию tmpdir — она указывает на каталог, который используется под создание временных таблиц. Если опции нет, то используется путь /tmp.

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

2. Если наша таблица повреждена, то пробуем ее востановить. Данный процесс зависит от типа таблицы, с которой возникла проблема.

В командной оболочке SQL вводим:

> REPAIR TABLE

USE_FRM;

После повторяем попытку создать резервную копию.

б) Если таблица типа INNODB.

Открываем конфигурационный файл СУБД:

В радел [mysqld] добавим опцию:

[mysqld]
.
innodb_force_recovery = 1

Перезапускаем сервер баз данных, например:

systemctl restart mysql

Пробуем сделать резервную копию. Если получим такую же ошибку, меняем значение innodb_force_recovery с 1 на 2:

[mysqld]
.
innodb_force_recovery = 2

И так по кругу, до значения 6, пока не получим положительный результат.

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

Читайте также

Для восстановления базы читайте инструкцию Как восстановить базу MySQL

Источник

Как создать дамп базы данных

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

Сервер с панелью управления ISPmanager

ISPmanager даёт возможность работать с сервером баз данных через графический интерфейс — создавать, удалять базы, настраивать пользователей и привилегии. Сюда же входит возможность скачивания и разворачивания дампов — через саму панель ISPmanager или устанавливаемый в наборе с ней phpMyAdmin.

ISPmanager

phpMyAdmin

Вы будете автоматически авторизованы в phpMyAdmin под пользователем, которому принадлежит выбранная база данных. Чтобы скачать дамп, перейдите в раздел «Экспорт» :

Здесь можно выбрать метод экспорта: быстрый — с минимумом настроек, или обычный — полностью настраиваемый экспорт, и нажать «Вперёд» — скачается дамп вашей базы данных.

Сервер без панели управления

Adminer

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

Чтобы начать работать с ним, достаточно скачать его с официального сайта на сервер и открыть в браузере:

Скачиваем скрипт на сервер в корневую папку нашего сайта:

Ссылку на актуальную версию скрипта можно найти на официальном сайте.

Настраиваем права доступа на скачанный скрипт:

Открываем загруженный скрипт в браузере по адресу:

После таких нехитрых манипуляций откроется интерфейс для управления базами данных. Для авторизации вам потребуются:

Чтобы создать дамп базы, после подключения в меню слева выберите «Экспорт» :

Выберите, какие данные хотите включить в дамп.

Чтобы скачать дамп, в строке «Выходные данные» выберите пункт «Сохранить» и нажмите «Экспорт»: дамп будет загружен на ваш компьютер.

Ручной дамп базы

Создать дамп базы вручную можно одной командой:

где db-username — имя пользователя базы данных, db-name — название базы данных, dump.sql — имя файла, в который будет записан дамп. Система запросит пароль указанного пользователя базы и запишет дамп в текущую рабочую директорию на сервере.

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

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

Синтаксис команды прост:

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее речь как раз пойдёт о корректном выводе данных MySQL в xls и csv форматы с помощью данной команды. А с musqldump в рамках данной статьи мы прощаемся.

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

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

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

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

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

На Windows:

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

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

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

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

Открылся данный файл в 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 файл данным способом, нам нужна следующая команда:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Более 5 лет опыта профессиональной разработки сайтов. Работа с PHP, OpenCart, WordPress, Laravel, Yii, MySQL, PostgreSQL, JavaScript, React, Angular и другими технологиями web-разработки.

Опыт разработки проектов различного уровня: лендинги, корпоративные сайты, Интернет-магазины, CRM, порталы. В том числе поддержка и разработка HighLoad проектов. Присылайте ваши заявки на email cccpblogcom@gmail.com.

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

Источник

• ← Что такое dump switch
• Что такое dump файл →