MySQL on MacOS

MySQL

MySQL is a relational database.

Installation Guide

I installed MySQL using ports on the Mac, with the "server" variant:

sudo port install mysql5 +server

If you already installed the non-server variant, do a sudo port cleanup mysql5 and try again.

Create databases

sudo -u mysql mysql_install_db5

Fixing LaunchDeamon script

When installing mysql5, ports creates an incorrect LaunchDaemon script. It is missing the program name in the ProgramArguments array.

This is the correct launchd plist:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE plist PUBLIC -//Apple Computer//DTD PLIST 1.0//EN
http://www.apple.com/DTDs/PropertyList-1.0.dtd >
<plist version='1.0'>
<dict>
<key>Label</key><string>org.darwinports.mysql5</string>
<key>Program</key><string>/opt/local/bin/daemondo</string>
<key>ProgramArguments</key>
<array>
	<string>daemondo</string>
	<string>--label=mysql5</string>
	<string>--start-cmd</string>
	<string>/opt/local/etc/LaunchDaemons/org.darwinports.mysql5/mysql5.wrapper</string>
	<string>start</string>
	<string>;</string>
	<string>--stop-cmd</string>
	<string>/opt/local/etc/LaunchDaemons/org.darwinports.mysql5/mysql5.wrapper</string>
	<string>stop</string>
	<string>;</string>
	<string>--restart-cmd</string>
	<string>/opt/local/etc/LaunchDaemons/org.darwinports.mysql5/mysql5.wrapper</string>
	<string>restart</string>
	<string>;</string>
	<string>--pid=none</string>
</array>
<key>Debug</key><false/>
<key>Disabled</key><false/>
<key>OnDemand</key><false/>
<key>RunAtLoad</key><false/>
</dict>
</plist>

See also DarwinPorts bug ID 10041.

Setting permissions

First set a password for the user "root":

mysqladmin5 -u root password 'my-new-password'

By default, mysql allows login from localhost (127.0.0.1), by hostname (still from the same host, but using the external IP of the host). For localhost, users do not need to give a password.

To make strict access rules, log in as root and remove the permission entries:

mysql5 -u root -p mysql

DELETE FROM user WHERE host!="localhost";
DELETE FROM user WHERE user="";
FLUSH privileges;

Then you can selectively create databases and grant privileges:

GRANT ALL PRIVILEGES ON *.* TO myuser@localhost IDENTIFIED BY 'MyBigSecret' WITH GRANT OPTION;
CREATE DATABASE mydatabase;
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON mydatabase.* TO dbuser@localhost IDENTIFIED BY 'AnotherSecret';
GRANT ALL ON mydatabase.* TO dbuser@localhost IDENTIFIED BY 'AnotherSecret';

Custom configuration

Finally, I changed these parameters in the MySQL configuration file. When installed with ports, the configuration file is located at /opt/local/etc/mysql5/my.cnf

# Custom configuration
# Only listen to localhost, for security reasons
bind-address    = 127.0.0.1
# Also search on short words (2 or 3 chars) with full text search
ft_min_word_len=2

For details on the last parameter, see MySQL bug id 12657 and bug id 18695

Starting LaunchDaemon script

Load the mysql service. The -w removes any "disabled" property:

sudo launchctl load -w /Library/LaunchDaemons/org.darwinports.mysql5.plist

MySQL is only accessible from the localhost. If you want to access it remotely, you first need to make a tunnel to the server. If the server is mickey:

ssh -L 3306:127.0.0.1:3306 freek@mickey

Now you can connect to 127.0.0.1 on the remote client to access the mysql server.

To stop MySQL, you must both call stop and unload:

sudo launchctl unload -w /Library/LaunchDaemons/org.darwinports.mysql5.plist

Note: start and stop are not necessary:

sudo launchctl start org.darwinports.mysql5
sudo launchctl stop org.darwinports.mysql5