man pages section 1: User Commands

Exit Print View

Updated: July 2014

mysqldump (1)


mysqldump - a database backup program


mysqldump [options] [db_name [tbl_name ...]]


MySQL Database System                                MYSQLDUMP(1)

     mysqldump - a database backup program

     mysqldump [options] [db_name [tbl_name ...]]

     The mysqldump client is a backup program originally written
     by Igor Romanenko. It can be used to dump a database or a
     collection of databases for backup or transfer to another
     SQL server (not necessarily a MySQL server). The dump
     typically contains SQL statements to create the table,
     populate it, or both. However, mysqldump can also be used to
     generate files in CSV, other delimited text, or XML format.

     mysqldump requires at least the SELECT privilege for dumped
     tables, SHOW VIEW for dumped views, TRIGGER for dumped
     triggers, and LOCK TABLES if the --single-transaction option
     is not used. Certain options might require other privileges
     as noted in the option descriptions.

     To reload a dump file, you must have the same privileges
     needed to create each of the dumped objects by issuing
     CREATE statements manually.

     If you are doing a backup on the server and your tables all
     are MyISAM tables, consider using the mysqlhotcopy instead
     because it can accomplish faster backups and faster
     restores. See mysqlhotcopy(1).

     There are three general ways to invoke mysqldump:

         shell> mysqldump [options] db_name [tbl_name ...]
         shell> mysqldump [options] --databases db_name ...
         shell> mysqldump [options] --all-databases

     If you do not name any tables following db_name or if you
     use the --databases or --all-databases option, entire
     databases are dumped.

     mysqldump does not dump the INFORMATION_SCHEMA database by
     default.  mysqldump dumps INFORMATION_SCHEMA only if you
     name it explicitly on the command line, although currently
     you must also use the --skip-lock-tables option. Before
     MySQL 5.5 mysqldump silently ignores INFORMATION_SCHEMA even
     if you name it explicitly on the command line.

     mysqldump does not dump the performance_schema database.

     Before MySQL 5.5.25, mysqldump does not dump the general_log
     or slow_query_log tables for dumps of the mysql database. As
     of 5.5.25, the dump includes statements to recreate those

MySQL 5.5            Last change: 03/22/2013                    1

MySQL Database System                                MYSQLDUMP(1)

     tables so that they are not missing after reloading the dump
     file. Log table contents are not dumped.

     mysqldump also does not dump the MySQL Cluster ndbinfo
     information database.

     To see a list of the options your version of mysqldump
     supports, execute mysqldump --help.

     Some mysqldump options are shorthand for groups of other

     o   Use of --opt is the same as specifying --add-drop-table,
         --add-locks, --create-options, --disable-keys,
         --extended-insert, --lock-tables, --quick, and
         --set-charset. All of the options that --opt stands for
         also are on by default because --opt is on by default.

     o   Use of --compact is the same as specifying
         --skip-add-drop-table, --skip-add-locks,
         --skip-comments, --skip-disable-keys, and
         --skip-set-charset options.

     To reverse the effect of a group option, uses its --skip-xxx
     form (--skip-opt or --skip-compact). It is also possible to
     select only part of the effect of a group option by
     following it with options that enable or disable specific
     features. Here are some examples:

     o   To select the effect of --opt except for some features,
         use the --skip option for each feature. To disable
         extended inserts and memory buffering, use --opt
         --skip-extended-insert --skip-quick. (Actually,
         --skip-extended-insert --skip-quick is sufficient
         because --opt is on by default.)

     o   To reverse --opt for all features except index disabling
         and table locking, use --skip-opt --disable-keys

     When you selectively enable or disable the effect of a group
     option, order is important because options are processed
     first to last. For example, --disable-keys --lock-tables
     --skip-opt would not have the intended effect; it is the
     same as --skip-opt by itself.

     mysqldump can retrieve and dump table contents row by row,
     or it can retrieve the entire content from a table and
     buffer it in memory before dumping it. Buffering in memory
     can be a problem if you are dumping large tables. To dump
     tables row by row, use the --quick option (or --opt, which
     enables --quick). The --opt option (and hence --quick) is

MySQL 5.5            Last change: 03/22/2013                    2

MySQL Database System                                MYSQLDUMP(1)

     enabled by default, so to enable memory buffering, use

     If you are using a recent version of mysqldump to generate a
     dump to be reloaded into a very old MySQL server, you should
     not use the --opt or --extended-insert option. Use
     --skip-opt instead.

     For additional information about mysqldump, see Section 7.4,
     "Using mysqldump for Backups".

     mysqldump supports the following options, which can be
     specified on the command line or in the [mysqldump] and
     [client] groups of an option file.  mysqldump also supports
     the options for processing option files described at
     Section, "Command-Line Options that Affect Option-
     File Handling".

     o   --help, -?

         Display a help message and exit.

     o   --add-drop-database

         Add a DROP DATABASE statement before each CREATE
         DATABASE statement. This option is typically used in
         conjunction with the --all-databases or --databases
         option because no CREATE DATABASE statements are written
         unless one of those options is specified.

     o   --add-drop-table

         Add a DROP TABLE statement before each CREATE TABLE

     o   --add-drop-trigger

         Add a DROP TRIGGER statement before each CREATE TRIGGER

             This option is supported only by mysqldump as
             supplied with MySQL Cluster. It is not available
             when using MySQL Server 5.5.

     o   --add-locks

         Surround each table dump with LOCK TABLES and UNLOCK
         TABLES statements. This results in faster inserts when
         the dump file is reloaded. See Section, "Speed
         of INSERT Statements".

MySQL 5.5            Last change: 03/22/2013                    3

MySQL Database System                                MYSQLDUMP(1)

     o   --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.

     o   --all-tablespaces, -Y

         Adds to a table dump all SQL statements needed to create
         any tablespaces used by an NDBCLUSTER table. This
         information is not otherwise included in the output from
         mysqldump. This option is currently relevant only to
         MySQL Cluster tables.

     o   --allow-keywords

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

     o   --apply-slave-statements

         For a slave dump produced with the --dump-slave option,
         add a STOP SLAVE statement before the CHANGE MASTER TO
         statement and a START SLAVE statement at the end of the
         output. This option was added in MySQL 5.5.3.

     o   --bind-address=ip_address

         On a computer having multiple network interfaces, this
         option can be used to select which interface is employed
         when connecting to the MySQL server.

         This option is supported only in the version of
         mysqldump that is supplied with MySQL Cluster. It is not
         available in standard MySQL Server 5.5 releases.

     o   --character-sets-dir=path

         The directory where character sets are installed. See
         Section 10.5, "Character Set Configuration".

     o   --comments, -i

         Write additional information in the dump file such as
         program version, server version, and host. This option
         is enabled by default. To suppress this additional
         information, use --skip-comments.

     o   --compact

         Produce more compact output. This option enables the
         --skip-add-drop-table, --skip-add-locks,

MySQL 5.5            Last change: 03/22/2013                    4

MySQL Database System                                MYSQLDUMP(1)

         --skip-comments, --skip-disable-keys, and
         --skip-set-charset options.

     o   --compatible=name

         Produce output that is more 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. See Section 5.1.7, "Server SQL

         This option does not guarantee compatibility with other
         servers. It only enables those SQL mode values that are
         currently available for making dump output more
         compatible. For example, --compatible=oracle does not
         map data types to Oracle types or use Oracle comment

         This option requires a server version of 4.1.0 or
         higher. With older servers, it does nothing.

     o   --complete-insert, -c

         Use complete INSERT statements that include column

     o   --compress, -C

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

     o   --create-options

         Include all MySQL-specific table options in the CREATE
         TABLE statements.

     o   --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 and USE statements are included in the
         output before each new database.

     o   --debug[=debug_options], -# [debug_options]

         Write a debugging log. A typical debug_options string is

MySQL 5.5            Last change: 03/22/2013                    5

MySQL Database System                                MYSQLDUMP(1)

         'd:t:o,file_name'. The default value is

     o   --debug-check

         Print some debugging information when the program exits.

     o   --debug-info

         Print debugging information and memory and CPU usage
         statistics when the program exits.

     o   --default-auth=plugin

         The client-side authentication plugin to use. See
         Section 6.3.6, "Pluggable Authentication".

         This option was added in MySQL 5.5.9.

     o   --default-character-set=charset_name

         Use charset_name as the default character set. See
         Section 10.5, "Character Set Configuration". If no
         character set is specified, mysqldump uses utf8, and
         earlier versions use latin1.

     o   --delayed-insert

         Write INSERT DELAYED statements rather than INSERT

     o   --delete-master-logs

         On a master replication server, delete the binary logs
         by sending a PURGE BINARY LOGS statement to the server
         after performing the dump operation. This option
         automatically enables --master-data.

     o   --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 faster
         because the indexes are created after all rows are
         inserted. This option is effective only for nonunique
         indexes of MyISAM tables. It has no effect for other

     o   --dump-date

         If the --comments option is given, mysqldump produces a

MySQL 5.5            Last change: 03/22/2013                    6

MySQL Database System                                MYSQLDUMP(1)

         comment at the end of the dump of the following form:

             -- Dump completed on DATE

         However, the date causes dump files taken at different
         times to appear to be different, even if the data are
         otherwise identical.  --dump-date and --skip-dump-date
         control whether the date is added to the comment. The
         default is --dump-date (include the date in the
         comment).  --skip-dump-date suppresses date printing.

     o   --dump-slave[=value]

         This option is similar to --master-data except that it
         is used to dump a replication slave server to produce a
         dump file that can be used to set up another server as a
         slave that has the same master as the dumped server. It
         causes the dump output to include a CHANGE MASTER TO
         statement that indicates the binary log coordinates
         (file name and position) of the dumped slave's master
         (rather than the coordinates of the dumped server, as is
         done by the --master-data option). These are the master
         server coordinates from which the slave should start
         replicating. This option was added in MySQL 5.5.3.

         The option value is handled the same way as for
         --master-data and has the same effect as --master-data
         in terms of enabling or disabling other options and in
         how locking is handled.

         This option causes mysqldump to stop the slave SQL
         thread before the dump and restart it again after.

         In conjunction with --dump-slave, the
         --apply-slave-statements and --include-master-host-port
         options can also be used.

     o   --events, -E

         Include Event Scheduler events for the dumped databases
         in the output.

     o   --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.

     o   --fields-terminated-by=..., --fields-enclosed-by=...,

MySQL 5.5            Last change: 03/22/2013                    7

MySQL Database System                                MYSQLDUMP(1)

         These options are used with the --tab option and have
         the same meaning as the corresponding FIELDS clauses for
         LOAD DATA INFILE. See Section 13.2.6, "LOAD DATA INFILE

     o   --first-slave

         Deprecated. Use --lock-all-tables instead.
         --first-slave was removed in MySQL 5.5.3.

     o   --flush-logs, -F

         Flush the MySQL server log files before starting the
         dump. This option requires the RELOAD privilege. If you
         use this option in combination with the --all-databases
         option, the logs are flushed for each database dumped.
         The exception is when using --lock-all-tables,
         --master-data, or (as of MySQL 5.5.21)
         --single-transaction: 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 --lock-all-tables,
         --master-data, or --single-transaction.

     o   --flush-privileges

         Send a FLUSH PRIVILEGES statement to the server after
         dumping the mysql database. This option should be used
         any time the dump contains the mysql database and any
         other database that depends on the data in the mysql
         database for proper restoration.

     o   --force, -f

         Continue even if an SQL error occurs during a table

         One use for this option is to cause mysqldump to
         continue executing even when it encounters a view that
         has become invalid because the definition refers to a
         table that has been dropped. Without --force, mysqldump
         exits with an error message. With --force, mysqldump
         prints the error message, but it also writes an SQL
         comment containing the view definition to the dump
         output and continues executing.

     o   --host=host_name, -h host_name

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

MySQL 5.5            Last change: 03/22/2013                    8

MySQL Database System                                MYSQLDUMP(1)

     o   --hex-blob

         Dump binary columns using hexadecimal notation (for
         example, 'abc' becomes 0x616263). The affected data
         types are BINARY, VARBINARY, the BLOB types, and BIT.

     o   --include-master-host-port

         For the CHANGE MASTER TO statement in a slave dump
         produced with the --dump-slave option, add MASTER_PORT
         and MASTER_PORT options for the host name and TCP/IP
         port number of the slave's master. This option was added
         in MySQL 5.5.3.

     o   --ignore-table=db_name.tbl_name

         Do not dump the given table, which must be specified
         using both the database and table names. To ignore
         multiple tables, use this option multiple times. This
         option also can be used to ignore views.

     o   --insert-ignore

         Write INSERT IGNORE statements rather than INSERT

     o   --lines-terminated-by=...

         This option is used with the --tab option and has the
         same meaning as the corresponding LINES clause for LOAD
         DATA INFILE. See Section 13.2.6, "LOAD DATA INFILE

     o   --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.

     o   --lock-tables, -l

         For each dumped database, lock all tables to be dumped
         before dumping them. The tables are locked with READ
         LOCAL to permit concurrent inserts in the case of MyISAM
         tables. For transactional tables such as InnoDB,
         --single-transaction is a much better option than
         --lock-tables because it does not need to lock the
         tables at all.

         Because --lock-tables locks tables for each database
         separately, this option does not guarantee that the

MySQL 5.5            Last change: 03/22/2013                    9

MySQL Database System                                MYSQLDUMP(1)

         tables in the dump file are logically consistent between
         databases. Tables in different databases may be dumped
         in completely different states.

     o   --log-error=file_name

         Log warnings and errors by appending them to the named
         file. The default is to do no logging.

     o   --master-data[=value]

         Use this option to dump a master replication server to
         produce a dump file that can be used to set up another
         server as a slave of the master. It causes the dump
         output to include a CHANGE MASTER TO statement that
         indicates the binary log coordinates (file name and
         position) of the dumped server. These are the master
         server coordinates from which the slave should start
         replicating after you load the dump file into the slave.

         If the option value is 2, the CHANGE MASTER TO statement
         is written as an SQL comment, and thus is informative
         only; it has no effect when the dump file is reloaded.
         If the option value is 1, the statement is not written
         as a comment and takes effect when the dump file is
         reloaded. If no option value is specified, the default
         value is 1.

         This option requires the RELOAD privilege and the binary
         log must be enabled.

         The --master-data option automatically turns off
         --lock-tables. It also turns on --lock-all-tables,
         unless --single-transaction also is specified, in which
         case, a global read lock is acquired only for a short
         time at the beginning of the dump (see the description
         for --single-transaction). In all cases, any action on
         logs happens at the exact moment of the dump.

         It is also possible to set up a slave by dumping an
         existing slave of the master. To do this, use the
         following procedure on the existing slave:

          1. Stop the slave's SQL thread and get its current

                 mysql> STOP SLAVE SQL_THREAD;
                 mysql> SHOW SLAVE STATUS;

          2. From the output of the SHOW SLAVE STATUS statement,
             the binary log coordinates of the master server from
             which the new slave should start replicating are the

MySQL 5.5            Last change: 03/22/2013                   10

MySQL Database System                                MYSQLDUMP(1)

             values of the Relay_Master_Log_File and
             Exec_Master_Log_Pos fields. Denote those values as
             file_name and file_pos.

          3. Dump the slave server:

                 shell> mysqldump --master-data=2 --all-databases > dumpfile

             Using --master-data=2 works only if binary logging
             has been enabled on the slave. Otherwise, mysqldump
             fails with the error Binlogging on server not
             active. In this case you must handle any locking
             issues in another manner, using one or more of
             --add-locks, --lock-tables, --lock-all-tables, or
             --single-transaction, as required by your
             application and environment.

          4. Restart the slave:

                 mysql> START SLAVE;

          5. On the new slave, load the dump file:

                 shell> mysql < dumpfile

          6. On the new slave, set the replication coordinates to
             those of the master server obtained earlier:

                 mysql> CHANGE MASTER TO
                     -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

             The CHANGE MASTER TO statement might also need other
             parameters, such as MASTER_HOST to point the slave
             to the correct master server host. Add any such
             parameters as necessary.

     o   --no-autocommit

         Enclose the INSERT statements for each dumped table
         within SET autocommit = 0 and COMMIT statements.

     o   --no-create-db, -n

         This option suppresses the CREATE DATABASE statements
         that are otherwise included in the output if the
         --databases or --all-databases option is given.

     o   --no-create-info, -t

         Do not write CREATE TABLE statements that re-create each
         dumped table.

MySQL 5.5            Last change: 03/22/2013                   11

MySQL Database System                                MYSQLDUMP(1)

             This option does not not exclude statements creating
             log file groups or tablespaces from mysqldump
             output; however, you can use the --no-tablespaces
             option for this purpose.

     o   --no-data, -d

         Do not write any table row information (that is, do not
         dump table contents). This is useful if you want to dump
         only the CREATE TABLE statement for the table (for
         example, to create an empty copy of the table by loading
         the dump file).

     o   --no-set-names, -N

         This has the same effect as --skip-set-charset.

     o   --no-tablespaces, -y

         This option suppresses all CREATE LOGFILE GROUP and
         CREATE TABLESPACE statements in the output of mysqldump.

     o   --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.

         The --opt option is enabled by default. Use --skip-opt
         to disable it.  See the discussion at the beginning of
         this section for information about selectively enabling
         or disabling a subset of the options affected by --opt.

     o   --order-by-primary

         Dump each table's rows sorted by its primary key, or by
         its first unique index, if such an index exists. This is
         useful when dumping a MyISAM table to be loaded into an
         InnoDB table, but will make the dump operation take
         considerably longer.

     o   --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, mysqldump prompts for one.

MySQL 5.5            Last change: 03/22/2013                   12

MySQL Database System                                MYSQLDUMP(1)

         Specifying a password on the command line should be
         considered insecure. See Section, "End-User
         Guidelines for Password Security". You can use an option
         file to avoid giving the password on the command line.

     o   --pipe, -W

         On Windows, connect to the server using a named pipe.
         This option applies only if the server supports
         named-pipe connections.

     o   --plugin-dir=path

         The directory in which to look for plugins. It may be
         necessary to specify this option if the --default-auth
         option is used to specify an authentication plugin but
         mysqldump does not find it. See Section 6.3.6,
         "Pluggable Authentication".

         This option was added in MySQL 5.5.9.

     o   --port=port_num, -P port_num

         The TCP/IP port number to use for the connection.

     o   --protocol={TCP|SOCKET|PIPE|MEMORY}

         The connection protocol to use for connecting to the
         server. It is useful when the other connection
         parameters normally would cause a protocol to be used
         other than the one you want. For details on the
         permissible values, see Section 4.2.2, "Connecting to
         the MySQL Server".

     o   --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

     o   --quote-names, -Q

         Quote identifiers (such as database, table, and column
         names) within "`" characters. If the ANSI_QUOTES SQL
         mode is enabled, identifiers are quoted within """
         characters. This option is enabled by default. It can be
         disabled with --skip-quote-names, but this option should
         be given after any option such as --compatible that may
         enable --quote-names.

MySQL 5.5            Last change: 03/22/2013                   13

MySQL Database System                                MYSQLDUMP(1)

     o   --replace

         Write REPLACE statements rather than INSERT statements.

     o   --result-file=file_name, -r file_name

         Direct output to a given file. This option should be
         used on Windows to prevent newline "\n" characters from
         being converted to "\r\n" carriage return/newline
         sequences. The result file is created and its previous
         contents overwritten, even if an error occurs while
         generating the dump.

     o   --routines, -R

         Include stored routines (procedures and functions) for
         the dumped databases in the output. Use of this option
         requires the SELECT privilege for the mysql.proc table.
         The output generated by using --routines contains CREATE
         PROCEDURE and CREATE FUNCTION statements to re-create
         the routines. However, these statements do not include
         attributes such as the routine creation and modification
         timestamps. This means that when the routines are
         reloaded, they will be created with the timestamps equal
         to the reload time.

         If you require routines to be re-created with their
         original timestamp attributes, do not use --routines.
         Instead, dump and reload the contents of the mysql.proc
         table directly, using a MySQL account that has
         appropriate privileges for the mysql database.

         Prior to MySQL 5.5.21, this option had no effect when
         used together with the --xml option. (Bug #11760384, Bug

     o   --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.

     o   --single-transaction

         This option sets the transaction isolation mode to
         statement to the server before dumping data. It is
         useful only with transactional tables such as InnoDB,
         because then it dumps the consistent state of the
         database at the time when START TRANSACTION was issued
         without blocking any applications.

MySQL 5.5            Last change: 03/22/2013                   14

MySQL Database System                                MYSQLDUMP(1)

         When using this option, you should keep in mind that
         only InnoDB tables are dumped in a consistent state. For
         example, any MyISAM or MEMORY tables dumped while using
         this option may still change state.

         Before MySQL 5.5.3, while a --single-transaction dump is
         in process, to ensure a valid dump file (correct table
         contents and binary log coordinates), no other
         connection should use the following statements: ALTER
         TABLE. A consistent read is not isolated from those
         statements, so use of them on a table to be dumped can
         cause the SELECT that is performed by mysqldump to
         retrieve the table contents to obtain incorrect contents
         or fail. As of MySQL 5.5.3, metadata locking prevents
         this situation.

         The --single-transaction option and the --lock-tables
         option are mutually exclusive because LOCK TABLES causes
         any pending transactions to be committed implicitly.

         This option is not supported for MySQL Cluster tables;
         the results cannot be guaranteed to be consistent due to
         the fact that the NDBCLUSTER storage engine supports
         only the READ_COMMITTED transaction isolation level. You
         should always use NDB backup and restore instead.

         To dump large tables, you should combine the
         --single-transaction option with --quick.

     o   --skip-comments

         See the description for the --comments option.

     o   --skip-opt

         See the description for the --opt option.

     o   --socket=path, -S path

         For connections to localhost, the Unix socket file to
         use, or, on Windows, the name of the named pipe to use.

     o   --ssl*

         Options that begin with --ssl specify whether to connect
         to the server using SSL and indicate where to find SSL
         keys and certificates. See Section, "SSL Command

     o   --tab=path, -T path

MySQL 5.5            Last change: 03/22/2013                   15

MySQL Database System                                MYSQLDUMP(1)

         Produce tab-separated text-format data files. For each
         dumped table, mysqldump creates a tbl_name.sql file that
         contains the CREATE TABLE statement that creates the
         table, and the server writes a tbl_name.txt file that
         contains its data. The option value is the directory in
         which to write the files.

             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.
         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-terminated-by

         Column values are converted to the character set
         specified by the --default-character-set option.

     o   --tables

         Override the --databases or -B option.  mysqldump
         regards all name arguments following the option as table

     o   --triggers

         Include triggers for each dumped table in the output.
         This option is enabled by default; disable it with

     o   --tz-utc

         This option enables TIMESTAMP columns to be dumped and
         reloaded between servers in different time zones.
         mysqldump sets its connection time zone to UTC and adds
         SET TIME_ZONE='+00:00' to the dump file. Without this
         option, TIMESTAMP columns are dumped and reloaded in the
         time zones local to the source and destination servers,
         which can cause the values to change if the servers are
         in different time zones.  --tz-utc also protects against
         changes due to daylight saving time.  --tz-utc is
         enabled by default. To disable it, use --skip-tz-utc.

     o   --user=user_name, -u user_name

         The MySQL user name to use when connecting to the

MySQL 5.5            Last change: 03/22/2013                   16

MySQL Database System                                MYSQLDUMP(1)

     o   --verbose, -v

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

     o   --version, -V

         Display version information and exit.

     o   --where='where_condition', -w 'where_condition'

         Dump only rows selected by the given WHERE condition.
         Quotes around the condition are mandatory if it contains
         spaces or other characters that are special to your
         command interpreter.



     o   --xml, -X

         Write dump output as well-formed XML.

         NULL, 'NULL', and Empty Values: For a column named
         column_name, the NULL value, an empty string, and the
         string value 'NULL' are distinguished from one another
         in the output generated by this option as follows.
         |Value:                | XML Representation:             |
         |NULL (unknown value)  | <field                          |
         |                      | name="column_name"              |
         |                      | xsi:nil="true" />               |
         |'' (empty string)     | <field                          |
         |                      | name="column_name"></field>     |
         |'NULL' (string value) | <field                          |
         |                      | name="column_name">NULL</field> |
         The output from the mysql client when run using the
         --xml option also follows the preceding rules. (See the
         section called "MYSQL OPTIONS".)

         XML output from mysqldump includes the XML namespace, as
         shown here:

             shell> mysqldump --xml -u root world City
             <?xml version="1.0"?>

MySQL 5.5            Last change: 03/22/2013                   17

MySQL Database System                                MYSQLDUMP(1)

             <mysqldump xmlns:xsi="">
             <database name="world">
             <table_structure name="City">
             <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
             <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
             <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
             <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
             <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
             <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
             Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
             <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
             Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
             Index_length="43008" Data_free="0" Auto_increment="4080"
             Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
             Collation="latin1_swedish_ci" Create_options="" Comment="" />
             <table_data name="City">
             <field name="ID">1</field>
             <field name="Name">Kabul</field>
             <field name="CountryCode">AFG</field>
             <field name="District">Kabol</field>
             <field name="Population">1780000</field>
             <field name="ID">4079</field>
             <field name="Name">Rafah</field>
             <field name="CountryCode">PSE</field>
             <field name="District">Rafah</field>
             <field name="Population">92020</field>

         Prior to MySQL 5.5.21, this option prevented the
         --routines option from working correctly--that is, no
         stored routines, triggers, or events could be dumped in
         XML format. (Bug #11760384, Bug #52792)

     You can also set the following variables by using
     --var_name=value syntax:

     o   max_allowed_packet

         The maximum size of the buffer for client/server
         communication. The maximum is 1GB.

     o   net_buffer_length

         The initial size of the buffer for client/server

MySQL 5.5            Last change: 03/22/2013                   18

MySQL Database System                                MYSQLDUMP(1)

         communication. When creating multiple-row INSERT
         statements (as with the --extended-insert or --opt
         option), mysqldump creates 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.

         Some options, such as --opt, automatically enable
         --lock-tables. If you want to override this, use
         --skip-lock-tables at the end of the option list.

     A common use of mysqldump is for making a backup of an
     entire database:

         shell> mysqldump db_name > backup-file.sql

     You can load 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

     To dump all databases, use the --all-databases option:

         shell> mysqldump --all-databases > all_databases.sql

     For InnoDB tables, mysqldump provides a way of making an
     online backup:

         shell> mysqldump --all-databases --single-transaction > all_databases.sql

     This backup acquires 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 the lock is released. If long
     updating statements are running when the FLUSH statement is
     issued, the MySQL server may get stalled until those
     statements finish. After that, the dump becomes lock free
     and does not disturb reads and writes on the tables. If the
     update statements that the MySQL server receives are short
     (in terms of execution time), the initial lock period should

MySQL 5.5            Last change: 03/22/2013                   19

MySQL Database System                                MYSQLDUMP(1)

     not be noticeable, even with many updates.

     For point-in-time recovery (also known as "roll-forward,"
     when you need to restore an old backup and replay the
     changes that happened since that backup), it is often useful
     to rotate the binary log (see Section 5.2.4, "The Binary
     Log") or at least know the binary log coordinates to which
     the dump corresponds:

         shell> mysqldump --all-databases --master-data=2 > all_databases.sql


         shell> mysqldump --all-databases --flush-logs --master-data=2
                       > all_databases.sql

     The --master-data and --single-transaction options can be
     used simultaneously, which provides a convenient way to make
     an online backup suitable for use prior to point-in-time
     recovery if tables are stored using the InnoDB storage

     For more information on making backups, see Section 7.2,
     "Database Backup Methods", and Section 7.3, "Example Backup
     and Recovery Strategy".

     If you encounter problems backing up views, please read the
     section that covers restrictions on views which describes a
     workaround for backing up views when this fails due to
     insufficient privileges. See Section E.5, "Restrictions on

     Copyright (C) 1997, 2013, Oracle and/or its affiliates. All
     rights reserved.

     This software and related documentation are provided under a
     license agreement containing restrictions on use and
     disclosure and are protected by intellectual property laws.
     Except as expressly permitted in your license agreement or
     allowed by law, you may not use, copy, reproduce, translate,
     broadcast, modify, license, transmit, distribute, exhibit,
     perform, publish, or display any part, in any form, or by
     any means. Reverse engineering, disassembly, or
     decompilation of this software, unless required by law for
     interoperability, is prohibited.

     The information contained herein is subject to change
     without notice and is not warranted to be error-free. If you
     find any errors, please report them to us in writing.

MySQL 5.5            Last change: 03/22/2013                   20

MySQL Database System                                MYSQLDUMP(1)

     If this software or related documentation is delivered to
     the U.S. Government or anyone licensing it on behalf of the
     U.S. Government, the following notice is applicable:

     U.S. GOVERNMENT RIGHTS Programs, software, databases, and
     related documentation and technical data delivered to U.S.
     Government customers are "commercial computer software" or
     "commercial technical data" pursuant to the applicable
     Federal Acquisition Regulation and agency-specific
     supplemental regulations. As such, the use, duplication,
     disclosure, modification, and adaptation shall be subject to
     the restrictions and license terms set forth in the
     applicable Government contract, and, to the extent
     applicable by the terms of the Government contract, the
     additional rights set forth in FAR 52.227-19, Commercial
     Computer Software License (December 2007). Oracle USA, Inc.,
     500 Oracle Parkway, Redwood City, CA 94065.

     This software is developed for general use in a variety of
     information management applications. It is not developed or
     intended for use in any inherently dangerous applications,
     including applications which may create a risk of personal
     injury. If you use this software in dangerous applications,
     then you shall be responsible to take all appropriate fail-
     safe, backup, redundancy, and other measures to ensure the
     safe use of this software. Oracle Corporation and its
     affiliates disclaim any liability for any damages caused by
     use of this software in dangerous applications.

     Oracle is a registered trademark of Oracle Corporation
     and/or its affiliates. MySQL is a trademark of Oracle
     Corporation and/or its affiliates, and shall not be used
     without Oracle's express written authorization. Other names
     may be trademarks of their respective owners.

     This software and documentation may provide access to or
     information on content, products, and services from third
     parties. Oracle Corporation and its affiliates are not
     responsible for and expressly disclaim all warranties of any
     kind with respect to third-party content, products, and
     services. Oracle Corporation and its affiliates will not be
     responsible for any loss, costs, or damages incurred due to
     your access to or use of third-party content, products, or

     This document in any form, software or printed matter,
     contains proprietary information that is the exclusive
     property of Oracle. Your access to and use of this material
     is subject to the terms and conditions of your Oracle
     Software License and Service Agreement, which has been
     executed and with which you agree to comply. This document
     and information contained herein may not be disclosed,

MySQL 5.5            Last change: 03/22/2013                   21

MySQL Database System                                MYSQLDUMP(1)

     copied, reproduced, or distributed to anyone outside Oracle
     without prior written consent of Oracle or as specifically
     provided below. This document is not part of your license
     agreement nor can it be incorporated into any contractual
     agreement with Oracle or its subsidiaries or affiliates.

     This documentation is NOT distributed under a GPL license.
     Use of this documentation is subject to the following terms:

     You may create a printed copy of this documentation solely
     for your own personal use. Conversion to other formats is
     allowed as long as the actual content is not altered or
     edited in any way. You shall not publish or distribute this
     documentation in any form or on any media, except if you
     distribute the documentation in a manner similar to how
     Oracle disseminates it (that is, electronically for download
     on a Web site with the software) or on a CD-ROM or similar
     medium, provided however that the documentation is
     disseminated together with the software on the same medium.
     Any other use, such as any dissemination of printed copies
     or use of this documentation, in whole or in part, in
     another publication, requires the prior written consent from
     an authorized representative of Oracle. Oracle and/or its
     affiliates reserve any and all rights to this documentation
     not expressly granted above.

     For more information on the terms of this license, or for
     details on how the MySQL documentation is built and
     produced, please visit blue]MySQL Contact & Questions].

     For additional licensing information, including licenses for
     third-party libraries used by MySQL products, see Preface
     and Legal Notices.

     For help with using MySQL, please visit either the
     blue]MySQL Forums] or blue]MySQL Mailing Lists] where you
     can discuss your issues with other MySQL users.

     For additional documentation on MySQL products, including
     translations of the documentation into other languages, and
     downloadable versions in variety of formats, including HTML
     and PDF formats, see the blue]MySQL Documentation Library].

     See attributes(5) for descriptions of the following

MySQL 5.5            Last change: 03/22/2013                   22

MySQL Database System                                MYSQLDUMP(1)

     |Availability   | database/mysql-55/client |
     |Stability      | Uncommitted              |
     For more information, please refer to the MySQL Reference
     Manual, which may already be installed locally and which is
     also available online at

     Oracle Corporation (

     This software was built from source available at  The original
     community source was downloaded from

     Further information about this software can be found on the
     open source community website at

MySQL 5.5            Last change: 03/22/2013                   23