search

wpmudev / multi-db

Allows you to partition your large WordPress Multisite database across multiple servers for scalability.
GNU General Public License v2.0
90 stars 64 forks source link

readme

Multi DB

INACTIVE NOTICE: This plugin is unsupported by WPMUDEV, we've published it here for those technical types who might want to fork and maintain it for their needs.

The standard WordPress Multisite installation requires only one database – which is great for hobbyists or people just looking to host a few dozen or few hundred sites.

When you get past a few hundred sites, a single database can get cramped.

Multi-DB works to make better use of your server by creating either 16, 256 or 4096 database tables and spreading new blogs evenly across your system.

If you’re in need of a tool that spreads sites on your network across multiple database tables, Multi-DB has been tested and used on thousands of sites since 2008.

It is a fantastic foundation for anyone looking to spread their content across more than one database.

Usage Docs

Important: Please note that this is not your normal “drop-the-files-here” plugin, and it is not to be uploaded to wp-content/plugins

This is sysadmin-level stuff. But don’t be scared off, with the right mindset (and server configuration) you can do it!

First, is your server setup right for this?

Preparation is Key

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 with adequate preparation for installation is essential to success.

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, we 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!

STOP! Before going any further, please make a full backup of your multisite so, just in case things go kaflooey, you can restore it!

Creating Your Databases

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 here’s a basic guideline:

There’s no performance hit for using 256 databases over 16. But 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 wordpress.com or edublogs.org!

As stated earlier, there are several factors to consider and this is meant to be a general guide. It’s really a decision specific to your site needs.

Decide if you need VIP databases

This plugin has a cool feature that allows for VIP databases. It enables you to place specific blog(s) in specific database(s).

Create the mySQL command

While you can name your databases anything you like, we know from experience that ordering them as we have in this walkthrough will make management easier down the road, and it also lets you more easily use the included db-config.php file.

We’ll get to that file in the next step below, but first we need to get the proper SQL command set up. We’ve provided an easy tool for that, you can check it under /db-tools/ within this repository.

IMPORTANT: Once you upload the db-tools directory to your site, any logged in user will be able to access it.

Be sure you’re on the correct tool for database creation by clicking the DB SQL link at the top of the page.

M

In the textarea below, you’ll now see all the instructions mySQL needs to create your new DBs.

M

Well, almost all; there is one other line you need to add manually to create the required global database.

Your final output for the required global database will look something like this:

CREATE DATABASE 'dbname_global' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Did you also decide to create optional VIP databases for some of your blogs/sites as mentioned above? If so…

The final output for each of your VIP databases would look something like this:

CREATE DATABASE 'dbname_vip1' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Be sure you’ve added these extra line(s) directly beneath those created by the DB SQL tool.

Create the Databases

Now, you need to create the databases, either via command line (hardcore) or through phpMyAdmin (much easier).

Log into your server and, if you’re using phpMyAdmin, be sure you’re in the root and click the SQL tab. Then paste in the SQL command you just generated (the contents of the textarea from the DB SQL tool), and click the “Go” button.

Note that the screenshot below does not include any lines for VIP databases. However, if you are creating some, they would be there.

M

Now when you click the “Databases” tab in a tool like phpMyAdmin, you should see loads of new databases where there used to be only one or two.

M

Note that even though your new databases may appear instantly in phpMyAdmin, it can sometimes take several minutes – even hours – for them to appear in cPanel.

In some instances, you may even need to wait until the next day. This is beyond our, and your, control. Patience, Grasshopper. :)

Assigning Users & Passwords

A username and password must be associated with each database.

You can create and assign usernames & passwords either directly in phpMyAdmin, or use the MySQL Databases section in your cPanel.

Creating Users & Passwords in phpMyAdmin

If you have CREATE USER privileges in phpMyAdmin, you can use any of the following commands in the SQL tab (the same place where you just added the command to create your new databases).

To assign all privileges on all databases to an existing user (probably you), you can use this command:

GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost'; FLUSH PRIVILEGES;

Or, if you want to create a new username/password combination & assign all privileges on all databases to that user, use this command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost'; FLUSH PRIVILEGES;

The asterisks in the above commands represent, respectively, all databases & all tables.

If you are using VIP databases, and want to create and grant privileges to different users in each of those, you’d first create the user(s) and their password(s), then specify the username for each database using a command like this (note the database specified instead of the first asterisk):

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'pass2';
GRANT ALL PRIVILEGES ON dbname_vip1 . * TO 'user1'@'localhost';
GRANT ALL PRIVILEGES ON dbname_vip2 . * TO 'user2'@'localhost';
GRANT ALL PRIVILEGES ON dbname_global . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_0 . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_1 . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_2 . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_3 . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_4 . * TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON dbname_5 . * TO 'username'@'localhost';
...
FLUSH PRIVILEGES;

Be sure to include an additional line for each database as indicated by the ellipsis (…).

In each of the above cases, replace the words user1, user2, pass1, pass2, username & password in the code with the actual usernames & passwords. :)

Also remember to change dbname_0, dbname_1, etc, to the actual names of your databases.

For more on this, see this handy tutorial at DigitalOcean.

Creating Users & Passwords in cPanel

If you do not have CREATE USER privileges in phpMyAdmin, or just don’t want to mess around in there, you can add the username & password to each new database in your cPanel.

To do that, scroll down to the Databases section on the main page of your cPanel, and click the MySQL Databases link.

On the next screen, fill in the fields in the Add New User section to create any new user(s) that you may want.

Then, assign existing user(s) to the appropriate database(s) in the Add User To Database section.

