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  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:

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

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

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

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