Have you ever forgotten to back up your data... and regretted it? Losing data, however it may occur, can pretty much ruin your day. (Don't ask me how I know!) It is always important to back up your data, and databases are no exception. Lucky for us, MYSQL has already anticipated the problem and provided us with the means of easily backing up our database

Using "mysqldump", you dump all of the MYSQL statements necessary to re-create the database into a single file.

Note: The mysqldump command is run directly from the command line, and cannot be run when you are already connected to MYSQL.

The syntax is:

mysqldump -u username -p password database > filename.sql


A practical example is:

mysqldump -u Ernest -p TopSecret pets > petsbackup.sql


This command will create a file containing MYSQL statements that, when run, will re-create your entire database.

There are several useful options that can be added into a mysqldump, but we will only look at two. The first, --add-drop-table, will add a DROP TABLE statement before each CREATE TABLE statement in the dumped file, so that during the restore process it is assured that no old data hangs around. The second option, --no-data, will dump only the database structure to the backup file, and will not dump/backup the data stored in the database.

mysqldump --add-drop-table -u username -p password database > filename.sql
mysqldump --no-data -u username -p password database > filename.sql


The restore process, should it ever become necessary, uses "mysqlimport", with the following syntax:

mysqlimport -u username -p password database filename.sql


A practical example is:

mysqlimport -u Ernest -p TopSecret pets petsbackup.sql