Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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.
288 Views
