shell> mysqldump [OPTIONS] database [tables]
If no tables are given, the entire database will be exported.
To see the various options supported by the version of mysqldump and what they mean, you can run this command:
shell> mysqldump --help
Here's a discussion of the main options supported by mysqldump and what they mean:
--add-locks : Add LOCK TABLES to each table before exporting.
--add-locks : Adds LOCK TABLES before each table export and UNLOCK TABLE afterwards.
--add-drop-table : Adds a drop table before each create statement.
--allow-keywords : Allows the creation of column names that are keywords. This is done by prefixing the table name to each column name.
-c, --complete-insert : Use complete insert statements (with column names).
-C, --compress : Compresses all information between the client and the server if both support compression.
--delayed : Inserts rows with the INSERT DELAYED command.
-e, --extended-insert : Uses the new multi-line INSERT syntax. (Gives a tighter and faster insert statement.)
-#, --debug[=option_string] : Traces the use of the program (for debugging purposes).
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--fields-terminated-by=...
These choices are used in conjunction with the -T choice and have the same meaning as the corresponding LOAD DATA INFILE clause LOAD DATA INFILE Syntax:
-F, --flush-logs : Flush logs files in the MySQL server before starting the export.
-f, --force : Continue even if we get a SQL error during a table export.
-h, --host=... : Export data from a MySQL server on a named host. The default host is localhost.
-l, --lock-tables : Lock all tables for the start of the export.
-t, --no-create-info : Do not write table creation information (CREATE TABLE statement)
-d, --no-data : Do not write any row information for the table. With this parameter we can get an export with only one table structure.
--opt : Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables.
-pyour_pass, --password[=PASSWORD] : Same as the connection to the server with the password. If you don't specify the "="PASSWORD" part, mysqldump needs the password from the terminal.
-P port_num, --port=port_num : The TCP/IP port number to use when connecting to a host. (This is used to connect to a host other than localhost, since it uses Unix sockets.)
-q, --quick : Do not buffer the query, export directly to stdout; use mysql_use_result() to do it.
-S /path/to/socket, --socket=/path/to/socket : The socket file to use when connecting to localhost (the default host).
-T, --tab=path-to-some-directory : For each given table, create a table_name.sql file, which contains the SQL CREATE command, and a table_name.txt file, which contains the data. Note: This only works if mysqldump is running on the same machine where the mysqld daemon is running. The format of the .txt file is based on the --fields-xxx and --lines-xxx options.
-u user_name, --user=user_name : The username used by MySQL when connecting to the server. The default value is your Unix login name.
-O var=option, --set-variable var=option sets the value of a variable. The possible variables are listed below.
-v, --verbose : Redundant mode. Prints out more information about what the program is doing.
-w, --where='where-condition' : Export only the records that are selected; note that quotes are mandatory.
"--where=user='jimf'" "-wuserid＞1" "-wuserid<1"
We can use mysqldump to make a backup of the entire database:
mysqldump --opt database ＞ backup-file.sql
Or we can populate one database with information from another MySQL database:
mysqldump --opt database | mysql --host=remote-host -C database Since mysqldump exports full SQL statements, it is easy to use the The mysql client program can easily import the data:
shell> mysqladmin create target_db_name
shell> mysql target_db_name ＜ backup-file.sql