WordPress Database Tutorial: Overview of MySQL Structure & phpMyAdmin

by | Aug 29, 2022

If you’re a beginner or intermediate WordPress user, it helps to have a look behind-the-scenes at the WordPress database. That’s what hooks into the admin area you’re probably familiar with to customize and generate your website.

A good starting point is reviewing the WordPress Codex database description, which includes a nice diagram with a complete description of each database table and its application on the WordPress UI.

If you have no idea of the WordPress database, that’s likely because you don’t need to understand the details to get up and running. Most of the time, hosting companies have a one-click WP installation that creates a database for you.

How to View WordPress Database

You’ll need to log in to your web hosting control panel to find that database. Many affordable hosting companies have cPanel, while some higher-end ones build a custom user interface.

In this example, you see my “Site Tools” from SiteGround. If you expand the Site section from the left menu and select MySQL (the database language), you can view any databases you’ve created, including one for WordPress.

If you’d like to see the contents of your database, you need to access phpMyAdmin. Here’s a look at phpMyAdmin.

The left panel shows the WordPress database hierarchy, including the tables from the database description page.

Let’s review a few database tables to get a high-level overview of how things work. Then if you need to find something more specific, you can use the WP codex help or debug it yourself!

WARNING: Editing the database can break your website. Proceed with caution, and make sure you have a backup!

WordPress 101 Video Training

Want this helpful free giveaway or training?

Download Now

WordPress Users Table (wp_users)

The wp_users table is where your WP admin account info is stored — user login, password (a hash value), email address, display name, plus a few other fields.

Every row within these tables will have a unique ID.

My first experience with the WordPress database was changing my password. Unfortunately, I lost the password and couldn’t log in. That meant I had to do it directly from the database! If you search for “WordPress hash password generator,” you should find many websites that do the trick. Then you copy and paste that value into the “user_pass” field.

Can you see why it’s crucial to get a background on this boring tech stuff?

WordPress User Meta Table (wp_usermeta)

If we jump into the user meta table, this is where more information on each user is stored, and it’s linked back to the wp_users table by the user_id field.

So if I go to our WordPress admin dashboard, navigate to the Users page, and select my account, some of these settings are what you see in that user meta table.

One example is the admin color scheme. Again, let’s go back into the table, and the admin_color meta key controls this setting.

Another vital aspect of WordPress is extending it with themes and plugins — both store details in the database.

For instance, we have a meta key value called wpseo_ignore_tour. The Yoast SEO plugin created this, and it’s a simple boolean value (0 or 1) that determines whether you see a Yoast tour upon logging into your account.

WordPress Terms Table (wp_terms)

The next table we’ll look at is the terms table which contains all the categories and tags associated with your blog posts.

In the example below, our two categories are “Uncategorized” (default upon installation) and “Web Design.” If we go to the Posts > Categories section from the admin area and add a new category called “Graphic Design,” you’ll see it’s there when we refresh this wp_terms table.

We also have two related tables — wp_termmeta, wp_term_taxonomy, and wp_term_relationships. I won’t go in-depth into these since their contents aren’t that descriptive

As mentioned previously, one cool thing about the WP database description page is that if you hover over a table, it’ll show you the associated areas within your admin panel connected to that part of the database.

WordPress Comments Table (wp_comments)

If someone leaves a comment on a blog post, this is where it’s stored, including the author’s name, email, IP address, and date.

FYI, blog comments are a significant source of spam, so I recommend you control how people can communicate with you from the Settings > Discussion page.

WordPress Posts Table (wp_posts)

The wp_posts table is arguably the most noteworthy since it includes all your posts, pages, menu items, media library attachments, and more, depending on your installed theme.

From the WP admin area, it’s helpful to note the post/page ID when you hover over an entry. At the bottom left of your browser window, you’ll see the ID embedded within the URL as shown here:

You can access these posts by ID, too.

If we go into Settings > Permalinks, I always recommend setting your permalink structure to “Post name” so you don’t have this funky-looking ?p=ID. However, if you copy/paste this plain structure, it will still load the correct page when you replace the ID with a real page/post ID.

Check out that post/page ID within the wp_posts table to see how the post_content column looks compared to what you see in the visual editor.

Below is the format of WordPress Gutenberg blocks within the post content table as an HTML comment.

Knowing the syntax within the database can be a lifesaver.

One example for me was a client website where we had many manual instances of a call-to-action. I wanted to replace them all with one single reusable block. So I did a database search/replace using the info I gathered from this table.

WordPress Options Table (wp_options)

The final table we’ll review is the wp_options table, probably the second most important one after the wp_posts table because this is where most of your WordPress settings are stored.

You can see above from the option_name column our site URL, blog name (Site Title), blog description (Tagline), etc.

In our example, this table stores 283 values, so I won’t go through them all, but if you scroll, you’ll see where the parent (template) and child theme (stylesheet) are defined.

Putting it All Together

If we break it down to basics, WordPress is a bunch of files on your server (mainly PHP) that tap into a database (MySQL) to read/save information from the admin area (i.e., you create a new page) or browser (i.e., the visitor leaves a comment).

I hope this gives you a solid foundation for how the WP database works at the most technical level.

Check out my other tutorial on how WordPress works. This video explores your WordPress installation’s folder structure and the template hierarchy for your active theme.

Or use the free training below to get WordPress help sent directly to you!

I’ll end with a similar warning to how we began. You have far more power when you access the WordPress database — but with that power comes responsibility. If you’re not careful, you can crash your website and render it unusable if you didn’t create a backup or document your edits. Best of luck, my fellow WP hacker!

WordPress 101 Video Training

Want this helpful free giveaway or training?

Download Now

Categories

Comments

0 Comments

Submit a Comment

×