4.8.5 mysqldump, Получение дампов данных и структуры таблицы

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

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

shell> mysqldump [OPTIONS] database [tables]
или  mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
или  mysqldump [OPTIONS] --all-databases [OPTIONS]

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

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

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

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

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

--add-locks

Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

--add-drop-table

Добавить команду DROP TABLE перед каждой командой CREATE TABLE.

-A, --all-databases

Произвести дамп всех баз данных. Аналогично опции --databases с указанием всех баз данных.

-a, --all

Включить все опции создания объектов, специфичные для MySQL.

--allow-keywords

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

-c, --complete-insert

Использовать полные команды INSERT (с именами столбцов).

-C, --compress

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

-B, --databases

Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных.

--delayed

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

-e, --extended-insert

Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

-#, --debug[=option_string]

Отслеживать прохождение программы (для отладки).

--help

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

--fields-terminated-by=...

--fields-enclosed-by=...

--fields-optionally-enclosed-by=...

--fields-escaped-by=...

--lines-terminated-by=...

Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE. См. раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE.

-F, --flush-logs

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

-f, --force,

Продолжать даже при получении ошибки SQL при выполнении дампа таблицы.

-h, --host=..

Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию - localhost.

-l, --lock-tables.

Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL, чтобы разрешить параллельные записи для MyISAM-таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция --lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях.

-K, --disable-keys

Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных.

-n, --no-create-db

В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций --databases или --all-databases.

-t, --no-create-info

Не записывать информацию о создании таблицы (команда CREATE TABLE).

-d, --no-data

Не записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы!

--opt

То же, что и --quick --add-drop-table --add-locks --extended-insert --lock-tables. Должно дать наиболее быстрый дамп для чтения на MySQL-сервере.

-pyour_pass, --password[=your_pass]

Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль.

-P port_num, --port=port_num

Номер порта TCP/IP, используемого для подключения к хосту (применяется при подсоединении к хостам, отличным от localhost, для которого используются сокеты Unix ).

-q, --quick

Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result().

-Q, --quote-names

Взять в кавычки имена таблиц и столбцов без символов ``'.

-r, --result-file=...

Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки '\n' в последовательность '\n\r' (новая строка + возврат каретки).

--single-transaction

Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB-таблицамии и уровнем изоляции транзакций READ_COMMITTED, так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM- или HEAP-таблицы при использовании данной опции могут все же изменить свое состояние. Опция --single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции --lock-tables, так как команда LOCK TABLES прерывает выполнение предыдущей транзакции.

-S /path/to/socket, --socket=/path/to/socket

Файл сокета для подсоединения к localhost (значение хоста по умолчанию).

--tables

Перекрывает параметр --databases (-B).

-T, --tab=path-to-some-directory

Для каждой заданной таблицы создает файл a `table_name.sql', содержащий SQL CREATE команды для создания таблицы, и файл `table_name.txt' с данными таблицы. Файл `.txt' имеет формат в соответствии с параметрами --fields-xxx и --lines--xxx. Примечание: Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld, причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу.

-u user_name, --user=user_name

Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix.

-O var=option, --set-variable var=option

Установить значения переменных. Доступные для использования переменные перечислены ниже.

-v, --verbose

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

-V, --version

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

-w, --where='where-condition'

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

"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

-X, --xml

Представляет дамп базы данных в виде XML.

-x, --first-slave

Блокирует все таблицы во всех базах данных.

-O net_buffer_length=#, where # < 16M

При создании многострочных операторов ввода (как и в случаях применения параметров --extended-insert или --opt) утилита mysqldump будет создавать строки длиной вплоть до указанной в net_buffer_length. При увеличении значения этой переменной необходимо также убедиться в том, что в MySQL-сервере для переменной max_allowed_packet указано значение больше, чем величина net_buffer_length.

Чаще всего утилита mysqldump используется для получения резервной копии всех баз данных. See section 4.4.1 Резервное копирование баз данных.

mysqldump --opt database > backup-file.sql

Можно, наоборот, прочитать этот файл на MySQL-сервере посредством команды:

mysql database < backup-file.sql

или

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

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

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

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

mysqldump --databases database1 [database2 ...] > my_databases.sql

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

mysqldump --all-databases > all_databases.sql

User Comments

On my system - mysqldump Ver 8.16 Distrib
3.23.46, for pc-linux-gnu (i686)

mysqldump -u root -p -A

only dumps the FIRST database, if the user
specified is not same as the current unix user.

In the above example, you must actually be
logged in as root to really get all the
subsequent databases.

Note that if you include the -F option (Flush
tables) with the -A option (All databases), and
you are using logging, your logs will be cycled
each time mysqldump starts another database. So
if you've got six databases, your logs will be
flushed six times in rapid succession as
mysqldump reads from each one. Not sure if it's a
bug or a feature. (MySQL 3.23.46)

