Moving the database out of /var/lib/mysql

模重复平方算法

[警告] 苹果apple id密码重置漏洞

Mar 21

felix021 @ 2013-3-21 23:44 [IT » 数据库] 评论(1) , 引用(0) , 阅读(21092) | 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): '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,

欢迎扫码关注：