Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

10.5: Control MySQL server with launchd UNIX
I recently installed a copy of the MySQL database server on my Leopard machine. The MySQL site offers a packaged version of MySQL 5.0.x for OS X 10.4 on their downloads page (current as of this posting: 5.0.51).

This 10.4 version works perfectly well with 10.5, except for some minor inconveniences. The first is that they include a preference pane for System Preferences, which doesn't work. They also offer to install a StartupItem to start the database server during boot. This works pretty well, except that Apple has marked StartupItems as depreciated since 10.4, noting that you should use launchd instead. There are many hints out there to create a launchd job using the MySQL supplied launchd_safe, which is basically a shell script for starting, monitoring, and restarting mysqld. That's a pretty ugly solution to start a server with launchd, and is actually discouraged by Apple.

So this hint is about replacing the StartupItem and the mysqld_safe script with a simple launchd job. It assumes you are familiar with the Terminal, logged with an administrator account, and have basic knowledge of launchctl and launchd.plist files. Also being familiar with MySQL configuration might help.

Here's how to set it up:
  1. Install the latest copy of mySQL (if not already installed) using the links above. This will place a copy of mysql in /usr/local/mysql-version#, and create a link at /usr/local/mysql. It might offer to install the mentioned StartupItem; do not do this. If you already have this StartupItem, remove it (it's located in /Library/StartupItems).
  2. Create the following plist file in your favorite text editor, or use the Property List Editor which comes with XCode, and save it as com.mysql.mysqld.plist:
  3. Copy (or move) this file to /Library/LaunchDaemons, and then change ownership and permissions in Terminal (supply your login password when asked):
    $ cd /Library/LaunchDaemons
    $ sudo chown root com.mysql.mysqld
    $ sudo chgrp wheel com.mysql.mysqld
    $ sudo chmod 644 com.mysql.mysqld
  4. Afterwards, submit the job to launchd by typing sudo launchctl load com.mysql.mysqld.plist. The server should start immediately. If not, check with Console.app for error messages from launchd with the tag com.mysql.mysqld.
This example implements just a simple mysql daemon install. You can modify the plist file to suit your needs. If you edit the file after you submitted it to launchd via launchctl load, you should always remember to first execute a launchctl unload com.mysql.mysqld.plist, check for user, group, and permissions, and then resubmit via launchctl load as in steps three and four above.

You can add various command-line options to mysqld if you like (check the MySQL documentation for all the options). In its default configuration, mysqld will also read the /etc/my.cnf file during startup, but if you are like me and have all configuration in one place, you can add them right here in the launchd.plist. There are command line options for all my.cnf parameters. As an example, I added the following to the ProgramArguments array, since I like to keep my database in the Library folder (whereas the default would be /usr/local/mysql/data):
<string>--datadir=/Library/mySQL/data</string>
<string>--pid-file=/Library/mySQL/data/iMac.local.pid</string>
You could also add something like the following to get specific log and error information (if you don't add these, they won't be lost; they end up in the general log in Console.app):
<key>StandardErrorPath</key>
<string>/var/log/mysqlerr.log</string>
<key>StandardOutPath</key>
<string>/var/log/mysqlout.log</string>
The provided launchd example also implements an always-running daemon (via RunAtLoad and KeepAlive), which starts at boot and never quits (and if it does, launchd restarts it). It should be possible to implement an "on demand server" by implementing a Sockets section in the plist file, but I haven't tried this (read man launchd.plist for details, or have a look the various plist files in /System/Library/LaunchDaemons).
    •    
  • Currently 2.88 / 5
  You rated: 5 / 5 (8 votes cast)
 
[42,573 views]  

10.5: Control MySQL server with launchd | 18 comments | Create New Account
Click here to return to the '10.5: Control MySQL server with launchd' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
10.5: Control MySQL server with launchd
Authored by: Anonymous on Feb 05, '08 10:22:34AM

How did you install that MySQL version on Leopard? I just tried and it completely destroyed my database, now none of my (small sites hosted on my comp) work at all.

