New to the community? Start here

Step by Step Multi DB Installation guide

After Breaking MY SITE i thought I would put in to play an in depth Installation Instruction guide with screen shots

http://billbailey.co.nz/multi-database-step-by-step-installation/

Hope this Helps Others

I am also Creating a Multi Database Instruction Guide for creating and granting rights to multiple databases thru the Command Line

Regards Bill

[Link fixed ~ drmike]

Wed, 4 Feb 2009 7:01:13
  • mafunk
    • Flash Drive

    Awesome post. Thanks so much for posting this. I have a question. I’m developing a blog site and am not sure how many blogs I will get. It’s possible it will only be 500-1000 (I hope for much more, of course). But here is the thing, when I looked at installing the support and video plugins it says that before doing so it is highly advisable for one to install the multi db plugin. So, if we are going to have only 500 or so blogs, but we are going to use the support system, should be install multi db?

    Thanks for your insight.

  • Andrew
    • Champion of Loops

    Hiya,

    I’d install Multi-DB with 16 databases. You might not really need it for performance reasons but if you ever need to edit something in the db via PHPMyAdmin it’s going to be much easier with everything split into different (smaller) databases.

    Thanks,

    Andrew

  • webmaestro
    • WPMU DEV Initiate

    This is very interesting and useful. It can be a little hard to follow, but it appears to be a valuable resource. Thank you!

    I haven’t yet decided to take the plunge to go the Multi-DB route (since we have under 1000 blogs) but if I do, I’ll likely read this post again.

    Andrew, if you haven’t already, would you mind taking a look at that Step-by-Step and confirm it meets with your approval?

    [OT] I’ve noticed that the ‘Installation’ tab on many WPMUDEV.org plugin pages is extremely brief, and might benefit from the inclusion of the README.TXT (so one doesn’t need to download the ZIP file in order to read the instructions).

    If maintenance is an issue, perhaps you might be able to make a ‘plugin’ adding the ability to add a tab showing the README.TXT file (obtained automatically from the ZIP file). You guys are wizards, so I have confidence it can be done! :wink:

  • dapadoo
    • Site Builder, Child of Zeus

    @Bill007 I tried to setup Multi DB several months ago but the directions I used weren’t as clear as yours are. When it came to global tables I didn’t have a clue. Having read your instructions I have a better understanding of global table.

    I still have a few questions if you or any one else wants to jump in and answer it would be greatly appreciated. I’ve looked at the table I have in my current single DB and see I have many tables that you made into global tables.

    How would I determine what tables need to be made global?

    If I run Multi DB and find after fact there is still another plugin that needs a global database will it need to be made manually?

    I have newer install of WP 3.0 and current version of Buddypress. There’s very little that I’ve added in regards to pluins yet. I plan on using a few plugins I believe need global tables like Supporter. Should I add these plugins before running Multi DB so I know what tables to make global?

    I think I may have broken through a mental block as I’ve been writing this post and may be able to answer the questions myself but I’d rather someone who knows do it for me. LOL I prone to try and put the cart before the horse at times. LOL

    Will be back on in about 16 hours to check replies. Thank you!

  • Andrew
    • Champion of Loops

    Hiya,

    How would I determine what tables need to be made global?

    Unfortunately that really just comes down to experience. To be honest, anyone running a site large enough to need Multi-Db should be able to look at a plugin and see which plugins need to be configured as global tables.

    All of our plugins that have tables come with a file called sql.txt that makes this easy. However, most other plugins do not provide such a file.

    If I run Multi DB and find after fact there is still another plugin that needs a global database will it need to be made manually?

    Yep. You need to add all global tables for installed plugins to db-config.php prior to enabling Multi-DB. When installing new plugins be sure to add the global tables to db-config.php prior to adding in the plugin files.

    Definitely let us know if you have any other questions. It’s much easier to do it right from the start then fix things later on :slight_smile:

    Thanks,

    Andrew

  • ChuckS
    • The Crimson Coder

    Hi,

    I’ve used your script generator in the past to generate the SQL script to create the 256 databases:

    http://db-tools.wpmudev.org/db_sql.php

    After creating the db’s in phpMyAdmin, my cPanel only lets me grant privileges and add a password one db at a time.. AUUGH! I’m creating a new site and don’t want to do that again. (I asked my host to do it and they said I should get a script from the plugin author :slight_smile: .)

    What can I add to your script to also grant all privileges on each db to a user and define their password?

    Here’s your script from the generator to create the first two db’s (wp00, wp01 are sample databases):

    CREATE DATABASE wp00 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    CREATE DATABASE wp01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    Thanks!

    Chuck S.

  • ChuckS
    • The Crimson Coder

    Hi,

    Here's a better copy of the script generated by the DB Tools. What would I add to each line of the script in order to define a user, create ALL privileges and define the user's password? Or is there a better alternate process?

    Thanks much!

    [attachments are only viewable by logged-in members]

  • Andrew
    • Champion of Loops

    Just so I’m clear. Did your hosting provider offer to run the script for you or did they just instruct you to get a script to run yourself? I’m asking because 99.9% of the time you cannot add users to a db via SQL using cPanel. The host would have to run the script.

    If you’re host is willing to run the script then a wildcard could be used to add a user to the databases in one go. Something like this:

    GRANT ALL PRIVILEGES ON wp.* TO USER@’localhost’

    Thanks,

    Andrew

  • ChuckS
    • The Crimson Coder

    Andrew,

    In cPanel I can create one DB at a time and define user and password. The Tools DB script (used through phpMyAdmin) creates the bulk DB’s but doesn’t define a user and password. So I then have to go into cPanel and add a user & pw to each DB. In this case, I would like to run the script the same way I create the bulk DB’s, through phpMyAdmin. Or better yet, bulk create the DB’s with a user & pw all at the same time.

    Chuck S.

  • Andrew
    • Champion of Loops

    The Tools DB script (used through phpMyAdmin) creates the bulk DB’s but doesn’t define a user and password.

    Do you have a reseller account? Usually you can’t create/manage users via SQL (copy/pasting SQL into PHPMyAdmin) when using cPanel.

    Also, are you wanted a different user for every single db? If not then I’m not sure why my suggestion of using a wildcard statement to add a single user to all databases wouldn’t work.

    Thanks,

    Andrew

  • ChuckS
    • The Crimson Coder

    Apologies for the misunderstanding. I have a reseller account. I want to use the same user for all db’s. On a new test site, I created the db’s using the script tool. But I can’t seem to get your second script right.

    GRANT ALL PRIVILEGES ON wp.* TO USER@’localhost’

    Say my first db is wp_00 and the user is wp_user, what would the script look like using the wildcard?

    Thanks.

    Chuck S.

  • ChuckS
    • The Crimson Coder

    Got it to work! I had to first grant myself privileges to grant user privileges, and, the user has to be in quotes ‘user’ . To create a new user and grant privileges at the same time you could use:

    GRANT ALL ON wp.* TO 'user'@'localhost' IDENTIFIED BY 'password';

    Thank you for all your help,

    Chuck S.

  • ChuckS
    • The Crimson Coder

    OK. I was wrong. I thought it worked but it didn't. The final script that worked for me, with root access, used a wildcard on the database and backticks " ` " for the defined items. For example (assuming the user and password are already created):

    Databases: wp_01, wp_02, wp_03, etc. 256

    Wildcard for db's beginning with wp_ : wp_%

    User: wp_me

    Wildcard for the user wp_me from all locations: %

    [attachments are only viewable by logged-in members]