How to back up your Wordpress database with phpMyAdmin
By Michael W. Jones
Bloggers that host their own Wordpress blogs should back up their databases regularly. If you don’t want to bother with a Wordpress plug-in, there is a very fast and easy way to do it with phpMyAdmin.
Your self-hosted Wordpress instance stores all of its data in a MySQL database. That includs all of your posts, your comments, your categories and tags, your blogroll, your setup information, etc. Although it is relatively rare, it is possible to lose all of this information, especially due to a hardware or software failure involving your shared Web server. If you have a backup, it’s easy to restore the database. If not, it is virtually impossible to retrieve all that information.
This procedure will work with any MySQL database on your Web host site, not just Wordpress files. It will result in a backup file on your local computer. The examples shown in this tutorial happen to be files from a Wordpress instance, but the same process can be used to back up Joomla, Gallery, or other sorts of application which uses a MySQL database. This may be an especially handy if you have several MySQL databases to archive, each from separate applications or domains, but on the same Web server.
PhpMyAdmin is an open source MySql utility that is probably already installed in your Web hosting control panel, from where it is very convenient to use. If you are using MySQL databases, this little app is almost certainly available to you. Just click on the PhpMyAdmin icon or text link, and you will be presented with a screen something like the one shown above. There is a list of databases at the left, highlighted by the red line. Click on the one which you wish to back up.
That click will lead to a screen like the one above. The files in the database are shown at the left side of the graphic, in the area indicated by the red numeral “-1-”. What you will be doing is called exporting, but the result is that you will be backing up both the structure of your Wordpress database and all of the information that it contains. In order to start the process, click on the “Export” link, which is just above the red numeral “-2-” in the diagram at the left.
That will cause the a screen like the one shown above to appear. Be very careful to set up this screen exactly as noted here. On most systems, the desired paramters will already be selected. First, click on the “Select All” link just under the red numeral “-1-” on the graphic. This will cause all of the Wordpress files to be selected for backup. Next, click on the “SQL” option next to the red numeral “-2-” on the diagram. Make sure that the circle next to “SQL” is selected.
In boxes three and four, make sure the options shown are selected. In the box with the red numeral “-3-” that should be the following:
Structure
Add IF NOT EXISTS
Add AUTO_INCREMENT Value
Enclose Table and Field names with Backquotes
In the box with the red numeral “-4-” the following options should be selected:
Data
Complete Inserts
Extended Inserts
Use Hexadecimal for BLOB
Also in this box, “the Maximal length of created query should probably be set at the default, which is usually 50,000. The drop-down list labeled “Export Type” should be set to “INSERT.”
In the box marked with the red numeral “-5-” the following options should be selected:
Save As File
Remember Template
None (in the compression option area)
In the box labeled “File Name Template” the default is fine. When all of the correct parameters for the export are marked, click on the “Go” button at the lower right of the window, marked by a red “-6-” on the diagram. That will bring up the normal file-saving command box for your operating system. Save the file in a place where you can find it. I have several blogs, so I have a file structure set up based on blog names and dates. I usually save the last two backups and delete any older ones. I back up at least once a week.
The file that you have just saved contains all the SQL commands necessary to rebuild the structure of your Wordpress MySQL database and refill it with the data that it contained at the moment of the file download. This means that if you lose your database somehow, you can recover to the point of the backup by replacing the information in the database, if any, with the information from the backup file, or even replace the entire database.
Nex time: How to use phpMyAdmin to restore your data.
Related:
Entry was posted
on Saturday, January 10th, 2009 at 3:51 pm
under
Read more entries by Michael W. Jones.
Stumble It!
August 10th, 2009
Well laid out article with great screenshots.
I didn’t like the idea of relying on a plugin and this article has shown me just what I needed.
I’m slowly getting to know wordpress by working on a local setup with XAMPP, I’ll look at backing up my database with the help of this tutorial.
August 17th, 2009
How do I reset my password?
Thanks
John Musca
John Musca