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