WPMu Multi-DB Tutorial

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 🙂

Image of phpMyAdmin Multi-DB

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.

Image of DB error

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

Note that the first and third lines above have the move-blogs.php file with an underscore (move_blogs.php) rather than a dash---that threw us off for a bit before Zach found the typo, so change this before you start the move--it will spare you some potential heart failure. It should look like this:

...'move-blogs.php?table=copy'>click hereclicking here

After that, if the gods are on your side, your entire database will be broken up into all kinds of pieces, and everything will magically work fine. One thing I noticed was that my plugins were all good, but the mapped domains (we had a few) broke after this upgrade, so keep that in mind. Good luck!

OK, I have to go home now, it is a snow day here in Virginia :)

This entry was posted in wordpress multi-user, wpmu and tagged , , , , , , , , , , , , . Bookmark the permalink.

25 Responses to WPMu Multi-DB Tutorial

  1. Andrea_R says:

    I took the morning off and LOOK what you did. 😀

  2. Bill007 says:

    Well thanks to Andrea_R I have something that resembles a multi database step by step instruction walkthru Thanks Ill let you Know How I go
    Regards Bill

  3. Matt says:

    This is awesome, Jim. Thanks so much — I’m setting up a new project and will be sure to follow your lead here.

    • Reverend says:

      @Bill007
      Let me know where I steer you wrong, and and all feedback to make this work, or show where it doesn’t would be appreciated.

      @Matt,
      This may be useful, but I’m guessing the syndication on the cheap is where you”ll find really interesting, this database stuff is boring, but fascinates me almost as much as domain mapping 😉 Syndication is what will glue a community together.

  4. PerS says:

    Thank you for writing this, I was just about to do a multi-db install (trying to grok db-config.php) when I found this guide.

    In 6. I believe there’s a typo. $newdb_prefix = ‘wpmu’; should be $newdb_prefix = ‘wpmu_’; (or am I wrong?)

  5. Reverend says:

    PerS,
    I believe when we ran the move-blogs.php script we did it without the hash. You may want to ask on the forums for a second opinion, but I’m pretty sure.

  6. so… what goes in the Global Tables section? it looks like you put a database name there, but isn’t it supposed to be a list of tables to be treated specially? What makes a table global? which tables should be added here? If I read correctly, your global tables section is likely just ignored because you won’t have a table name matching the database name you provided. Or am I misunderstanding what that means?

    in the add_db_server section, why did you drop the LAN_HOST parameter? did it bork? is it unnecessary? the docs say to add it, but don’t really say why.

    I’m also confused in that section, what the “global-db”, “globaluser” and “globalpassword” parameters are for. Are they to identify the database name, username and password to use for that database server? What does “global” mean in THIS instance. It smells different than the previous use of “global” – tables etc…

  7. oh. wait. the modified code that Zach wrote does include the LAN_HOST parameter. Ignore that part of my previous comment.

  8. I just ran a test migration on my desktop, and it appears to have been successful. I now have 17 databases (in addition to the old überdatabase) with what appear to be properly populated tables.

    I’ll plan a migration of the server ASAP – it’s comforting to know that if it borks, I can just back out the multi-db files and go back to single überdatabase without losing anything.

  9. Reverend says:

    D’Arcy, I had 18 databases, did you have a VIP database 16 others and a global? The global d for plugins and the like so they can spread across all the different dbs. The VIP might be good for the tags blog and your home blog. The other 16 you can just leave to chance.

    I’m also confused in that section, what the “global-db”, “globaluser” and “globalpassword” parameters are for. Are they to identify the database name, username and password to use for that database server?

    They are for a uber db-config file that all the dbs can call. I just used my existing db name, db username, and password, but you have to set this up in the move-blogs.php file. But it sounds like you did this cause it worked. Let me know where I can tighten this baby up.

  10. Alex says:

    Hello Jim,
    First thanks for opening my eyes to WPMU and Buddypress. Saved us from an ugly Joomla hack.

    My question for you is what can a 16DB structure realistically scale to as far as users? More importantly what kind of hardware are you running this install on?

    Best,
    Alex

  11. Reverend says:

    Hey Alex,
    Glad to be of service. I kept our install to 16 dbs because for 256 you would be scaling to the 10s of thousands and I don;t think, given the size of our school, we will get there anytime soon. So I decided to go smaller and scale up if need be.

    We have this running on a dedicated Linux server with PHP 5+ and MySQL 5+ Apache, etc. The exact specs of the server I don;t have off hand, but can get them if you need it. we also do nightly backups on a remote server which is part of the sleep soundly program, and it is working out quite well so far.

  12. Alex says:

    Thanks for the quick feedback Jim. I was wondering what kind of a load this would take because we have a client considering this for ~10,000 user community with WPMU/Buddypress (not so much blogs). Big day is 1,000 members logging in.

    Curious if it can start out on the dedicated linux box that also hosts their e-commerce without impacting performance significantly. 4gb ram quad core linux w/latest apache,php,mysql as well as an empty hard drive.

    Thanks,
    Alex

  13. Andrea_R says:

    Alex, that sounds like enough to start – my only worry is BuddyPress. You have to cache & compress and optimize that sucker right from the start.

  14. Reverend says:

    Alex,

    Meet @Andrea_R, she rules 🙂 I’m sure you have seen her around the forums, but she is the real deal, and is intimately familiar with this stuff. So, I would definitely look into optimizing BuddyPress—how to go about that is another question though.

    Thanks for chiming in Andrea, when setups get that big—I am out of my element.

  15. Alex says:

    Nice to meet you Andrea. I think once we think this through a little more I will be giving you a call. 🙂

    Best,
    Alex

  16. John says:

    Could anyone post a video tutorial for installing multi-db in a fresh wordpress mu installation? I already bought a domain and hosting account at ixwebhosting and installed wordpress mu version 2.8.4a. I really need some help on this task. Thank you guys for your consideration 🙂

  17. Girish says:

    when i install multi-db plugin it shows Fatal error: Call to undefined function add_dc_ip() in /home/mumbaie1/public_html/gtubook.com/wp-content/plugins/1591308346_multi-db-2.6.0/db-config-sample-256.php on line 16

    how can i resolve this? is there any versioning problem? please guide me

  18. Marco says:

    Hi sorry my english is not good ok ?
    i m Marco i have wordpress 2.9.2 and i use the multi-db i create 256 db and move all blogs in this db but i dont no how connecting wordpress to all 256 db?
    Please if have manual step by step script post it.
    Please help me is very important.
    tanks to all frend, good day.
    Marco

  19. cvh says:

    After installing multi-db

    can we safely remove db-config.php, db.php, move-blogs.php from /wp-content/

    Pls reply

    • Reverend says:

      I’ve stopped using multi-db, I now use SharDB, which is similar but it frees me from the WPMUDev Premium nightmare. As far as I remember though, you need at least db-config.php and dp.php (you can get rid of more-blogs.php). Best bet thought is to check out the forums of the plugin developer to be sure cause I haven;t been following the development of this code for over a year.

  20. cvh says:

    correct
    WPMUDev Premium nightmare is a hell.
    The multi-db is also hell
    I had to spend some 20 hours over 3 days to debug and install and use it.

  21. Lindeni says:

    I have followed this guide and it worked except that for one blog it missed a few tables, this blog is the only one that had a mapped domain. 5 tables were not created and when I tried to access the blog using its sub and mapped domain, I go a wordpress database connection error. WordPress suggested I repair the database tables, I attempted the repair as proposed but only the tables of this blog failed to repair. I had to delete the blog and recreate it from a backup.

    What do you think was the cause of the failed database tables to be moved? Does the Multi-Db plugin has issues or conflicts with the Domain Mapping plugin by WPMUDev?

    • Reverend says:

      Lindeni,

      I have to be honesxt, it has been so long since I thought about the particulars that I would be of little use to you in this. Apologies. Also, we moved to the SharDB setup years ago, so I don;t even have a multi-db instance to look at.

Leave a Reply

Your email address will not be published. Required fields are marked *