MySQLdump справочник опций и параметров


Содержание

MySQLdump: справочник опций и параметров

Экспорт и импорт данных в MySQL обычно требуется при переносе информации из одной базы данных MySQL в другую и для осуществления резервного копирования.
Резервное копирование данных носит чисто технологический характер. Это означает, что в случае какого-либо программного или аппаратного сбоя оборудования, будет возможность восстановить актуальные данные.
Единственный способ быть уверенным в 100% восстановлении данных — самостоятельно выполнять регулярное резервное копирование информации. Для этого необходимо воспользоваться утилитой mysqldump, которая доступна через unix shell.

Пример команд для экспорта и импорта базы данных:

mysqldump -u имя_пользователя -p -h имя_сервера_БД имя_базы > dump.sql

Данные будут сохранены в файле dump.sql. Далее (в случае необходимости) восстанавливаем данные из резервной копии (дампа) так:

mysql -u имя_пользователя -p -h имя_сервера_БД имя_базы Запускаемые указанным образом утилиты для работы с MySQL будут запрашивать пароль к базе данных. Нужно вводить пароль соответствующего пользователя БД.

По умолчанию система считает, что данные в базе хранятся в кодировке Windows-1251. Если ваши данные хранятся в другой кодировке, то после создания резервной копии (дампа) для последующего корректного восстановления данных необходимо открыть созданный файл текстовым редактором и исправить в нём строчку:

/*!40101 SET NAMES cp1251 */;

Например, для кодировки UTF8 нужно указать:

/*!40101 SET NAMES utf8 */

Дополнительные опции утилиты mysqldump:

—add-drop-table — опция, которая добавляет команду DROP TABLE перед созданием таблиц. Перед восстановлением таблиц из дампа, таблицы с таким же именем в рабочей базе данных будут удалены и пересозданы из резервной копии. Рекомендуется использовать для предотвращения возможных ошибок после восстановления;
—add-locks — опция, которая добавляет команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы. Применяется для ускорения доступа к MySQL;
—quote-names — опция, сообщающая утилите о необходимости ставить кавычки для названий таблиц и столбцов. Рекомендуется к использованию для MySQL версий, младше 4.1.1. В старших версиях она активирована по умолчанию.
Опции —quick и —opt рекомендуется использовать, если база данных MySQL слишком большая для того, чтобы целиком поместиться в памяти.
При этом утилита mysqldump выдает ошибку:

mysqldump: Out of memory (Needed XXXXX bytes)
mysqldump: Got error: 2008: MySQL client
run out of memory when retrieving data from server

В итоге строчка для создания копии базы данных получается следующей:

mysqldump —opt -u имя_пользователя -p -h имя_сервера_БД —add-drop-table имя_базы > dump.sql
mysqldump —quick -u имя_пользователя -p -h имя_сервера_БД —add-drop-table имя_базы > dump.sql

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

—set-variable max_allowed_packet=2M
-O max_allowed_packet=2M

Если во время импорта вы получите ошибку вида

mysqldump: Error 2020:Got packet bigger than ‘max_allowed_packet’bytes when dumping table `some_table_name ` at row: 2

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

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

—compatible=mysql40

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

Дампы баз данных MySql – mysqldump

Общее описание программы mysqldump

Для работы с дампами баз данных MySql существует клиентская программа mysqldump, изначально написанная Игорем Романенко (Igor Romanenko). Mysqldump входит в состав дистрибутивов всех клонов MySql. Полная документация – http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html, а здесь только краткая шпаргалка по использованию основных функций.

Программа mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL сервер (не обязательно MySQL сервер).

Существует три основных способов использования mysqldump: создать набор из одной или более таблиц, набор из одного или более полных баз данных, или весь сервер MySQL, как показано здесь:

Например, сделать дамп базы DATABASE:

  • -u или -–user=… – имя пользователя
  • -h или –host=… – удаленный хост (для локального хоста можно опустить этот параметр)
  • -p или –password – запросить пароль
  • database – имя базы данных
  • damp.sql – файл для дампа

