- mysqldump -u [username] -p[password] [other options] db_name > dumpFileName.sql /// To Backup single database
- mysqldump -u [username] -p[password] [other options] db_name [tbl_name1 tbl_name2 tbl_name2 ...] > dumpFileName.sql /// To Backup one or more tables
- mysqldump -u [username] -p[password] [other options] --databases db_name1 db_name2 db_name3 ... > dumpFileName.sql /// To Backup one or more complete databases
- mysqldump -u [username] -p[password] [other options] --all-databases > dumpFileName.sql /// To Backup entire MySQL server
|--||# Server login options|
|Host (IP address or hostname) to connect to. Default is |
|MySQL password. Important: When using |
-p, there must not be a space between the option and the password. Example:
|--||# Dump options|
|Add a |
DROP DATABASE statement before each
CREATE DATABASE statement. Useful if you want to replace databases in the server.
|Add a |
DROP TABLE statement before each
CREATE TABLE statement. Useful if you want to replace tables in the server.
|Suppress the |
CREATE DATABASE statements in the dump. This is useful when you're sure the database(s) you're dumping already exist(s) in the server where you'll load the dump.
|Suppress all |
CREATE TABLE statements in the dump. This is useful when you want to dump only the data from the tables and will use the dump file to populate identical tables in another database / server.
|Do not write table information. This will only dump the |
CREATE TABLE statements. Useful for creating "template" databases
|Include stored procedures / functions in the dump.|
|Disable keys for each table before inserting the data, and enable keys after the data is inserted. This speeds up inserts only in MyISAM tables with non-unique indexes.|
The output of a
mysqldump operation is a lightly commented file containing sequential SQL statements that are compatible with the version of MySQL utilities that was used to generate it (with attention paid to compatibility with previous versions, but no guarantee for future ones). Thus, the restoration of a
mysqldumped database comprises execution of those statements. Generally, this file
DROPs the first specified table or view
CREATEs that table or view
- For tables dumped with data (i.e. without the
LOCKs the table
INSERTs all of the rows from the original table in one statement
- Repeats the above for all other tables and views
DROPs the first included routine
CREATEs that routine
- Repeats the same for all other routines
The presence of the
CREATE for each table means that if the schema is present, whether or not it is empty, using a
mysqldump file for its restoration will populate or overwrite the data therein.
Backup database with stored procedures and functions
This modified text is an extract of the original Stack Overflow Documentation created by following contributors
and released under CC BY-SA 3.0