notes on converting ucalgaryblogs.ca to use multi-db

I followed Jim’s instructions to get UCalgaryBlogs.ca converted from using a single database (as is the default) to using multiple databases (17 separate databases now) via the premium.wpmudev.org Multi-DB code to prevent growing pains. The single database config is good for getting up and running, but with 300 blogs in the system, table explosion was causing grief on the shared MySQL database server – there were almost 3000 tables, which was making the automated backup script complain a bit.

While reading the documentation, I was rather confused by the term “global” – which appeared to be used in slightly different ways. Eventually, I plugged through, and got it working. The key is to test it all on a local copy of the database before running the migration script on the production server. Thankfully, the script doesn’t delete anything, so I was confident that if anything borked I could just back out the multi-db files and revert to single database config without losing anything.

“Global Tables” are tables that will be stored in a shared, common database rather than in each blog’s database in one of the 16 databases used by the multi-db code. These are things that are accessed by all blogs on the WPMU install, and include administrative stuff.

In the db-config-sample-16.php file that ships with multi-db, it also mentions “global-db”, “globaluser”, and “globalpassword” – those are just the database server address, username, and password to use when connecting to the “Global” database containing the “global tables”. They used “global-” in these parameters because it’s possible to configure each of the 17 databases to use different database servers, different usernames, and different passwords. For simplicity, I used the same database server and account for all 17 databases.

My db-config.php file was edited as follows:

<?php
//	Plugin Name: Multi-DB
//	Plugin URI: http://premium.wpmudev.org/project/Multiple-Databases
//	Author: Andrew Billits (Incsub)
//  Version: 2.7.0
//------------------------------------------------------------------------//
//---DB Scaling-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	16,256,4096
define ('DB_SCALING', '16'); // use 16 databases for the blogs
//------------------------------------------------------------------------//
//---DC IPs---------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_dc_ip(IP, DC)
//	EX: add_dc_ip('123.123.123.', 'dc1');
add_dc_ip('127.0.0.1', 'dc1'); // DN: change this to the IP address of your WEB SERVER
//------------------------------------------------------------------------//
//---Global Tables--------------------------------------------------------//
//------------------------------------------------------------------------//
//	Do not include default global tables
//	Leave off base prefix (eg: wp_)
//
//	Usage: add_global_table(TABLE_NAME)
//	EX: add_global_table('something');
// DN: These are tables that will be stored in the global database configured below (wpmu_global)
//     rather than in the 16 blog databases.
add_global_table('mass_mailer');
add_global_table('registration_log');
add_global_table('reports_comment_activity');
add_global_table('reports_post_activity');
add_global_table('reports_user_activity');
add_global_table('signups');
add_global_table('support_faq');
add_global_table('support_faq_cats');
add_global_table('support_tickets');
add_global_table('support_tickets_cats');
add_global_table('support_tickets_messages');
add_global_table('domain_mapping');
add_global_table('comment_activity');
add_global_table('blog_activity');
add_global_table('user_activity');
add_global_table('post_activity');

//------------------------------------------------------------------------//
//---DB Servers-----------------------------------------------------------//
//------------------------------------------------------------------------//
//	Database servers grouped by dataset.
//	R can be 0 (no reads) or a positive integer indicating the order
//	in which to attempt communication (all locals, then all remotes)
//
//	Usage: add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
//	EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
// DN: NOTE: change 'dbserver.com' to the address of the mysql server,
//   'username' to your mysql username,
//   'password' to the appropriate password.

add_db_server('global', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_global', 'username', 'password');
add_db_server('0', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_0', 'username', 'password');
add_db_server('1', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_1', 'username', 'password');
add_db_server('2', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_2', 'username', 'password');
add_db_server('3', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_3', 'username', 'password');
add_db_server('4', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_4', 'username', 'password');
add_db_server('5', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_5', 'username', 'password');
add_db_server('6', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_6', 'username', 'password');
add_db_server('7', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_7', 'username', 'password');
add_db_server('8', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_8', 'username', 'password');
add_db_server('9', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_9', 'username', 'password');
add_db_server('a', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_a', 'username', 'password');
add_db_server('b', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_b', 'username', 'password');
add_db_server('c', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_c', 'username', 'password');
add_db_server('d', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_d', 'username', 'password');
add_db_server('e', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_e', 'username', 'password');
add_db_server('f', 'dc1', 1, 1, 'dbserver.com', 'dbserver.com', 'wpmu_f', 'username', 'password');

//
//	Note: you can also place this section in a file called db-list.php in wp-content
//  EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser',  'globalpassword');
//------------------------------------------------------------------------//
//---VIP Blogs------------------------------------------------------------//
//------------------------------------------------------------------------//
//	Usage: add_vip_blog(BLOG_ID, DS)
//	EX: add_vip_blog(1, 'vip1');
// DN: I didn't add any VIP blogs.
?>

To create the databases, I used the script at http://db-tools.wpmudev.org/db.php and it generated the code below, which I ran on the MySQL server to create the databases:

CREATE DATABASE `wpmu_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
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;

After copying the db.php and db-config.php files into place as per Jim’s instructions, it all Just Worked™. New content was being stored in the 16 blog databases, and sites were behaving as expected, but with slightly less table explosion bloat as before.

One thing that makes me a little nervous is that the multi-db code isn’t core to WordPress, and is part of the premium.wpmudev.org subscription. This means that it can break in the future – there is no obligation for WordPress to continue to work with it, and if for some reason premium.wpmudev.org decides to abandon the plugin or stop updating it, I’m locked into WordPress 2.7. Neither of these made me lose too much sleep. Worst case scenario, I can always recombine the tables from all 17 databases back into a single überdatabase, assuming we haven’t outgrown the physical limits of a single MySQL database by then.