How to Backup Your WordPress Database
As part of your website maintenance, you should do a regular backup of your database. Although your webhost keeps a recent copy of your files, you still have to do manual backups yourself just to be sure.
There are 3 ways you can back your WordPress database up: using your cPanel, using phpMyAdmin and by using straight MySQL commands.
Backup WordPress using cPanel
cPanel is the hosting management interface used by most webhosting companies. They have a backup feature that you can easily use to backup your data. Go to the Database section of your cPanel and look for the ‘Download a MySQL Database Backup’. Click the name of the database you want to download, a *gz file will be automatically downloaded to your computer.
If you want to restore the database, you can just go to the same section of your cPanel, then upload the *gz file you’ve downloaded. If you want to upload the database to a new hosting company then you should recreate your database first. Use the same details as your old account.
Note that when doing a cPanel backup, do not choose the “full backup” option as this is not restorable.
Backup WordPres using phpMyAdmin
phpMyAdmin is a program used for database management. You can also find this in your cPanel. Here’s how you backup a WordPress database using it:
2. On your phpMyAdmin dashboard, click on “Databases”.
3. Click on your WordPress database. You will then see a list of all your database tables.
4. Click on the “Export” tab on top of the tables.
5. Click “Select All”.
6. Tick on the “SQL” button.
7. On the SQL section, tick everything except these two: “Add CREATE PROCEDURE / FUNCTION” and “Creation/Update/Check dates
8. On the Data section, check these boxes: “Data”, “Complete inserts” and “Extended inserts”.
9. Check on “Save as file”.
10. Leave the “File name template” to its default value.
11. Tick on “None” for the compression option.
12. Finally, click “Go”. You will be prompted to save the file. The download of your database may take a few minutes, depending on its size.
Backup WordPress using MySQL commands
If you are trying to download a very large database then you should use MySQL. phpMyAdmin can’t handle downloads of large databases. Here’s how to do it:
1. Make sure that your directory is set to the directory where you want to dump your database. See sample command below to change directory.
user@linux:-> cd document/files
2. Use the mysqldump command to dump all of your database tables.
user@linuz:-/document/files> mysqldump –add-drop-table –h mysqlhostserver
-u mysqlusername –p databasename (tablename tablename tablename) | bzip2
-c > myblog.bak.sql.bz2
Enter password: (enter your mysql password)
If you only want to download some of your database tables, then replace the tablename with the name of the table you want to download. Just separate tables with space.
bzip2 means that your database are being compressed automatically, and the myblog.bak.sql.bz2 means that the compressed file is being saved as myblog.bak.sql.bz2
You can also use other compression algorithms in place of bzip2 like .z, .zip or.gz.
Image by Titanas (flickr)