mysqldump - A MySQL Database Backup Program

MySQLMySQLi Database

The mysqldump client utility helps performs logical backups, thereby producing a set of SQL statements which can be executed to reproduce the original database object definitions and table data.

mysqldump Usage

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

  • The mysqldump command also generates output in CSV, other delimited text, or XML format.

  • The utility mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and PROCESS if --no-tablespaces option is not used.

  • The databases can be cloned for the purpose of development and DBA work, or can be used to produce slight variations of an existing database that could be required for testing purposes.

  • For large-scale backup and restore, a physical backup is suggested, and then copying the data files in their original format thereby restoring the data quickly:

  • If the tables are primarily InnoDB tables, or if there is a mix of InnoDB and MyISAM tables, the mysqlbackup command is used for the MySQL Enterprise Backup product. It provides the best performance for InnoDB backups and has minimal disruption.

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

Syntax

There are three ways to use mysqldump, to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server. They have been shown below −

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 following db_name shouldn’t be named, or the --databases or --all-databases option has to be used.

raja
Updated on 10-Mar-2021 13:02:01

Advertisements