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!
你好，你认为先配置一个数据库，往后有发展需要的时候再配置hyperdb使用多数据库可以吗？
是必须在建博客的开始的时候就要配置好hyperdb吗？还是等我在虚拟主机试用一段时间，发现虚拟主机已经无法满足我的需求后，我可以把数据库移植到我的独立主机，移植过来的单独数据库里面已经有100个用户了，然后我再配置hyperdb,中间再配置hyperdb的方案可行吗？
等候佳音
当然可以。是否使用 hyperdb、以及何时使用它，这都取决你的应用要求和系统承载的压力情况。
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
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..
Pingback: wordpress mu plus hyperdb | 管中窥豹、林荫之辉
Pingback: 漫声细语 » Blog Archive » Rocks! WordPress MU + HyperDB
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
@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.
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 -
@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. ^_^
你好，按照你的方法我没有在本机安装成功
‘可否给我一份设置好的 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′);
@f8f8
数据库的名字不重要，随便你叫。wpmu_db* 是我的命名，你尽可随意。global, s* 这些名字是逻辑名，你也可以自己随意命名。看清楚我文中代码的逻辑你就明白了。
我的机器上已经没有 wpmu 环境了。其实我的 db-settings.php 就是其原文件加上了上面文中我贴出的代码片段。
已安装成功，谢谢你的说明。
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
Pingback: Bookmarks for November 15th through November 16th « LostFocus
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
Put the require db-settings clause AFTER MySQL configuration and everything will work.
Hi, thanks for this explanation.
In block // add global tables which are in global database
it´s not necessary declare wp_users table ?
add_db_table(‘global’, ‘wp_users’);
你好，按照你的方法安装本机不成功，wpmu2.92+hyperDB
hyperDB 0.1
错误提示：
Fatal error: Call to undefined function add_db_table() in D:\PHPnow-1.5.5_1\htdocs\wpmu\db-settings.php on line 78
@carn
这是因为 hyperdb 升级了，它的配置方式变了。不过原理未变。
不推荐你继续使用 WPMU，因为从 WP 3.0 开始，其自身就支持 multisite 了。
搞了好久弄不出来，可不可以麻烦你帮忙配置一下发到我的邮箱？谢谢
还是没搞掂，郁闷啊，可以帮帮忙吗？
哥们，实不好意思，搞不定，又来求你了，希望你能帮帮忙，谢谢
不好意思啊，最近忙着赶产品开发进度，非常非常忙，完全没有时间玩 WP。明年3月底发布第一个版本后才有空玩这些。
自己搞定,谢谢
@carn
哈哈，功夫不负用心人。写篇文章分享给大家吧。
呵呵,其实我自己都没整明白,我用的是虚拟主机,有的时候连不上数据库,不知道是不是得整服务器才行.