Залить базу из файла дампа:

Основные опции программы mysqldump:

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

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

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

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

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

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

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

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

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

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

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

Добавляет ключевое слово REPLACE в оператор INSERT.

Параметр направляет дамп в файл file. Имеет смысл только в Windows. В Linux системах лучше перенаправить результат в файл при помощи последовательностей > и >>.

Файл сокета для подсоединения к localhost. Это для любителей использовать нестандартные настройки.

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

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

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

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

Вывести справочную информацию и выйти из программы.

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

Создание только структуры базы без данных

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

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

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

Добавить комментарий Отменить ответ

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

Как проверить параметры конфигурации сервера MySQL

Главное меню » Базы данных » База данных MySQL » Как проверить параметры конфигурации сервера MySQL

В MySQL 8.0.16 была введена новая опция «validate-config» , чтобы помочь пользователям быстро протестировать конфигурацию сервера без необходимости запуска сервера. Если проблем не найдено, сервер завершает работу с нулевым кодом выхода. Сервер завершит работу с ошибкой (ошибка № 1) при первом появлении недопустимой конфигурации. Например, давайте рассмотрим серверную опцию «tx_read_only», которая устарела в MySQL 5.7 и удалила MySQL 8.0:

Параметр validate-config также можно использовать с файлом конфигурации для проверки параметров, указанных в файле конфигурации. Например:

Обратите внимание, что при использовании параметра defaults-file он должен быть первым параметром в командной строке, как показано выше.

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

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

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

Область действия ‘validate-config’ ограничена проверкой параметров, которые могут выполняться без обычного запуска сервера. Следовательно, опции, специфичные для механизмов хранения и плагинов, которые инициализируются во время обычного запуска сервера, не охватываются ‘ validate-config ‘. Информация о ‘ validate-config ‘ также может быть найдена в документации MySQL. Мы надеемся, что эта новая опция облегчит жизнь пользователям MySQL, особенно во время обновлений.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Дополнительные параметры экспорта данных mysqldump_wrokbench не заполнены и поддержка всех опций mysqldump

Это не полная поддержка и не гибкость (WORKBENCH), до сих пор нет решения.

Я хочу создать сценарии дампа каждой таблицы в моей базе данных. workbench 5.2.47 Версия CE, которую я использую. Я проверил Мою базу данных и все таблицы И такие параметры, как резервные хранимые процедуры, события дампа

в расширенных опциях я проверил.

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

CREATE DATABASE, ЕСЛИ НЕ mydatabase /*! 40100 УСТАНОВКА ПО УМОЛЧАНИЮ SET latin1 */; ИСПОЛЬЗОВАТЬ mydatabase ;

. Этот я хочу пропустить или исключить скрипты дампа формы, но нет никаких параметров в workbench, поскольку в команде mysql есть —no-create-db, -n и для удаления mydatabase USE mydatabase удалить как —databases

Все параметры mysqldump недоступны, что мне нужно.

Любое решение для вышеуказанных вопросов или любые пользовательские варианты создания в workbench. Спасибо..

Я только что встретил подобные проблемы и нашел следующий способ добавления опций. Моя среда:

  • Windows 8/64bit
  • MySQL Workbench 6.08 (вы можете настроить шаг в соответствии с вашей системой, такой как Linux)

Сначала закройте MySQL Workbench

В папке: C:\Program Files (x86)\MySQL\MySQL Workbench CE 6.0.8\modules имеется много файлов python, есть файл, называемый

  1. wb_admin_export_options.py, который является исходным кодом Python, и есть еще один файл с тем же именем
  2. wb_admin_export_options.pyc, который является скомпилированным файлом Python.

Шаг к редактированию

переименуйте 2 в wb_admin_export_options.pyc-org, чтобы избежать программы
прочитайте старый список опций и сделайте копию 1, если есть
проблема

