Linux – MySQL, NFS and symbolic links

debianlinuxMySQLnfssymbolic-link

I have been migrating an older server running apache2 + mysql under ubuntu to a new server running debian (wheezy).
The migration works fine while the databases are storted localy (in our case /srv/mysql), but when i try to move them to our centralized storage running NFS and creating symbolic links to the moved files mysql does not seem to find the databases at all. I get no errors from mysql, it just seems to believe there is no such database.

This is the layout of /srv/mysql (few examples):

user@server:/srv/mysql# ls -al
total 135440
drwxr-xr-x 50 mysql mysql      4096 May 22 09:59 .
drwxr-xr-x  7 root  root       4096 May 22 09:59 ..
drwxrwx---  2 mysql mysql      4096 May 21 20:13 database_dir_1
drwx------  2 mysql mysql      4096 May 21 19:07 database_dir_2
drwxrwx---  2 mysql mysql      4096 May 21 20:15 database_dir_3
drwx------  2 mysql mysql      4096 May 21 20:15 database_dir_4
drwxrwx---  2 mysql mysql      4096 May 21 20:15 database_dir_5

How i create symbolic links:

mv /srv/mysql/database_dir_1 /mnt/centralstorage/customer1/db/database_dir_1
ln -s /mnt/centralstorage/customer1/db/database_dir_1 /srv/mysql/database_dir_1
ls -al /srv/mysql/
drwxrwx---  1 root root      28 May 21 20:13 database_dir_1 -> /mnt/centralstorage/customer1/db/database_dir_1

After this mysql no longer sees database_dir_1, but it is fully browseable from cli.

The mounts for /mnt/centralstorage looks like this:

192.168.12.222:/srv/storage/customers on /mnt/centralstorage/ type nfs (rw,relatime,vers=3,rsize=1048576,wsize=1048576,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.12.222,mountvers=3,mountport=49535,mountproto=udp,local_lock=none,addr=192.168.12.222)

And the export on the central server:

/srv/storage 192.168.12.30(rw,async,no_subtree_check,no_root_squash)

(all names and such have been changed)

Anyone see any issues with the setup?

With regards,
FrontSlash

Edit1:

After some help from @Fox the problem seems to be the NFS connection.
Does anyone see any issues in the nfs configuration i posted above? If you need more information i will post it.

Edit2:

Did a quick test, exportet a new folder on the NFS server, /srv/temp, with the same settings as the other two exports.

Mounted this on the sql server with fstab instead of the previous startup-script that ran.

The script simply did a 

mount $host:$dir $mnt_dir/$mmount

Which produced this mount:

192.168.12.222:/srv/storage/customers on /mnt/centralstorage/ type nfs (rw,relatime,vers=3,rsize=1048576,wsize=1048576,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.12.222,mountvers=3,mountport=49535,mountproto=udp,local_lock=none,addr=192.168.12.222)

The fstab mount:

192.168.12.222:/srv/temp /mnt/temp nfs rw,sync,hard,intr 0   0

Produced this:

192.168.12.222:/srv/temp on /mnt/temp type nfs (rw,relatime,vers=3,rsize=1048576,wsize=1048576,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.12.222,mountvers=3,mountport=49535,mountproto=udp,local_lock=none,addr=192.168.12.222)

And here is the weird part, now moving the database dir to the /mnt/temp folder, and creating a link to this, it works. I will continue exploring.

Edit3:
Solution added as answer, nfs-kernel-server had option –manage-gids in /etc/nfs-kernel-server that affected secondary groups for the mysql user.

Best Answer

You don't state what engine are you using, but let me presume it's InnoDB (as it's pretty much standard these days) then in MySQL Docs

(Using actual symbolic links has never been supported for InnoDB tables.)

and

The DATA DIRECTORY clause is a supported alternative to using symbolic links, which has always been problematic and was never supported for individual InnoDB tables.

You probably could create the .isl files by hand (but test before doing that on live).

And there is one warning that may be of interest:

Do not put MySQL tables on an NFS-mounted volume. NFS uses a message-passing protocol to write to files, which could cause data inconsistency if network messages are lost or received out of order.

Edit: alrighty then... this was not the correct answer, as it's not InnoDB. But I'll keep it in case someone else gets here looking for InnoDB solution.

There is further reading on MySQL and symlinks.

Especially interesting might be

If you are not using symlinks, start mysqld with the --skip-symbolic-links option to ensure that no one can use mysqld to drop or rename a file outside of the data directory.

As that could be default on Debian. (I do not know.)

Edit2: Ok a better way of checking: 

SHOW VARIABLES LIKE 'have_symlink';

Another reason for not picking up databases from outside the data-dir is AppArmor, or similar security measure.

btw. it would be worth testing, if it's related to NFS - if symlinks to a completely different part of local fs (or better different fs at all) works it's in NFS, if it doesen't, it's in symlinks ...

Related Topic