标题：Moving the database out of /var/lib/mysql
出处：Felix021
时间：Thu, 21 Mar 2013 23:44:54 +0000
作者：felix021
地址：https://www.felix021.com/blog/read.php?2113

内容：
After wasting over 45 minutes on moving database out of the default "/var/lib/mysql" on Ubuntu, I think this crazy problem should be written down, in case someone get confused by the same problem again.

I thought this problem to be very simple: go to /etc/mysql/, edit my.cnf, change the "datadir = /var/lib/mysql" to a new place, say, /home/mysql, and run "service mysql restart". But mysqld just refused to start, it says something like this:
引用
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130321 22:48:26 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130321 22:48:26 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130321 22:48:26 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
130321 22:48:26 [Note] Server socket created on IP: '127.0.0.1'.
130321 22:48:26 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
130321 22:48:26 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)


I'm very sure I've done chmod/chgrp to "/home/mysql", so it won't be file permission problem. It occurred to me that the original database 'mysql' may contain some path information, since it's a newly installation, so I backed up the mysql directory, tried to run "mysql_install_db --user=mysql --datadir=/home/mysql", and failed again:
引用
$ mysql_install_db --user=mysql --datadir=/home/mysql/ 
Installing MySQL system tables...
130321 23:18:17 [Warning] Can't create test file /home/mysql/localhost.lower-test
130321 23:18:17 [Warning] Can't create test file /home/mysql/localhost.lower-test

Installation of system tables failed!  Examine the logs in/home/mysql/ for more information.


and two more lines were added to the error.log:引用
ERROR: 1005  Can't create table 'db' (errno: 13)
130321 23:06:48 [ERROR] Aborting


That was weird. I used bash -x to run mysql_install_db again (it's a bash script) and found out that It started mysqld with some certain parameters which printed the Warning message shown above. So the problem went back to mysqld. A forum thread somewhere also noticed the problem but didn't come up with a solution.

With no choice, I went through the manual of mysql_install_db, and fortunately found a comment at the end of the page:
引用
Ubuntu 9.10
Moving the database from /var/lib/mysql to /data/databases/mysql

You'll get errors when running mysql_install_db until you go into /etc/apparmor.d, update the usr.sbin.mysql file, and run /etc/init.d/apparmor restart

You may also get an error when running /etc/init.d/mysql start:

Access denied for user debian-sys-maint at localhost

Check /etc/mysql/debian.cnf for the account information.

You'll need to run mysql, add the grant tables, and then restart mysql.


As the 'usr.sbin.mysql' requests, I added two lines to the end of "/etc/apparmor.d/local/user.sbin.mysql" and restarted apparmor(/etc/init.d/apparmor restart), thus fixed the problem.
引用
  /home/mysql/ r,
  /home/mysql/** rwk,




Generated by Bo-blog 2.1.0