mysqldump - MySQL database backup program

The mysqldump client utility helps perform logical backups, thereby generating a set of SQL statements that can be executed to regenerate the original database object definitions and table data.

mysqldump usage

• It dumps one or more MySQL databases as a backup or transfer to another SQL server.

• The mysqldump command can also generate CSV, other delimited text, or XML output.

Format.

The utility mysqldump requires at least SELECT permission on the dump table, SHOW VIEW on the dump view, TRIGGER permission on the dump trigger, LOCK TABLES (if the --single-transaction option is used ) is not used, or PROCESS if the --no-tablespaces option is not used.

The database can be cloned for development and DBA work purposes, or can be used to produce minor changes to an existing database that may be needed for testing purposes.

For large-scale backup and recovery, it is recommended to perform a physical backup and then copy the data files to quickly restore the data:

If the table is mainly is an InnoDB table, or a mixture of InnoDB and MyISAM tables, use the mysqlbackup command of the MySQL Enterprise Backup product. It provides the best performance for InnoDB backups with minimal disruption.

mysqldump can also be used to retrieve and dump table contents row by row, or it can be used to retrieve the entire contents of a read table and buffer it in memory before dumping .

Syntax

Using mysqldump, you can dump a set of data in three ways: one or more tables, a set of one or more complete databases, or the entire MySQL server. They are as follows -

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

If the user wishes to dump the entire database, the tables after db_name should not be named, or the --databases or --all-databases option must be used.

The above is the detailed content of mysqldump - MySQL database backup program. For more information, please follow other related articles on the PHP Chinese website!