12.3 Dumping the structure and data from MySQL databases and tables

Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server. The dump will contain SQL statements to create the table and/or populate the table.

shell> mysqldump [OPTIONS] database [tables]

If you don't give any tables, the whole database will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump --help.

Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

mysqldump supports the following options:

--add-locks

Add LOCK TABLES before and UNLOCK TABLE after each table dump. (To get faster inserts into MySQL).

--add-drop-table

Add a drop table before each create statement.

--allow-keywords

Allow creation of column names that are keywords. This works by prefixing each column name with the table name.

-c, --complete-insert

Use complete insert statements (with column names).

-C, --compress

Compress all information between the client and the server if both support compression.

--delayed

Insert rows with the INSERT DELAYED command.

-e, --extended-insert

Use the new multiline INSERT syntax. (Gives more compact and faster inserts statements)

-#, --debug[=option_string]

Trace usage of the program (for debugging).

--help

Display a help message and exit.

--fields-terminated-by=...

--fields-enclosed-by=...

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

--fields-escaped-by=...

--fields-terminated-by=...

These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. LOAD DATA.

-F, --flush-logs

Flush logs file in the MySQL server before starting the dump.

-f, --force,

Continue even if we get an SQL error during a table dump.

-h, --host=..

Dump data from the MySQL server on the named host. The default host is localhost.

-l, --lock-tables.

Lock all tables for starting the dump.

-t, --no-create-info

Don't write table creation info (The CREATE TABLE statment)

-d, --no-data

Don't write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!

--opt

Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables. Should give you the fastest possible dump for reading into a MySQL server.

-pyour_pass, --password[=your_pass]

The password to use when connecting to the server. If you specify no `=your_pass' part, mysqldump solicits the password from the terminal.

-P port_num, --port=port_num

The TCP/IP port number to use for connecting to a host. (This is used for connections to hosts other than localhost, for which Unix sockets are used.)

-q, --quick

Don't buffer query, dump directly to stdout; Uses mysql_use_result() to do this.

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

The socket file to use when connecting to localhost (which is the default host).

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

Creates a table_name.sql file, that conntains the SQL CREATE commands, and a table_name.txt file, that contains the data, for each give table. NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon. The format of the .txt file is made according to the --fields-xxx and --lines--xxx options.

-u user_name, --user=user_name

The MySQL user name to use when connecting to the server. The default value is your Unix login name.

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

Set the value of a variable. The possible variables are listed below.

-v, --verbose

Verbose mode. Print out more information what the program does.

-V, --version

Print version information and exit.

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

Dump only selected records; Note that QUOTES are mandatory!

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

The most normal use of mysqldump is probably for making a backup of whole database:

mysqldump --opt database > backup-file.sql

But it's also very useful to populate another MySQL server with information from a database:

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