- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
mysqldump - A MySQL Database Backup Program
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.
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.
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.
- Related Articles
- mysqlpump - A MySQL Database Backup Program
- How can we take a backup of the single database by using mysqldump client program?
- How can we take a backup of a particular table from a database by using mysqldump client program?
- Duplicate a MySQL Database without using mysqldump?
- How can we take a backup of multiple databases by using mysqldump client program?
- How to take MySQL database backup using MySQL Workbench?
- How can we take a backup of all the databases by using mysqldump client program?
- How to take backup of a single table in a MySQL database?
- Database Backup and Recovery
- How can I restore a database dumped by mysqldump?
- How to Create a Backup of a SQLite Database Using Python?
- MYSQL - select database?
- Creating and Using a MySQL Database
- Creating and Selecting a MySQL Database
- How to alter the database engine of a MySQL database table?