Managing Your WordPress Database

In this article, you will learn how to create, backup, clear, and restore your WordPress MySQL database with phpMyAdmin.

WordPress uses a MySQL database to store content and other information for the blog. A good hosting service will provide a management console that includes all of the tools you need to create and manage a MySQL database.

phpMyAdmin is a user interface that makes the management of a MySQL database a snap. A necessary step when installing a WordPress blog is to create an empty MySQL database in which WordPress will store, among other things, posts and pages as well as blog settings. It is very easy to create the database with phpMyAdmin; and the best news is that once you create an empty database, the WordPress installer will create the database tables for you.

At the end of a WordPress installation, and periodically thereafter, the database should be downloaded to your local PC. The database can then be uploaded again in case you need to restore WordPress at a later date.

A common mistake for WordPress newbies is to think that backing up the WordPress files is enough to protect the blog. However, unless the MySQL database is also backed up, restoring the blog may not be possible. Always backup the MySQL database *and* any WordPress files that might have changed since the last backup.

Note: the figures below might look somewhat different at your host.

CREATE A DATABASE

The first step in creating an empty WordPress database is to navigate to the management console at your host and select phpMyAdmin (Figure 1). From the phpMyAdmin home page, all that is required to create a WordPress database is to fill in the database name and hit the “create” button (Figure 2). That’s it!. The WordPress installer will add the tables it needs.

Figure 1: Accessing phpMyAdmin from the Control Panel

Accessing phpMyAdmin from the Control Panel

Figure 2: Create a MySQL Database

Create a MySQL Database

BACKUP A DATABASE

Backing up the WordPress database is essentially a three-step process:

  • Select the Database,
  • Select the “Export” Tab, and
  • Save the mySQL File.

From the phpMyAdmin home page, select the database (Figure 3) and click the “Export” tab (Figure 4).

Figure 3: Select the Database

Select the Database

Figure 4: Select the Export Tab

Select the Export Tab

There are many options on the export page. Most likely, you will not have to change any of the defaults, although it is interesting to note that there are other export-file types that you could select. Most importantly, all of the tables should be selected for export and the SQL export type should also be selected (Figure 5).

Figure 5: All Tables and SQL Export Type Selected

All Tables and SQL Export Type Selected

At the bottom of the export page (Figure 6), check the “Save as file” box and choose the type of compression you want: I usually export the database as a “zipped” file. Then, click the “Go” button.

Figure 6: Save File As “zipped”

Save File As

Dialog boxes will then coach you through the saving of your backup file to a location on your local hard disk (Figures 7 and 8). For my backup files, I have created a “wordpress” folder on my local hard disk. Within this folder, I create a sub-folder for each backup. For example, for a backup to be done on Christmas Day, I would create a sub-folder named, “online-backup-122508.” On December 25, 2008, I would export the MySQL database and download the WordPress files into this folder.

Figure 7: Save to Disk

Save to Disk

Figure 8: Browse to the Local Directory and Save the File

Browse to the Local Directory and Save the File

Pretty easy, right? Now, you should never be caught without a good backup for your blog.

CLEAR A DATABASE

Before restoring your blog from a backup, I would suggest backing up the existing, online database—just in case. Even if the database has been corrupted, you might still be able to recover important data. After taking this precautionary measure, you can then clear, or empty, the database.

To clear a database, select the database, as in Figure 3. At the bottom of the table list, click the “Check All” link and select “Drop” from the “With selected:” drop-down menu (Figure 9).

Figure 9: Clearing a Database

Clearing a Database

The next screen you see will list the tables to be deleted and ask if you really do want to delete them. Answer “Yes” to delete the tables and clear the database.

An information screen will then indicate that the tables have been dropped from the database: the database is now empty.

RESTORE A DATABASE

After reading this far, restoring a database should be easy for you:

  • Select the database (Figure 3),
  • Click the “Import” tab,
  • Click the “Browse” button to navigate to and select your MySQL backup file (Figure 10), and
  • Click the “Go” button.
Figure 10: Import a MySQL Backup File

Import a MySQL Backup File

phpMyAdmin will “make your day” with a screen similar to this:

Figure 11: Database Restored

Database Restored

phpMyAdmin is a terrific user interface for maintining a WordPress MySQL database. It has many other uses besides the ones I have discussed in this article. For example, you can browse, edit, or drop individual table entries.

You can find complete documentation about phpMyAdmin at the official phpMyAdmin website. Documentation for MySQL can be found at the MySQL website. Also, you might check for additional documentation at your host’s website and at WordPress