Backup using mysqldump
suggest changeSyntax
- 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 mysqldump
ed database comprises execution of those statements. Generally, this file
DROP
s the first specified table or viewCREATE
s that table or view- For tables dumped with data (i.e. without the
--no-data
option) LOCK
s the tableINSERT
s all of the rows from the original table in one statementUNLOCK TABLES
- Repeats the above for all other tables and views
DROP
s the first included routineCREATE
s 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.