Skip to content

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

Posted in MySQL, web development.

0 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Some HTML is OK

or, reply to this post via trackback.

Sorry about the CAPTCHA that requires JS. If you really don't want to enable JS and still want to comment, you can send me your comment via e-mail and I'll post it for you.

Please solve the CAPTCHA below in order to fight spamWordPress CAPTCHA