Mysql change datadir location

datadir in [mysqld] section of a my.cnf (mysql configuration file) defines where db will store inserted data.

Change of the directory where mysql is storing its data

change option datadir in my.cnf for example we want /home/mysql

[mysqld]
datadir=/home/mysql 

Ubuntu and apparmor

apparmor controls where the binaries can writes its data and reads data from. The change of db settings only is not sufficient, you need to tell that datadir for mysql changed to apparmor service also.

apparmor alias

You can use alias to tell that instead of /var/lib/mysql will be /home/mysql used edit /etc/apparmor.d/tunables/alias file and set there

alias /var/lib/mysql/ -> /home/mysql/

(stop mysql service and move with cp -a /var/lib/mysql /home/ and start service again re-check kern.log for apparmor messages)

apparmor config

when you need to migrate data of a db to new schema you can run into problem that the new db should be on new partition, but old db should be kept on old partition. (When not enough space for both dbs on one partition). Then the using of the alias method not sufficies here. You need to tell to apparmor that there will be more locations/dirs that databases uses: Add into the file /etc/apparmor.d/usr.sbin.mysqld new location - example is for adding /mnt/db_vol/mysql (last two lines added)

#Allow data dir access
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/mnt/db_vol/mysql/ r,
/mnt/db_vol/mysql/** rwk,

Then make sure apparmor know about the change

aa-enforce /etc/apparmor.d/usr.sbin.mysqld

And restart mysql service

service mysql restart

apparmor complain mode for profile

You can also tell to apparmor that instead of blocking the access to non-configured directories for binary, to notify about braking the rules in kern.log Run following command to set complain mode for mysqld profile

aa-complain /etc/apparmor.d/usr.sbin.mysqld

(restart mysql service and re-check kern.log for apparmor messages)