WordPress Multisite Database Structure

First of all – WordPress Multisite uses one database and I want to show you how. Everything begins when you click the "Install" button.

After that my WordPress database will look like on the screenshot below (multisite tables are highlighted). But please note, that I use wpms_ prefix instead of the default wp_ when install WordPress. I think it is very important to use custom database prefixes for security reasons.

The New Database Tables which appear in WordPress Multisite

And now a short overview of each of them.

wp_blogs

This table contains the information about each blog of a network

blog_id

Unique integer idetifier for the each blog.

site_id

Did you know that WordPress supports multiple networks on the same WordPress installation? So, this column value is the ID of the network. Can be found in wp_site table.

domain

Base domain. On the screenshot above domain is the same because I installed only one network and used subdirectories for blogs, not subdomains.

path

Path to the blog homepage relatively to the blog base domain.

registered

Time when this blog has been registered. In Y-m-d H:i:s format.

last_updated

Shows the latest time when a post (of any custom type) was published or updated on the blog.

public, archived, mature, spam, deleted

If you go to Sites > All sites in your superadmin dashboard and then click "Edit" link on the non-main blog, you will see all these attributes there.

lang_id

If you’ve just installed your blog and have not changed the language, this parameter value will be 0, but if you’ll change the language even once a time, this value will become and remains 1.

wp_blog_versions

When you update the WordPress version your network is running, sometimes databases of some of the websites should be upgraded manually and in that case this table contains the information about the current database revision of the specific blogs.

wp_registration_log

In this table you can find the information about blog registrations.

ID

The unique auto increment number. Actually it doesn’t mean anything.

email

Email of the user who has registered this blog.

IP

IP of the user.

blog_id

ID of the blog.

date_registered

Registration date and time in Y-m-d H:i:s format.

wp_signups

You won’t see any data in this table unless you allow the registration of users in your network.

After that when someone has registered, you will see it in this MySQL table.

Let me describe what each column mean. For users who doesn’t register a blog, columns domain, path, title, meta remain empty.

signup_id

The unique auto increment ID of the sign-up.

domain

Base domain or subdomain of the blog.

path

Path to the homepage relative to the base domain.

title

Title of the registered blog.

user_login

Username.

user_email

User’s email.

registered

Registration date and time in Y-m-d H:i format

activated

Date and time of the activation (when user clicks a link in his confirmation email)

active

1 — activated, 0 — not activated

activation_key

Activation key that users receive by email

meta

Blog parameters: Language and Search engines visibility.

wp_site

This table contains a little bit information about your network.

It looks like nothing especial but did you know that WordPress has the build-in functionality that allows to create a Network of WordPress Multisite Networks?

And wp_site is the table where all the networks should be displayed. If it is interesting for you, make a look at "WP Multi Network" plugin and maybe a little later I will create a step-by-step tutorial for you.

wp_sitemeta

This table functionality is similar to wp_options but it looks like wp_postmeta. It contains all the information about your network(s).

meta_id

The unique auto increment ID of the row.

site_id

ID of the Network. If you don’t use multiple networks within one WordPress installation, this parameter always remains 1.

meta_key

Option name

meta_value

Option value

What happens with other MySQL tables when running WordPress Multisite

First of all — wp_users and wp_usermeta tables become global for all network blogs. So, it doesn’t matter, how much network blogs you will add, all the users (shared users) and their meta info will be stored in these tables.

Just to remember — I use the custom database prefix wpms_ instead of wp_ for security purposes and recommend you to do the same.

Second – at the end of wp_users table will be added two new columns – spam and deleted.

And finally — all the other WordPress non-multisite tables will be copied for each blog with the number (blog ID) after the prefix. So, for second installed blog prefix will be wp_2_ or in my case wpms_2_. Look at the screenshot and it will become clear for you.

So, now you know how WordPress Multisite avoids usage of different databases.

Get Table Prefix in WordPress Multisite

Now you know, that prefixes of any website within WordPress Multisite installation and the global multisite prefix will be different.

Did you use this code to get your WordPress table prefix for your current website?

global $wpdb;
$prefix = $wpdb->prefix; // wp_2_ , wp_3_, etc

You can use the similar code to get the network prefix too:

global $wpdb;
$prefix = $wpdb->base_prefix; // wp_

How to Separate a Single Website from WordPress Multisite Database

I was asked about how to split multisite database a couple times, so, here is my answer, step by step:

1. Take all the tables with a specific blog ID prefix (wp_2_ or wp_3_ or so), export and import them into a new database
2. Export and import global tables wp_users and wp_usermeta. But if you want only specific blog users to be exported, you should do it manually I suppose.
3. Remove spam and deleted columns from just imported wp_users table.

That’s all 🏁😁

Multisite Global Post Indexer plugin. A short description of the tables it uses.

You probably know and use my plugin that allows to query posts and terms globally from the network. I want to tell you a little more about the database tables it uses.

Actually it adds 7 database tables and knowing these tables helps you to run direct SQL queries for any purposes.

wpms_network_log

Here you can find a log of all successful and failed operations of the plugin, unless you turned it off in the configuration file inc/config.php.

wpms_network_postmeta

My plugin indexes posts with their custom fields, so, this table is similar to wp_postmeta (the only difference is the blog_id column) and stores all posts meta data.

wpms_network_posts

All posts are here, it is like wp_posts table but with the BLOG_ID column.

wp_network_rebuildqueue

Includes system information about the rebuilding process.

wpms_network_terms, wpms_network_term_relationships, wpms_network_term_taxonomy

My plugin allows you to index and query not only posts but terms as well, so these tables contain the information about terms, taxonomies and their relationships to posts.

# Multisite

Misha Rudrastyh

Hey guys and welcome to my website. For more than 10 years I've been doing my best to share with you some superb WordPress guides and tips for free.

Need some developer help? Contact me

Follow me on Twitter