Symbolic link to mysql database

I had to make a symbolic link on one of databases to another location on MySQL (Debian 6.0). So /var/lib/mysql/dbname points to /home/user/dbname, but MySQL gives

MySQL error: 1017 (Can't find file: './dbname/tbl201206.frm' (errno: 13))

I have read some manuals about making symbolic links on data dirs or table files, but can mysqld read also this kind of links?

Solution 1:

The error code (errno: 13) means permission denied. Check that the mysql user has permission to read and write /home/user/dbname and all the files below it.

You can do this with sudo su mysql and then some command that accesses the file such as file /home/user/dbname/tbl201206.frm.

To fix it, you probably want to do chown -R mysql:mysql /home/user/dbname. You also may need chmod +x /home/user.

Solution 2:

If you are running Ubuntu (and possibly other recent Linux versions), then it can happen that MySQL is denied access to the location of your file, even though the file permissions are correct. This is caused by AppArmor, a Linux security module that implements name-based mandatory access controls. To check if you are running AppArmor, try the command:

$ sudo apparmor_status

If this works and includes mysqld, you will probably need to change your configuration. This has been described in a helpful post by andol over at AskUbuntu, but this solved the problem for me only at first glance (I reproduce andol's post below for convenience, and in case the other page changes).

After moving the database file and placing a symlink, the file did not grow any further and the main InnoDB file was written to instead (even though I do use innodb_file_per_table to have individual DB files). I assume now that the changes suggested in the post by andol (and many other places on the web) are not in fact sufficient. I have eventually moved my whole MySQL data directory and edited the AppArmor file /etc/apparmor.d/tunables/alias instead (and no other file) to make the move (documentation is in the file). This worked properly on Ubuntu 12.4 (precise).

Here is what andol wrote -- as I said above, this did not work for me.

Let us assume that your new datadir will be /home/data/mysql.

If you open the file /etc/apparmor.d/usr.sbin.mysqld you will among the rules find these two lines.

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

Assuming our example above, they will have to be replaced or (probably preferable) complemented by these two lines.

/home/data/mysql/ r,
/home/data/mysql/** rwk,

Before we can startup our MySQL server, with its new datadir, we will also have to explicitly reload our new apparmor profile.

$ sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld