Most IT specialists know the MySQL database server. Most of them use it. And everything is fine as long as there's enough disk space in the /var partition to store this server's databases and the administrator is happy with that location. However, there are times when you need to change the location of the databases: the partition has run out of space and a second hard drive was bought, it was planned that way from the start (for example, a small system disk), it's simply inconvenient, and so on.
In this post I'll explain how to change the location of the MySQL server's database directory for several OSes.
FreeBSDOption 1.
Stop MySQL:
# /usr/local/etc/rc.d/mysql-server stop
Edit the /etc/rc.conf file and add the following lines to it:
mysql_datadir="/dir1/dir2"
Create the needed directory and copy into it everything located in the default directory (/var/db/mysql):
# cd /dir1
# mkdir dir2
# cp -R /var/db/mysql/ /dir1/dir2
# chown -R mysql:mysql /dir1/dir2
Start MySQL:
# /usr/local/etc/rc.d/mysql-server start
That's it, MySQL now takes its data from the directory we need.
Option 2.
Stop MySQL:
# /usr/local/etc/mysql-server stop
Create the needed directory, move all our databases there, delete the default directory, and create a symlink:
# cd /dir1
# mkdir dir2
# cd /var/db
# cp -R /var/db/mysql/ /dir1/dir2
# rm -r /var/db/mysql
# ln -s /dir1/dir2 /var/db/mysql
# chown -R mysql:mysql /dir1/dir2
Start the server back up:
# /usr/local/etc/rc.d/mysql-server start
Now our MySQL server takes its databases by looking in its usual location, but thanks to the Unix file system, it's actually landing in a completely different directory, or even a different physical disk.
Debian (not Ubuntu!)Option 1.
Stop the MySQL server.
# /etc/init.d/mysql stop
Edit the /etc/mysql/my.cnf file, create the needed folder and copy the current databases into it.
# cd /etc/mysql
# nano my.cnf
// change the value of the DB path - the datadir= line
// for example, to /dir1/dir2
# cd /dir1
# mkdir dir2
# cp -R /var/lib/mysql/ /dir1/dir2
# chown -R mysql:mysql /dir1/dir2
The old directory can be deleted after the server starts successfully.
And start the server back up.
# /etc/init.d/mysql start
Option 2.
Option 2 for Debian is similar to option 2 for FreeBSD.
Stop MySQL:
# /etc/init.d/mysql stop
Create the needed directory, move all our databases there, delete the default directory, and create a symlink:
# cd /dir1
# mkdir dir2
# cd /var/lib
# cp -R /var/lib/mysql/ /dir1/dir2
# rm -r /var/lib/mysql
# ln -s /dir1/dir2 /var/lib/mysql
# chown -R mysql:mysql /dir1/dir2
Start the server back up:
# /etc/init.d/mysql start
Now the MySQL server looks for its databases in its usual location, but thanks to the Unix filesystem it's actually landing in a completely different directory, or even a different physical disk.
Ubuntu (-Server)The options are exactly the same as in Debian, but with an addition.
Since Ubuntu uses the Apparmor system, neither the first nor the second option will work right away - MySQL simply won't start. So, after carrying out the steps for whichever option is convenient for you, before starting the MySQL server, we need to add some entries to Apparmor.
To do this, edit the file /etc/apparmor.d/usr.sbin.mysqld
Here's roughly what it should look like. Pay attention to the added lines regarding the /dir1/dir2 directories:
# vim:syntax=apparmor
# Last Modified: Tue Jun 19 17:37:30 2007
#include <tunables/global>
/usr/sbin/mysqld {
#include <abstractions/base>
#include <abstractions/nameservice>
#include <abstractions/user-tmp>
#include <abstractions/mysql>
#include <abstractions/winbind>
capability dac_override,
capability sys_resource,
capability setgid,
capability setuid,
network tcp,
/etc/hosts.allow r,
/etc/hosts.deny r,
/etc/mysql/*.pem r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/* r,
/etc/mysql/my.cnf r,
/usr/sbin/mysqld mr,
/usr/share/mysql/** r,
/var/log/mysql.log rw,
/var/log/mysql.err rw,
/dir1/dir2/ r,
/dir1/dir2/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
/var/run/mysqld/mysqld.pid w,
/var/run/mysqld/mysqld.sock w,
/sys/devices/system/cpu/ r,
}
Restart apparmor:
# /etc/init.d/apparmor restart
After editing this file, you can go ahead and start the MySQL server.
CentOS/RedHatStop MySQL:
# /etc/init.d/mysqld stop
Copy the current databases:
# cd /var/lib
# cp -R mysql /dir1/dir2
# chown -R mysql:mysql /dir1/dir2
Edit the file /etc/my.cnf
In this file, find the datadir option and change it to the path you need:
datadir = /dir1/dir2
Start MySQL:
# /etc/init.d/mysqld start
That's it, the database directory has been changed.
Applies to: MySQL on Debian (Lenny, Squeeze), Ubuntu, CentOS/RHEL, FreeBSD
Comments