I can't uninstall or downgrade because the installer tells me I can't install an older version on the newer on.e

---
Life only demands from you the strength you possess. Only one feat is possible - not to have run away.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: leamanc on Feb 05, '08 12:45:50PM

Unless you have a backup of your mysql data directory or have .sql dumps (you do, don't you?), you are pretty well hosed.

Upgrading mysql using MySQL's pkg installers has been a nightmare. You usually have to trash the receipt out of /Library/Receipts (this will fix your problem of not being able to install/upgrade) before running the new package. Sometimes it works fine, other times all your databases go missing after the upgrade is done.

I generally don't upgrade MySQL on a Mac once I've got it running, unless there is a glaring security hole or some great new feature. If I do plan to upgrade though, first step is to get .sql dumps of all my databases. Once the server has been upgraded (and if my databses are missing), I then run the .sql dumps and am back in business.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: Anonymous on Feb 06, '08 07:37:04AM

I had a ~30 hour old complete SQL dump so I was able to quickly get back online after removing all traces of the new installation and then installing the older version again. Lost some data though. The .MYI files all disappeared during the installation project.

Well I guess that is what you get for not having a fresh backup when installing new versions.

---
Life only demands from you the strength you possess. Only one feat is possible - not to have run away.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: derekhed on Feb 05, '08 01:44:33PM
Look in /usr/local/ for directories named 'mysql*'. All the installers I have used create a new directory named after that version and link 'mysql' to the new directory.

Your data will still exist in your old directory. See http://www.entropy.ch/software/macosx/mysql/ for more detailed instructions.

The good news is that unless you have deleted something, your data is probably still there.

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: Anonymous on Feb 06, '08 07:40:46AM

That is what I expected would happen as is has before (when I used 10.4) but no old SQL folder was present, only the newly created one.

---
Life only demands from you the strength you possess. Only one feat is possible - not to have run away.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: mmnw on Feb 06, '08 12:43:36AM

Actually what I did, before installing leopard I made a full backup of my tiger machine. On Leopard I did a fresh mySQL Install without the old databases present. Then when this new copy was installed and running, I copied my old databases from the tiger backup into /usr/local/mysql/data. Don't forget to set the right file permissions, mysql likes it's database files to be owned by the _mysql user and read and writeable only by this user.
I actually had some problems with the authorization settings, meaning all the tables in the "mysql" db. For some reason mysql was messing this up. What I did there was renaming my backup mysql-folder, then copying it over in the data directory, restarting mysql and then copying all the table content of the backup tables to the new mysql tables by hand (with sql comands).



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: mmnw on Feb 06, '08 01:09:06AM

I have to add something I forgot in the last comment. As mentioned in the hint itself after the succesfull install I moved my data directory to a different location, in my case /Library/mySQL/data (this is because I do a daily backup of the /Library Folder, but not the system folders).

I guess this would eleminate the problems with loosing the database after an upgrade as described by derekhed in a previous comment. This would even work without the described launchd method, as long as you keep a /etc/my.cnf file, with the appropriate datadir option, since the pkg installer won't overwrite this file.

In general I would advise to move the data folder out of the /usr/local/mysql*/ directory, this is not a good database location in general and especially on OS X. The whole directory structure of OS X is not layed out for this. I oriented my choice on the location of the apache documents folder, which is also located inside the /Library folder.




[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: max.m on Feb 05, '08 09:34:24PM
Can anyone explain why mysql has been ignoring a 64bit build for x86 since Apple made the transition?

http://dev.mysql.com/downloads/mysql/5.0.html#macosx-dmg

This has been raised as a mysql bug since 10/10/2006:
http://bugs.mysql.com/bug.php?id=23146

Please add to the bug report if you'd also like to see a mysql supported 64bit build for x86.

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: mmnw on Feb 06, '08 12:47:26AM

Good Idea to file a bug, never thought of this. I actually tried to compile it myself, but had no success. But then I'm not an expert there and didn't put much effort into it ...



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: mmnw on Feb 06, '08 08:54:00AM
Just found this (marked red) on the mySQL download page:
It is good practice to back up your data before installing any new version of software. Although MySQL has done its best to ensure a high level of quality, you should protect your data by making a backup. MySQL generally recommends that you dump and reload your tables from any previous version to upgrade to 5.0.
Guess it says it all ;)

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: kanenas on Apr 18, '08 09:39:01PM
When added to the launchd plist, the following works for launching a MySQL server installed via MacPorts under Tiger:
<key>Sockets</key>
<dict>
        <key>MySQL-file</key>
        <dict>
                <key>SockPathName</key>
                <string>/opt/local/var/run/mysql5/mysqld.sock</string>
        </dict>
</dict>
The same should work for other instances of MySQL using UNIX sockets if you change the value of SockPathName. You might need to add a "SockPathMode" property. For IP sockets, try adding the following to the "Sockets" dict:
        <key>MySQL-net</key>
        <dict>
                <key>SockServiceName</key>
                <string>mysql</string>
        </dict>
I suspect this won't work, as launchd binds to the mysql port and MySQL servers don't AFAIK support passing sockets a la inetd.

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: kanenas on Oct 06, '08 09:17:45AM

Another note for Tiger users: use the "OnDemand" key rather than "KeepAlive". "OnDemand" is supported but deprecated in Leopard.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: af3556 on Jan 14, '09 06:59:59PM
Using the Sockets key to "on-demand" start the MySQL server only works partly and by accident. The Sockets key requires the job to use a launch API (launchd.plist.5). Using the above config causes launchd to create the socket in /tmp/mysql.sock, and when the client connects launchd notices and starts the server. The MySQL server sees that the socket exists but it doesn't expect it to, as it didn't create it, so removes it and creates its own. From then on all is business as usual. Meanwhile the first client is left hanging on a socket that has now been unlinked and won't ever be answered. This has been tested on 10.5.5, MySQL 5.0.75 installed via Macports. Also if the server exits (is shutdown or crashes), I'm not sure that launchd will recreate it. So it doesn't look like it's possible to start the mysqld server on-demand, at least until someone adds launchd support to mysql.

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: aljoscha on Jul 02, '09 08:45:34AM

Thanks for the hint, but can you elaborate on why this method is better than the "mysqld_safe"-method?
I thought this was the method encouraged by mysql.com?
Cheers, Alex



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: mmnw on Oct 04, '09 09:46:25AM

It aint better per se, it's different. The method suggested by mysql.com totally ignores the architecture of OS X and treats it like any other *NIX system. mysqld_safe is a nice idea, but OS X already provides a tool for everything mysqld_safe does: watching over the daemon, logging and restarting if necessary. The only reason mysql.com suggests mysqld_safe is because nothing like launchd exists on other *NIX systems.



[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: palahala on Jul 02, '09 10:10:44AM
As for the preference pane not working: see bug 28854, which in the comments has a link to a fixed MySQL.prefPane package. Drop it in /Library/PreferencePanes/ (overwriting the existing package) and you're good.

[ Reply to This | # ]
10.5: Control MySQL server with launchd
Authored by: benthoma on Sep 17, '10 11:04:43PM

This is an *excellent* article. I upgraded from 10.5 to 10.6 and tried to reinstall MySQL, but did not remember hitting the Startup Piece. Things kind of worked until I got rid of the Startup Item. Thanks for pointing me in the right direction!



[ Reply to This | # ]
Connecting to MySQL from PHP scripts.
Authored by: Landau on Jul 22, '11 05:32:04AM
Upgrading to Lion makes it necessary to start MySql through launchd. One thing not mentioned in this article is that if you plan to access your databases from php scripts you probably want to create a php.ini file in /etc and reassign the mysql default socket To find the location of the socket file, open a command shell and type
netstat -ln | grep mysql
Create the php.ini file
sudo cp /etc/php.ini.default /etc/php.ini
Edit the file and change pdo_mysql.default_socket and mysql.default_socket to point to the location of the socket file.

[ Reply to This | # ]