can't start mysql56 after move datadir

[Murray Eisenberg]

Thanks to repeated help from Ryan Schmidt and Brandon Allbery, I finally was able to get mysql56 to work with a non-default datadir location. Here's what I did:

0. Uninstalled the macports mysql56-server and 
   deleted both /opt/local/var/db/mysql56 and /Volumes/MacHD/Users/thisuser/Databases/mysql/data.

1. Verified that the existing /opt/local/etc/mysql56/my.cnf included the desired setting for datadir:

   /Volumes/MacHD/Users/thisuser/Databases/mysql/data

2. Created directory /Volumes/MacHD/Users/thisuser/Databases/mysql/data and changed its owner to _mysql:

   chown _mysql:_mysql /Volumes/MacHD/Users/thisuser/Databases/mysql/data

3. [First crucial step] Gave user _mysql search permission to those directories on the path to the datadir that did not already have +x permission for all:

   chmod +a "_mysql allow search" /Volumes/MacHD/Users/thisuser/Databases

   chmod +a "_mysql allow search" /Volumes/MacHD/Users/thisuser/Databases/mysql

4. Followed Steps 1 and 2 at https://trac.macports.org/wiki/howto/MySQL ... _EXCEPT_ near the end of Step 2...

5. [Second crucial step] Created a database but explicitly specifying the datadir; this is a modification of the end of Step 2 at https://trac.macports.org/wiki/howto/MySQL. 

   sudo -u _mysql mysql_install_db --datadir=/Volumes/MacHD/Users/thisuser/Databases/mysql/data

6. Continued with the rest of Step 2 at https://trac.macports.org/wiki/howto/MySQL to change ownership to _mysql for 
   /opt/local/var/db/mysql56/ , /opt/local/var/run/mysql56/ , and /opt/local/var/log/mysql56/

7. Continued with the remaining Steps 4-5 at https://trac.macports.org/wiki/howto/MySQL.


Perhaps such a drastic procedure is not required, and it would suffice to install mysql56-server as usual, but stopping short of creating a database; edit my.cnf to specify the non-default datadir; and carry out the two "crucial steps" above.


> On 12 Feb2015, at 5:05 PM, Murray Eisenberg <murrayeisenberg at gmail.com> wrote [corrected:
> 
> After upgrading from OS X Mavericks to Yosemite, I did the standard procedure for migrating macports. But I found that the mysql server would not start. So I uninstalled mysql56 completely and installed anew. Worked just fine.
> 
> But, I want my datadir to be someplace else from the default, so I edited my.cnf as shown below, then did an _exact_ copy (* preserving permissions *) of /opt/local/var/db/mysql56 to new location ~/Databases/mysql/data, i.e.,
> /Volumes/MacHD/Users/thisuser/Databases/mysql/data.
> 
> When I execute
> 
>   sudo /opt/local/share/mysql56/support-files/mysql.server start
> 
> now I get the dreaded message
> 
>   Starting MySQL
> . ERROR! The server quit without updating PID file (/Volumes/MacHD/Users/thisuser/Databases/mysql/data/MyMachineName.local.pid).
> 
> ...

> What's wrong?
> 
> Below is my /opt/local/etc/mysql56/my.cnf.
> 
> 
>   ========== begin file my.cnf ===========
> 
> # Use default MacPorts settings
> !include /opt/local/etc/mysql56/macports-default.cnf
> 
> [mysqld]
> datadir = /Volumes/MacHD/Users/thisuser/Databases/mysql/data 
> port = 3306
> socket = /opt/local/var/run/mysql56/mysqld.sock
> 
> sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
> 
> [client]
> socket = /opt/local/var/run/mysql56/mysqld.sock
> 
>    ============ end file =============

---
Murray Eisenberg		murrayeisenberg at gmail.com
503 King Farm Blvd #101		Home (240)-246-7240
Rockville, MD 20850-6667	Mobile (413)-427-5334