Mysqldump и проблема переноса хранимок

Откуда: Санкт-Петербург
Сообщений: 54

Добрый день, коллеги. Православных с Рождеством!

Вопрос по утилите mysqldump.
Делаю дамп, базы с хранимками, смотрю созданный дамп, вижу
1. Как избавиться от указания DEFINER=XXXXX
2. Как избавиться от не совсем внятных конструкций типа /*!50003.
3. Как экспортировать не все хранимые процедуры (функции), например с помощью списка исключений.
Может какой-то ключ надо указывать? Сейчас использую
есть подозрение что при переносе на хостинг именно инструкции типа DEFINER и прочие псевдокоментарии вызывают неполный импорт хранимок.
Локальный Mysql 5.0.45 под виндами, на хостинге 5.0.51 под freeBSD

Re: mysqldump и проблема переноса хранимок. Удить инструкции DEFINER [new]

Откуда:
Сообщений: 6907

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

сомневаюсь. для начала заюзай при импортe ключ -force иначе любая ошибка приведет к остановке импорта. посмотри на что будет ругаться. не знаю как на 5.0 а на 5.1 все импортируются даже если указаны не существующие дефайнеры, другое дело что выполняться они не будут. тут можно посоветовать либо одноименных юзеров в обоих базах иметь либо search&replace на дампе

Re: mysqldump и проблема переноса хранимок. Удить инструкции DEFINER [new]

Откуда: Санкт-Петербург
Сообщений: 54

пока использую последний вариант. Но всё равно какая-то засада:
это с ключом -f (--force) разница лишь в том, что сейчас больше ошибок. (тогда была одна)
Одноимённые юзеры не подходят, т.к. указывается их IP. сильно геморно, т.к. надо и на работе и дома с базой трудиться так буду только синхронизировать настройки.

А чем ещё можно залить дамп (сделать), короче как проще сделать точную копию БД?
Сейчас лезу через putty по SSH2
Есть EMS (правда 2005 ибо 2007 глючный сильно)

Re: mysqldump и проблема переноса хранимок. Удить инструкции DEFINER [new]

Откуда: Санкт-Петербург
Сообщений: 54

Заново сделал дамп, попытался залить на свой же денвер (во вновь созданную БД), получил ошибку.
Пипец - сам сделал и сам же не проглатывает.
Чётко видно, что матерится на CALL A(..) в тексте процедуры B. Смотрю, дамп, понимаю, что сама процедура (A) на которую ссылка, стоит ниже. Перенёс выше объявленной. Это ситуацию не разрулило - никак!

Задрали уже эти фишки с mysql. Когда была версия 3 было хорошо, сейчас надо с бубном плясать, то таблицы InnoDB почему-то станут MyISAM читай все внешние ключи слетели (оказывается mysql прочитал из корня диска С my.cnf от старой версии), потом выяснится, что при переносе на другой комп этого денвера InnoDB таблицы бьются из-за того что имена компов разные, а там видимо логи сохраняются под именами компов. В пятой версии появились процедуры и функции - вот здорово, только можно о них забыть если не сделать дамп с ключом -R. Я уже молчу про фишки с кодировкой, при переходе от 4.0. к 4.1 версии. Вроде делается всё для удобства, но с гигантскими оговорками, где-то работает, где-то кракозябры или переносятся только данные. а логика, а правильная кодировка? Нахер тогда их вводить, если должную поддержку не организовать. Теперь вот, мля. дамп не читается сервером, который его создал! Просто атас!

Re: mysqldump и проблема переноса хранимок. Удить инструкции DEFINER [new]

Откуда: Санкт-Петербург
Сообщений: 54

Да, действительно у меня нет мастер доступа на хостинговый mysql, но я в его phpMyAdmin'e сделал SELECT CURRENT_USER() получил значение типа `user`@`host` и сделал по своему дампу поиск с заменой `root`@`localhost` на то что получил в результате того селекта. и уже полученный дамп пытался залить. Результат - известен.

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

Пробовал и зависимые ставить в хвост дампа.

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

Я считаю, что должно быть какое-то средство передать работающую базу на другой сервак. Очень был удивлён, когда штатные средства (не какие-то сторонние утилиты) не понимают друг друга даже в рамках одной версии.

На мой взгляд СУБД должна вести себя более предсказуемо. Надо переходить на постгрес. Там, как говорят, такие вопросы решили уже много лет назад, да и будущее его врят-ли от оракла будет зависеть (что бы последние там не декларировали про mysql).

Участникам chpasha и RXL спасибо.

Mysqldump инструкция

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

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

По-умолчанию в Ubuntu MySQL устанавливается с кодировкой latin1. В этом можно убедиться посмотрев вывод запроса:

В связи с этим, даже используя при работе с сервером команду

и используя при создании таблиц

невозможно добиться полной поддержки кодировки utf8:

Кодировка по-умолчанию все равно останется latin1, что неудобно и может привести к ошибкам.

Чтобы сервер сразу загружался с нужной кодировкой, необходимо отредактировать файл /etc/mysql/my.cnf.

В секцию [mysqld] добавьте следующие строки:

Так же желательно установить кодировку для клиента и mysqldump. Для этого в секциях [client] и [mysqldump] необходимо добавить строчку:

Перезагрузите сервер MySQL:

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

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

Mysqldump Резервное копирование MySQL - Базы данных - Каталог статей - Системный администратор г

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

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 серверу через socket, если нужно подключиться по IP, то нужно задать IP хоста, например -h 127.0.0.1.

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

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

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

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

Параметры запуска Mysqldump

–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 .

–lock-tables, -l Блокировка таблиц базы данных, на время создания резервной копии. При дампе всех баз данных с этой опцией, таблицы каждой базы блокируются отдельно. Для транзакционных таблиц, типа InnoDB и BDB. предпочтительней использовать опцию –single-transaction. –log-error= имя_файла Писать ошибки и предупреждения в файл “имя_файла “. –no-autocommit Включает операторы INSERT для каждой таблицы, в операторы SET AUTOCOMMIT и COMMIT. для увеличения скорости выполнения большого количества запросов INSERT –no-create-db, -n Данная опция подавляет вывод в дамп оператора CREATE DATABASE. при использовании опций –databases и –all-databases. –no-create-info, -t Не писать оператор CREATE TABLE. для пересоздания каждой таблицы из резервной копии. –no-data, -d Не скидывать в дамп содержимое таблиц. Оставляет только операторы CREATE TABLE для создания структуры. –opt Групповая опция. Синоним включения опций, –add-drop-table. –add-locks. –create-options. –disable-keys. –extended-insert. –lock-tables. –quick. –set-charset. Ускоряет общий процесс создания резервной копии, включена по-умолчанию. Отключается опцией –skip-opt. –order-by-primary Сортировать ряды таблиц по первичному ключу или по первому уникальному индексу, если индекс существует. Полезна в случае создания дампа таблиц MyISAM с последующей вставкой в таблицы типа InnoDB. –password[=password], -p[password] Пароль пользователя, для подключения к серверу. Не забывайте, что имя должно идти сразу за опцией, без разделяющего пробела. Если указаны только сама опция, без пароля, пароль будет запрошен из командной строки. –port= номкр_порта, -P номкр_порта Номер порта, для подключения к серверу по протоколу TCP/IP. –protocol= Использовать для подключения к серверу MySQL, указанный протокол. –quick, -q Данная опция вынуждает Mysqldump. восстанавливать строки, по одной за раз, вместо того что-бы скидывать весь объем строк в буфер памяти и выписывать их оттуда. Очень полезна при создании резервных копий больших таблиц. –quote-names, -Q Обрамлять имена баз данных, таблиц и колонок, ковычками. Включена по-умолчанию. –replace Использовать оператор REPLACE вместо INSERT. Начиная с версии MySQL 5.1.3. –result-file= имя_файла, -r имя_файла Вывод результатов в указанный файл. Имейте в виду, если файл с таким именем уже существует, он будет перезаписан и в случае возникновения ошибки, данные могут быть потеряны. –routines, -R Записывать в дамп хранимые процедуры и функции. Для использования данной опции, необходимы права SELECT на таблицу proc. системной базы данных mysql. Дамп будет содержать операторы CREATE PROCEDURE и CREATE FUNCTION. При использовании этой опции, эти операторы не будут содержать атрибутов времени создания и модификации хранимых процедур и функций и после восстановления дампа они будут равны времени восстановления. Если вам необходимо сохранить исходные атрибуты времени, вместо использования данной опции, сделайте отдельный дамп таблицы mysql.proc. под именем пользователя, который имеет на это достаточные права. Опция появилась с версии MySQL 5.1.2. –set-charset Добавляет в дамп оператор SET NAMES со значением кодировки по-умолчанию. По-умолчанию, данная опция включена, что-бы подавить, используйте –skip-set-charset. –single-transaction Выполняет оператор BEGIN SQL. перед началом создания резервной копии. Опция используется только для транзакционных таблиц, типа InnoDB. Только этот тип таблиц может быть сохранен в дамп в актуальном состоянии, после выполнения BEGIN SQL. и без блокирования приложения. Например таблицы типа MyISAM или MEMORY. могут измениться в процессе создания резервной копии с использованием данной опции, в итоге, информация в дампе будет противоречивой, неактуальной. Опции –single-transaction и –lock-tables. являются взаимоисключающими. –socket= путь_к_файлу_сокета, -S путь_к_файлу_сокета Использовать файл unix-сокета, для подключения к localhost. –tables Имена идущие за этой опцией, считаются именами таблиц. –triggers Включать в резервную копию триггеры, для каждой таблицы. Отменить действие опции можно с помощью –skip-triggers. –user= имя_пользователя, -u имя_пользователя Имя пользователя для подключения к MySQL серверу. –verbose, -v Вывод служебной информации о ходе выполнения программы. –where=’where_условие’, -w ‘where_условие’ Скидывать в дамп информацию, выбранную по условию WHERE. –xml, -X Создать дамп в формате XML

Backup баз данных при помощи Mysqldump

Создан: 20 октября 2012

Создаем backup баз данных при помощи Mysqldump

Команда для создания backup базы данных, с помощью Mysqldump, выглядит следующим образом:

-u [имя пользователя] - пользователь с правами, хотябы для чтения.
-p[пароль] - пароль пользователя, указываем без пробела перед ключем, иначе запросит пароль
--databases [имя базы данных] - краткий вариант -B, указываем имя одной или нескольких баз данных для выгрузки в дамп. При создании backupа одной базы данных, опцию --databases, можно и не писать.
> - перенаправление вывода (stdout)

Создадим backup одной базы данных mysql:

После -p нет пробела, сразу указываем пароль. Таким образом созданный файл резервной копии, Будет содержть структуру и данные в /backup/info-linux.ru.sql. Если данные из этой базы не нужны, необходимо только сохранить структуру таблиц, то написать команду можно сдедующим образом

Ключ --no-data ( краткий вариант -d ), таким образом ключ указывает mysqldump, в дамп не скидывать данные.

Можно создавать дампы только необходимых таблиц, конкретной базы данных:

Вышеприведенная команда, создаст backup таблиц article и users базы данных info-linux.ru и сохраняет в файл /backup/info-linux.ru_tables.sql.

Если нужен дамп без данных, а только со структурой таблиц, то используем снова ключ --no-data.
#mysqldump -u adminDB -padminDBpass --no-data -B info-linux.ru --tables access users > ./backup/info-linux.ru_tables_nodata.sql
Если необходимо создать backup нескольких баз данных, то просто перечисляем из название после ключа --databases ( -B ):

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

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

Восстановим данные из нашего backup

Ничего сложного в восстановлении базы данных нет, для этого воспользуемся стандартной программой-клиент mysql, перенаправим (stdin) в нее файл с нашей резервной копии. Делается это так:

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

В приведенном выше примере, помещаем в базу test таблицу test_table.
При помощи программы mysqldump, очень быстро копировать таблицы с данными или только структуры таблиц, из одной базы данных в другую. Небольшой пример:

Сбрасываем в дамп структуру таблицы test_table из базы данных test1.

Восстанавливаем структуру таблицы, из только созданной, резервной копии и разворачиваем ее в базу данных other_testdb.

Параметры программы Mysqldump

--help, -? Выводит справку по опциям.
--add-drop-database Перед каждой командой CREATE DATABASE добавляет команду, DROP DATABASE
--add-drop-table Перед каждой командой CREATE TABLE добавляет команду, DROP 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 при использовании оператора CREATE TABLE включать все MySQL опции
--databases, -B Позволяет делать дамп нескольких баз данных, перечисленных после опции. В отсутствии этого параметра, mysqldump, воспринимает первое значение в качестве имени базы данных, а остальные как имена таблиц.
--default-character-set= кодировк а. Опция устанавливающая кодировку по-умолчанию.
--delayed-insert Использует INSERT DELAYED, вместо оператора INSERT.
--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 сервера. По-умолчанию backup делается для localhost
--hex-blob Представляет бинарные данные полей в шестнадцатиричном формате ( hex ) BINARY, VARBINARY, BLOB и BIT.
--ignore-table= имя_базы.имя_таблицы Не скидывать в дамп "имя_таблицы" из базы "имя_базы". Для каждой игнорируемой таблицы, опцию нужно использовать повторно,
--insert-ignore В оператор INSERT дописывать опцию IGNORE. --lock-all-tables, -x. Во время создания резервной копии, блокировка всех таблиц, Опция отключает автоматически --single-transaction и --lock-tables.
--lock-tables, -l Блокирует таблицы базы данных, на момент создания резервной копии. Таблицы каждой базы блокируются отдельно, при дампе всех баз данных с этим ключом. Для так называемых транзакционных таблиц, типа InnoDB и BDB, лучше использовать с ключом --single-transaction.
--log-error= имя_файла В файл "имя_файла" пишет ошибки и предупреждения.
--no-autocommit Для увеличения скорости выполнения большого количества запросов INSERT, Включает операторы INSERT для каждой таблицы, в операторы SET AUTOCOMMIT и COMMIT.
--no-create-db, -n При использовании опций --databases и --all-databases, данная опция подавляет вывод в дамп оператора CREATE DATABASE,
--no-create-info, -t Для пересоздания каждой таблицы из резервной копии, не писать CREATE TABLE,
--no-data, -d Оставлять только операторы CREATE TABLE для создания структуры. Не скидывать в дамп содержимое таблиц.

--opt Групповая опция. Синоним включения опций, --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset. Включена по-умолчанию, ускоряет процесс создания резервной копии. Отключается опцией --skip-opt.
--order-by-primary Сортирует ряды таблиц по первому уникальному индексу, если индекс существует или по первичному ключу. Полезна если создается дамп таблиц MyISAM с дальнейшей вставкой в таблицы InnoDB.
--password[=password], -p[password] Пароль юзера, для подключения к серверу. Надо не забывать, что пароль должен идти сразу за ключом без разделяющего пробела. Если указать только саму опцию без пароля, он будет запрошен.
--port= номкр_порта, -P номкр_порта Номер порта.
--protocol= для подключения к MySQL серверу использовать указанный протокол.
--quick, -q Вместо того что-бы скидывать весь объем строк в буфер памяти, ключ принуждает Mysqldump восстанавливать строки по одной за раз. Полезна когда создаютя резервные копий при больших таблицах.
--quote-names, -Q Обрамляет имена ковычками баз данных, таблиц и колонок. Включена по-умолчанию.
--replace Начинается с версии MySQL 5.1.3, использует оператор REPLACE вместо INSERT.
--result-file= имя_файла, -r имя_файла Вывод результатов в файл. Естественно, если файл с таким именем существует, то он будет перезаписан.
--routines, -R Для использования данной опции, необходимы права SELECT на таблицу proc, системной базы данных mysql. Записывать в дамп хранимые процедуры и функции. Дамп будет содержать операторы CREATE PROCEDURE и CREATE FUNCTION. Эти операторы не будут содержать атрибутов времени создания и модификации хранимых процедур и функций и после восстановления дампа они будут равны времени восстановления, при использовании этой опции. Если нужно сохранить исходные атрибуты времени, вместо использования данной опции, лучше сделать дамп таблицы mysql.proc, под именем пользователя, который имеет на это достаточные права. Появление опции началось с версии MySQL 5.1.2.
--set-charset По-умолчанию, данная опция включена, что-бы подавить, используйте --skip-set-charset. Добавляет в дамп оператор SET NAMES со значением кодировки по-умолчанию.
--single-transaction Выполнять оператор BEGIN SQL, перед началом создания резервной копии. Данная опция используется только для таблиц, типа InnoDB. Только этот тип таблиц может быть сохранен в дамп в актуальном состоянии, после выполнения BEGIN SQL, и без блокировки приложения. Например могут измениться в процессе создания резервной копии таблицы типа MyISAM или MEMORY, с использованием данной опции, в итоге, информация в дампе будет противоречивой и неактуальной. Опции являются взаимоисключающими: --single-transaction и --lock-tables.
--socket= путь_к_файлу_socket, -S путь_к_файлу_socket Для подключения к localhost, использовать файл unix-socket.
--tables Названия стоящие за этой опцией, будут считаться именами таблиц.
--triggers Включает в backup триггеры, для каждой таблицы. Отменить действие можно с помощью --skip-triggers.
--user= имя_пользователя, -u имя_пользователя Имя юзера для подключения к серверу MySQL.
--verbose, -v Выводит информацию в ходе выполнения программы.
--where='where_условие', -w 'where_условие' Сбрасывает в дамп информацию, выбранную условием WHERE.
--xml, -X Создает дамп в формате XML

Рейтинг статьи: 10

MySQLdump: скачать, работа с mysqldump, примеры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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