WordPress Database: The Basics

Once upon a time, everything on your site actually existed in hard copy in PHP or HTML files. This isn’t so with WordPress. The script stores everything in a database. Every post, comment, link, option, widget setting, plugin setting and everything else that’s not your theme is store in the database. Whenever a visitor is on your site viewing a category, WordPress retrieves the relevant data from the database. Without a database, you have no blog.

Accessing the WordPress Database

To view your database, you can use a tool called phpMyAdmin, which you’ll find when you log in to your host’s control panel — not your WordPress dashboard. phpMyAdmin shows up under “Databases.” Select the user with access to your WordPress database (there may be multiple users and databases), and it will log you in.

When you first arrive in phpMyAdmin, you may have to click on the database name in the left navigation. Then, all the tables that the database contains will appear in a list. My database  currently houses 3 WordPress installs and nothing else for a total of 62 tables. Yours will likely have fewer for a single site and more if several scripts use the same database.

Default WordPress Tables

As of WordPress 3.8, there are exactly 12 tables that you never want to drop (delete) as a whole. You may edit these tables, but they should always remain to keep your blog working properly. They are:

• wp_commentmeta
• wp_comments
• wp_links
• wp_options
• wp_postmeta
• wp_posts
• wp_post_relationships
• wp_terms
• wp_term_relationships
• wp_term_taxonomy
• wp_usermeta
• wp_users

Any other table has been created by a plugin, and some plugins may create multiple tables.

If you specified a table prefix in your config.php file, your tables will have different names. For example, all my default WordPress tables for Reviews by Cole start with “rbc.”

When you’re looking at the list of tables, you can select one or several and perform options such as drop (delete) or empty (delete contents but keep the table). You can also optimize the tables, which I’ll touch on in the next post in this series.

Every table has an icon that allows you to browse it.

Changing WP Password through phpMyAdmin

Let’s say your website was hacked, and they changed the password to your installation. It’s the weekend, and your host has checked out for the night. What do you do? You change your password in phpMyAdmin. To do this, use the following steps:

1. Log in to phpMyAdmin.
2. Click “wp_users.”
3. Click “Browse “tab.
4. Click the Edit icon next to your account.
5. Type your new password into the “Value” for “user_pass”.” The existing password is shown using encryption. This is good in the event that someone gains access to your database.
6. Select “encrypt” from the Function drop-down menu.
7. Click “Go” button.

You’ll now be able to log in to WordPress with your new password!

Exporting Tables in phpMyAdmin

Backing up the WordPress Database

phpMyAdmin is the easiest way to save your entire blog, and it only takes a few steps. While plugins allow you to back up your blog manually and download it to your computer or to schedule email backups, phpMyAdmin provides a simple way to create that backup without every logging in to your WP dashboard.

1. Log in to phpMyAdmin. Click the database name on the left.
2. Click “Export” tab. Select any WordPress tables from the list.
3. Click “Save as file.” Type in a database name.
4. Choose a compression type. None saves as a larger SQL file. Bzip provides the best compression. I personally use Gzip.
5. Click “Go.”

Your database will download to your computer via your browser.

These are the basic processes I think all WordPress users/website owners should know when it comes to phpMyAdmin and your databases. There’s much more that you can do, which I will touch on in subsequent posts.

If you have any specific questions about the database or anything I mentioned in this post, let me know in the comments. I’ll answer them directly or add them to my upcoming posts if they fit!