I need to get Multi-DB set up A.S.A.P but having problems

I've tried setting Multi-DB up before but ran into problems and gave up. Now I'm on a mission to get this set up because i need it done A.S.A.P. It's driving me crazy.

The site I'm working on is running on Hostgator VPS using less them 5% of the following resources
CPU-1.98 GHZ, RAM 1344 MB, Disk Space 59 GB, Bandwidth 1050 GB

This site also has a dedicated IP

I've just set up a new WP 3.1 with buddypress 1.2.8 and multi-db-3.0.5 This is the 6th time I've installed a multi-site and getting quick. I've been working with mysql for many years.

I've set up the databases per the instructions but still getting errors when I try to run move-blogs.php

Here are the files I needed to set up and a screen shot of move-blogs.php with one of the many ways I tried to make it work. As far as I can see I have it set up like masonjames has it set up on the instuction page for the plugin. The sample pages with the plugin are different so I made changes t reflect the differences and still did run the script correctly.

PLEASE, HELP!!!! Once I get it right I'll redo it again and change the database name.

<?php
// Plugin Name: Multi-DB
// Plugin URI: https://premium.wpmudev.org/project/Multiple-Databases
// Author: Andrew Billits (Incsub)
// Version: 2.9.2
//------------------------------------------------------------------------//
//---DB Scaling-----------------------------------------------------------//
//------------------------------------------------------------------------//
// 16,256,4096
define ('DB_SCALING', '16';
//------------------------------------------------------------------------//
//---DC IPs---------------------------------------------------------------//
//------------------------------------------------------------------------//
// Usage: add_dc_ip(IP, DC)
// EX: add_dc_ip('123.123.123.', 'dc1';
add_dc_ip('174.121.148.119', 'dc1';
//------------------------------------------------------------------------//
//---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';
add_global_table('global';
add_global_table('affiliatedata';
add_global_table('affiliatereferrers';
add_global_table('am_actions';
add_global_table('am_queue';
add_global_table('am_schedule';
add_global_table('autoblog';

add_global_table('bp_activity';
add_global_table('bp_activity_meta';
add_global_table('bp_friends';
add_global_table('bp_groups';
add_global_table('bp_groups_groupmeta';
add_global_table('bp_groups_members';
add_global_table('bp_messages_messages';
add_global_table('bp_messages_notices';

add_global_table('bp_messages_recipients';
add_global_table('bp_notifications';
add_global_table('bp_user_blogs';
add_global_table('bp_user_blogs_blogmeta';
add_global_table('bp_xprofile_data';
add_global_table('bp_xprofile_fields';
add_global_table('bp_xprofile_groups';

add_global_table('domain_mapping';

//------------------------------------------------------------------------//
//---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';
//
// 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';
add_db_server('global', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_global', 'inohc_test', 'xxxxxxxxx';

add_db_server('0', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_0', 'inohc_test', 'xxxxxxxxx';
add_db_server('1', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_1', 'inohc_test', 'xxxxxxxxx';
add_db_server('2', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_2', 'inohc_test', 'xxxxxxxxx';
add_db_server('3', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_3', 'inohc_test', 'xxxxxxxxx';
add_db_server('4', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_4', 'inohc_test', 'xxxxxxxxx';
add_db_server('5', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_5', 'inohc_test', 'xxxxxxxxx';
add_db_server('6', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_6', 'inohc_test', 'xxxxxxxxx';
add_db_server('7', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_7', 'inohc_test', 'xxxxxxxxx';
add_db_server('8', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_8', 'inohc_test', 'xxxxxxxxx';
add_db_server('9', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_9', 'inohc_test', 'xxxxxxxxx';
add_db_server('a', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_a', 'inohc_test', 'xxxxxxxxx';
add_db_server('b', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_b', 'inohc_test', 'xxxxxxxxx';
add_db_server('c', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_c', 'inohc_test', 'xxxxxxxxx';
add_db_server('d', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_d', 'inohc_test', 'xxxxxxxxx';
add_db_server('e', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_e', 'inohc_test', 'xxxxxxxxx';
add_db_server('f', 'dc1', 1, 1,'','174.121.148.119', 'inohc_test_f', 'inohc_test', 'xxxxxxxxx';

//------------------------------------------------------------------------//
//---VIP Blogs------------------------------------------------------------//
//------------------------------------------------------------------------//
// Usage: add_vip_blog(BLOG_ID, DS)
// EX: add_vip_blog(1, 'vip1';

?>

and

<?php
/*
Plugin Name: Multi-DB (Conversion Tool)
Plugin URI: https://premium.wpmudev.org/project/Multiple-Databases
Description:
Author: Ron Dillehay - FanMail to rdillehay@hotmail.com (no tech support please) - Modified slightly by Andrew Billits (Incsub)
Version: 2.9.2
Author URI:
Descriptiom:
This script is to move blogs from a wordpress mu single database setup, to a wordpress mu multiple database setup using an MD5 hash to establish blog routing
Use this script at your own risk. My test setup uses php 5, mysql 5, and wordpress mu 1.2.5a
*/

//------------------------------------------------------------------------//
//---Config---------------------------------------------------------------//
//------------------------------------------------------------------------//

///DB Settings
$dbname = "inohc_test_"; //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 = 'inohc_test_'; //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 = 'inohc_test';
$dbpass = 'xxxxxxxxx';

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

HERE ARE SOME SCREEN SHOTS AND ERROR FOUND ON MOVE_BLOGS.PHP

  • becky

    1. I ran into same problem yesterday. I learnt that even with VPS, you need to run a script to map the database so they will all show up in CPanel. I contacted support and asked them to help map them. They did it in a jiffy.

    2. The next thing you have to tackle with is to assign a user to each of the DB. Man, that's really time consuming for me for 256 DB but since you are creating 16 DBs only, u can do that by going to CPanel MySql Database and assign a user (can be the same as original WP user) to each of the DB.

    3. I think you got this line "add_global_table('global');" wrong. You need to replace ('global') with ('your_global_dbname'). I had to spend quite a long time to try out before I figure this out.

    I wish the installation instructions could be clearer. Hope the above will help you.

    Now if only someone could tell me if I should delete the move-blogs.php after moving the blogs

  • dapadoo

    UPDATE

    On db-config.php removed "119" (not sure if needed to be done)
    From add_dc_ip('174.121.148.119', 'dc1')

    On move-blogs.php removed "_" after test (had to be done)
    $newdb_prefix = 'inohc_test_';

    Both of the changes above are different then what's shown in plugin instructions.

    Had to add user(same as on main site/blog) manually to all new DB's created.

    Added 3 new sites/blog and checked myPhpAdmin and tables were being added as they are supposed to be.

    Now questions that need to be answered please.

    1. Is the plugin supposed to add the user(same as main site/blog) to the DB's automatically or do we have to add them manually?

    2. Should move-blogs.php be removed because of possible security risk after completion?

  • Mason

    Hiya,

    1. Is the plugin supposed to add the user(same as main site/blog) to the DB's automatically or do we have to add them manually?

    How did you add the new databases? with the SQL output from our tool or some other method? Regardless, do a check of your databases and make sure they have the apropriate db username associated. - you should be able to do this through the database management for your server.

    2. Should move-blogs.php be removed because of possible security risk after completion?

    This file isn't necessary after multi-db is set up and should be removed.

    Thanks!

  • Barry

    Shouldn't this been automatically done by multi-DB?

    No, that would mean giving the script your root username and allowing it access to do so. Also some people have different usernames / passwords for different databases, or put databases on different servers. It's safer for the script to only handle the moving of tables rather than messing with creating databases and users and permissions.

  • dapadoo

    @masonjames I was able to set it up OK but still am wondering how I can assign the user to the database using a script instead of one-by-one in cpanel. I have access to the the root as I have a VPS server with hostgator. Support there has been very helpful and said if I can find out how to do it they would run it for me if I didn't want to take any chances of messing up things. I built a 16 DB one and am working on a 256 DB. Sure is slow going doing adding user one-by-one. Any thoughts or know how to do it with a script?

  • Aaron

    Do you have a root user for mysql, one with permissions for all databases? If so you can use sql:
    http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

    Something like
    GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost';

    Of course if you run other dbs than wpmu on that db server you should limit the user to just wpmu tables for security, like:

    GRANT ALL PRIVILEGES ON wpmudb1.* TO 'monty'@'localhost';
    GRANT ALL PRIVILEGES ON wpmudb2.* TO 'monty'@'localhost';
    GRANT ALL PRIVILEGES ON wpmudb3.* TO 'monty'@'localhost';

    etc.

  • sharesteading

    @dapadoo

    Hi dapadoo, how did your install go? You had commented on a recent post of mine regarding this exact same issue. I have a HostGator VPS as well and I am the root, although my site is not on a dedicated server (I could put it on one though if necessary) If you finally got it to work for your site, is there any way you could explain the steps for me as far as specific HostGator install instructions? I am getting really discouraged because I did everything it explained to do, but when I used the move-blogs.php I got the "Houston, we have a problem" message over and over, and after trying to rearrange things again, it kept telling me that the user could not access mySQL. I really need to get this done so I can lay out my site correctly before I launch it.
    At this point I'm not exactly sure what I did wrong, and I'm really discouraged. And on top of that, I tried to do the 4096 database install- so I have thousands of databases just sitting there with no user/password. I wish WPMU would have mentioned that before hand or been more detailed with their instructions. One specific question though- do I have to be using my site's cPanel as 'root' in order for this to work even if admin is allowed root access? also, all of my users and databases in mySQL have 'admin_' listed before them (eg. 'admin_admin') and for databases eg."admin_wdprs1_whatever#" Would I enter them that way in the php scripts or just as 'admin' and "wdprs1_whatever#'?

  • becky

    @sharesteading,

    Checkout my reply in the 2nd thread to dapadoo. You need to get Hostgator to run a script (they have it) to map the DBs so you can add user(s) and then move the blogs.

    You can use ."admin_wdprs1_whatever#" or ."admin_whatever#". I have tried both and works.

    Adding user bit - Aaron has replied above. I haven't check out the script as having been installing new site. You can check with Hostgator support to see if they can add the users with the script mentioned by Aaron, for you.

    Hope this helps.

  • dapadoo

    I wish I could give you some good news but I don't have any. I played around with adding users thru the root for about 1 1/2 hours with out great success. I was able to add users but the privileges were different then adding though MySQL databases in cpanel. Until I have time to do a new install, that should it fail it doesn't matter, I'm not able to help much more. Some plugins are harder then others to use and learn, Multi-DB is one of them. It definitely worth the time to learn. I understand that WPMU can't support and have documentation on all the different web host that can run Multi-sites.

    I agree with you the instructions could be better and more detailed but, the plugin works as it says it is supposed to work. There's just some info missing from the instructions that would make it easier to understand the time it takes to install. Yet, some we need isn't WPMU responsibility as they don't have any thing to do with our servers. Any extra help I get here is a bonus.

    If I get to this I'll let you know. If you come up with a solution please let me know.

  • dapadoo

    @Becky. I had to have them map the databases also. They didn't tell me what I could do to fix it myself until I called and complained when It happened on another domain I was working on and didn't want to wait several hours for them to get to it. It doesn't happen to all setups as I had one I didn't have any problems with. The only time the script they write about below needs to be ran is if after running SQL to build the databases you can't see the databases in MySQL databases in cPanel. This is were you would normally add databases manually and more then likely built the main database for you wordpress install. It is also where you would add the user to each database manually.

    Here's the reply I received from Hostgator support to fix the problem of not seeing databases in MySQL databases when they have already been built using the script provided by WPMU and show up in phpMyAdmin.

    Hello,

    I apologize for the previous confusion. You should be able to view the databases in cpanel now.

    The issue was with the database names. You had database names with underscore "_". Cpanel doesn't map database properly if you have multiple underscores in database name (it already creates database with username_ )

    So we need to manually map the database to the useraccount. To do this you need to log in to server as root and run the following script: /usr/local/cpanel/bin/setupdbmap

    If you see this issue again you can email us or contact us through live chat and we can do that for you. Let us know if you have any questions.

    If you have any questions or concerns, please don't hesitate to contact us. We are more than happy to assist you.

    Kind regards,
    XXXXXXXXXXXXXXX
    Linux Systems Administrator
    HostGator.com LLC

  • Shawn

    Hi everyone. I've been using Multi-DB on a couple of large sites now for almost a year. It has made huge performance improvements. However, I'm running dedicated servers with full root access, and the sites are not "sharing" resources with anyone. If I were using a VPS or a shared hosting server, I would definitely not consider using Multi-DB. There are several reasons for that.

    First, Multi-DB is intended to help you scale large WP multisite installs. If you don't have at least a thousand sites on your install, it's likely not going to do much, if anything, for you. Using this on a smaller site will actually add complexity that will complicate direct management, for example, thru phpMyAdmin.

    Second, while you can get your shared/vps host to help you setup the databases required to operate Multi-DB -- chances are they're imposing other limits that may actually reduce the overall performance when split this way. MySQL really needs to be tuned for scale on a system like this, and it's critical that you not exceed the number of simultaneous connections that your user account(s) are allowed via MySQL. This can be configured within my.ini, but only if you have sufficient privileges to access and edit this file. If you don't, chances are that's really the least of your problems. You'd likely have exactly the same benefits simply by setting up a second (or more) user account and cycling thru them randomly within wp-config.php.

    Finally, by the time your install has this many "sites", it will almost assuredly exceed the limits imposed through your VPS or shared hosting provider...which causes far worse problems of scale. Getting a dedicated server at this point becomes an absolute necessity. The worst part is that moving your MySQL content from a shared host or VPS to a dedicated server when it's split between 256+ databases is near impossible without significant downtime. You already don't have the direct access you need to be able to manage the data effectively, so migrating it is going to be a huge PITA. Definitely not something I'd ever want to do, and I love this stuff.

    The bottom line, if you're not using the resources that will really make Multi-DB a necessity for you (I would say this means **at least** 1,000+ "sites", which equates to roughly 10,000 tables or as many as 20,000 directory indices within the "/var/lib/mysql/yourdb" tree), DO NOT use it. If you ARE using that many system resources, you need to seriously reconsider your current hosting setup BEFORE you go this route, because it's going to complicate upgrading outside of your existing limitations.

    You may not even be aware of the limitations imposed on you by your host - but some of this can be obtained within phpMyAdmin by looking up the "max user connections" and "max connections" options on the variables tab. There are others there specifically to do with caching that also apply to a large site (where Multi-DB would make sense to use).

    I absolutely LOVE Multi-DB. But that doesn't mean it makes sense for every install. Quite the opposite. Use the right tool for the job you're trying to accomplish. Based only on the fact that you're using a VPS/shared setup, you'd probably gain far more improvement by using a caching plugin. The Quick Cache plugin is *nearly* perfect in this regard, and extremely simple to setup.

  • dapadoo

    @Shawn I agree that Multi-DB isn't for every site. According to what I've read on WPMU if you have intentions of have X amount of sites on an install you should have y amount of DB's. It is also said it's esier to install the extra DB's on a new install rather than one that is already being used.

    I also agree that once a site using Multi-DB's get active and starts using up a lot resources it needs to be on a dedicated server. In the development stage building on a VPS is fine as long as you have plenty of resources and know your limits. Even launching from a VPS is OK as long as you know what it takes to upgrade right away. With HostGator you have 9 levels of VPS service and can upgrade at any point. If you move from a VPS they will move every thing for you and it reduces the down time greatly. I've already did it once.

    I disagree with you when it comes to moving DB's. I moved 50 DB's from a reseller account on a shared server to a VPS server in about 15 minutes.

    I heard of some people with horror stories about the company they host with and limits placed on accounts. I can't believe that some people still believe you can get unlimited bandwidth or disk space. What's worse is companies that advertise it. Yes, HostGator does for shared accounts but not for VPS or dedicated servers. What it came down to is you get what you pay for.