Misha Rudrastyh

Misha Rudrastyh

WordPress Developer

WordPress Multisite Database Tutorial

In this tutorial I will show you in details what happens with the WordPress database structure after the Multisite Network installation.

November 1, 2016 Leave comment

First of all — WordPress Multisite uses one database and I want to show you how.

Everything begins when you click the «Install» button.

The new database tables will be created just after clicking this button.
The new database tables for the network will be created just after clicking the «Install» button here.

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.

Tables for WordPress Multisite are highlighted

The Database Tables that appears 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

wp_blogs WP Multisite table
wp_blogs / {PREFIX}blogs
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.
Network blog attributes in the edit menu
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.

wp_registration_log table in phpMyAdmin
wp_registration_log / {PREFIX}registration_log
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.

User accounts and new sites in a network may be registered.
Allow new registrations in Settings > Network Settings

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

wp_signups table
wp_signups / {PREFIX}signups

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.

wp_site_table in WP Multisite
wp_site / {PREFIX}site

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).

wp_sitemeta Multisite table
wp_sitemeta / {PREFIX}sitemeta
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.

wp_users and wp_usermeta are global for all the network blogs
wp_users / {PREFIX}users and wp_usermeta / {PREFIX}usermeta will remain the same for all blogs in a network

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

spam and deleted columns in wp_users table for WP Multisite
«spam» and «deleted» columns at the end of the wp_users table

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.

Database tables that will be copied for each network blog

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

How to split WordPress Multisite database into the separate different websites? #

I was asked this question a couple times, so, here is my answer, step by step:

  1. Take all the tables with the specific blog ID prefix (wp_2_ or wp_3_ or so), export and import them into the new database (change prefix to wp_).
  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.

Download Plugin

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

MySQL tables added by my mltisite indexer plugin.
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.

Only new posts about WordPress

once a week

Leave your question or feedback

phpjsHTMLCSSSQLCode
Please, enter a comment
Please, enter a name
Incorrect email
© 2016 Misha Rudrastyh
Developed on with