Mar 21

Moving the database out of /var/lib/mysql 不指定

felix021 @ 2013-3-21 23:44 [IT » 数据库] 评论(1) , 引用(0) , 阅读(9717) | Via 本站原创 | |
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): ''; port: 3306
130321 22:48:26 [Note]  - '' resolves to '';
130321 22:48:26 [Note] Server socket created on IP: ''.
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,

转载请注明出自 ,如是转载文则注明原出处,谢谢:)
felix021 Email Homepage
2016-11-15 00:12
分页: 1/1 第一页 1 最后页
昵称   密码   *非必须
网址   电邮   [注册]