6593 pointsLike some sort of WPMU DEV GodExceptionally helpfulLifetime member
Keeper of the Dark Chocolate
—
23rd July 2009 20:51
#
Make sure all of the charsets are the same on all of the databases before you start throwing around content though. Really easy to mess that up if they're not. They should be though.
Andrew: when you say move the tables back into a single db, wont they stay in the original db so I could just change the values in wp-config ?
I can't help but feel a bit anxious about doing the multi-db upgrade. I am pretty good at following manuals and have a decent level of experience of doing these things but I'd feel much better if there was a step by step tutorial that went into more detail than the ones I have seen around the forums. Things like step 1, make new table here, step 2, fill in these fields with these values for this reason etc
I searched around the forums but didn't see a spoonfeed type tutorial, is there one?
when you say move the tables back into a single db, wont they stay in the original db so I could just change the values in wp-config ?
Tables are copied to the 16, 256, or 4096 databases. The original db is left intact but once changes are made it's not a usable backup unless your ok with losing those changes.
I searched around the forums but didn't see a spoonfeed type tutorial, is there one?
You really need to have a decent understanding of both PHP and MySQL to install Multi-DB. It's just a touch more complex than installing a plugin.
I managed to install the whole site to a local webserver and upgrade to 2.8.2 and install multi-db for 256 databases with only a few minor stumbles on the way.
I think I'm ready to do it for the real server now and will probably do a narrated screencast so others don't have to scream in panic at a page full of errors if they want to do the same thing!
one question,
if I want to install a new plugin in mu-plugins that uses a database table, do I need to add the table to the global table using the same name set in the plugin before I install it?
do I need to add the table to the global table using the same name set in the plugin before I install it?
You add the table (minus prefix) to the global tables section in db-config.php. It's fairly important that this is done before the plugin is added. Otherwise the plugin could install into the wrong database and mark it's self as being installed.
Hope you don't mind I'm hijacking the thread just for a tad. I think moving all databases into one, you can do this -- please correct me if i'm wrong, and please test this first before try it on production server.
after backup all data, open the alldatabases.sql.bz2 by doing this -- bunzip2 alldatabases.sql.bz2
then you have to use vim or vi to open up the sql file -- example: vim alldatabases.sql
Your original db must have prefix like myserver_db1 and myserver_db2 and so on. You can use OpenOffice spreadsheet (calc) or Microsoft spreadsheet to edit out _db1 or _db2 parts. Then replace all myserver_ with a single db -- ex: myserver_newdb and this newdb will be the only variable for all myserver_ in your alldatabases.sql.
After you got that part done, you can create a new db that you just named after in your server/phpmyadmin. Remember myserver_newdb?
Allow the original user that works with all of those 256 or 4096 dbs to work with myserver_newdb -- allow privilege in phpmyadmin or using mysql grant command.
When done, you can do mysql -u username -p -h hostname < alldatabases.sql
I think this will get all databases' tables into one database!
Please let me know if this work since I haven't test this myself while writing this, but I have done this before long time ago, so I hope this is exactly that.
I wouldn't ship a query in a plugin like that just due to the possibility of the prefix being different. However, if it's just used on your site then it should be just fine.
Responses (10)
Erstwhile founder — 23rd July 2009 19:12 #
You just move all of the tables back into a single db, update wp-config.php with the details for that db and then remove the Multi-DB files.
Thanks,
Andrew
Keeper of the Dark Chocolate — 23rd July 2009 20:51 #
Make sure all of the charsets are the same on all of the databases before you start throwing around content though. Really easy to mess that up if they're not. They should be though.
Member — 23rd July 2009 21:06 #
Andrew: when you say move the tables back into a single db, wont they stay in the original db so I could just change the values in wp-config ?
I can't help but feel a bit anxious about doing the multi-db upgrade. I am pretty good at following manuals and have a decent level of experience of doing these things but I'd feel much better if there was a step by step tutorial that went into more detail than the ones I have seen around the forums. Things like step 1, make new table here, step 2, fill in these fields with these values for this reason etc
I searched around the forums but didn't see a spoonfeed type tutorial, is there one?
Erstwhile founder — 23rd July 2009 21:12 #
Tables are copied to the 16, 256, or 4096 databases. The original db is left intact but once changes are made it's not a usable backup unless your ok with losing those changes.
You really need to have a decent understanding of both PHP and MySQL to install Multi-DB. It's just a touch more complex than installing a plugin.
This is a fairly recent thread that goes through the entire process:
http://premium.wpmudev.org/forums/topic/couple-questions-and-please-let-me-know-is-this-how-you-install-multidb
Thanks,
Andrew
Member — 28th July 2009 20:41 #
thanks Andrew and Drmike!
I managed to install the whole site to a local webserver and upgrade to 2.8.2 and install multi-db for 256 databases with only a few minor stumbles on the way.
I think I'm ready to do it for the real server now and will probably do a narrated screencast so others don't have to scream in panic at a page full of errors if they want to do the same thing!
one question,
if I want to install a new plugin in mu-plugins that uses a database table, do I need to add the table to the global table using the same name set in the plugin before I install it?
Erstwhile founder — 28th July 2009 20:45 #
You add the table (minus prefix) to the global tables section in db-config.php. It's fairly important that this is done before the plugin is added. Otherwise the plugin could install into the wrong database and mark it's self as being installed.
Thanks,
Andrew
Member — 29th July 2009 07:11 #
Hope you don't mind I'm hijacking the thread just for a tad. I think moving all databases into one, you can do this -- please correct me if i'm wrong, and please test this first before try it on production server.
mysqldump -u username -p -h hostname --all-databases | bzip2 -c > alldatabases.sql.bz2after backup all data, open the alldatabases.sql.bz2 by doing this -- bunzip2 alldatabases.sql.bz2
then you have to use vim or vi to open up the sql file -- example: vim alldatabases.sql
Your original db must have prefix like myserver_db1 and myserver_db2 and so on. You can use OpenOffice spreadsheet (calc) or Microsoft spreadsheet to edit out _db1 or _db2 parts. Then replace all myserver_ with a single db -- ex: myserver_newdb and this newdb will be the only variable for all myserver_ in your alldatabases.sql.
After you got that part done, you can create a new db that you just named after in your server/phpmyadmin. Remember myserver_newdb?
Allow the original user that works with all of those 256 or 4096 dbs to work with myserver_newdb -- allow privilege in phpmyadmin or using mysql grant command.
When done, you can do
mysql -u username -p -h hostname < alldatabases.sqlI think this will get all databases' tables into one database!
Please let me know if this work since I haven't test this myself while writing this, but I have done this before long time ago, so I hope this is exactly that.
Good luck!
Member — 30th July 2009 10:22 #
I think I'm just about ready for doing the real db upgrade on my live server. oo-er.
one thing, if I have my own code that writes to the wp database, what conventions do I have to use for table names?
ie. if i had
$wpdb->prepare ( "updatewp_userssetdisplay_name= %s WHEREID= %d" ... etcwould that still work after the move to multi-db ? I'm sure there's a way to reference table names using wpdb but I can't find nor remember it!
Member — 30th July 2009 15:09 #
nevermind. got it
FROM {$wpdb->users}Erstwhile founder — 30th July 2009 15:10 #
I wouldn't ship a query in a plugin like that just due to the possibility of the prefix being different. However, if it's just used on your site then it should be just fine.
Thanks,
Andrew
Become a member