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?
7430 pointsLike some sort of WPMU DEV GodMindblowingly helpful memberLifetime member
Erstwhile founder
—
5th February 2009 (3 years ago)
#
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.
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! ;-)
Quick question... if I have not setup with multi-db right now until our site grow to several hundreds then switch over to Multi-DB. Will it be possible to do this without a huge mess of upgrade on older database setup without Multi-DB ?
@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!
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 :)
@Andrew Thank you, I'll definitely be asking questions as I set this up. I've read enough throughout the forum its easy to do it in the early stages of a site install then later when there's a lot of activity.
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 :) .)
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;
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!
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:
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.
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.
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?
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';
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: %
Responses (21)
Member — 5th February 2009 (3 years ago) #
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.
Erstwhile founder — 5th February 2009 (3 years ago) #
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
Member — 6th February 2009 (3 years ago) #
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! ;-)
Member — 7th February 2009 (3 years ago) #
What bits are confusing?
I'll try to simplify them.
Bill
Keeper of the Dark Chocolate — 7th February 2009 (3 years ago) #
Hmm, I could have sworn I wrote a response here this morning.
Anyway, I was going to suggest breaking it down into steps as the article seems to run all together.
Member — 9th February 2009 (3 years ago) #
OK Will get to it
I see what your saying
It Rambles
Bill
Member — 9th February 2009 (3 years ago) #
Quick question... if I have not setup with multi-db right now until our site grow to several hundreds then switch over to Multi-DB. Will it be possible to do this without a huge mess of upgrade on older database setup without Multi-DB ?
Keeper of the Dark Chocolate — 24th June 2010 (1 year ago) #
Link fixed ~ drmike
Member — 1st July 2010 (1 year ago) #
@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!
Erstwhile founder — 1st July 2010 (1 year ago) #
Hiya,
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.
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 :)
Thanks,
Andrew
Member — 1st July 2010 (1 year ago) #
@Andrew Thank you, I'll definitely be asking questions as I set this up. I've read enough throughout the forum its easy to do it in the early stages of a site install then later when there's a lot of activity.
Member — 3rd July 2010 (1 year ago) #
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 :) .)
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
wp00DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE
wp01DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;Thanks!
Chuck S.
Member — 7th July 2010 (1 year ago) #
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!
Erstwhile founder — 7th July 2010 (1 year ago) #
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
Member — 7th July 2010 (1 year ago) #
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.
Erstwhile founder — 7th July 2010 (1 year ago) #
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
Member — 7th July 2010 (1 year ago) #
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.
Erstwhile founder — 8th July 2010 (1 year ago) #
The SQL wasn't copy/paste ready ;)
You need to switch the USER bit with your user. Give this a try:
GRANT ALL PRIVILEGES ON wp.* TO wp_user@'localhost'
Thanks,
Andrew
Member — 8th July 2010 (1 year ago) #
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.
Erstwhile founder — 8th July 2010 (1 year ago) #
Glad you got it sorted :)
Thanks,
Andrew
Member — 14th July 2010 (1 year ago) #
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: %
Become a member