1. Multisite Database Tables
  2. wp_blogs
  3. wp_blogmeta
  4. wp_registration_log
  5. wp_signups
  6. wp_site
  7. wp_sitemeta
  8. Multisite indexer tables
  9. Non-multisite tables transition
  10. Get table prefix
  11. Extract a subsite

WordPress Multisite Database Tutorial

In this tutorial I would like to dive deep into how the database works with WordPress Multisite installed, so if you’re still wondering, “Does wordpress multisite use one database?” then this tutorial is exactly for you.

However, having some basic WordPress database knowledge before reading this tutorial wouldn’t hurt.

As you probably know WordPress Multisite network isn’t being installed out of the box, usually a normal WordPress site gets upgraded into a Multisite. What does it mean? Of course, it means that we will have all the default database tables like wp_posts, wp_postmeta, wp_users even wp_options unchanged after the migration into a multisite.

But once you hit the “Install” button all the new tables are going to be created.

how to install WordPress multisite network

Multisite Database Tables Structure Explained

wp_blogs

This table contains information about each blog (sub-site) of a network.

wp_blogs database table
Here you can find quite an interesting picture. My multisite network is installed on localhost in a folder and it uses subdirectory configuration.
ColumnDescription
blog_idUnique integer identifier for each blog.
site_idDid you know that WordPress kind of supports multiple multisite networks on the same WordPress installation? Though I never knew any WordPress website which is that colossal. So, this column value is the ID of the network. It can be found in wp_site table.
domainBase domain. In the screenshot above domain is the same because I installed only one network and used subdirectories for blogs, not subdomains.
pathPath to the blog homepage relative to the blog base domain.
registeredThe time when this blog has been created. In Y-m-d H:i:s format.
last_updatedShows the latest time when a post (of any custom type) was published or updated on the blog.
lang_idIf you’ve just installed your blog and have not changed the language, this parameter value will be 0, but if you change the language even once a time, this value will become and remains 1.
public, archived, mature, spam, deletedIf you go to “Sites > All sites” in your super admin dashboard and then click the “Edit” link on the non-main blog, you will see all these attributes there. Check the screenshot below.
Specific site attributes within a WordPress Multisite network

wp_blogmeta

When you update the WordPress version where the 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.

Before WordPress 5.1 version it was wp_blog_versions table.

It was suggested to move some options from wp_#_options to wp_blogmeta, for example, admin_email and blogname, but it was never accomplished and at this moment, this table only contains db_version and db_last_updated information of every subsite. That’s also pretty much the reason, why my Duplicate Site for WordPress Multisite plugin doesn’t even copy the information in this table.

wp_registration_log

wp_registration_log database table
IP is equal to “1” because I am working on localhost.

If you take a look at the screenshot above, you can see that we have the same user email but different blog_id and date_registered column values. So this table logs the time when a specific user has been added to a specific blog.

ColumnDescription
IDThe unique auto-increment number. Actually, it doesn’t mean anything.
emailEmail of the user who has created this blog.
IPThe IP address of the user.
blog_idID of a subsite where the user is registered.
date_registeredRegistration date and time in Y-m-d H:i:s format.

wp_signups

This table is kind of specific – it stores user registration records. So, if you’re just about to add a user manually via the network dashboard and even if you then assign the user to a specific website – nothing will happen with this table, it will remain empty.

What you need to do is first of all make sure that user registration is allowed on your network:

WordPress Multisite user registration settings
Make sure that either “User accounts may be registered” or “Both sites and user accounts can be registered” option is selected.

Below you can see an example of a registered but not yet activated user:

wp_signups database table
Columns domain, path, title and meta are empty for this user because the user hasn’t been assigned to any blogs yet.
Column nameDescription
signup_idThe unique auto-increment ID of the sign-up.
domainBase domain or subdomain of the blog the user was assigned to during the sign-up.
pathPath of the blog homepage related to the domain.
titleTitle of the blog.
user_loginUsername.
user_emailEmail of the user.
registeredRegistration date and time in Y-m-d H:i format.
activatedDate and time of the activation (when a user clicks a link in the confirmation email).
active1 — activated, 0 — not activated.
activation_keyActivation key which this user received by email.
metaAdditional information as a serialized string.

wp_site

On a regular WordPress Multisite install this table doesn’t usually have a lot of data:

wp_site multisite database table

The question comes to mind, why do we even need this table in the first place?

The thing is that WordPress is supposed to be extended not only to a multisite network but also to a network of multisite networks! And this table is a ground for that.

wp_sitemeta

In case you’re familiar with non-multisite database tables then I can say that the purpose of this table is similar to wp_options one. Though it looks like a meta table (wp_postmeta, wp_usermeta etc).

See it yourself:

wp_sitemeta multisite database table

This table, by the way, is also a part of the idea of a network of multisite networks. And by the way, functions get_site_option() and update_site_option() work directly with this table.

ColumnDescription
meta_idThe unique auto-increment ID of the row.
site_idID of the Network. If you don’t use multiple networks within one WordPress installation, this parameter always remains 1.
meta_keyOption name.
meta_valueOption value.

Multisite Indexer database structure

Some time ago, I developed a plugin that allows to query all the posts across the multisite network in a single query. In order to accomplish something like this without huge performance issues I needed to create additional tables in the database and to index network posts there.

So the plugin creates an additional set of tables:

  • wp_network_posts, wp_network_postmeta – indexed posts and post meta are stored here,
  • wp_network_terms, wp_network_term_relationships, wp_network_term_taxonomy – indexed taxonomy terms and their relationships,
  • wp_network_log – just a debug log,
  • wp_network_rebuildqueue – information about the index rebuild process (if any is in progress).

What Happens with Standard WordPress Tables When Running a Multisite Network?

Just a couple of things:

  • wp_users and wp_usermeta tables become global for all network blogs. So, it doesn’t matter, how much subsites in your network you have, all the users (shared users) and their meta data will be stored in these tables.
  • wp_users table will be slightly modified, though – at the end of it, you can find two more columns – “spam” and “deleted”.
  • All the other blog-related tables are going to be duplicated for each blog with the number (blog ID) after the prefix. So, for the second installed blog, the prefix will be wp_2_ (unless you’re using a custom prefix of course).

Get Table Prefix in WordPress Multisite

Now you know that the prefixes of subsite-related tables and the global database prefix are going to be slightly different.

I guess you know how to get a blog prefix from $wpdb object?

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

If you would like to get a global database prefix, then you can use this:

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

How to Extract a Single Site from WordPress Multisite Database?

Though we could consider this as a custom development work, which, by the way, my team is happy to help you with (just leave us a message), I would like to give you a step-by-step here.

  1. We need all the tables with a specific blog ID prefix (wp_2_ or wp_3_ or so), export and import them into a new database. Change the prefix.
  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 the just imported wp_users table.

# Multisite

Misha Rudrastyh

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 X