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