Change your MySQL datafolder

When you use MySQL, it defaults its data files to /var/lib/mysql. In case you have a separate data disk mounted (e.g. /data). You might want to change the data folder used by MySQL. You can change the folder if you follow these steps:

Stop your mysql instance:

$ sudo service mysql stop

Create the datadir folder, eg. /data/mysql/data

$ mkdir -p /data/mysql/data

Change the owner of the data folder

$ sudo chown -R mysql:mysql /data/mysql/data

Copy the existing data from the /var/lib/mysql folder to /data/mysql/data

$ sudo cp -a /var/lib/mysql/* /data/mysql/data/

Note: this would copy a directory and retain the permissions of the copied folder

$ sudo cp -rp /var/lib/mysql/* /data/mysql/data/

Edit the MySQL config file (my.cnf) and update the datadir configuration

$ sudo vi /etc/mysql/my.cnf
 
...
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
# datadir = /var/lib/mysql
datadir = /data/mysql/data
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
...

AppArmor is not keen on changes. So make sure that it allows you to change the datadir of MySQL

$ sudo vi /etc/apparmor.d/usr.sbin.mysqld
 
# vim:syntax=apparmor
 
# Last Modified: Tue Jun 19 17:37:30 2007
#include 
 
/usr/sbin/mysqld {
...
 
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/var/log/mysql/ r,
/var/log/mysql/* rw,
...
/run/mysqld/mysqld.sock w,
/data/mysql/data/ r,
/data/mysql/data/** rwk,
 
...
}

Add the configuration of the datadir at the bottom (in our case /data/mysql/data/)


Restart both apparmor and mysql

$ sudo service apparmor restart
* Reloading AppArmor profiles Skipping profile in /etc/apparmor.d/disable: usr.sbin.rsyslogd
[ OK ]
$ sudo service mysql restart
mysql stop/waiting
mysql start/running, process 1532

And now you are done.