Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to get a macports-installed MySQL to use a data directory stored inside my FileVault-protected home dir.

I used sudo cp -a /opt/local/var/db/mysql5 ~/db/ (the -a to ensure file permissions remain intact) and then replaced the original mysql5 directory with a soft link: sudo ln -s ~/db/mysql5 /opt/local/var/db/mysql5

However, when I now try to start MySQL it fails. It follows the soft link at least to the extent that it modifies some files in the ~/db/mysql5 dir, notably the error log which gets appended to it this:

110108 15:33:08 mysqld_safe Starting mysqld daemon with databases from /opt/local/var/db/mysql5
110108 15:33:08 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
110108 15:33:08 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
110108 15:33:08 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.
110108 15:33:08 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql5/ is case insensitive
110108 15:33:08 [Note] Plugin 'FEDERATED' is disabled.
110108 15:33:08 [Note] Plugin 'ndbcluster' is disabled.
/opt/local/libexec/mysqld: Table 'mysql.plugin' doesn't exist
110108 15:33:08 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110108 15:33:09  InnoDB: Started; log sequence number 4 1596664332
110108 15:33:09 [ERROR] /opt/local/libexec/mysqld: Can't create/write to file '/opt/local/var/db/mysql5/mac.local.pid' (Errcode: 13)
110108 15:33:09 [ERROR] Can't start server: can't create PID file: Permission denied
110108 15:33:09 mysqld_safe mysqld from pid file /opt/local/var/db/mysql5/gPod.local.pid ended

I can't see why MySQL can't create the pid file, since manually creating it using the _mysql user succeeds (sudo -u _mysql touch mac.local.pid from inside ~/db/mysql5)

Any ideas how to resolve this?

share|improve this question
1  
I am not a mysql user, but it seems to me that the "cp -a" should have been "cp -R", in case /opt/local/var/db/mysql5 had sub-directories. –  harrymc Jan 11 '11 at 7:11
    
For a moment I thought @harrymc figured that -a does not include -R, while it does: -a Same as -pPR. But I guess it's the implicit -P that Harry is referring to? -P If the -R option is specified, no symbolic links are followed. Might indeed be an issue. –  Arjan Jan 11 '11 at 9:59
    
Ooops, I guess "not followed" does not mean it does not create new symbolic links while copying. I guess it just implies it's not going to duplicate its content...? If true, then cp -a is fine after all. –  Arjan Jan 11 '11 at 10:01
    
@Arjan: No, I only got a very old page when searching for "cp man page". But I still think the poster should verify his cp result, just in case. –  harrymc Jan 11 '11 at 10:33
    
@GJ: Have you verified that the cp target was identical to the source on ALL attributes before replacing it by the link? And why did you use cp rather than mv? Do you have a backup of the replaced old directory? –  harrymc Jan 11 '11 at 13:07
show 1 more comment

4 Answers

I've commented on one of your similar questions, since I think there's a better way to store MySQL data in an encrypted state.


This sounds like a permissions issue. On your home folder, FileVault sets permissions to rwx------ (i.e. owner can read, write and execute, but the group and other users have no permissions), versus the default rwxr-xr-x of a typical folder.

The factor in play here is that execute permissions allow a user to search (a.k.a. traverse) the folder. So when the _mysql user invokes mysqld and it follows the symlink, it chokes trying to traverse your home folder. It's mysqld_safe that writes the error messages to the log, using the permissions of the user that invoked it (probably root if mysqld_safe is started using the MacPorts startup script, sudo /path/to/mysqld_safe or similar.)

You can test this hypothesis by running the following commands in sequence:

$ cd ~/db/mysql5/
$ sudo -u _mysql touch mac.local.pid

should succeed because there's no traversal involved, and

$ sudo -u _mysql touch ~/db/mysql5/mac.local.pid
$ sudo -u _mysql touch /Users/[username]/db/mysql5/mac.local.pid

should both fail, but

$ sudo -u _mysql touch ../mac.local.pid

will probably succeed, since ~/db/ probably has the more lenient default folder permissions mentioned earlier.

A straightforward solution would be to set the group on your home folder to _mysql, and give the group permissions to search your home folder:

$ sudo chown :_mysql ~
$ chmod g=x ~

but this has serious security ramifications. Anyone acting as the _mysql user (potentially through a vulnerability in MySQL) could navigate into your home folder and manipulate files and folders according to their permissions, by default rwxr-xr-x for folders and rw-r--r-- for files. As we saw earlier, this includes ~/db/ and any user-created folders or files, as well as ~/Public/ and ~/Sites/. Even though an attacker couldn't directly get a listing of files in your home folder, there are plenty of common files to read, and many ways to use them to gather data about what else is laying around. (.bash_history springs to mind.)

share|improve this answer
add comment
up vote 1 down vote accepted
+100

Tait Lewis explained very well the issue with the execute permission. However doing that to a real user account is both a security risk and may potentially prevent legitimate execution by other staff group members.

The way I solved the same problem was as follows:

  • Create a new standard user account (for this example we'll call it db) with FileVault enabled for it. Use a password at least as strong as that of your primary user.
  • While logged in as your primary user, run the following commands:
  • mount the FileVault image of the db user, without making it browsable in the Finder:

sudo hdiutil attach /Users/db/db.sparsebundle -owners on -mountpoint /Users/db -nobrowse

  • Fix the permissions so that the _mysql user can execute the db user's home directory:

sudo chown :_mysql /Users/db; sudo chmod g=x /Users/db

  • Shutdown MySQL:

sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper stop

  • Copy the MySQL files into the FileVault:

sudo cp -a /opt/local/var/db/mysql5 /Users/db/ (do NOT add a / after mysql5)

  • Now you either delete, secure delete, or just rename the /opt/local/var/db/mysql5 directory.

  • Next, create the soft link:

sudo ln -s /Users/db/mysql5 /opt/local/var/db/mysql5

  • Finally, start MySQL again:

sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start

Note that every time you reboot the Mac you'll need to mount the image (with the sudo hdiutil ... command) and start MySQL manually as it will fail to mount when launchd tries to start it automatically before the image is mounted.

share|improve this answer
add comment

First off, if you haven't already done so, be sure to verify that your version of MySQL will support symlinks for the table(s) in question. According to this 2008 article, symlinks were only supported for MyISAM tables at that time and "For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links."

Once you're convinced that your version of MySQL should support what you're doing, double-check which user you're trying to start mysqld_safe as and compare that against the permissions on /opt/local/var/db/mysql5/. Since you used cp -a, it's likely that the directory is owned and writable by the _mysql user (which is why it works when you sudo -u _mysql touch...), but you're getting failures when trying to start MySQL from your normal user account (which isn't _mysql and, therefore, wouldn't have write access to the directory).

share|improve this answer
1  
The article shows a trick how to avoid manually creating the symlink. As far as I understand, if the permissions are right and there are no special caveats imposed by the FileVault system, the symlinking should be totally transparent to MySQL and hence it's not an issue of MySQL compatibility. –  GJ. Jan 11 '11 at 11:59
add comment

This may be far fetched, but if you are soft linking to a separate partition, you could always mount that partition at the soft link point, then do a soft link where you would normally mount it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.