Backup & Restore MySQL Using MySQL Dump

Little post because I caught myself looking this up a little more then I usually do. Thought this would be a good time to jot some things down for it.

There is a useful command for backing up your mysql databases called mysqldump. This will pull all of the entries from the database and generate a little script for use. You specify the database and away you go.

This can also be useful for performing camparisons on previous backups to see what data is missing. If you perform regular backups that is.

To dump a database the following command will be used. This will create a script named mydatabase.sql so it can be run during restore.

The following option will ask for a password prompt to sign into.

mysqldump -u root -p mydatabase > mydatabase.sql

On Ubuntu systems the following command can be entered to dump the database as the root user with no password prompt. This is if the root password has been saved in the .my.cnf file.

mysqldump -u root mydatabase > mydatabase.sql

To restore the database. The following command needs to be run. This will execute the mydatabase.sql script. This will update the database with the same tables and table entries as the backed up server.

mysql -u root -p mydatabase < mydatabase.sql

This is probably the basic use case for this method. It’s extremely useful during migrations. Other use cases will probably come up in the future. Just wanted to provide this.