bavatuesdays

Sooner or later we had to do it on UMW Blogs, and over a week ago we made the jump to a multi-database setup for our WordPress Multi-User environment. We have a subscription to the WPMu Dev Premium service, which gives us access to the Multi-DB script. This code enables you to break one database up into many, allowing your site to scale for a whole lot of blogs… it might also improve performance significantly. Given the fact that UMW Blogs got hit quite hard the first week of classes, I spent the following Sunday working with the ever-brilliant Zach Davis of Cast Iron Coding upgrading our database structure. I enlisted Zach’s support because a) he is much smarter than me about this stuff (and in general), and b) the documentation for making the move from a single to a multi-database environment was rather fragmented and scarce, which made me a bit uncomfortable with experimenting 🙂

So, in short, what follows is my attempt to consolidate documentation for this process in one place. I can’t vouch that everything is 100% accurate or will definitely work on your install, rather I’ll depend on folks out there to correct any oversights, mistakes, or outright lies I perpetrate here. I think of this tutorial as a much needed start to documenting this process step-by-step, something which is unfortunately non-existent currently.

1. First things first, if you want to get the scripts and code that powers this process, you’ll need to get a subscription for the WPMu Dev Premium service. And if you’re running a big WPMu site and don’t have your own group of in-house programmers, this may prove an affordable alternative because as of now I’m not aware of a free alternative. Also, as of the writing of this post the plugin works up to WPMu version 2.6.5.

2. Once you have the script files, go ahead and create your databases.  But before you do, a question will immediately arise— how many databases should you have: 16, 256 or 4096? This really depends on how many blogs you see in your WPMu site’s future, we only went up to 16 dbs (which is actually 18, but more on that soon) because at 2400 blogs over the course of two years our growth will remain both steady and limited over time (we’re a small school). We really don’t expect 50,000 to 100,000 blogs anytime soon–it would take us almost 50 years to get to the lower end of that spectrum, and while  I love WPMu, I’m  not sure it will be the platform of choice in the year 2059. However, if you foresee 50,000 to 100,000 blogs/users in a shorter period of time—say a year or two—I would opt for 256 databases, and if you plan on growing to much more than that (say 100,000+), I would imagine 4096 databases would be what you want.

3. After you have figured this out for yourself, you can create your databases, my example for this tutorial will be with the 16 databases option, but it should be relevant to any configuration. The first potential issue you may have will be with the actual naming of the databases. And while the documentation suggests you can name your databases along the lines of any convention you want, we found that not to be true. In fact, we tried to just number them consecutively, and this created an error. As of now, you should really stick to the alpha-numeric database number convention when creating new databases that you’ll get here in the DB_SQL tool. If you are like me, you will grab something like the code for the first 16 lines below, I used wpmu_ as my database name prefix.

CREATE DATABASE `wpmu_0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_4` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_5` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_6` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_7` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_8` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_9` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_a` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_b` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_c` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_d` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_e` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_f` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `wpmu_vip1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


You might notice there are actually 18, not 16, databases here. I added the last two based on the code from the previous 16. The reason for these two extras is important, the Global database has all the tables for global plugins, site meta, etc., the VIP database allows me to put the main blog for UMW Blogs in its own database which should optimize its performance given it is probably the most heavily trafficked blog in the environment. So, there’s the logic behind having, in reality, 18 databases, and now that I think about it, I should have created another VIP blog database for the UMW Blogs tags blog–might have to see if I can do this after the fact–anyone know if this is possible?

Once you got this code, you can create the new databases using phpMyAdmin or use the command line—if you aren’t afraid of it like I am 🙂

After you have created your databases, you need to make sure you have one, über admin user who has permissions for all the databases you just created. This is probably much easier to do in command line, but you’ll have to do this in CPanel if you work like I do, something I wouldn’t recommend for 256 or 4096 dbs —any one wanna share the command line code?

4) OK, once you databases have been created, and the permissions are set, you are going to need to setup the db-config.php file. This is a key part of the process, so be sure to take a little time with this and make sure everything is right. Additionally the dc_ip terminology can get confusing (dc stands for data center, and I believe it is misleading).

This is where you choose how many databases you want to scale to:
// 16,256,4096
define ('DB_SCALING', '16');

This is where you specify the IP address of your server. Don’t be confused by the DC (or data center abbreviation), also keep in mind that unless your WPMu is spread out over several IP addresses, you should only have to specifiy on IP address here–am I right with this?
// Usage: add_dc_ip(IP, DC)
// EX: add_dc_ip('123.123.123.', 'dc1');
add_dc_ip('123.123.123.', 'dc1');

