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_5How 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_1After 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 0Produced 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.
Why don’t you point your MySQL server configuration to that directory instead of using symlinks?
–
Is there anything in your mysqld log?
–
@Sven There are 30 databases, all are stored under different folders on the central storage (/srv/centralstorage/customer1/db/database_dir_1/, /customer2/db/database_dir_2 and so on, which is why every database is symlinked to the folders. The /srv/centralstorage folder has roughly 400 folders, most of them are for apache data, but 30 of them also have databases.
–
@Fox All my mysql logs are quite empty, i will try enabling debuging (i guess?) to see if i can get anything
–
@Fox The general log shows pretty much nothing When the database is local: 150522 11:24:32 967 Connect database1@localhost on 967 Init DB database1 When its symlinked: 150522 11:25:01 968 Connect database1@localhost on 150522 11:25:02 969 Connect database1@localhost on And nothing happens, no errors
–
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-linksoption 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 …
Its all MyISAM, the server i migrated from is from 2008. Also, the old server has been running the same configuration since it was launched, symlinked databases stored on a central NFS server.
–
symbolic-links TRUE Is the output i get, so it seems to be enabled
–
| have_symlink | YES | Creating a symlink pointing to a local fs works, so i guess it has to be the NFS, damn
–
have you tried reading/writing the symlinked files as user mysql is running under? (not just directories, but the files)
I am starting to think that is has something to do with access, what would be a good test to see if mysql has r/w access?
–
Thanks for the help @Fox and @Sven, i have now solved the problem.
It was a nfs-kernel-server setting, /etc/defaults/nfs-kernel-server contained the option –manage-gids that breaks the usage of secondary groups. So while the mysql user had the right permissions through a secondary group the permissions on the nfs-server’s side were wrong.
Hope someone else with the issue sees this before wasting a good few hours!
With regards, FrontSlash
source : https://serverfault.com/questions/693785/mysql-nfs-and-symbolic-links