WordPress Multisite Database Structure
As you probably know Multisite allows to run multiple WordPress sites within one MySQL database. In this tutorial I will show what happens with default database schema after network installation.
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
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.
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 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.
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?
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.
This table functionality is similar to wp_options but it looks like wp_postmeta . It contains all the information about your network(s).
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.
Second – at the end of wp_users table will be added two new columns – spam and deleted .
"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.
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?
You can use the similar code to get the network prefix too:
If you want to change the default table prefix wp_ , I recommend this tutorial and this tool.
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_2_ or wp_3_ or so), export and import them into a new database (change prefix to wp_ ).
- 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.
- Remove spam and deleted columns from just imported wp_users table.
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.
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.
More about WordPress Multisite
Passionate about WordPress and snowboarding, creating websites for over 10 years! Let’s work together — just contact me.
If you are a developer too, subscribe to my facebook page.
The MU forums have moved to WordPress.org
WordPress database error in apache2 log. (4 posts)
Posted 8 years ago #
My apache2 error log is growing huge with these 2:
WordPress database error Table ‘hubbledata.wp_addingblocks’ doesn’t exist for query SELECT * FROM wp_addingblocks WHERE enabled=’1′ made by require, require_once, include, get_header, locate_template, load_template, require_once, wp_head, do_action, call_user_func_array, addingblocks_css
WordPress database error Table ‘hubbledata.wp_addingblocks’ doesn’t exist for query SELECT * FROM wp_addingblocks WHERE enabled=’1′ made by require, require_once, include, get_footer, locate_template, load_template, require_once, do_action, call_user_func_array, addingblocks_html
Apparently this dosen’t visibly affects my site, but apache2 records this error to every ip.
What I understand from this error is that I need wp_addingblocks table.
Can you help me please?
This isn’t a default table in WordPress or WordPress MU.
you’re in the MU forums, btw.
Look for a plugin called "adding blocks"
Posted 8 years ago #
Yes andrea, a MU install is what my server runs 🙂 I’m not here by accident. Thank you very much for the reply.
It turns out that the problem was exactly the "adding blocks". My friend forgot to tell me about this little plugin. Removed it and the error stopped showing in the log.
Well, I mention it because there was just the use of WordPress and not MU. 🙂
We get a lot of people wandering in here with single-wp questions. 🙂
Quickly Disable or Enable All WordPress Plugins via the Database
Recently, while dealing with the dreaded white screen of death, I found myself unable to login to the WordPress Admin area to manually disable all of the plugins used here at Perishable Press. In the past, I have dealt with this situation by simply deleting all plugin files from the server, however this time, time was of the essence — I had only a few minutes with which to troubleshoot, diagnose, and ultimately resolve the deadly white-screen syndrome. Fortunately, after a few minutes of digging through the WordPress Codex, I had discovered enough information to successfully complete my mission. Now that the fiasco is over, I want to share a simple technique for quickly disabling and (re-)enabling your entire set of WordPress plugins.
In the blink of an eye..
Before beginning, make a backup copy of your database. Then, login to your database using phpMyAdmin (or whatever), and navigate to the “ active_plugins ” column of the “ wp_options ” table using the following SQL query (edit the default WordPress table prefix “ wp_ ” if needed):
Once the active_plugins column appears, click to edit it. You will see something similar to the following, depending on the number and type of plugins you have installed:
Alternately, here is a one-second query to disable all plugins:
Upon execution, this query will clear the active_plugins field of all active plugins (duh), effectively disabling (without uninstalling or modifying) the entire set. This method is great if you plan on re-enabling each plugin individually, say, after resolving some heinous server error. Whereas the previous technique makes it easy to re-enable all plugins en masse, this query is perfect for simply “nuking” all active plugins with no remorse. 😉
Apparently, this method works only for WordPress versions less than 2.9. For 2.9 and better, use this instead:
So use that if working with WP 2.9 or beyond!
Or: just rename the wp-content/plugins/ folder.
You will have to manually reactivate the plugins after you rename the plugins folder back to its original name, but all plugin options are preserved too.
Ahh yes, good call there, Ozh! Simply renaming the plugins folder is another efficient, effective technique for quickly disabling all plugins.. Why am I not surprised that I haven’t thought of this myself? Thanks for the tip! 😉
Thank you, that’s exactly what I was looking for.
My pleasure! Thanks for the feedback!
Thank you very much for this post. You saved me from a bad maintenance mode plug-in during the upgrade from 2.5.1 to 2.6.
There is a reason why they tell you to disable ALL plug-ins. 🙂
Thanks a ton for taking the time to put this up!
Happy to help, pilotmike — thanks for the comment!
Thanks — that was a big help!
My pleasure, Kyle — glad to be of service! 🙂
Hi, i’m new on WordPress. I’m googling about how to add ‘notify me off follow-up comments via email’ and land on this page :). I’m glad to know this blog, i’ve got something new to learn. Thanks.
Glad to be of service, Phoenix! Thanks for the feedback 🙂
Thanks. You’ve helped me bring my site back up after my provider moved servers on me and decided not to assist. Avoid Dreamhost.
Happy to help, Jonathan — thanks for the heads up on Dreamhost! 😉
WP Database Backup
WP Database Backup plugin helps you to create Database Backup and Restore Database Backup easily on single click.Manual or Automated Database Backups And also store database backup on safe place- Dropbox,FTP,Email,Google drive, Amazon S3
- Create Database Backup
WP Database Backup plugin helps you to create Database Backup easily on single click.
Backup automatically on a repeating schedule
- Download backup file direct from your WordPress dashboard
- Easy To Install(Very easy to use)
WP Database Backup is super easy to install.
- Simple to configure(very less configuration), less than a minute.
- Restore Database Backup
WP Database Backup plugin helps you to Restore Database Backup easily on single click.
- Multiple storage destinations
- Store database backup on safe place- Dropbox,Google drive,Amazon s3,FTP,Email
- Reporting- Sends emailed backups and backup reports to any email addresses
- Exclude Table
- Database backup list pagination
- Search backup from list(Date/ Database Size)
- Sort backup list (Date/ Database Size)
- Save database backup file in zip format on local server And Send database backup file to destination in zip format
Few of the Key Features
- Database Backup easily on single click.
- Restore Database Backup easily on single click.
- Store database backup on safe place- Dropbox,Google drive,Amazon s3,FTP,Email.
- Search and sort database backup feature.
Get Pro ‘WP All Backup’ Plugin
WP All Backup will backup and restore your entire site at will, complete with FTP & S3 integration
- Complete Backup
- Create Database Backup easily on single click.
- Autobackup Backup automatically on a repeating schedule
- Backup Listing : Pagination.
- Manual backup
- Multisite compatible
- Backup entire site
- Exclude specific folders and files
- Downloadable log files
- Simple one-click restore
- Set number of backups to store
- Automatically remove oldest backup
- Multiple storage destinations
- FTP integration
- Dropbox integration
- Google drive integration
- Amazon s3 integration
- Email Notification
- Reporting- Sends emailed backups and backup reports to any email addresses
- Backup zip labeled with the site name(Help when backing up multiple sites).
- Select Backup Type: Only Database,Only Files, Complete Backup
- Inline Help
- Search backup from list(Date/Size)
- Sort backup list (Date/Size)
- Easy To Install(Very easy to use)
- Simple to configure(very less configuration).
- Clone Site
- Move Site
- Change Backup folder name
- Uses zip and mysqldump for faster back ups if they are available.
- Exclude Tables from your back ups.
- System Check (i.e backup folder permission, execution time etc)
- Disk space checkup(memory available).
- And More….
Remove/Replace Content from the WordPress Database
Posted by Jeff Starr • Updated on February 7th, 2017
A useful tool to have in in your WordPress toolbelt is the ability to quickly and easily search for, find, and replace specific strings of text directly from the MySQL database. We can do this by entering SQL queries either directly or through one of those handy interface applications like phpMyAdmin, which seems like one of the most prevalent PHP applications on servers today.
Before we get started, remember to
backup your database before making any changes!
- rel="external nofollow"
- rel="nofollow external"
In addition to these instances of the attribute, our custom query will also match any instances of other attribute values besides “ external ”. A couple of things to keep in mind about SQL selectors:
All right, let’s look at some of the useful things we can do using a few custom SQL queries..
Display all instances of a given text string
Before making any sitewide changes to your database, it’s a good idea to simply search and find all instances of a given text string. So in our example, we are looking for three different nofollow patterns, as described above.
Using phpMyAdmin, click on the “SQL” tab and enter the following SQL query:
This query selects everything from the wp_posts table that matches any of our three target cases. Note that if you are using a unique table prefix (i.e., anything other than “ wp_ ”), you will need to change the “ wp_posts ” in the first line to match.
From here, you can check the results by evaluating the overall number of posts and “zooming in” on any specific post(s) of interest. To search for a text string in a different table, replace “ wp_posts ” with something else, such as “ wp_comments ” or whatever you like.
If desired, we could locate all instances of each specific pattern individually using each of the following queries:
So with our nofollow example, let’s say we have 100 posts that contain some instance of the target string. We no longer want these attributes polluting our post content, so let’s remove them..
Remove all instances of a given text string
Once we are ready to actually remove all instances of the target string in our post content, we run the following three queries, either all at once or one at a time:
Bam! Just like that, we have removed all of those silly nofollow attributes throughout our entire site without affecting anything else. It’s as if they never existed in the first place. So much power! 😉
How does it work? Easy: each command is saying, “replace all matches in the post_content field with exactly nothing.” As far as I know, there is no way to combine these queries into a single command, so if there are any SQL wizards reading, please enlighten.
As you may guess, the general pattern for removing any text string from all post content in the database looks like this:
And from here, we can just as easily replace any matching text..
Replace all instances of a given text string
Lastly, let’s look at how to replace our text string with some different text. We’re actually using the same query as in the previous section, only instead of empty quotes in the second argument, we’re going to add our replacement text.
Let’s say that, instead of deleting our nofollow attributes, we want to replace them with “dofollow”. We wouldn’t actually do this on a production site because dofollow is not a valid value for the rel attribute. But for the sake of our hypothetical, let’s just do it anyway. Here’s how it looks:
Same idea as before, only now we can see that anything can be used for the replacement text, even imaginary attribute values 😉
And finally, here is the general formula for replacing post content via SQL :
And of course keep in mind that this query may be modified as explained in the article to search for, remove, and replace any content – markup, text, special characters – in your WordPress database. For site admins and serious bloggers, this is an excellent way to speed up maintenance and get things done.
Of course, there’s probably a plugin that will do all of this for you 😉