How to Move MySQL's Data Directory
2013-11-30
Sometimes it becomes necessary to move the location where MySQL stores databases. Either you have run out of room or something else has happened and you need to modify MySQL's data directory. Here's how to do it.
First, I would stop MySQL's daemon if you aren't running production databases currently (which I would suggest reading this entire guide and thinking of a different way of doing this if you are):
sudo service mysql stop
Next, move or copy the current database directory. I copied it because I had room and I wanted to play it safe. For Ubuntu, the default MySQL database directory is /var/lib/mysql, so to copy it I did:
sudo cp -R /var/lib/mysql /newdir/
where newdir is where I want all of the MySQL databases and data to be.
Then you'll need to edit your MySQL configuration file. Edit /etc/mysql/my.cnf and change this line
datadir = /var/lib/mysql
to this
datadir = /newdir/mysql
and change newdir to your chosen directory.
Now, you'll need to change part of AppArmor's MySQL entry to reflect your new database directory. Edit /etc/apparmor.d/usr.sbin.mysqld and change
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,to
/newdir/mysql/ r,
/newdir/mysql/** rwk,replacing newdir with your new directory. Now that you have changed everything, you can start MySQL again with sudo service mysql start.