If you have a dedicated server with a second Hard Drive then you can often achieve better performance (reduce io wait issues) if you move your MySQL databases to the second Hard Drive.

This was done on a server with CentOS operating system.

Let’s say your second HDD is mounted as /home2

1. The first step is to stop MySQL so that all your data gets copied correctly.

$ /etc/rc.d/init.d/mysql stop

- or -

$ service mysql* stop

2. Create the new database directory in the second HDD and for this let’s say it’s named as mysqldata

$ mkdir /home2/mysqldata

3. Copy the database files from the first HDD to the second HDD

$ cp -R /var/lib/mysql/ /home2/mysqldata

4. Set the correct owner and group, permissions of the new database directory on the 2nd HDD

$ chown -R mysql.mysql /home2/mysqldata/

5. Rename your old database directory

$ mv /var/lib/mysql/ /var/lib/mysql_old

6. Create a symbolic link from the old database directory to the new one for any programs that rely on the default location

$ ln -s /home2/mysqldata/ /var/lib/mysql

7. Set the correct owner and group on the symbolic link

$ chown mysql.mysql /var/lib/mysql

8. Edit the configuration file (/etc/my.cnf) to update the changes

Comment out the old settings and add a line for the new one as you can see below

[mysqld]
#datadir=/var/lib/mysql
datadir=/home2/mysqldata
#socket=/var/lib/mysql/mysql.sock
socket=/home2/mysqldata/mysql.sock

#basedir=/var/lib
basedir=/home2

save my.cnf and exit your text editor

Note that for MySQL version 5 you have to remove the line basedir. The basedir line is only for those who are using MySQL version 4.

9. Restart MySQL

$ /etc/rc.d/init.d/mysql start

- or -

$ service mysql* start

10. If MySQL refuses to start look in /var/log/mysqld.log for the reason