Richard's Weblog

Indeed it's very easy to get WPMU installed and up, but you'll find the number of tables in WPMU database increases every time a new blog is created. If you're providing public blogging service, the number of tables is going to 100, 1000, … very soon. Yes, WPMU is great, but the tables keep increasing, that may drive you mad. I was mad, at least.

In WPMU version 2.7, there are 9 global tables: blogs, blog_versions, registration_log, signups, site, sitecategories, sitemeta, usermeta, users. These 9 tables are fixed and serving for WPMU system globally. Every blog has its own 8 tables: comments, links, options, postmeta, posts, terms, term_relationships, term_taxonomy. These 8 tables are created over and over every new blog is created.

This designing is cool and useful from scalability view, especially for those big blogging service providers, wordpress.com is a good example. But WPMU uses only one database by default, it's hard to manage the excessive tables in one database, and the performance should also not be good. OK, here comes HyperDB, which can solve this problem. Actually HyperDB is derived from the code using by wordpress.com.

Before starting installing and configuring, we need to define a rule for databases. In this post, the rule is:

• One database (wpmu_db0) for the global tables and the tables of first blog. It can be called global database.
• Additional 3 databases (wpmu_db1, wpmu_db2, wpmu_db3) for the tables of other blogs, each database serves 2 blogs.

Of course, you can define your own rules based-on your needs. It's flexible.

We can start now. The first thing is to set up a standard WPMU using the global database in the installation wizard. After that, go to WordPress website and download HyperDB. The version I'm using is 2008-11-27. There are three files in HyperDB package:

• db.php, which needs to be uploaded to wp-content directory
• db-settings.php, which needs to be uploaded to the directory that holds wp-config.php
• readme.txt, oh, you know what it means

Then add the following lines near the top of wp-config.php

define('WPMU', true);
require('db-settings.php');

Then add the blow code at the bottom of db-settings.php

// a handy function for mapping blog tables to dataset
function add_blog_tables($ds, $blog_id){
    add_db_table($ds, 'wp_' . $blog_id . '_comments');
    add_db_table($ds, 'wp_' . $blog_id . '_links');
    add_db_table($ds, 'wp_' . $blog_id . '_options');
    add_db_table($ds, 'wp_' . $blog_id . '_postmeta');
    add_db_table($ds, 'wp_' . $blog_id . '_posts');
    add_db_table($ds, 'wp_' . $blog_id . '_terms');
    add_db_table($ds, 'wp_' . $blog_id . '_term_relationships');
    add_db_table($ds, 'wp_' . $blog_id . '_term_taxonomy');
}

// add databases
add_db_server('global', 0, 'mysql3326_1', 1, 1, 'localhost:3326', '', 'wpmu_db0', 'wpuser', 'thepwd');
add_db_server('s1', 0, 'mysql3326_2', 1, 1, 'localhost:3326', '', 'wpmu_db1', 'wpuser', 'thepwd');
add_db_server('s2', 0, 'mysql3306_1', 1, 1, 'localhost:3306', '', 'wpmu_db2', 'wpuser', 'thepwd');
add_db_server('s3', 0, 'mysql3306_2', 1, 1, 'localhost:3306', '', 'wpmu_db3', 'wpuser', 'thepwd');

// add global tables which are in global database
add_db_table('global',  'wp_blogs');
add_db_table('global', 'wp_blog_versions');
add_db_table('global', 'wp_registration_log');
add_db_table('global', 'wp_signups');
add_db_table('global', 'wp_site');
add_db_table('global', 'wp_sitecategories');
add_db_table('global', 'wp_sitemeta');
add_db_table('global', 'wp_usermeta');

// add the tables for the first blog (created during wpmu installation)
// the first blog's tables are in global database
// of course, you can move it to any database you want
add_blog_tables('global', 1);

$dbsnum=3; // 3 additional databases
$blogs_per_db=2; // each database serves 2 blogs

for($db_id=1; $db_id<=$dbsnum; $db_id++){
    $dataset = 's' . $db_id;
    $max = $db_id * $blogs_per_db + 1; // include
    $min = $max - $blogs_per_db + 1; // include
    for($blog_id=$min; $blog_id<=$max; $blog_id++){
        add_blog_tables($dataset, $blog_id);
    }
}

That's it!

• 邓林海 Says at 9:38, March 23, 2009
  

  你好，你认为先配置一个数据库，往后有发展需要的时候再配置hyperdb使用多数据库可以吗？

  是必须在建博客的开始的时候就要配置好hyperdb吗？还是等我在虚拟主机试用一段时间，发现虚拟主机已经无法满足我的需求后，我可以把数据库移植到我的独立主机，移植过来的单独数据库里面已经有100个用户了，然后我再配置hyperdb,中间再配置hyperdb的方案可行吗？

  等候佳音

• richard Says at 10:43, March 23, 2009
  

  当然可以。是否使用 hyperdb、以及何时使用它，这都取决你的应用要求和系统承载的压力情况。

• Alberto Says at 6:57, April 3, 2009
  

  Hi!

  how to correct below ploblem!

  Thanks.

  ALberto.

  Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: Name or service not known in /home/thiswell/wpmu/wp-content/db.php on line 1066

  Warning: fsockopen() [function.fsockopen]: unable to connect to DB_HOST:3306 (Unknown error) in /home/thiswell/wpmu/wp-content/db.php on line 1066

  Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL Server Host ‘DB_HOST’ (1) in /home/thiswell/wpmu/wp-content/db.php on line 561

  Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL Server Host ‘DB_HOST’ (1) in /home/thiswell/wpmu/wp-content/db.php on line 561

  Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL Server Host ‘DB_HOST’ (1) in /home/thiswell/wpmu/wp-content/db.php on line 561

  Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL Server Host ‘DB_HOST’ (1) in /home/thiswell/wpmu/wp-content/db.php on line 561

  Warning: Cannot modify header information - headers already sent by (output started at /home/thiswell/wpmu/wp-content/db.php:1066) in /home/thiswell/wpmu/wpmu-settings.php on line 135

• richard Says at 13:36, April 3, 2009
  

  Seems that it's caused by MySQL configurations.. Did you install and configure your MySQL well? Did you input the right database information when installing WP? Double check it..

• wordpress mu plus hyperdb | 管中窥豹、林荫之辉 Says at 15:06, April 3, 2009
  

  […] 文章来源：http://huang.yunsong.net/2009/wpmu-hyperdb.html […]

• 漫声细语 » Blog Archive » Rocks! WordPress MU + HyperDB Says at 16:06, April 3, 2009
  

  […] 推荐(0) 上一篇 分类：未分类 | Trackback 没有相关日志没有相关日志 […]

• smcnally Says at 21:28, April 17, 2009
  

  Thank you for the excellent write-up. It was very helpful to us getting this working in a Test environment.

  Would you please provide some more detail around your last function; are you spawning new databases for every 2 new blogs? I see the additional tables being created - where is the new db ($dataset) being created?

  Many, many thanks

  $blogs_per_db=2; // each database serves 2 blogs

  for($db_id=1; $db_id

• richard Says at 18:15, April 20, 2009
  

  @smcnally The tables are created automatically, but the DBs are created by yourself in advance. And the add_db_server method is used to specify the connection details of each DB.

• smcnally Says at 7:47, April 22, 2009
  

  Thank you, Richard, for this blog post and your response.

  Are you available for consulting work as we work to implement hyperdb in our WPMU platform?

  Again, many thanks -

• richard Says at 16:44, April 22, 2009
  

  @smcnally Yes, I'm glad to have a chance to provide help for wordpress users. I love it so much. But.. actually there is a commercial plugin “multi-db” which can do the same works as hyperdb. Maybe you have interets to try it… http://premium.wpmudev.org/project/multi-db

  If you still wanna invite me after tried multi-db, please drop me a message. ^_^

• f8f8 Says at 1:36, May 11, 2009
  

  你好，按照你的方法我没有在本机安装成功
  ‘可否给我一份设置好的 db-settings.php
  另外是否需要我自己在PHPMyAdmin里新设置库global，s1,s2,s3
  还是设置wpmu_db0，wpmu_db1，wpmu_db2，wpmu_db3
  我原来已安装好的wpmu，数据库名为：mu
  我是需要更改wp-config.php里的这些

  // ** MySQL settings - You can get this info from your web host ** //
  /** The name of the database for WordPress */
  define('DB_NAME', ‘mu');

  /** MySQL database username */
  define('DB_USER', ‘f8f8′);

  /** MySQL database password */
  define('DB_PASSWORD', ‘123456′);

  /** MySQL hostname */
  define('DB_HOST', ‘127.0.0.1′);

• richard Says at 11:10, May 11, 2009
  

  @f8f8
  数据库的名字不重要，随便你叫。wpmu_db* 是我的命名，你尽可随意。global, s* 这些名字是逻辑名，你也可以自己随意命名。看清楚我文中代码的逻辑你就明白了。
  我的机器上已经没有 wpmu 环境了。其实我的 db-settings.php 就是其原文件加上了上面文中我贴出的代码片段。

• f8f8 Says at 5:42, May 14, 2009
  

  已安装成功，谢谢你的说明。

• George Says at 15:59, May 19, 2009
  

  When I try to test this on my local install I get the following errors:

  Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: No such host is known. in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 1077

  Warning: fsockopen() [function.fsockopen]: unable to connect to DB_HOST:3306 (Unknown error) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 1077

  Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: No such host is known. in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 1077

  Warning: fsockopen() [function.fsockopen]: unable to connect to DB_HOST:3306 (Unknown error) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 1077

  Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on ‘localhost’ (10061) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 572

  Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on ‘localhost’ (10061) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 572

  Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on ‘localhost’ (10061) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 572

  Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on ‘localhost’ (10061) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php on line 572

  Warning: Cannot modify header information - headers already sent by (output started at D:\Programs\XAMPP\xampp\htdocs\blog.com\wp-content\db.php:1077) in D:\Programs\XAMPP\xampp\htdocs\blog.com\wpmu-settings.php on line 149

• Bookmarks for November 15th through November 16th « LostFocus Says at 2:02, November 17, 2009
  

  […] Rocks! WordPress MU + HyperDB – Richard’s Weblog – […]