Skip to main content

How to move your MySQL database directory

Timothy J Bruce

If you have a dedicated database server and the partition with the MySQL database fills up (or starts getting low), you can move your MySQL databases to a different location.

Let’s say you want to move the database to /home/mysql

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

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

  1. Create the new database directory in the new location

$ mkdir /home2/mysql

(NOTE: At this point many people would use the move command (mv) instead of the copy command (cp). I ALWAYS copy the files… so that I can go back and undo my changes later!)

  1. Copy the database files from the first harddrive to the second harddrive

$ cp -R /var/lib/mysql/ /home/mysql

  1. Set the correct owner and group, permissions of the new database directory

$ chown -R mysql.mysql /home/mysql/

  1. Rename your old database directory

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

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

$ ln -s /home/mysql/ /var/lib/mysql

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

$ chown mysql.mysql /var/lib/mysql

  1. 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

save your configuration files (my.cnf) and exit your text editor

  1. Restart MySQL

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

  1. Update the AppArmor configuration file (if necessary).
  2. If MySQL refuses to start look in /var/log/mysqld.log for the reason