Moving MySQL into a second hard drive?

[kirkas]

I heard that it is better to put the MySQL databases into a second hard drive. Supposedly, putting MySQL in a separate hard drive will improve server speed by reducing I/O bottlenecks. If you have already have existing MySQL databases on the first hard drive, though, how do you go about moving it to a second hard drive? Also, can that be done without having to force people to edit their scripts (ie. will it still use localhost)?

[Dave M]

Does it really increase speed that much? Hmm... that's something to think about...

Anyway, I'm sorry, but I don't really know how to move your MySQL onto a second hard drive. Still, I'm sure that BODhost could probably move it for you.

[iamsam]

Yep, a busy MySQL database can indeed slow things down due to the I/O wait. To move it to a second hard drive, try the following (but be sure to backup first!):

First, of course, you'll need to login to your server via SSH. Next, you'll need to know where your second hard drive is located. To do so, run

df -h

The second hard drive usually shows as /backup, /home or /home2. Or it can be something different, depending on how it was setup. If you're not sure, ask your host.

Next, you'll need to find my.cnf, so type

locate my.cnf

It's usually in /etc/my.cnf . You'll need to edit that file, so type

edit /etc/my.cnf

Of course, you'll need to change the path to my.cnf if it's located in a different directory in your server. If you can also use any editor you like to edit the file, like pico or vi.

Look for the line

datadir = /var/db/mysql

That should be the default for MySQL, but your setup may be different. Anyway, now that we know the location, let's go ahead and move it. Exit my.cnf and shut down MySQL. You can shut it down using the following command:

service mysql stop

Next, move the MySQL directory to the second hard drive with

mv /var/db/mysql /backup

Of course, change the MySQL path if yours is different. Same with the location of the second hard drive if yours is different. Then, symlink the directories:

ln -s /usr/local/mysql/ /var/db/mysql

Then chown the moved directory:

chown -R mysql:mysql /backup/mysql
chown -R mysql:mysql /backup/mysql/*
chown -R mysql:mysql /var/db/mysql/*

Now, you'll have to edit my.cnf to point to the new database location. Type edit /etc/my.cnf again and change the following lines:

socket = /backup/mysql/mysql.sock
datadir = /backup/mysql
socket = /backup/mysql/mysql.sock
basedir = /backup

Save and exit my.cnf. Once that's done, just start MySQL with

service mysql start

And you're done! Everything should work fine now and there's no need to edit any of your existing scripts (localhost should still work).