WordPress Multisite Database Tour

In my previous post I took you through all the tables of the WordPress database for a single site install. In this tour I will be looking at the tables and database changes for a Multisite installation of WordPress.

When a WordPress site is converted to a Multisite install, a “network” of subsites is created. The existing site is converted to the first subsite in the network. The database classes the network itself as a site (wp_site), and each subsite as a blog (wp_blogs).

Certain tables are used only by a subsite, and a new set of tables are created every time a site is added to the network. Each set of tables is differentiated by the blog_id for the subsite used in the table prefix. e.g. `wp_2_posts`. The following tables are subsite specific:

The wp_users and wp_usermeta tables become global across the subsites in the network, and during the network installation a couple of extra columns are added to the wp_users table:

  • spam – mark as a spam user.
  • deleted – mark as a deleted user.

Multisite Only Tables

The following tables are created during the network installation to help manage the network:

wp_site

This table will contain the one network for the installation although the table is structured to allow multiple networks in one database. This has never been implemented in WordPress itself, but can be managed using a plugin like WP Multi Network or Networks for WordPress.

  • id – unique number assigned to each site.
  • domain – base domain of the site.
  • path – path of the site.

wp_sitemeta

This table is like the wp_options for the network. It stores all the network related configuration and information, as well as other data such as settings for network enabled plugins.

  • meta_id – unique number assigned to each row of the table.
  • site_id – ID of the related site. (Reference to the wp_site table.)
  • meta_key – an identifying key for the piece of data.
  • meta_value – the actual piece of data.

wp_blogs

All the subsites in the network are stored in this table.

  • blog_id – unique number assigned to each blog (subsite).
  • site_id – the site ID that the blog belongs to. (Reference to the wp_site table.)
  • domain – base domain of the blog.
  • path – path of the blog.
  • registered – time and date when the blog was registered.
  • last_updated – time and date when the blog was last updated.
  • public – if the blog is publicly visible.
  • archived – if the blog is archived.
  • mature – if the blog is for a mature audience, ie. NSFW.
  • spam – if the blog has been marked as spam.
  • deleted – if the blog has been deleted.
  • lang_id – language ID of the blog.

wp_blog_versions

When you upgrade the version of WordPress your site is running there are sometimes database changes. Upgrading a Multisite install to a new WordPress version will make those changes to the global tables. However, the set of tables for the subsites in the network will also need to have the upgrade applied. This table records the database version of each blog in the network, so WordPress knows which blogs need the upgrade and updates it after it has been run.

  • blog_id – the blog ID. (Reference to the wp_blogs table.)
  • db_version – the current WordPress DB revision for the blogs tables.
  • last_updated – the time and date of the last upgrade.

wp_signups

This table stores data on blogs which have been signed up for but not activated when the network allows new sites to be registered. Once a site is activated the record is deleted and a record is created in wp_blogs.

  • signup_id – unique number assigned to each row of the table.
  • domain – base domain of the blog.
  • path – path of the blog.
  • title – name of the blog.
  • user_login – username of the user registering the blog.
  • user_email – email of the user.
  • registered – time and date of registration.
  • activated – if the blog has been activated.
  • active – if the blog is active.
  • activation_key – activation key used in emails to active the blog.
  • meta – data about the signup.

wp_registration_log

This table records the user who registers a new site once it has been activated.

  • ID – unique number assigned to each row of the table.
  • email – email address of the user.
  • IP – IP Address of the user.
  • blog_id – the blog ID. (Reference to the wp_blogs table.)
  • date_registered – time and data the blog was registered.

I hope this Multisite database tour has been helpful and informative. If there are any specific plugins that you would like a guide to how they store their data or any custom tables they use, then let me know.

About the Author

Iain Poulson

Iain is a WordPress and PHP developer from England. He builds free and premium plugins, as well as occasionally blogging about WordPress. Moonlights as a PhpStorm evangelist.

  • raisononline

    Awesome resource – top work.

  • John

    Many thanks for this excellent guide! Makes life easier…

  • disqus_6A42cRdm3f

    Hi Iain, very useful thanks. We want to do an audit of our multisite tables and fields to be sure that we don’t have old tables or cust fields added by uninstalled plugins still left over.

    Is there a clever way to do this ?

    Marc

  • Thank you, very useful.

  • Jeremy

    I’m trying to work out how a WordPress Multisite installation knows which users are connected to which sites. Your site is the closest I have found so far to someone discussing the multisite database structure, maybe you know the answer?!

    If I am logged in as the Super Admin and go manually add a user from a particular site’s dashboard then I see an entry in wp_usermeta called ‘primary_blog’ which lists the blog_id value correctly.

    But if I go add a user using an upload manager (I used the Cimy User Manager plugin) then it adds the users to WordPress OK and, if I go into the Super Admin’s Network dashboard to look at All Users in the network I see that my newly uploaded users are all indicated as belonging to the correct site.

    But there is no meta_value in wp_usermeta that has an entry such as ‘primary_blog’.

    I cannot see how it knows which user registered with which site by looking at the database.

    I have the main site created and I have created 2 sub-sites so can see that the users who were added to the one sub-site are not listed as belonging to either the main site or the second sub-site.

    This has got my brain going in circles today.

    Any pointers appreciated!

    Thanks,

    Jeremy.

  • James

    Thank you! Very helpful.

  • Levi Brereton

    This is good information for database, Thanks for sharing this Blog with us .DataSunrise provide the databadse security software .It is a very important Software to secure all databases in the organization. Your comment is awaiting moderation.

  • Reynaldo Ps

    Hi ian, is it possible to connect same ID on multisite ? siteA with database id 2 , siteB with database id 3 , i would like to make siteC with database id 2 ?

  • Omicans

    Hi Ian,

    Is it possible use main site’s db tables for all the Subsites in wordpress multi site network? I am setting a set of stores in WP in which all the sites will be using same data that is on the main site with an exception of ability to change pricing of each product on each sub site. Other than pricing, everything should be fetched from main site tables and everything should be sent to main site tables. By sending i mean, every order should go to main site.

    Is this even possible? Thanks