How’s work WordPress Multisite Database?

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

Leave a Reply