This is where you specify your global tables name, we named ours umwblogs_wpmu_global, this is your own call, and the umwblogs_ is our overarching db prefix.
// Do not include default global tables
// Leave off base prefix (eg: wp_)
//
// Usage: add_global_table(TABLE_NAME)
// EX: add_global_table('something');
add_global_table('umwblogs_wpmu_global');

This is where you specify the details of your server, including db name, IP, HOST, DB Name, User, PW). We didn’t include a second DC IP address nor a Lan_Host, and everything still worked fine–once again, am I being misleading here? Also, the add_server terminology is odd as well, you have already created databases, and I’m not sure they can be understood as servers, but disregard this—it is inexact language that kills us in this field—and I am a large part of the problem 😉

// Usage: add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
// EX: add_db_server('wpmu_global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser', 'globalpassword');
add_db_server('global', 'dc1', 1, 1,'64.120.23.72','192.168.0.101', 'wpmu_global', 'db_user_name', 'db_user_name_pass');

add_db_server(‘0’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_0’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘1’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_1’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘2’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_2’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘3’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_3’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘4’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_4’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘5’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_5’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘6’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_6’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘7’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_7’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘8’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_8’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘9’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_9’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘a’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_a’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘b’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_b’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘c’, ‘dc1′, 1, 1,’64.120.23.72′,’ ‘wpmu_c’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘d’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_d’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘e’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_e’, ‘db_user_name’, ‘db_user_name_pass’);
add_db_server(‘f’, ‘dc1′, 1, 1,’64.120.23.72′,’ ‘wpmu_f’, ‘db_user_name’, ‘db_user_name_pass’);

add_db_server(‘wpmu_vip1’, ‘dc1′, 1, 1,’64.120.23.72’, ‘wpmu_vip_1’, ‘db_user_name’, ‘db_user_name_pass’);

[Notice in the above code I got rid of the second IP address and LAN Host.]

Moving on, in the next area you want to be sure to specify the correct blog ID of the blog (or blogs) you want to have their own VIp database.
// Usage: add_vip_blog(BLOG_ID, DS)
// EX: add_vip_blog(1, 'vip1');
add_vip_blog(1, 'vip1');

5. Now that’s it for the db-config.php file, but before I move on–and skip this step if you are not interested in changing code— I just wanted to note that Zach actually re-wrote the PHP code in this file to make it a bit more streamlined, I offer it up here to ask if it makes sense, and might be integrated into the next version of the plugin, it seems cleaner, but I might be missing something. Below is Zach’s code for adding the db_servers and vip blog:

$umwBlogs_host = 'localhost';
$umwBlogs_user = 'db_user_name';
$umwBlogs_pw = 'db_user_name_pass';
$umwBlogs_databases = array(
'global',
'0',
'1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'a',
'b',
'c',
'd',
'e',
'f',
'vip1',
);

// create the rest
foreach($umwBlogs_databases as $umwBlogs_dbKey) {
$dbName = ‘umwblogs_wpmu_’.$umwBlogs_dbKey;
add_db_server($umwBlogs_dbKey, ‘dc1’, 1, 1, $umwBlogs_host, $umwBlogs_host, $dbName, $umwBlogs_user, $umwBlogs_pw);
}
add_vip_blog(1, ‘vip1’);

I’m definitely interested in what you think of this?

6. Now that this is all setup, we need to do one more bit of configuration in the move-blogs.php file:

///DB Settings
$dbname = 'umwblogs_umwblogs'; //This is your current database
$blog_table_prefix = 'wp_'; //Prefix of your wpmu blog tables, most likely this won't need to be changed
$newdb_prefix = 'wpmu'; //This is the prefix of the db's you're moving your tables into - we assume they are all the same, if not, you're in trouble

//We need info to connect to the databases
$dbhost = ‘localhost’;
$dbuname = ‘db_user_name’;
$dbpass = ‘db_user_name_ pass’;

//How many db’s are you moving into (16, 256, or 4096)?
$db_scaling = ’16’;

7. Once you have done this, you are ready to take the plunge and try and move all your blogs to the new database structure. If you do this, and haven’t backed up your existing database four times, you are nuts. Now that I have said that, the next step is to call up the move-blogs.php file (which you shoud have placed in the wp-content/scripts directory) in your browser and see if it finds your databases, and everything has been created correctly, etc.

We didn’t create our databases the first time around because I was under the false impression the db-config file would do this, but from my experience it doesn’t, and here is the error we got.

Once we created them and named them according to the convention outlined above, we were on our way, but there was one more issue worth noting. Before going to the move-blogs.php script, there are actually two typos that could really throw you off on lines 83 and 85 on the move-blogs.php file:

...'move_blogs.php?table=copy'>click hereclicking here