Mark, everything you are asking you'll really need to hire a competent sysadmin for. It's beyond what we can do (or what I know). You need someone who can take charge of it and do it themselves.
For starters though to change your number of databases, you'll have to write your own custom script for moving them based on our here. And if you are running out of space on your server you can either upgrade it, or split your db across multiple servers if it's a load issue. Example at edublogs we have 3 db servers, 1 for global db, and the blog dbs split evenly between another 2.
1) So we set up all the new DB 240Dbs
2) Change DBconfig to 256 and add all the db details to DB config
3) We edit move blogs with only the global DB details
4) Run move blogs and then hold thumbs.
If my thinking is right all move blogs do is move stuff so with stuff already in place it should just say some thing like table already exist and move on to the next table / db and so on .
I hope i am making sense and yes @Aaron i am the server admin and no i do not have money to get a DBA in place to help out with the setup but i know that i am in the right place to ask :)
1) SO a recap if move blogs are reworked we should be able to upgrade to 16 to any amount of DB ?
2) The only danger here is that if move blogs is not coded correctly it could delete every thing
Guys what would happen if we leave the setup as ease with the 16 + 1 db setup and then we just let the DBs grow and grow.
well sir I would approach removing the comments from the databases using Navicat which is one of the greatest pieces of software I own - not only that it is great for backing up database tables too like nothing else I have ever seen.
Once you have Navicat running and talking to your database, go into the comments tables and delete the unwanted data within :-)
Maybe this could be used to create your larger 256 database by creating the 256 database as directed then copying the tables from the 16 to the 256 in the exact place they are in now :-)
Moving the database to a different directory or drive:
@aecnu Trying out as support staff - let me know how I go! Trust me you will do good :)
Recap on this post all DB has been moved to the back up drive Post To The Tread hope i get some rep points thank you for all the help @aecnu
Now the next step is to convert from 16db to 4096db i will put this in the a new tread as i will set up a Def site to play with the idea before i go live :)
At the moment, the way to go from 256 databases to 4096 is to create a master db, import all the existing 256 databases into. Create your new 4096 databases and then move them out to those.
A lot of work. If you know you are going to need 4096 databases in 8 months, I strongly suggest starting with 4096 databases.
Now that is telling me it is doable to move from 17 Db back to one Db and then back to 4096 :)
Responses (19)
Support Chimp — 26th January 2012 #
Hey Mark de Scande.
I'd deal with those large DBs direct on the DB. You could use SSH if needed.
http://dev.mysql.com/downloads/workbench/5.2.html
http://dev.mysql.com/downloads/gui-tools/5.0.html
Take care.
Lifetime Member — 27th January 2012 #
Hiya,
Maybe you can try master and slave mysql servers and make cluster but I guess this is over your head.
Did you try move db's 16 to 256?
Member par excellence — 27th January 2012 #
@Mustafa Thank you for the tip but your right that is way over my head :)
Softlayer Gave me a tip to move the /var/lib/mysql to the back up drive
I will looking in to it and let you know how it went
Ps if any body wants to tip in please do :)
Lifetime Member — 27th January 2012 #
If you want to move the /var/lib/mysql dont' forget to stop mysql service firs.Because if mysql running some tables will damage.
Generally is the best way mysqldump for backup.
Member par excellence — 27th January 2012 #
Set server in to Single User Mode
mkdir /backupdrive/multidb/
/etc/init.d/mysqld stop
mv -v /var/lib/mysql/* /backupdrive/multidb/
ln -s /backupdrive/multidb/ /var/lib/mysql/
/etc/init.d/mysqld restart
Reboot from Single User Mode
Then hope it all worked
Member par excellence — 30th January 2012 #
Hey Guys do any one know of any posts to upgrade from 16 to 256 db ?
Please post the :)
Member par excellence — 30th January 2012 #
@Barry Is there a easy way of doing this
The set up at the moment is sitting at 32GB for all the dbs
16db + 1 Global
So what would you advise we do
1) Upgrade to 4096 and if yes how and were do we start :)
2) Upgrade to 256 and if yes how and were do we start :)
yes it is a pain in the BUTT the main thing is that i never thought i would grow this big this fast.
Please guys helps me out on this one :)
Lead Developer — 30th January 2012 #
Mark, everything you are asking you'll really need to hire a competent sysadmin for. It's beyond what we can do (or what I know). You need someone who can take charge of it and do it themselves.
For starters though to change your number of databases, you'll have to write your own custom script for moving them based on our here. And if you are running out of space on your server you can either upgrade it, or split your db across multiple servers if it's a load issue. Example at edublogs we have 3 db servers, 1 for global db, and the blog dbs split evenly between another 2.
Member par excellence — 30th January 2012 #
Thank you for the tips :)
Member par excellence — 30th January 2012 #
@Aaron move blogs scripts will it only add stuff ? It will not delete any thing ?
Member par excellence — 30th January 2012 #
@Aaron My thoughts
1) So we set up all the new DB 240Dbs
2) Change DBconfig to 256 and add all the db details to DB config
3) We edit move blogs with only the global DB details
4) Run move blogs and then hold thumbs.
If my thinking is right all move blogs do is move stuff so with stuff already in place it should just say some thing like table already exist and move on to the next table / db and so on .
I hope i am making sense and yes @Aaron i am the server admin and no i do not have money to get a DBA in place to help out with the setup but i know that i am in the right place to ask :)
Love you guys :)
Lifetime Member — 30th January 2012 #
I guess move-blogs script for that.
Look
and change with
$blogid = $blogid_get[16]; important poin for find existing dbs.And than re-move with md5 hash .
Lead Developer — 31st January 2012 #
You'll have to rewrite move blogs script to be able to pull from the 256 dbs. Currently it's only designed to move from a single db to the multiple.
Member par excellence — 31st January 2012 #
@Mustafa and @Aaron Thank you for helping
1) SO a recap if move blogs are reworked we should be able to upgrade to 16 to any amount of DB ?
2) The only danger here is that if move blogs is not coded correctly it could delete every thing
Guys what would happen if we leave the setup as ease with the 16 + 1 db setup and then we just let the DBs grow and grow.
Thank you guys for helping out.
Support Kangaroo — 31st January 2012 #
Greetings Mark :-)
well sir I would approach removing the comments from the databases using Navicat which is one of the greatest pieces of software I own - not only that it is great for backing up database tables too like nothing else I have ever seen.
Once you have Navicat running and talking to your database, go into the comments tables and delete the unwanted data within :-)
Maybe this could be used to create your larger 256 database by creating the 256 database as directed then copying the tables from the 16 to the 256 in the exact place they are in now :-)
Moving the database to a different directory or drive:
1. Make a full mysqldump file:
mysqldump --all-databases | gzip > /home/alldatabases.sql.gz2. If using CPanel or other Panel that auto starts MySQL then shut that off:
Uncheck monitor in WHM > Service Manager for Mysql and save the area
3. Stop MySQL
/etc/init.d/mysql stop~or~
service mysql stop4. Make the directory for MySQL in /home or where you want it, move it and symlink it:
5. Re-check CPanel Monitoring that was unchecked in step 2.
If anything goes wrong, you have the full mysqldump backup, and can use these steps to restore from it:
First, you'll need the MySQL root password:
cat /root/.my.cnfOnce you have the password, then you can use this command to restore from that file:
gunzip < /home/alldatabases.sql.gz | mysql -u root -pYou'll be asked for the password here, so simply enter the one noted earlier from /root/.my.cnf location.
Joe :-)
Member par excellence — 31st January 2012 #
@aecnu drop me a main please :) mark@bloglines.co.za i need to chat with you :)
Support Kangaroo — 31st January 2012 #
Greetings Mark :-)
this has been accomplished sir :-)
Joe :-)
Member par excellence — 3rd February 2012 #
@aecnu Trying out as support staff - let me know how I go! Trust me you will do good :)
Recap on this post all DB has been moved to the back up drive
Post To The Tread hope i get some rep points thank you for all the help @aecnu
Now the next step is to convert from 16db to 4096db i will put this in the a new tread as i will set up a Def site to play with the idea before i go live :)
Member par excellence — 5th February 2012 #
@barry :) THANK YOU FOR ONE POINT
http://premium.wpmudev.org/forums/topic/multi-db-fresh-install-common-imp-questions#post-74224
Now that is telling me it is doable to move from 17 Db back to one Db and then back to 4096 :)
Become a member