MySQL 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
| Option | Effect |
|---|---|
| -- | # 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-database | Add a DROP DATABASE statement before each CREATE DATABASE statement. Useful if you want to replace databases in the server. |
--add-drop-table | Add a DROP TABLE statement before each CREATE TABLE statement. Useful if you want to replace tables in the server. |
--no-create-db | 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. |
-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 viewCREATEs that table or view- For tables dumped with data (i.e. without the
--no-dataoption)LOCKs the tableINSERTs 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 routineCREATEs 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.
