Is your MySQL database consuming a significant amount of space, and your current drive is running out of storage? Moving your MySQL database to another drive can be a viable solution, minimizing downtime while potentially boosting MySQL performance, particularly on high-speed drives like Raptor or SCSI. In this guide, we’ll walk you through the process.

I’m assuming:
– the second drive mounted as “/home2”
– current MySQL datadir is “/var/lib/mysql”
– the content of “/var/lib/mysql” is the following:
[root@server ~]# ls -o /var/lib/mysql
-rw-rw---- 1 mysql 10485760 Jul 9 19:04 ibdata1
-rw-rw---- 1 mysql 5242880 Jul 9 19:04 ib_logfile0
-rw-rw---- 1 mysql 5242880 Jul 9 07:13 ib_logfile1
drwx------ 2 mysql 4096 Jul 15 07:40 joomla
drwx------ 2 mysql 4096 Jul 9 06:03 mysql
srwxrwxrwx 1 mysql 0 Aug 13 09:42 mysql.sock
drwx------ 2 mysql 4096 Jul 9 11:31 users
drwx------ 2 mysql 4096 Jul 9 08:22 wordpress
[root@server ~]#

Steps:

1. Shutdown MySQL to ensure data integrity during the copying process.
   /etc/init.d/mysql stop
   or
   /etc/rc.d/init.d/mysql stop
   if those doesn’t work for you try:
   /etc/init.d/mysqld stop
   or
   /etc/rc.d/init.d/mysqld stop
   For the sake of simplicity, let assume the correct one is “/etc/init.d/mysql stop”.
2. Optional, if your are using cron jobs and afraid to break things, stop it.
   /etc/init.d/crond stop
3. Create a directory on “/home2” to hold MySQL data
   mkdir /home2/mysql
4. Copy only databases from current MySQL datadir to the new directory recursively. Remember to copy database only, [b]do not copy[/b] ibdata*, ib_logfile*, mysqld-log-bin or mysqld-log-bin.* files!
   In this case, I have 4 databases: joomla, mysql, users & wordpress.
   cp -R /var/lib/mysql/joomla /home2/mysql
cp -R /var/lib/mysql/mysql /home2/mysql
cp -R /var/lib/mysql/users /home2/mysql
cp -R /var/lib/mysql/wordpress /home2/mysql
5. Create a backup of the MySQL configuration file as a precautionary measure.
   cp /etc/my.cnf /root/my.cnf.backup
6. Update the MySQL configuration file.
   nano /etc/my.cnf
7. Adjust “datadir” value and if needed also “socket” to match the following:
   datadir=/home2/mysql
socket=/home2/mysql/mysql.sock
8. Save
   Ctrl + O
Enter
9. Update the permissions of the new directory to ensure MySQL has the necessary access.
   chown -R mysql:mysql /home2/mysql
10. Rename old directory
    mv /var/lib/mysql /var/lib/mysql-old
11. Create a symlink on old location in case there is unknown programs that depend on it
    ln -s /home2/mysql /var/lib/mysql
12. Create a backup of the MySQL init script.
    cp /etc/init.d/mysql /root/mysql.backup
13. Update the MySQL init script to reflect the new directory.
    nano /etc/init.d/mysql
14. Find “get_mysql_option” line.
15. Adjust the value to reflect the new directory
    get_mysql_option mysqld datadir "/home2/mysql"
16. Optional: If you’re using Webmin, update its configuration to match the new settings.
17. Login to Webmin
18. Go to “Servers” >> “MySQL Database Server” >> Click “Module Config” link at the top.
19. Update “Path to MySQL databases directory” value to:
    /home2/mysql

We are done 🙂

Start MySQL and cron:
/etc/init.d/mysql start
/etc/init.d/crond start

Test the new location by browsing around your site. Once you are satisfied, you can completely remove the old MySQL datadir:
rm -fr /var/lib/mysql-old
I advised you do not remove the old directory for a few days, as precautions, until you really sure everything is going fine.

If something goes wild, restore them all:
/etc/init.d/mysql stop
/etc/init.d/crond stop
cp -f /root/my.cnf.backup /etc/my.cnf
cp -f /root/mysql.backup /etc/init.d/mysql
mv /var/lib/mysql-old /var/lib/mysql
/etc/init.d/mysql start
/etc/init.d/crond start

Good luck!