Multi-DB is one of those plugins that becomes an absolute necessity for any Multisite network that is seeing or expecting substantial numbers of blogs/sites. It is powerful in what it does (spreading the WordPress tables across several databases) but as with anything else, getting it set up correctly (and adequate preparation for installation) is essential to success. Please note that this is not your normal “drop the files here” plugin, but sysadmin level stuff. That being said, don’t be scared off. With the right mindset you can do it!
The key to success with this plugin is to have the settings edited and triple-checked BEFORE uploading to your site. If you’ve not attempted this before, I’d recommend reading through this entire process first. Then read it again and follow along. Finally, go back through and make sure each setting is correct. Then you should be ready for an install without incident!
This guide provides instruction and illustration for the entire process. It’s divided across multiple pages with separate navigation for each section provided at the bottom of each page so you can refer back to specific portions later. Let’s get started!
Step 1: Decide how many databases you want (16, 256, 4096)
So, how do you know that this plugin is necessary for your install? Well, there are several factors to consider beyond the scope of this walkthrough, but as a basic guideline:
1-5,000 blogs/sites you should be fine with your WordPress default database
5,000 – 50,000 blogs/sites – go with 16 databases
50,000 – 100,000 blogs/sites – use 256 databases
100,000+ blogs/sites – use 4096 databases
*Important note, there’s no performance hit for using 256 databases over 16. If you’re expecting massive growth planning ahead at this point will save you additional work down the road. In fact unless you already know you won’t be growing past 50,000 blogs/sites it’s best just to do the 256 just in case. Using 4096 DBs is usually overkill unless you plan on being the next wp.com or edublogs.org! As stated earlier there are several factors to consider, this is meant to be a general guide. It’s really a decision specific to your site needs.
Step 2: Create the databases in mySQL
(Use WPMU DEV tool) http://premium.wpmudev.org/db-tools/db_sql.php
We’ve provided an excellent tool for you to use to get your databases created and ready. While you can name and create these tables anything you like, we know from experience that ordering them the way we’ve provided with this tool will make management easier down the road and it also lets you more easily use the included template db-config files for 16 and 256 databases.
Select the DB SQL link and type your database name (same name as your default WordPress DB) followed by an underscore (_). Choose the number of databases and click “Submit”.
This will give you the instructions needed for mySQL to create your new DBs. (You’re welcome :D)
There is one other table you’ll need to create (2 if you intend to provide a VIP database). To create these tables you’ll just want to copy one of the lines generated from the database tool and use the same root database name with additional tags of “global” (and vip1, vip2, etc if needed). So you’re final output for this line will look something like this:
CREATE DATABASE `dbname_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
A note about VIP databases: These allow you to place a blog in a specific database. Unless you have a blog/site that gets a ton of traffic and you want to put it on another physical server for performance reasons, then it’s not really worth it to bother with that feature. Again, the vast majority of installs do not need to use VIP blogs, so skip over it unless you are sure you need to use this feature. Each VIP blog/site will need it’s own database which is why you’d use vip1, vip2, etc. You’ll use the add_vip_blog() function in db-config.php to move specific blogs to these databases.
Now, you need to create the databases either via command line (hardcore) or through phpMyAdmin. Log into your server and if you’re using phpMyAdmin you’ll enter the instructions generated above here:
Just select the “SQL” tab and enter in the text area provided.
Select “Go”
You should now see loads of tables where there used to be only one or two.
One final step is to make sure you have assigned a username and password to be associated with each database. This could be the same as your username and password for the original WordPress database. It’s up to you, just make sure that there IS a username and password associated with each one and note what it is. You’ll need this information for the next step.
Note that if your host or server is using a control panel like cPanel or they run DB servers externally (ie – shared hosting, grid hosting, etc), you may not have permissions to create DBs via an SQL script. If that is the case, first see if you can login as root and run it via command line, or you may have to create the DBs manually one at a time through the control panel. Though if you are big enough to need Multi-DB, you really should be using a VPS or dedicated server that gives you root access anyway.
Step 3: db-config.php
Now that are databases are created and in place, it’s time to start configuring the plugin to handle the heavy lifting. We’ve got 5 separate areas to configure here (6 if you’re using VIP databases)
- Line 10: Change the DB_SCALING to however many databases you’re using (16, 256, or 4096)
- Line 16: Don’t let this scare you. It just needs your server’s IP address.
- Line 25: Provide the same name you assigned for your global database in Step 2. example ‘dbname_global’
Note:
- We have provided the ability for those who have their database hosted on a different IP from the WordPress install (a remote server). For many users, this is not an issue.
- If your database is at the same IP as your WordPress site, you can ignore the need for a remote server entirely.
We’re almost there! Now, go back to the WPMU DEV DB Tools and select ”DB Servers” http://premium.wpmudev.org/db-tools/db_servers.php
Fill in your DB Name: (same as in step 2), Your DB Username and Password, For DB Local Host enter you server IP again. If you’re using a Remote Host enter that here, if you’re not just leave it blank. Finally select the number of databases you created and click “Submit”.
Your output should look similar to the following:
add_db_server('0', 'dc1', 1, 1,'remotehost','localhost', 'dbname_0', 'dbuser', 'dbpass');
The actual values will be the ones you entered. If you did not enter anything for the remote host, that value will simply be empty.
4. You’ll want to copy and past these lines directly above the commented out “VIP Blogs” section.
5. Finally, we’ll add the line for our global database, directly above the lines you just pasted. Assuming you used “global” for your global database, this line will look the same as the others we generated. Following the pattern here it’ll look similar to this (again, with the values you entered):
add_db_server('global', 'dc1', 1, 1,'remotehost','localhost', 'dbname_global', 'dbuser', 'dbpass');
Optional: 6: To add a blog/site to your VIP database simply follow the example given at the very bottom of the file. To add your blog ID of ’1′ to the vip1 database just enter
add_vip_blog(1, ‘vip1′);
Create a new line for each of the VIP databases you need. Paste them all in directly above the ‘?>’ which closes the file.
Step 4: move-blogs.php
- Open move-blogs.php and scroll down to line 19 for the Config section.
- Configure lines 19-29 with the correct information as asked for in the file.
- Remember that these configurations are all for your original WordPress database – not the new ones we are creating.
Step 5: Uploading the Files
NOW it’s time. Here is where all the fruits of our labor pays off. You did double and triple check everything above, right?
Upload both the db.php and db-config.php files to your wp-content directory.
Let’s upload move-blogs.php first. We recommend creating a folder inside wp-content called “scripts” and uploading the file there. It really doesn’t matter though, as long as you remember where you put it as we’ll need to head there in our web browser next.
Open up your web browser and go to move-blogs.php
There are 9 instructions at the top of this page. Really, this is more about verifying that everything is correct. Check for green “exists” under the new db column on the first page load. When you’ve verified that everything looks good and then click the link on step 4 of this page.
Once completed, use the link provided to refresh the page. You should now see “table in new db” under the status column for each row. If all’s good there then you have successfully completed your multi-db installation! Congratulations!
Upgrade Instructions
To upgrade from one version of Multi-DB to another, unless otherwise noted, you can simply replace db.php. Basically, you just copy the db.php from the more recent version into your installed folder.
