Moving the MySQL data directory

Has this happened to you?

You run for a while on the default installation of MySQL. The data increases and suddenly you realize that the system disk (a fast SSD of 256 GB) wont suffice. OK, we need to move the data directory to that extra disk you have, of 3TB. But how?

This is what you do ( I assume you have the new disk mounted and ready. I also use nano for editing, you can use vi or emacs if you wish )

1) sudo service mysql stop : stops your MySQL service
Copy the complete MySQL directory to the new disk
2) cp -rp /var/lib/mysql /new/mysql/dir
Time to add the new dirs to the AppArmor service
3) sudo nano /etc/apparmor.d/usr.sbin.mysqld
4) Add the following two lines to the bottom:
/new/mysql/dir r,
/new/mysql/dir/** rwk,
5) sudo service apparmor restart : restarts the AppArmor thingy
6) sudo nano /etc/mysql/my.cnf

(EDIT: Just followed my own notes, and in MySQL 5.7 it’s  etc/mysql/mysql.conf.d/mysqld.cnf)
7) Change datadir from:
8) sudo service mysql restart

You can now remove the old data directory

Credits to christonabike:

Leave a Reply

Your email address will not be published. Required fields are marked *

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