запустите редактор кода в режиме администратора, если в Windows для причины UAC. Откройте файл wb_admin_export_options.py

Примечание. Вы увидите список опций для mysqldump, который появится в Workbench-> Администрирование [Tab] → Дополнительные параметры. [кнопка в правом верхнем углу]

Примечание по редактированию файла

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

«create-options»: [«Включить все параметры таблицы, специфичные для MySQL, в операторах CREATE TABLE.», «TRUE»],

Откройте MySQL Workbench еще раз, и он прочитает обновленный список опций и позволит вам выбрать.

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

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

  • Изменить параметры запуска MySQL сервера
  • Изменить путь к конечному файлу экспорта 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.

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

MySQLdump: справочник опций и параметров

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

Недостатки Mysqldump

  1. Во время создания дампа, mysqldump блокирует таблицы и в этом случае пользователи не смогут работать с БД, что для высоко нагруженных проектов неприемлемо. Однако, в случае таблиц InnoDB это ограничение можно обойти с помощью ключа –single-transaction. Как известно mysqldump может не блокировать таблицы при создании дампа, но тогда во время выполнения дампа, которое занимает продолжительное время, многие таблицы могут быть изменены работающими пользователями, что приведёт к логической не целостности данных.
  2. Резервная копия представляет из себя текстовый файл, содержащий SQL инструкции для создания необходимых таблиц и заполнения их информацией (возможно можно делать дампы, содержащие только структуру таблиц). При значительном объеме БД (например 100Гб), дамп может создаваться несколько суток!, для восстановления потребуется столько же времени!
  3. Если MySQL сервер находится под высокой нагрузкой, то запуск mysqldump при достаточно большом объёме данных, вызовет дополнительную нагрузку на сервер, что теоретически может привести к перенагрузке сервера и его падению.

В свете вышеизложенных недостатков рекомендую использовать Open Source утилиту Percona XtraBackup. Инструкция по использованию Percona XtraBackup. В любом случае для резервирования БД ответственных высоконагруженных проектов необходимо использовать зеркалирование MySQL сервера на другом физическом сервере и снимать резервные копии уже с зеркала, так как даже при использовании Percona XtraBackup, кратковременное прерывание в обслуживание таблиц БД все-таки происходит.

mysqldump очень удобно использовать если вам нужно просто перенести базу данных с одного сервера на другой и для этого вам нужен просто дамп базы данных в plain text.

Если вы все еще хотите использовать mysqldump, приступим к инструкции.

Создание дампа базы данных MySQL

Дамп одной базы данных со структурой и данными

  • в данном примере мы подключаемся к MySQL серверу через socket, если нужно подключиться по IP, то нужно задать IP хоста, например -h 127.0.0.1.

Получаемый дамп можно сразу сжать:

Дамп всех баз данных на сервере со структурой и данными

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

Скрипт, для удобства:

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

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

–help, -? Вывод справки по опциям и используемым переменным. –add-drop-database Добавляет команду, DROP DATABASE перед каждой командой CREATE DATABASE –add-drop-table Добавляет команду, DROP TABLE перед каждой командой CREATE TABLE –add-locks Обрамляет дамп командами LOCK TABLES и UNLOCK TABLES, для ускорения операций вставки. –all-databases, -A Создает полную резервную копию всех баз данных, текущего сервера. –allow-keywords Разрешить имена столбцов, совпадающие с зарезервированными ключевыми словами. К имени такого столбца, добавляется префикс таблицы. –character-sets-dir= путь Директории с установленными наборами символов кодировок –comments, -i Добавить в файл дампа, дополнительную информацию ( например, версию программы, версию MySQL, имя хоста ), отменяется опцией –skip-comments –compact Создает более компактный дамп. Использование данного параметра, автоматически включает опции: –skip-add-drop-table, –skip-add-locks, –skip-comments, –skip-disable-keys и –skip-set-charset. –compatible= имя Данная опция, пытается повысить совместимость создаваемого дампа, с базой данных другого типа или с более старой версией MySQL. Возможные значения: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options или no_field_options. Можно использовать несколько значений, разделенных запятыми. –complete-insert, -c Использовать полную форму оператора INSERT, включая имена столбцов. –compress, -C Использовать компрессию, при пересылке данных между клиентом и сервером, при условии, что они оба поддерживают компрессию. –create-options Включать все MySQL опции при использовании оператора CREATE TABLE. –databases, -B Делать дамп нескольких баз данных, перечисленных после данной опции. Без этого параметра, mysqldump, воспринимает в качестве имени базы данных, первое значение, остальные интерпретируются как имена таблиц. –default-character-set= кодировка Донная опция устанавливает кодировку по-умолчанию. Если не определена, используется utf8, в ранних версиях latin1. –delayed-insert Вместо оператора INSERT, использовать INSERT DELAYED. –delete-master-logs Удалять бинарный лог на основном сервере репликаций после создания дампа. При использовании этой опции, автоматически включается опция –master-data. –disable-keys, -K Оператор INSERT для каждой таблицы, обрамляется выражением /*!40000 ALTER TABLE tbl_name DISABLE KEYS */ и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */. Данная опция ускоряет загрузку при восстановлении из дампа, для таблиц типа MyISAM, за счет того, что индексы создаются после вставки всех данных. –dump-date Если включена опция –comments, добавлять дату создания дампа. –extended-insert, -e Использовать многострочный синтаксис оператора INSERT. Уменьшает размер дампа и ускоряет последующую вставку данных. –flush-logs, -F Переоткрыть лог файлы, перед созданием резервной копии. Старый файл будет сохранен с суффиксом -old. При использовании с опцией –all-databases ( сокращенный вариант -A), будут переоткрыты лог-файлы каждой базы данных, для которой делается дамп. –flush-privileges Выполнять команду FLUSH PRIVILEGES после создания дампа базы данных. –force, -f Продолжать создание резервной копии даже в случае возникновения ошибки. –host= имя_хоста, -h имя_хоста Указывает хост MySQL сервера. По-умолчанию резервная копия делается для хоста localhost –hex-blob Представлять бинарные данные полей BINARY, VARBINARY, BLOB и BIT в шестнадцатиричном формате ( hex ). –ignore-table= имя_базы.имя_таблицы Не скидывать в дамп таблицу “имя_таблицы” из базы “имя_базы“. Опцию нужно использовать повторно, для каждой игнорируемой таблицы. –insert-ignore Дописывать в оператор INSERT, опцию IGNORE.

–lock-all-tables, -x Блокировка всех таблиц, во всех базах, на время создания резервной копии. Данная опция автоматически отключает –single-transaction и –lock-tables.

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

Утилита практически всегда имеется и устанавливать ее дополнительно не нужно. С mysqldump пример создания дампа сводится к одной команде.

mysqldump -u USERNAME -pPASSWORD tablename > tablename.sql

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

Восстановление из дампа выполняется так (предварительно нужно создать таблицу tablename):

mysql -u USERNAME -pPASSWORD tablename all.sql

mysqldump исключить таблицу

С дополнительным ключем можно исключить одну из таблиц базы —ignore-table=tablename.one-table, это может потребоваться когда дампится одна БД.

mysqldump -u USERNAME -pPASSWORD tablename —ignore-table=tablename.one-table > tablename.sql

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

Утилита mysqldump и шпаргалка по параметрам

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

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

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

Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

  • -u или -–user=. — имя пользователя
  • -h или —host=. — удаленный хост (для локального хоста можно опустить этот параметр)
  • -p или —password — запросить пароль
  • database — имя базы данных
  • database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

Развертывание дампа

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

Ну, а если у нас gz-архив к примеру, то:

Пример использование некоторых параметров

Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

Или нам нужна только структура, без данных:

Делаем дамп только триггеров, процедур и событий:

Шпаргалка по параметрам

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

mysqlhotcopy для MyISAM

Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

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

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

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

Делаем резервную копию (он же дамп, он же бекап)

Все данные одной базы

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

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

Добавляем параметр —databases или -B

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

Данные всех баз

Указываем ключ —all-databases или -A .

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

Дамп только структуры базы данных MySQL

За это это отвечает параметр —no-data или -d .

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

Сразу архивируем дамп MySQL

Добавляем дату и время к дампу

Восстанавливаем дамп MySQL

Заливаем все данные

Заливаем данные в конкретную базу

Заливаем данные из заархивированного дампа

Распространенные ошибки, возникающие при создании дампа MySQL

Ошибка связана с тем, что не хватает времени для выполнения операции. Для исправления в конфигурационном файле /etc/my.cnf в секции [mysqldump] увеличьте значение параметров wait_timeout=600 и interactive_timeout=600 . Значение времени подберите под ваши условия.

Ошибка связана с тем, что объем вносимых данных превышает разрешенный. Решается 2-я способами:

  1. добавить параметр —max_allowed_packet=128M к нашим командам
  2. Добавить параметр max_allowed_packet=128M в секцию [mysqldump] конфигурационного файла /etc/my.cnf

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

Данная ошибка может быть связана, как с недостаточным значение параметра wait_timeout , так и с маленьким размером max_allowed_packet . Решения собственно уже приведены.

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

Не смотря на то, что Microsoft делает все возможное, что бы Skype стал как можно…

Оценить размер папки в Linux (занимаемого места) можно с помощью стандартной консольной команды du. Давайте…

90% книг написанных про Linux начинают знакомство с командной строкой с cat. По статистике (на…

Утилита mysqldump. Бекап базы mysql через консоль.

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

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

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

Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:

  • -u или -–user=… — имя пользователя
  • -h или —host=… — удаленный хост (для локального хоста можно опустить этот параметр)
  • -p или —password — запросить пароль
  • database — имя базы данных
  • database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

Развертывание дампа

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

Пример использование некоторых параметров

Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:

Или нам нужна только структура, без данных:

Шпаргалка по параметрам

Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.

—add-drop-database Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE. —add-drop-table Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE. —add-locks Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). —all-databases, -A Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера. —allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. —comments, -i Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL. —compact Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments. —compatible=name Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми. —complete-insert, -c Используется полная форма оператора INSERT (с именами столбцов). —create-options Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры. —databases, -B Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп. —delayed Использовать команду INSERT DELAYED при вставке строк. —delete-master-logs На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data». —disable-keys, -K Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных. —extended-insert, -e Использовать команду INSERT с новым многострочным синтаксисом (повышает
компактность и быстродействие операторов ввода). —flush-logs, -F Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. —force, -f Продолжать даже если в процессе создания дампа произошла ошибка. —hex-blob Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263. —ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров. —insert-ignore Добавляет ключевое слово IGNORE в оператор INSERT. —lock-all-tables, -x Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных. —lock-tables, -l Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп. —no-autocommit Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных. —no-create-db, -n Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases. —no-data, -d Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных. —opt Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset. Начиная с MySQL 4.1, параметр —opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров —skip-opt —order-by-primary Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу. —port, -P Номер TCP порта, используемого для подключения к хосту. —protocol= Параметр позволяет задать протокол подключения к серверу. —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/to/file, -T /path/to/file При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом тбуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx. —tables Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц. —triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers. —tz-utc при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах. —verbose, -v Расширенный режим вывода. Вывод более детальной информации о работе программы. —version, -V Вывести информацию о версии программы. —where=’where-condition’, -w ‘where-condition’ Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. —xml, -X Представляет дамп базы данных в виде XML. —first-slave, -x Блокирует все таблицы во всех базах данных. —debug=…, -# Отслеживать прохождение программы (для отладки). —help Вывести справочную информацию и выйти из программы.

Еще пару слов о бекапе в MySQL

mysqlhotcopy для MyISAM

Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

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

Илон Маск рекомендует:  Что такое код get_class_vars
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL