Spec-Zone .ru
спецификации, руководства, описания, API

4.5.4. mysqldump — A Database Backup Program

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output 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.

mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.

Performance and Scalability Considerations

mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on.

For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly:

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 enabled by default, so to enable memory buffering, use --skip-quick.

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

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

Syntax

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here: 

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

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

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

Option Syntax - Alphabetical Summary

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 4.2.3.4, "Command-Line Options that Affect Option-File Handling".

Table 4.7. mysqldump Options

Format Option File Description Introduced
--add-drop-database add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement
--add-drop-trigger add-drop-trigger Add a DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locks add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases all-databases Dump all tables in all databases
--allow-keywords allow-keywords Allow creation of column names that are keywords
--apply-slave-statements apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
--bind-address=ip_address bind-address Use the specified network interface to connect to the MySQL Server
--comments comments Add comments to the dump file
--compact compact Produce more compact output
--compatible=name[,name,...] compatible Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert complete-insert Use complete INSERT statements that include column names
--create-options create-options Include all MySQL-specific table options in CREATE TABLE statements
--databases databases Dump several databases
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--delayed-insert delayed-insert Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
--disable-keys disable-keys For each table, surround the INSERT statements with statements to disable and enable keys
--dump-date dump-date Include dump date as "Dump completed on" comment if --comments is given
--dump-slave[=value] dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master
--events events Dump events from the dumped databases
--extended-insert extended-insert Use multiple-row INSERT syntax that include several VALUES lists
--fields-enclosed-by=string fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by fields-escaped-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string fields-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--flush-logs flush-logs Flush the MySQL server log files before starting the dump
--flush-privileges flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql database
--help Display help message and exit
--hex-blob hex-blob Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)
--host host Host to connect to (IP address or hostname)
--ignore-table=db_name.tbl_name ignore-table Do not dump the given table
--include-master-host-port include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave
--insert-ignore insert-ignore Write INSERT IGNORE statements rather than INSERT statements
--lines-terminated-by=string lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables lock-all-tables Lock all tables across all databases
--lock-tables lock-tables Lock all tables before dumping them
--log-error=file_name log-error Append warnings and errors to the named file
--login-path=name Read login path options from .mylogin.cnf 5.6.6
--master-data[=value] master-data Write the binary log file name and position to the output
--max_allowed_packet=value max_allowed_packet The maximum packet length to send to or receive from the server
--net_buffer_length=value net_buffer_length The buffer size for TCP/IP and socket communication
--no-autocommit no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db no-create-db This option suppresses the CREATE DATABASE statements
--no-create-info no-create-info Do not write CREATE TABLE statements that re-create each dumped table
--no-data no-data Do not dump table contents
--no-set-names no-set-names Same as --skip-set-charset
--no-tablespaces no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located
--port=port_num port The TCP/IP port number to use for the connection
--quick quick Retrieve rows for a table from the server a row at a time
--quote-names quote-names Quote identifiers within backtick characters
--replace replace Write REPLACE statements rather than INSERT statements
--result-file=file result-file Direct output to a given file
--routines routines Dump stored routines (procedures and functions) from the dumped databases
--set-charset set-charset Add SET NAMES default_character_set to output
--set-gtid-purged=value set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output 5.6.9
--single-transaction single-transaction This option issues a BEGIN SQL statement before dumping data from the server
--skip-add-drop-table skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks skip-add-locks Do not add locks
--skip-comments skip-comments Do not add comments to the dump file
--skip-compact skip-compact Do not produce more compact output
--skip-disable-keys skip-disable-keys Do not disable keys
--skip-extended-insert skip-extended-insert Turn off extended-insert
--skip-opt skip-opt Turn off the options set by --opt
--skip-quick skip-quick Do not retrieve rows for a table from the server a row at a time
--skip-quote-names skip-quote-names Do not quote identifiers
--skip-set-charset skip-set-charset Suppress the SET NAMES statement
--skip-triggers skip-triggers Do not dump triggers
--skip-tz-utc skip-tz-utc Turn off tz-utc
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-crl=file_name ssl-crl The path to a file that contains certificate revocation lists 5.6.3
--ssl-crlpath=dir_name ssl-crlpath The path to a directory that contains certificate revocation list files 5.6.3
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--tab=path tab Produce tab-separated data files
--tables tables Override the --databases or -B option
--triggers triggers Dump triggers for each dumped table
--tz-utc tz-utc Add SET TIME_ZONE='+00:00' to the dump file
--user=user_name user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--where='where_condition' where Dump only rows selected by the given WHERE condition
--xml xml Produce XML output

Connection Options

The mysqldump command logs into a MySQL server to extract information. The following options specify how to connect to the MySQL server, either on the same machine or a remote system.

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

DDL Options

Usage scenarios for mysqldump include setting up an entire new MySQL instance (including database tables), and replacing data inside an existing instance with existing databases and tables. The following options let you specify which things to tear down and set up when restoring a dump, by encoding various DDL statements within the dump file.

Debug Options

The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems.

Help Options

The following options display information about the mysqldump command itself.

Internationalization Options

The following options change how the mysqldump command represents character data with national language settings.

Replication Options

The mysqldump command is frequently used to create an empty instance, or an instance including data, on a slave server in a replication configuration. The following options apply to dumping and restoring data on replication master and slave servers.

Format Options

The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Filtering Options

The following options control which kinds of schema objects are written to the dump file: by category, such as triggers or events; by name, for example, choosing which databases and tables to dump; or even filtering rows from the table data using a WHERE clause.

Performance Options

The following options are the most relevant for the performance particularly of the restore operations. For large data sets, restore operation (processing the INSERT statements in the dump file) is the most time-consuming part. When it is urgent to restore data quickly, plan and test the performance of this stage in advance. For restore times measured in hours, you might prefer an alternative backup and restore solution, such as MySQL Enterprise Backup for InnoDB-only and mixed-use databases, or mysqlhotcopy for MyISAM-only databases.

Performance is also affected by the transactional options, primarily for the dump operation.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

Option Groups

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.

Examples

To make a backup of an entire database: 

shell> mysqldump db_name
        > backup-file.sql

To load the dump file back into the server: 

shell> mysql db_name < backup-file.sql

Another way to reload the dump file: 

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

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

Or: 

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

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

Restrictions

mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the --skip-lock-tables option.

mysqldump never dumps the performance_schema database.

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

Before MySQL 5.6.6, mysqldump does not dump the general_log or slow_query_log tables for dumps of the mysql database. As of 5.6.6, the dump includes statements to recreate those tables so that they are not missing after reloading the dump file. Log table contents are not dumped.

If you encounter problems backing up views due to insufficient privileges, see Section E.5, "Restrictions on Views" for a workaround.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved.Legal Notices
Home