Stats

762 Contributors: 16 Thursday, February 16, 2017
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap

Backup using mysqldump

Syntax

  • 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

Parameters

OptionEffect
--# Server login options
-h (--host)Host (IP address or hostname) to connect to. Default is localhost (127.0.0.1) Example: -h localhost
-u (--user)MySQL user
-p (--password)MySQL password. Important: When using -p, there must not be a space between the option and the password. Example: -pMyPassword
--# Dump options
--add-drop-databaseAdd a DROP DATABASE statement before each CREATE DATABASE statement. Useful if you want to replace databases in the server.
--add-drop-tableAdd a DROP TABLE statement before each CREATE TABLE statement. Useful if you want to replace tables in the server.
--no-create-dbSuppress 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.
-t (--no-create-info)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.
-d (--no-data)Do not write table information. This will only dump the CREATE TABLE statements. Useful for creating "template" databases
-R (--routines)Include stored procedures / functions in the dump.
-K (--disable-keys)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.

Remarks

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 --no-data option)
    • LOCKs the table
    • INSERTs all of the rows from the original table in one statement
  • UNLOCK TABLES
  • 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 DROP before 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.

Related Examples