mysqldump | MYSQL in a Nutshell (In a Nutshell (OReilly))

mysqldump

mysqldump [options] database [table]

Use this to export data and table structures from MySQL. Typically, you use this utility to make backups of databases or to move databases from one server to another. You can run it on an active server. For consistency of data between tables, the tables should be locked (see the --lock-tables option) or the daemon should be shutdown. To make a backup of a database, enter something like the following from the command line:

mysqldump -u russell -p -l db1 > /tmp/db1.sql

In this example, the username is given with the -u option. The -p option tells the utility to prompt the user for a password. The -l option is the same as the --lock-tables option. It has the server lock the tables, making the backup, and then unlock them when it's finished. Next the database to back up (db1) is specified. Finally, using the redirect (the greater-than sign), the output is saved to the path and filename given.

The dump file created will be in the text file format. It will contain a CREATE TABLE statement for each table in the database, along with a separate INSERT statement for each row of data. To restore the data from a dump file created by mysqldump, you can use the mysql client. To restore the file created by the preceding statement, you can enter the following from the command line:

mysql -u russell -p < /tmp/db1.sql

This example redirects the stdin tHRough the use of the less-than sign. This instructs the mysql client to take input from the file given.

The contents of the dump file can be determined by the options chosen. Here is a list of options, along with an explanation of each:

--add-drop-table: Instructs the utility to add a DROP TABLE statement to the export file before each set of INSERT statements for each table.
--add-locks: Instructs the utility to add a LOCK statement before each set of INSERT statements and an UNLOCK after each set.
--all, -a: Instructs the utility to include all MySQL-specific statements in the export file. This option is deprecated as of Version 4.1.2 of MySQL. It is replaced with the --create-options option.
--all-databases, -A: Instructs the utility to export all databases.
--allow-keywords: Makes keywords allowable for column names by including the table name and a dot before such column names in the export file.
--character-sets-dir= path: Specifies the directory containing character sets.
--comments[=0|1]: If this option is set to a value of 1 (default), any comments from a table's schema will be included in the export file. If it is set to 0, they won't be included.
--compatible= type: Use this to have the utility make the export file's contents compatible with other database systems. The choices currently are: mysql323, msyql40, postgresql, oracle, mssql, db2, sapdb, no_key_options, no_table_options, and no_field_options. More than one type may be given in a comma-separated list.
--complete-insert, -c: Instructs the utility to generate complete INSERT statements in the export file.
--compress, -C: Instructs the utility to compress data passed between it and the server if supported.
--create-options: Instructs the utility to include all MySQL-specific statements (e.g., CREATE TABLE) in the export file. It's synonymous with the --all option.
--databases, -B: Names more than one database to export. Table names may not be given with this option unless using the --tables option.
--debug[= options], -# [ options]: Instructs the utility to write debugging information to the log specified along with various settings (e.g., 'd:t:o,logname').
--default-character-set= set: Specifies the default character set.
--delayed: Instructs the utility to add the DELAYED flag to INSERT statements in the export file.
--delete-master-logs: Instructs the utility to lock all tables on all servers and then to delete the binary logs of a master replication server after completing the export.
--disable-keys, -K: For MyISAM tables, this option instructs the utility to add an ALTER TABLE...DISABLE KEYS statement to the export file before each set of INSERT statements, and an ALTER TABLE...ENABLE KEYS statement after each set to optimize later restoration.
--extended-insert, -e: Instructs the utility to bundle INSERT statements together for each table in the export file to make the export faster.
--fields-enclosed-by= characters: Use this with the --tab option to identify the characters to use to indicate the start and end of fields in the data text file.
--fields-escaped-by= character: Use this with the --tab option to identify the character to use to escape special characters in the data text file. Backslash is the default.
--fields-optionally-enclosed-by= characters: Use this with the --tab option to identify the characters that may indicate the start and end of fields in the data text file.
--fields-terminated-by= character: Use this with the --tab option to identify the characters that indicate the end of fields in the data text file.
--first-slave, -x: Locks all tables on all servers.
--flush-logs, -F: Instructs the utility to flush all logs.
--force, -f: Instructs the utility to continue processing data despite errors.
--help, -?: Displays basic help information.
--host= host, -h host: Specifies the name or IP address of the server for connection.
--lines-terminated-by= character: Use this with the --tab option to identify the characters that indicate the end of records in the data text file.
--lock-tables, -l: Instructs the utility to lock all tables before exporting data.
--no-create-db, -n: Instructs the utility not to add CREATE DATABASE statements to the export file when the --all-databases option or the --databases option is used.
--no-create-info, -t: Instructs the utility not to add CREATE TABLE statements to the export file.
--no-data, -d: Instructs the utility to export only database and table schema and not data.
--opt: Instructs the utility to use several options with just this one option: --add-drop-table, --add-locks, --all (or --create-options instead as of MySQL Version 4.1.2), --extended-insert, --lock-tables, and --quick. As of Version 4.1, the --opt option is enabled by default.
--password[= password], -p[ password]: Provides the password to pass to the server. A space is not permitted after -p if the password is given. If the password is not given, the user will be prompted for one.
--port= port, -P port: Specifies the port number to use for connecting to the server.
--quick, -q: Instructs the utility not to buffer data into a complete results set before exporting. Instead, data is exported one row at a time directly to the export file.
--quote-names, -Q: Instructs the utility to place the names of databases, tables, and columns within backticks (`). This is the default option.
--result-file= filename, -r filename: Provides the path and the name of the file to which data should be exported.
--single-transaction: Instructs the utility to run a BEGIN statement before exporting.
--skip-comments: Instructs the utility not to export any comments from a table's schema to the export file.
--skip-opt: Disables the --opt option.
--skip-quote-names: Disables the --quote-names option.
--socket= filename, -S filename: Provides the path and name of the server's socket file.
--tab= path, -T path: Instructs the utility to create two separate export files: one for the table schema (e.g., table.sql) and another for the data (e.g., table.txt). The data text file will contain data in a tab-separated format.
--tables: Names specific tables with the --databases option. All names after the --tables option are treated as table names.
--user= user, -u user: Specifies the username for connecting to the server.
--verbose, -v: Displays more information.
--version, -V: Displays the version of the utility.
--where= 'condition', -w 'condition': Sets a WHERE condition for selecting rows for export.
--xml, -X: Exports databases in XML format.