If you'd like a quick example example of how to
dump to a local machine (with the mysqldump
binary locally) from a remote server this is how
i did it (windows machine):

>mysqldump -C -h SERVERADDRESS -u USER -pPASSWORD
DATABASE [table] > c:\localpath.sql

how do i get the mysqldump to write the file with
the owner set to my username, not as 'nobody'

Workaround for the -F -A problem is to use 'mysql
-u root -e "FLUSH LOGS"' before running mysqldump,
but this makes the flush and backup less 'atomic'.
Not a problem on our system, since no one will be
running updates at 2:00am

In older versions, mysqldump dump table structure
and data but nothing about indexes.
Recent versions dump index information too.

Sorry the documentation and examples is very small
here. This is not very good. There is a lot of
errormessages to read until you get a dump. And if
youre in the situation to do some more dedicated
stuff (e.g. dumping only some tables) there is no
more help than the stuff i read if i use the dump on
my shell.
So there is no need for this here ?

Okay, here is my problem..im using phpMyAdmin and
the MySQL server is hosted by Ausgamers. And i
need to dump everything in my database, can
someone please tell me how to do it??

I wanna save it to my space or my HDD

To restore a complete backup of all your
databases, use "mysql
< /path/to/all_databases_backup.sql"

mysqldump is a bit tricky to use, yet not a bad
tool for backups. Esp. the pipe right into
another servers db is a nice thing. I wonder if
it would be possible to add a replace syntax,
instead of only an insert. That would make
incremental backups right into another db much
easier (a new timestamp triggers a backup of an
updated row) . Now one has to dump a csv file on
one server, transfer it to another and then use
load data infile replace into table ...
OK, one could also use the server/slave variant -
problems with that is, I keep hearing it is not
as easy to use and understand as I'd like. ;-)

How can I mysqldump.exe a database with table
create options when there is fields with a %
character? And how to import that with mysql.exe?
Removing % is not a option, and I dont want to
manually add ` characters to the dumped file. Im
using innodb.

How can I use mysqldump to select some data from
Tables..
mysqldump -u root -p DB_Name Table_Name --
where='where Name='SIM'' doesn't work
how can I use quote in where quote...

mysql database < backup-file.sql

what is the syntax of this command when multiple
databases are backed up into one file with
mysqldump --all-databases > bkp.sql
how are he databases retrieved?

If you want to compress your backups directly even
without hitting your HD. You can try this command:
#mysqldump --opt -u user --password="password"
database | bzip2 -c > database.sql.bz2

hi,i use mysqldump --opt DB1 -p >DB1.sql,then i use
the command "mysqldump --delay DB2 -p
<DB1.sql",but i found no data imported at DB2.

any ideas?

I'm using the following script as daily cron, it works.

#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c
> /var/backup/databasebackup-$date.sql.bz2

Problem!!!
mysqldump and MERGE tables

no need to dump data
need to generate "CREATE TABLE ...." at end of sql script, after all merged tables

also don't work BACKUP TABLE with MERGE tables

To dump specific rows from a table:

C:\>mysqldump "--where=cat='1'" -t database table > dump.sql.txt

Hi,

Plz tell the exact syntax of the command to be executed on telnet prompt to dump a ".sql" file into a DB on Linux.

Example:
DB Name: testdb
User: testuser
Pass: testpass

Thanks!

To dump only select records into a file based on a timestamp field you can use this (last_modified is the timestamp field). This is used in a shell script to be used as a cron to take records that are more than a month old and dump them into an archive file (then the dumped records are deleted).
/yourpath/mysqldump "--where=(month(last_modified)+year(last_modified)*12 <
month(current_date)+(year(current_date)*12)-1)" database table > archive.sql

I'm looking for an example that would give me a CSV type file using mysqldump. I do not understand the differance between --option and -option nor can I find any information on the format of an option file and any 'groups' clauses inside of an option file. Not that I don't enjoy spending an afternoon trying to firure out cryptic examples....
And yes I can cron a job that dumps all records created between two dates but I need CSV output. That is why I need to use mysqldump, I can not access mysql via a shell.
Thanks

does anyone know why I would be getting the following error:

virga 15> mysqldump weather > weather.sql
mysqldump: option `--databases' doesn't allow an argument

no matter what I do with mysqldump I get that same error. I checked "alias" and it's not aliased to anything. Does anyone know where else this error might be coming from?

does anyone know why I would be getting the following error:

virga 15> mysqldump weather > weather.sql
mysqldump: option `--databases' doesn't allow an argument

no matter what I do with mysqldump I get that same error. I checked "alias" and it's not aliased to anything. Does anyone know where else this error might be coming from?

Add your own comment.