DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) mysqldump

Info Catalog (mysql.info.gz) Using mysqlcheck (mysql.info.gz) Client-Side Scripts (mysql.info.gz) mysqlhotcopy
 
 8.8 The `mysqldump' Database Backup Program
 ===========================================
 
 The `mysqldump' client can be used to dump a database or a collection
 of databases for backup or for transferring the data to another SQL
 server (not necessarily a MySQL server).  The dump will contain SQL
 statements to create the table and/or populate the table.
 
 If you are doing a backup on the server, and your tables all are
 `MyISAM' tables, you could consider using the `mysqlhotcopy' instead
 (faster backup, faster restore).  `mysqlhotcopy' mysqlhotcopy.
 
 There are three general ways to invoke `mysqldump':
 
      shell> mysqldump [OPTIONS] DB_NAME [TABLES]
      shell> mysqldump [OPTIONS] --databases DB1 [DB2 DB3...]
      shell> mysqldump [OPTIONS] --all-databases
 
 If you don't name any tables or use the `--databases' or
 `--all-databases' option, entire databases will be dumped.
 
 To get a list of the options your version of `mysqldump' supports,
 execute `mysqldump --help'.
 
 If you run `mysqldump' without the `--quick' or `--opt' option,
 `mysqldump' loads the whole result set into memory before dumping the
 result.  This will probably be a problem if you are dumping a big
 database. As of MySQL 4.1, `--opt' is on by default, but can be
 disabled with `--skip-opt'.
 
 If you are using a recent copy of the `mysqldump' program to generate a
 dump to be reloaded into a very old MySQL server, you should not use
 the `--opt' or `-e' options.
 
 Before MySQL 4.1.2, out-of-range numeric values such as `-inf' and
 `inf', as well as NaN (not-a-number) values are dumped by `mysqldump'
 as `NULL'.  You can see this using the following sample table:
 
      mysql> CREATE TABLE t (f DOUBLE);
      mysql> INSERT INTO t VALUES(1e+111111111111111111111);
      mysql> INSERT INTO t VALUES(-1e111111111111111111111);
      mysql> SELECT f FROM t;
      +------+
      | f    |
      +------+
      |  inf |
      | -inf |
      +------+
 
 For this table, `mysqldump' produces the following data output:
 
      --
      -- Dumping data for table `t`
      --
 
      INSERT INTO t VALUES (NULL);
      INSERT INTO t VALUES (NULL);
 
 The significance of this behavior is that if you dump and restore the
 table, the new table has contents that differ from the original
 contents.  This problem is fixed as of MySQL 4.1.2; you cannot insert
 `inf' in the table, so this `mysqldump' behavior is only relevant when
 you deal with old servers.
 
 `mysqldump' supports the following options:
 
 `--help, -?'
      Display a help message and exit.
 
 `--add-drop-table'
      Add a `DROP TABLE' statement before each `CREATE TABLE' statement.
 
 `--add-locks'
      Surround each table dump with `LOCK TABLES' and `UNLOCK TABLES'
      statements. This results in faster inserts when the dump file is
      reloaded.   Insert speed.
 
 `--all-databases, -A'
      Dump all tables in all databases. This is the same as using the
      `--databases' option and naming all the databases on the command
      line.
 
 `--allow-keywords'
      Allow creation of column names that are keywords.  This works by
      prefixing each column name with the table name.
 
 `--comments[={0|1}]'
      If set to `0', suppresses additional information in the dump file
      such as program version, server version, and host.
      `--skip-comments' has the same effect as `--comments=0'.  The
      default value is `1' to not suppress the extra information.  New
      in MySQL 4.0.17.
 
 `--compact'
      Produce less verbose output.  This option suppresses comments and
      enables the `--skip-add-drop-table', `--no-set-names',
      `--skip-disable-keys', and `--skip-add-locks' options.  New in
      MySQL 4.1.2.
 
 `--compatible=NAME'
      Produce output that is compatible with other database systems or
      with older MySQL servers.  The value of `name' can be `ansi',
      `mysql323', `mysql40', `postgresql', `oracle', `mssql', `db2',
      `maxdb', `no_key_options', `no_table_options', or
      `no_field_options'.  To use several values, separate them by
      commas.  These values have the same meaning as the corresponding
      options for setting the server SQL mode.   Server SQL mode.
 
      This option requires a server version of 4.1.0 or higher.  With
      older servers, it does nothing.
 
 `--complete-insert, -c'
      Use complete `INSERT' statements that include column names.
 
 `--compress, -C'
      Compress all information sent between the client and the server if
      both support compression.
 
 `--create-options'
      Include all MySQL-specific table options in the `CREATE TABLE'
      statements.  Before MySQL 4.1.2, use `--all' instead.
 
 `--databases, -B'
      Dump several databases. Normally, `mysqldump' treats the first name
      argument on the command line as a database name and following
      names as table names.  With this option, it treats all name
      arguments as database names.  `CREATE DATABASE IF NOT EXISTS
      DB_NAME' and `USE DB_NAME' statements are included in the output
      before each new database.
 
 `--debug[=DEBUG_OPTIONS], -# [DEBUG_OPTIONS]'
      Write a debugging log. The DEBUG_OPTIONS string often is
      `'d:t:o,FILE_NAME''.
 
 `--default-character-set=CHARSET'
      Use CHARSET as the default character set.   Character sets.
      If not specified, `mysqldump' from MySQL 4.1.2 or later uses
      `utf8', and earlier versions use `latin1'.
 
 `--delayed-insert'
      Insert rows using `INSERT DELAYED' statements.
 
 `--delete-master-logs'
      On a master replication server, delete the binary logs after
      performing the dump operation.  This option automatically enables
      `--first-slave' before MySQL 4.1.8 and enables `--master-data'
      thereafter.  It was added in MySQL 3.23.57 (for MySQL 3.23) and
      MySQL 4.0.13 (for MySQL 4.0).
 
 `--disable-keys, -K'
      For each table, surround the `INSERT' statements with `/*!40000
      ALTER TABLE TBL_NAME DISABLE KEYS */;' and `/*!40000 ALTER TABLE
      TBL_NAME ENABLE KEYS */;' statements.  This makes loading the dump
      file into a MySQL 4.0 server faster because the indexes are
      created after all rows are inserted.  This option is effective
      only for `MyISAM' tables.
 
 `--extended-insert, -e'
      Use multiple-row `INSERT' syntax that include several `VALUES'
      lists.  This results in a smaller dump file and speeds up inserts
      when the file is reloaded.
 
 `--fields-terminated-by=...'
 `--fields-enclosed-by=...'
 `--fields-optionally-enclosed-by=...'
 `--fields-escaped-by=...'
 `--lines-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' LOAD DATA.
 
 `--first-slave, -x'
      Deprecated, renamed to `--lock-all-tables' in MySQL 4.1.8.
 
 `--flush-logs, -F'
      Flush the MySQL server log files before starting the dump. This
      option requires the `RELOAD' privilege. Note that if you use this
      option in combination with the `--all-databases' (or `-A') option,
      the logs are flushed _for each database dumped_.  The exception is
      when using `--lock-all-tables' or `--master-data': In this case,
      the logs are flushed only once, corresponding to the moment that
      all tables are locked. If you want your dump and the log flush to
      happen at exactly the same moment, you should use `--flush-logs'
      together with either `--lock-all-tables' or `--master-data'.
 
 `--force, -f'
      Continue even if an SQL error occurs during a table dump.
 
 `--host=HOST_NAME, -h HOST_NAME'
      Dump data from the MySQL server on the given host. The default host
      is `localhost'.
 
 `--hex-blob'
      Dump binary string columns using hexadecimal notation (for example,
      `'abc'' becomes `0x616263'). The affected columns are `BINARY',
      `VARBINARY', and `BLOB' in MySQL 4.1 and up, and `CHAR BINARY',
      `VARCHAR BINARY', and `BLOB' in MySQL 4.0.  This option was added
      in MySQL 4.0.23 and 4.1.8.
 
 `--lock-all-tables, -x'
      Lock all tables across all databases. This is achieved by
      acquiring a global read lock for the duration of the whole dump.
      This option automatically turns off `--single-transaction' and
      `--lock-tables'. Added in MySQL 4.1.8.
 
 `--lock-tables, -l'
      Lock all tables before starting the dump.  The tables are locked
      with `READ LOCAL' to allow concurrent inserts in the case of
      `MyISAM' tables. For `InnoDB' tables, `--single-transaction' is a
      much better option, because it does not need to lock the tables at
      all.
 
      Please note that when dumping multiple databases, `--lock-tables'
      locks tables for each database separately. So, this option does
      not guarantee that the tables in the dump file will be logically
      consistent between databases.  Tables in different databases may
      be dumped in completely different states.
 
 `--master-data[=VALUE]'
      This option causes the binary log position and filename to be
      written to the output. This option requires the `RELOAD'
      privilege. If the option value is equal to 1, the position and
      filename are written to the dump output in the form of a `CHANGE
      MASTER' statement that will make a slave server start from the
      correct position in the master's binary logs if you use this SQL
      dump of the master to set up a slave.  If the option value is
      equal to 2, the `CHANGE MASTER' statement is written as an SQL
      comment.  This is the default action if VALUE is omitted.  VALUE
      may be given as of MySQL 4.1.8; before that, do not specify an
      option value.
 
      The `--master-data'  option turns on `--lock-all-tables', unless
      `--single-transaction' also is specified (in which case, a global
      read lock is only acquired a short time at the beginning of the
      dump. See also the description for `--single-transaction'. In all
      cases, any action on logs happens at the exact moment of the dump.
      This option automatically turns off `--lock-tables'.
 
 `--no-create-db, -n'
      This option suppresses the `CREATE DATABASE /*!32312 IF NOT
      EXISTS*/ db_name' statements that are otherwise included in the
      output if the `--databases' or `--all-databases' option is given.
 
 `--no-create-info, -t'
      Don't write `CREATE TABLE' statements that re-create each dumped
      table.
 
 `--no-data, -d'
      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'
      This option is shorthand; it is the same as specifying
      `--add-drop-table --add-locks --create-options --disable-keys
      --extended-insert --lock-tables --quick --set-charset'.  It should
      give you a fast dump operation and produce a dump file that can be
      reloaded into a MySQL server quickly. *As of MySQL 4.1, `--opt' is
      on by default, but can be disabled with `--skip-opt'*. To disable
      only certain of the options enabled by `--opt', use their `--skip'
      forms; for example, `--skip-add-drop-table' or `--skip-quick'.
 
 `--password[=PASSWORD], -p[PASSWORD]'
      The password to use when connecting to the server.  If you use the
      short option form (`-p'), you _cannot_ have a space between the
      option and the password.  If you omit the PASSWORD value following
      the `--password' or `-p' option on the command line, you will be
      prompted for one.
 
 `--port=PORT_NUM, -P PORT_NUM'
      The TCP/IP port number to use for the connection.
 
 `--protocol={TCP | SOCKET | PIPE | MEMORY}'
      The connection protocol to use.  New in MySQL 4.1.
 
 `--quick, -q'
      This option is useful for dumping large tables.  It forces
      `mysqldump' to retrieve rows for a table from the server a row at
      a time rather than retrieving the entire row set and buffering it
      in memory before writing it out.
 
 `--quote-names, -Q'
      Quote database, table, and column names within ``' characters.  If
      the server SQL mode includes the `ANSI_QUOTES' option, names are
      quoted within `"' characters.  As of MySQL 4.1.1, `--quote-names'
      is on by default, but can be disabled with `--skip-quote-names'.
 
 `--result-file=FILE, -r FILE'
      Direct output to a given file. This option should be used on
      Windows, because it prevents newline `\n' characters from being
      converted to `\r\n' carriage return/newline sequences.
 
 `--set-charset'
      Add `SET NAMES DEFAULT_CHARACTER_SET' to the output. This option
      is enabled by default. To suppress the `SET NAMES' statement, use
      `--skip-set-charset'. This option was added in MySQL 4.1.2.
 
 `--single-transaction'
      This option issues a `BEGIN' SQL statement before dumping data from
      the server. It is useful only with `InnoDB' tables, because then it
      will dump the consistent state of the database at the time then
      `BEGIN' was issued without blocking any applications.
 
      When using this option, you should keep in mind that only `InnoDB'
      tables will be dumped in a consistent state. For example, any
      `MyISAM' or `HEAP' tables dumped while using this option may still
      change state.
 
      The `--single-transaction' option was added in MySQL 4.0.2.  This
      option is mutually exclusive with the `--lock-tables' option,
      because `LOCK TABLES' causes any pending transactions to be
      committed implicitly.
 
      To dump big tables, you should combine this option with `--quick'.
 
 `--socket=PATH, -S PATH'
      The socket file to use when connecting to `localhost' (which is the
      default host).
 
 `--skip-comments'
      See the description for the `--comments' option.
 
 `--tab=PATH, -T PATH'
      Produce tab-separated data files.  For each dumped table,
      `mysqldump' creates a `TBL_NAME.sql' file that contains the
      `CREATE TABLE' statement that creates the table, and a
      `TBL_NAME.txt' file that contains its data.  The option value is
      the directory in which to write the files.
 
      By default, the `.txt' data files are formatted using tab
      characters between column values and a newline at the end of each
      line.  The format can be specified explicitly using the
      `--fields-XXX' and `--lines--XXX' options.
 
      * This option should be used only when `mysqldump' is run on
      the same machine as the `mysqld' server. You must have the `FILE'
      privilege, and the server must have permission to write files in
      the directory that you specify.
 
 `--tables'
      Override the `--databases' or `-B' option. All arguments following
      the option are regarded as table names.
 
 `--user=USER_NAME, -u USER_NAME'
      The MySQL username to use when connecting to the server.
 
 `--verbose, -v'
      Verbose mode.  Print out more information on what the program does.
 
 `--version, -V'
      Display version information and exit.
 
 `--where='WHERE-CONDITION', -w 'WHERE-CONDITION''
      Dump only records selected by the given `WHERE' condition.  Note
      that quotes around the condition are mandatory if it contains
      spaces or characters that are special to your command interpreter.
 
      Examples:
 
           "--where=user='jimf'"
           "-wuserid>1"
           "-wuserid<1"
 
 `--xml, -X'
      Write dump output as well-formed XML.
 
 
 You can also set the following variables by using `--VAR_NAME=VALUE'
 options:
 
 `max_allowed_packet'
      The maximum size of the buffer for client/server communication.
      The value of the variable can be up to 16MB before MySQL 4.0, and
      up to 1GB from MySQL 4.0 on.
 
 `net_buffer_length'
      The initial size of the buffer for client/server communication.
      When creating multiple-row-insert statements (as with option
      `--extended-insert' or `--opt'), `mysqldump' will create rows up
      to `net_buffer_length' length. If you increase this variable, you
      should also ensure that the `net_buffer_length' variable in the
      MySQL server is at least this large.
 
 
 It is also possible to set variables by using
 `--set-variable=VAR_NAME=VALUE' or `-O VAR_NAME=VALUE' syntax. However,
 this syntax is deprecated as of MySQL 4.0.
 
 The most common use of `mysqldump' is probably for making a backup of
 an entire database:
 
      shell> mysqldump --opt DB_NAME > BACKUP-FILE.SQL
 
 You can read the dump file back into the server like this:
 
      shell> mysql DB_NAME < BACKUP-FILE.SQL
 
 Or like this:
 
      shell> mysql -e "source /PATH-TO-BACKUP/BACKUP-FILE.SQL" DB_NAME
 
 `mysqldump' is also very useful for populating databases by copying
 data from one MySQL server to another:
 
      shell> mysqldump --opt DB_NAME | mysql --host=REMOTE_HOST -C DB_NAME
 
 It is possible to dump several databases with one command:
 
      shell> mysqldump --databases DB_NAME1 [DB_NAME2 ...] > my_databases.sql
 
 If you want to dump all databases, use the `--all-databases' option:
 
      shell> mysqldump --all-databases > all_databases.sql
 
 If tables are stored in the `InnoDB' storage engine, `mysqldump'
 provides a way of making an online backup of these (see command below).
 This backup just needs to acquire a global read lock on all tables
 (using `FLUSH TABLES WITH READ LOCK') at the beginning of the dump. As
 soon as this lock has been acquired, the binary log coordinates are
 read and lock is released. So if and only if one long updating
 statement is running when the `FLUSH...' is issued, the MySQL server
 may get stalled until that long statement finishes, and then the dump
 becomes lock-free. So if the MySQL server receives only short (in the
 sense of "short execution time") updating statements, even if there are
 plenty of them, the initial lock period should not be noticeable.
 
      shell> mysqldump --all-databases --single-transaction > all_databases.sql
 
 For point-in-time recovery (also known as "roll-forward", when you need
 to restore an old backup and replay the changes which happened since
 log::) or at least know the binary log coordinates to which the dump
 corresponds:
 
      shell> mysqldump --all-databases --master-data=2 > all_databases.sql
      or
      shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql
 
 The simultaneous use of `--master-data' and `--single-transaction'
 works as of MySQL 4.1.8. It provides a convenient way to make an online
 backup suitable for point-in-time recovery, if tables are stored in the
 `InnoDB' storage engine.
 
 For more information on making backups, see  Backup.
 
Info Catalog (mysql.info.gz) Using mysqlcheck (mysql.info.gz) Client-Side Scripts (mysql.info.gz) mysqlhotcopy
automatically generated byinfo2html