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.
Though 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_options unchanged after the migration into a multisite.
But once you hit the “Install” button all the new tables are going to be created.
Multisite Database Tables Structure Explained
This table contains the information about each blog (sub-site) of a network.
|Unique integer idetifier for the each blog.
|Did 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. Can be found in wp_site table.
|Base domain. On the screenshot above domain is the same because I installed only one network and used subdirectories for blogs, not subdomains.
|Path to the blog homepage relatively to the blog base domain.
|Time when this blog has been created. In
Y-m-d H:i:s format.
|Shows the latest time when a post (of any custom type) was published or updated on the blog.
|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
|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. Check the screenshot below.
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
If you take a look at the screenshot above, you can see that we have the same user email but different
date_registered column values. So this table logs the time when a specific user has been added to a specific blog.
|The unique auto increment number. Actually it doesn’t mean anything.
|Email of the user who has registered this blog.
|IP address of the user.
|ID of a subsite where the user is registered.
|Registration date and time in
Y-m-d H:i:s format.
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 to make sure that user registration is allowed on your network:
Below you can see an example of a registered but not yet activated user:
|The unique auto increment ID of the sign-up.
|Base domain or subdomain of the blog the user was assigned to during the sign-up.
|Path of the blog homepage related to the domain.
|Title of the blog.
|Email of the user.
|Registration date and time in
Y-m-d H:i format.
|Date and time of the activation (when user clicks a link in the confirmation email).
|1 — activated, 0 — not activated.
|Activation key which this user received by email.
|Additional information as a serialized string.
On a regular WordPress Multisite install this table doesn’t usually have a lot of data:
The question comes to the 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.
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 (
See it yourself:
This table by the way is also a part of the idea of a network of multisite networks. And by the way, functions
update_site_option() work directly with this table.
|The unique auto increment ID of the row.
|ID of the Network. If you don’t use multiple networks within one WordPress installation, this parameter always remains
Multisite Indexer database structure
Some time ago I developed a plugin which 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_postmeta– indexed posts and postmeta are stored here,
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 in a progress).
What Happens with Standard WordPress Tables When Running a Multisite Network?
Just a couple of things:
wp_usermetatables 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_userstable 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 table and the global database prefix are going to be slightly different.
I guess you know how to get a blog prefix from
$prefix = $wpdb->prefix; // wp_2_ , wp_3_, etc
If you would like to get a global database prefix, then you can use this:
$prefix = $wpdb->base_prefix; // wp_
How to Extract a Single Site from WordPress Multisite Database?
Though we could consider this as a custom develop work which by the way my team are happy to help you with (just leave us a message), I would like to give you a step by step here.
- We need all the tables with a specific blog ID prefix (
wp_3_or so), export and import them into a new database. Change the prefix.
- Export and import global tables
wp_usermeta. But if you want only specific blog users to be exported, you should do it manually I suppose.
deletedcolumns from the just imported