Backing Up and Restoring a MySQL Database
This mini-guide will explain how to make backups of your MySQL Database, and how to restore the data.
This task is usually done when migrating your data to another server, but you should take care to make backups of your data on a routine basis as well. Maintaining backups in another location is a great way to ensure your data is safe in the case of an unexpected event.
This guide assumes that you have shell access to the machine hosting the database. Users managing their database from a control panel should refer to the documentation provided by the hosting company.
The syntax for a complete database backup is as follows:
mysqldump -u[username] -p[password] [database name] > [backup file]
You will then be asked to enter the user’s password and hit enter. So to backup a database named wp_database, using the username wp_user, and the password wp_password, saved to the file wp_backup.sql, you would enter:
mysqldump -uwp_user -pwp_password wp_database > wp_backup.sql
Enter the password at the prompt and the file containing your database will be created.
To only backup certain tables from your database, just list them after the database name. For example, to only backup the tables named wp_comments and wp_posts, you would use:
mysqldump -uwp_user -pwp_password wp_database wp_comments wp_posts > wp_backup.sql
Restoring your database from a backup is just as easy, the syntax for the command is:
mysql -u[username] -p[password] [database name] < [backup file]
So to restore a database named wp_database, using the username wp_user, with the password wp_password, from the file wp_backup.sql, you would use:
mysql -uwp_user -pwp_password wp_database < wp_backup.sql
It’s that easy.