Getting Started with Migrating WordPress Database: Basic Database Knowledge

When working on a WordPress-based project, arguably one of the most frustrating or tedious aspects of deployment is actually getting the databases in your environment to synchronize with each other.

Sure, it makes sense to use test data in development, user data in staging, and real data in production, but there's no magic bullet, right? This means that test data sometimes will work; other times it won't.

For example, let's say you inherit a project for which you have to pull the database and then start using the existing data. Or suppose you have to migrate your entire website or application from one server to another.

In this case, test data is not much help. Instead, you need a tool. Of course, the WordPress Importer is a great tool for basic migrations, and if you’re familiar with the database front-end and working with SQL itself, running SQL exports and imports is fine.

But what about those in between?

Make migration easier

The truth is, it’s a mixed bag when it comes to WordPress database migrations, as many of us have different skill levels depending on which part of the stack we use most.

I mean:

• Those more familiar with front-end work may be less familiar with the application layer and/or database layer
• People who are used to working at the application layer may also be good at the front end, but not so good at the database (and vice versa)
• Those who live in databases may feel uncomfortable with the layers above

This is not to say that there are no full stack developers. Obviously, there is; however, not everyone is in this position.

So when it comes to migrating WordPress databases, some people are in a much more difficult position than others. Or, while people are familiar with SQL, some may just be looking for a tool to help simplify the entire process.

In this series, we will introduce a utility that can achieve this purpose, but before that, let’s take a quick look at the WordPress database to ensure that we are all on the same page.

WordPress Database

When it comes to discussing WordPress databases, an entire series of articles could be written discussing every table, every column, the schema, how to write the best queries, and more.

This is not a series.

Instead, we will do two things in this article:

1. We will make sure that we all have a clear conceptual understanding of the database so that we know how to picture it in our minds
2. We will look at each table in the WordPress database to understand what type of data each table holds

Ultimately, this should help explain or demystify some of the underlying workings for those who spend more time on the front end, and possibly help those who spend more time on the application layer using the WordPress API understand what Which table the function is matched to (this ultimately leads to writing better code).

What is a database?

In general, I think most readers of Wptuts know what a database is.

Directly from Wikipedia:

A database is an organized collection of data. This data is typically organized to model relevant aspects of reality (e.g., availability of hotel rooms) to support processes that require this information (e.g., finding hotels with available rooms).

This is a fair definition, but I don't think it describes a WordPress database or similar web application very well - it's a bit too general. So from here on, let's create our own working definition to use throughout the rest of this series.

Let’s try this:

The database consists of at least one table. Tables are made up of rows and columns, and each row stores unique information. Each row is called a record. Multiple tables can exist in a database, and sometimes tables can be related to each other.

Perhaps the most confusing part of what I shared above is that tables can be related to each other. We’ll revisit this idea before the end of the article – but first, let’s discuss the WordPress database.

WordPress Database Architecture

In short, a WordPress database consists of 11 tables (unless you use Multisite, but that’s beyond the scope of this series).

Now, each table also has its own set of columns that represent the various information stored in the table. For example, the wp_posts table has a column named post_content which represents the actual content stored in the post.

The form and its description are as follows:

• wp_users Contains the list of users registered for the WordPress installation. This includes email address, password, display name, etc.
• wp_usermeta Contains information related to each user. You can store additional information about each user here.
• wp_posts is where all post information is stored. In fact, whether it’s a post, page, or custom post type, all the information like title, content, etc. is stored here.
• wp_postmeta is where the metadata for each post is stored. This table allows you to save and retrieve more information about each post.
• wp_comments is where comments for each post (again, regardless of type) are stored.
• wp_commentmeta As with other "meta" tables, you can store more information about each comment than is already saved in the comments table.
• wp_terms is where categories and tags are stored. Because the relationships between posts, pages, custom post types, categories, and tags can become more complex, some additional tables are required.
• wp_term_taxonomy Provide descriptions of the categories or tags (or even links, if you are still using them) in the wp_terms table.
• wp_term_relationship Stores the relationship of a given post to its category (or categories) and/or tag (or tags).
• wp_options is where all settings are saved - this includes settings shipped and configured with WordPress as well as settings created using the Settings API.
• wp_links is a table that still exists in the WordPress database, although there is no longer a UI option for the data. If you've ever used this feature, you're familiar with links and how they work, and this is the table where they are stored.

This is what the WordPress database is all about. It's relatively simple and straightforward, right?

Posts are saved in the posts table, comments are saved in the comments table, users are saved in the users table, etc. Of course, there are some subtle differences (eg pages are stored in the Posts table); however, this is a relatively uncomplicated pattern.

This is a good thing.

Also, remember we mentioned before that some tables can reference each other? Comments and posts are a good example. Since the comment is left on a specific post, the comment needs to know which post ID it is associated with so that when the post is loaded, the comments associated with that post ID can be retrieved.

Anyway, this is more detail than we've gone into in this series, but hopefully it's enough to give you an idea. If you’re interested in more technical information, relationships between tables, columns, and more, then be sure to check out the WordPress Codex article on database descriptions.

in conclusion

At this point, we have covered everything you need to know about getting started with the WordPress database. Hopefully this helps pull back the curtain on what’s going on behind the scenes when you save information in WordPress, but now that we’ve covered that, it’s time to look at a tool that makes data migration incredibly easy.

Considering that we now understand how the database is organized, we should also understand how migrations work.