Difference between revisions of "Mysql change datadir location"
Latest revision as of 14:19, 15 October 2021
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
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.
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)
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 knows about the change
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
(restart mysql service and re-check kern.log for apparmor messages)