MySQL maintenance – making and restoring backups

I use this to back up a MediaWiki and a phpBB database. This is just a generic way to back up and restore databases with MySQL. You may want to compress this too, but we’ll cover only the very basics here, eg just do a backup and restore it.

To make the backup:
mysqldump --user=[the user] --password=[the password] [database name] > [target path/target file.sql]

To restore the backup:
mysql --verbose --user=[the user] --password=[the password] [database name] < [backup path/backup file.sql]

Some various remarks :
1) --verbose is optional and slower, but I like using it 😀

2) alternative syntax if you don’t want to input the password in the initial command (you’ll then be prompted for the password):
mysqldump -u [the user] -p [database name] > [target path/target file.sql]
(the same modifications work for the import as well)
The great thing about that is that this way the password won’t be stored in your typing history.

3) by default, mysqldump will NOT export stored procedures ! To export stored procedures, add the --routines parameter. Like:
mysqldump -u [the user] -p --routines [database name] > [target path/target file.sql]

For some more info about potentially useful parameters, check out this post: MDLog:/sysadmin – Dumping MySQL Stored Procedures, Functions and Triggers