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.
This table contains the information about each blog of a network
- Unique integer idetifier for the each blog.
- 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.
- 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 registered. In
- 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.
- 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
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.
In this table you can find the information about blog registrations.
- The unique auto increment number. Actually it doesn’t mean anything.
- Email of the user who has registered this blog.
- IP of the user.
- ID of the blog.
- Registration date and time in
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
meta remain empty.
- The unique auto increment ID of the sign-up.
- Base domain or subdomain of the blog.
- Path to the homepage relative to the base domain.
- Title of the registered blog.
- User’s email.
- Registration date and time in
- Date and time of the activation (when user clicks a link in his confirmation email)
- 1 — activated, 0 — not activated
- Activation key that users receive by email
- Blog parameters: Language and Search engines visibility.
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?
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.
This table functionality is similar to
wp_options but it looks like
wp_postmeta. It contains all the information about your network(s).
- 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
- Option name
- Option value
What happens with other MySQL tables when running WordPress Multisite
First of all —
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 –
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:
- Take all the tables with a specific blog ID prefix (
wp_3_or so), export and import them into a new database
- 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 just imported
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.
- Here you can find a log of all successful and failed operations of the plugin, unless you turned it off in the configuration file
- My plugin indexes posts with their custom fields, so, this table is similar to
wp_postmeta(the only difference is the
blog_idcolumn) and stores all posts meta data.
- All posts are here, it is like
wp_poststable but with the
- 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.