How to move MySQL datadir to another drive

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 ~]#


  1. Shutdown MySQL to ensure data integrity during the copying process.
    /etc/init.d/mysql stop
    /etc/rc.d/init.d/mysql stop
    if those doesn’t work for you try:
    /etc/init.d/mysqld stop
    /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:
  8. Save
    Ctrl + O
  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:

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!

No Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: