Home:ALL Converter>Unable to change the MySQL data directory

Unable to change the MySQL data directory

Ask Time:2016-05-23T22:03:46         Author:Willy G

Json Formatter

I'm trying to change the data directory of my MySQL database from the default location /var/lib/mysql to a new path which in my case is /home/dev/Desktop/iiab/db/mysql.

I have tried following the answer provided by Victor in the following post: How to change MySQL data directory? (I have also edited the alias file as per the instruction from mak in the comments on Victor's answer).

BUT, I am getting the following terminal error when trying to start MySQL again:

mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

When inspecting the log file using below command

tail -f /var/log/mysql/error.log

It shows the following:

2016-05-23T13:43:11.608141Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2016-05-23T13:43:11.608234Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-05-23T13:43:11.608252Z 0 [ERROR] Failed to initialize plugins.
2016-05-23T13:43:11.608260Z 0 [ERROR] Aborting

2016-05-23T13:43:11.608297Z 0 [Note] Binlog end
2016-05-23T13:43:11.608416Z 0 [Note] Shutting down plugin 'MyISAM'
2016-05-23T13:43:11.608469Z 0 [Note] Shutting down plugin 'CSV'
2016-05-23T13:43:11.609124Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

My my.cnf file looks as follows (I omitted the initial comments ##):

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
datadir=/home/dev/Desktop/iiab/db/mysql
socket=/home/dev/Desktop/iiab/db//mysql.sock

My /etc/apparmor.d/usr.sbin.mysqld file looks as follows (I only included the parts which were affected by the instructions i.e. replace var/lib/mysql with the new path).

# Allow data dir access
  /home/dev/Desktop/iiab/db/mysql/ r,
  /home/dev/Desktop/iiab/db/mysql/** rwk,

# Allow data files dir access
  /home/dev/Desktop/iiab/db-files/ r,
  /home/dev/Desktop/iiab/db-files/** rwk,

# Allow keyring dir access
  /home/dev/Desktop/iiab/db-keyring/ r,
  /home/dev/Desktop/iiab/db-keyring/** rwk,

My /etc/apparmor.d/tunables/alias file looks as follows (I omitted initial comments ##):

# Or if mysql databases are stored in /home:
alias /var/lib/mysql/ -> /home/dev/Desktop/iiab/db/mysql/,

I am able to restart and reload apparmor successfully.

I'm using Ubuntu 16.04.

Anyone know what I am doing wrong?

Author:Willy G,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/37393172/unable-to-change-the-mysql-data-directory
yy