How to Moved a BIG DB setup /var/lib/mysql to a backup drive and get drive I/O Down

Hey guys i am posting this to get rep point and to help the guys out there

First problem was that /var was filling up so as we all know /var it the place were the dbs are kept and it is one of the most hit dir on the server when i comes to drive I/O

So the trick is to move your DB Setup to a spear drive 15K SAS drive will do the trick or if you like me 7200rpm/250Gb drive did the tick well i did not have money for a 15K SAS drive :slight_smile:

Dont for get you need back ups and more backups

WHM Backup make sure you have a backup setup
SSH Then type # /scripts/cpbackup --force

SSH in to your box and then type screen (If you see 777 then SFTP in to your box and run CMOD 777 on the dir it ask for)
screen screen

MySql Dump
SSH Then Type # screen (screen will be your friend it will run your script in the back ground) now run
# mysqldump --all-databases --single-transaction | gzip -c > /backupdrive/alldatabases.sql.tar.gz

Dont forget if your DBs are large 40GB + this takes for ever with mySQL DUMP

Now we need to find the screen process Just log in to WHM and click load a Load Averages then look for the word SCREEN and have a look at the number so now to get back to the screen

SSH # screen -r 7384 then if the dump is done type exit

So now we have a lot of back ups and a dump file so if any thing goes wrong we can restore.

Now the easy stuff

SSH # /etc/init.d/mysql stop
SSH # mkdir /backupdrive/var_mysql
SSH screen mv /var/lib/mysql /backupdrive/var_mysql (this will take about 4 hours if your DB are 40GB)

Now open These Up

Keep File Zilla open to make sure the files are being copyed /backupdrive/var_mysql/mysql just hit F5
Keep WHM open Main >> System Health >> Process Manager ( mv -i /var/lib/mysql /backupdrive/var_mysql )
Keep WHM open Main >> System Health >> Show Current Disk Usage (you sould see the back up drive getting content)
IO Statistics Sould show you Blocks Written/Sec ( if you look at the back updrive it should be working hard )

When done rename /var/lib/mysql to /var/lib/backup_mysql (Just keep the backup in there till your 100% sure it is all working)

ln -s /backupdrive/var_mysql/mysql /var/lib/mysql
chown -R mysql:mysql /backupdrive/var_mysql/mysql

/etc/init.d/mysql start

Now pull up your site and see if it is working if yes do below

Restart Server
Make sure that sites work after reboot
Now do Clean up /var/lib/backup_mysql
SSH # rm -rf /var/lib/backup_mysql

Now have a look at your WHM Main >> System Health >> Show Current Disk Usage also have a look at the bottom of the screen IO Statistics you will see a lot better screen as the load on the drives has been split :slight_smile:

I hope this helps any of you guys out there to move DB to a new drive :slight_smile: