On Unix-like operating systems running MySQL, the mysqldump command “dumps” a MySQL database, or a collection of databases, for backup or transferral to another SQL server.

Description

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can 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.

  • Description
  • Performance and scalability considerations
  • Invoking mysqldump
  • Syntax
  • Options
  • Restrictions
  • Examples
  • Related commands
  • Linux commands help

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, etc.

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:

  • If your tables are primarily InnoDB tables, or if you have a mix of InnoDB and MyISAM tables, a better tool may be the mysqlbackup command of the MySQL Enterprise Backup product (which is not free). It provides the best performance for InnoDB backups, and can also back up tables from MyISAM and other storage engines; and it provides many convenient options to accommodate different backup scenarios.
  • If your tables are primarily MyISAM tables, consider using the mysqlhotcopy instead, for better performance than mysqldump of backup and restore operations.

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 cause problems 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.

Invoking mysqldump

There are three general ways to use mysqldump. It can dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server. These three uses, respectively, are shown here:

mysqldump [options] db_name [tbl_name …]

mysqldump [options] –databases db_name …

mysqldump [options] –all-databases

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

Syntax

mysqldump [options] [db_name [tbl_name …]]

Options

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] groups of an option file.

Connection options

You can also set the following variables 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 in 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.

– Dump completed on DATE

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.

The output from the mysql client when run using the –xml option also follows the preceding rules.

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.

–where=“user=‘jimf’”

-w"userid>1"

-w"userid<1"

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

  • The –opt option turns on several settings that work together to perform a fast dump operation. All these settings are on by default, because –opt is on by default. Thus you rarely if ever specify –opt. Instead, you can turn these settings off as a group by specifying –skip-opt, then optionally re-enable certain settings by specifying the associated options later on the command line.
  • The –compact option turns off several settings that control whether optional statements and comments appear in the output. Again, you can follow this option with other options that re-enable certain settings, or turn all the settings on using the –skip-compact form.

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.

Usage notes

  • 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.)
  • To reverse –opt for all features, except index disabling and table locking, use –skip-opt –disable-keys –lock-tables.

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.

Examples

mysqldump db_name > backup-file.sql

Back up the entire database db_name to the file backup-file.sql.

mysql db_name < backup-file.sql

Load the backup file backup-file.sql into the database db_name.

mysql -e “source /path-to-backup/backup-file.sql” db_name

This is another way to do the same as the previous example (load the backup file into the database).

mysqldump –opt db_name | mysql –host=remote_host -C db_name

Populate a remote mysql server’s database db_name with local database db_name.

mysqldump –databases db_name1 [db_name2 …] > my_databases.sql

The command above dumps multiple databases (db_name1, db_name2…) to the single backup file my_databases.sql.

mysqldump –all-databases > all_databases.sql

Dumps all databases to the backup file all_databases.sql.

myisamchk — Check, repair, optimize, or fetch information about a MySQL database.mysql — An open-source relational database management system.