Comments on my MultiDB Setup ?

Hey guys i know this is the best place to ask

I am sitting with a BIG Multi DB setup Here is the list of the db and there sizes

2.1G blogline_0
1.9G blogline_1
2.4G blogline_2
2.9G blogline_3
2.7G blogline_4
2.7G blogline_5
2.1G blogline_6
2.0G blogline_7
2.3G blogline_8
2.4G blogline_9
2.0G blogline_a
1.9G blogline_b
2.3G blogline_c
2.2G blogline_d
2.0G blogline_e
2.2G blogline_f
297M blogline_global

1) First Problem MySql dont like to load up BIG dbs it hangs and then the page just goes white

I need to clean up the DBs i want to remove all comments form all blogs

So what i need is to have a plug or script that can do this for me.

Please guys i know some one out there has set this up for a customers please let us have it so we can use it with our Multi DB setups please

  • Mark de Scande
    • Syntax Hero

    @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 :slight_smile:

    2) Upgrade to 256 and if yes how and were do we start :slight_smile:

    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 :slight_smile:

  • Aaron
    • CTO

    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.

  • Mark de Scande
    • Syntax Hero

    @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 :slight_smile:

    Love you guys :slight_smile:

  • Mustafa
    • Syntax Hero

    I guess move-blogs script for that.


    //Here we find our blog id, hash it, and establish our new db names
    	$blogid_get = explode("_", $row[0]);
    	$blogid = $blogid_get[1];
    	$md5_hash = md5($blogid);
    	$md5_dbprefix = substr($md5_hash, 0, $newdbsize);
    	$this_blog_new_db = $newdb_prefix.$md5_dbprefix;

    and change with

    //Here we find our blog id, hash it, and establish our new db names
    	$blogid_get = explode("_", $row[0]);
    	$blogid = $blogid_get[16];
    	$md5_hash = md5($blogid);
    	$md5_dbprefix = substr($md5_hash, 0, $newdbsize);
    	$this_blog_new_db = $newdb_prefix.$md5_dbprefix;

    $blogid = $blogid_get[16]; important poin for find existing dbs.And than re-move with md5 hash .

  • Mark de Scande
    • Syntax Hero

    @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.

  • aecnu
    • WP Unicorn

    Greetings Mark :slight_smile:

    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 :slight_smile:

    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 :slight_smile:

    Moving the database to a different directory or drive:

    1. Make a full mysqldump file:

    mysqldump --all-databases | gzip > /home/alldatabases.sql.gz

    2. 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


    service mysql stop

    4. Make the directory for MySQL in /home or where you want it, move it and symlink it:

    mkdir /home/var_mysql
    mv /var/lib/mysql /home/var_mysql
    chown -R mysql:mysql /home/var_mysql/mysql
    ln -s /home/var_mysql/mysql /var/lib/mysql
    /etc/init.d/mysql start

    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.cnf

    Once you have the password, then you can use this command to restore from that file:

    gunzip < /home/alldatabases.sql.gz | mysql -u root -p

    You'll be asked for the password here, so simply enter the one noted earlier from /root/.my.cnf location.

    Joe :slight_smile:

  • Mark de Scande
    • Syntax Hero

    @aecnu Trying out as support staff - let me know how I go! Trust me you will do good :slight_smile:

    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 :slight_smile:

  • Mark de Scande
    • Syntax Hero

    @barry :slight_smile: THANK YOU FOR ONE POINT

    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 :slight_smile:

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.