When you click the Add button to add a user to a database, you’ll get a screen where you can assign the privileges for that user & database.

Simply check the All Privileges box and click Make Changes.

You’ll need to add a user to each database and grant permissions for each one in the manner detailed above.

So, if you have added 16 databases, you’ll do this 16 times; for 256 databases, you’ll do it 256 times; 4096 databases, well, you get the idea. :)

Configuring the Plugin Files

Your databases are created and you’ve assigned usernames & passwords to each one?

Excellent! Now it’s time to start configuring the plugin to handle the heavy lifting. We’ve got several areas to configure in 2 different files.

So if you haven’t already downloaded your copy of Multi-DB by clicking the big button at the top of this page, please do that now.

Configuring db-config.php

Unzip the file you just downloaded, and open db-config.php in a text editor like Sublime Text

The first thing to do is enter the number of new databases you just created, and the IP address of your multisite.

Next, we want to add in the new global database name.

The other global table lines in there are required by the plugins specified.

The next thing we want to configure in this file is the DB Servers section.

To do that, we’re going to use another of the online tools we have provided: http://yourdomain.com/db-tools/db_servers.php

Click on that link now, and be sure you’re on the correct tool by clicking the DB Servers link at the top of the page.

In the textarea below, you’ll now see all the instructions you need to paste in the DB Servers section of db-config.php

Your output should look similar to the following:

Multi-DB Servers Created

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.

Just as we did when creating the new databases, we want to add a special line here too for our global database.

add_db_server('global', 'dc1', 1, 1,'','111.222.333.444', 'dbname_global', 'username', 'password');

Did you also create VIP database(s)? If so, you’ll want to add a line here too for each one.

add_db_server('vip1', 'dc1', 1, 1,'','111.222.333.444', 'dbname_vip1', 'username', 'password');

If you have created more than one VIP database, add a similar line for each one. Change vip1 and dbname_vip1 in each new line to the corresponding values (vip2, dbname_vip2, etc).

Important: If you have assigned different username(s) & password(s) to different databases in the previous steps, be sure to edit them here now for each of those databases.

All done? Please double-check to make sure that you have the correct number of lines in there corresponding to the number of databases you created (16, 256 or 4096), as well as one for the global database, and any VIP databases you may have created too.

Now copy the entire contents of the textarea, and paste it in db-config.php at line 67, replacing all the existing examples there.

That section in your db-config.php should now look like this:

Again, this screenshot does not include any lines for VIP databases. But if you created some, they should be there.

The last section we want to configure is the VIP Blogs section. This is where you will actually designate which database(s) should be used by which blog(s).

If you did not create any databases for VIP blogs in the previous steps, you can skip this part.

To designate a blog/site as a VIP blog/site and give it its own database, simply follow the example given at the very bottom of the file.

To add your blog with an ID of ‘4’ to the vip1 database, just enter the following:

add_vip_blog(4, 'vip1');

Create a new line for each of the VIP databases you need. Paste them all in directly above the ?> which closes the file.

Multi-DB VIP Blogs

Note that you can only designate a VIP database for your main site if the table names are prefixed with a number, like this: wp_1_posts wp_1_options etc...

However, if they do not have a numerical prefix as follows, then they cannot be moved from the global database (the table names are dependent on how/when multisite was installed). wp_posts wp_options etc...

All done with db-config.php? Save your file!

Configuring move-blogs.php

Now open move-blogs.php in your text editor.

This file is much simpler to configure as we only need to enter a few bits of data.

However, please take care to enter the data exactly as described below.

The screenshot below shows what the move-blogs.php file would look like with all the same sample data as given throughout this usage guide. Yours will, of course, contain your own data. :)

Multi-DB Files Uploaded

All done with move-blogs.php? Remember to save that file too.

Uploading the Plugin Files

NOW it’s time. Here is where all our labor finally pays off. You did double and triple check everything above, right?

There are 3 files we need to upload: the 2 you just edited – db-config.php and move-blogs.php – as well as the db.php file which does not require any edits.

Upload both db.php and the db-config.php that you edited to your wp-content folder.

You can upload the files either via FTP, or use the File Manager in your cPanel. The screenshot below shows the result of the upload via FTP using FileZilla.

Multi-DB Files Uploaded

Next, we want to upload move-blogs.php. We recommend creating a folder inside wp-content called scripts and uploading the file there. Some hosts may prevent direct access to your wp-content folder, in which case you can create or move your /scripts/ directory to the root directory of your WP installation. Your scripts directory will need execute permissions in order for this to work correctly.

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.

Final Step: Copy & Verify DBs

We’re almost done! This last step is the one that actually copies existing tables to their respective databases according to the configuration you just completed, and ensures that new blogs/sites get properly distributed amongst those new databases.

Open up your web browser and enter the full URL to the move-blogs.php file on your server. If you created a scripts folder and uploaded it there, the URL would look like this (of course, replace yourdomain.com with your actual site name):

http://yourdomain.com/wp-content/scripts/move-blogs.php

You’ll see that there are 10 instructions at the top of this page.

Multi-DB Move Blogs Tool

Simply follow the instructions at the top of that screen. The main things to check are:

When you’ve verified that everything looks right, click the link in step 5 of that screen. Once the process completes, click the link in step 7 to refresh the page. You should now see 'table in new db' under the status column for each row.

If all is 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 upload the db.php file from the new version to overwrite the old one.

If you have trouble with any aspect of configuration, or if you have a cool feature suggestion to make, please head on over to the community forums where support staff and other helpful members are waiting to lend a hand.