How to change data location on Ubuntu

Question

I already saw this question here: change mysql data directory but it's not very helpful.

1. What I want - to move my sql data folder to my different place (/media/data)

2. What I did:

   a) sudo service mysql stop

   b) mv /var/lib/mysql/ /media/data/

   c) sudo vim /etc/mysql/my/cnf:

   • section [mysqld]:

     datadir = /var/lib/mysql/ - > datadir = /media/data/lib/

   • section [mysqld_safe]:

     added: datadir = /media/data/lib/

   d) sudo vim /etc/apparmor.d/usr.sbin.mysqld:

   • added:

     /media/data/mysql/ r,

     /media/data/myql/** rwk,

   • commented out:

     #/var/lib/mysql/ r,

     #/var/lib/mysql/** rwk

   e) sudo /etc/init.d/apparmor reload

   f) sudo service mysql start

3. What I got:

   a) tail -f /var/log/mysql/error.log:

   120215 1:30:01 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 120215 1:30:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 120215 1:30:01 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation.

   b) Even when sometimes log is clean I cannot connect to server:

   mysql -u root -p

   Enter password:

   ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run /mysqld/mysqld.sock' (2)

Please help..

Answer 1

I've done that on ubuntu, I don't use apparmor but these are the settings I've changed, all under [mysqld] (I think mysql_safe also uses these settings)

datadir = /mysql_data
innodb_data_home_dir = /mysql_data
innodb_log_group_home_dir = /mysql_data

I've also put my log files there but that is not needed.

Answer 2

Simply delete ib_logfile0, ib_logfile1, ibdata1, *.err, *.pid files from your new data directory. Hope it will work for you!

Answer 3

It's a shame to admit but the problem was exactly what the error message stated: postgres user had no privileges to access to files even thought all files had correct access rights set. Unfortunately one level up directory hadn't...