MySQL on MacOS

From Exterior Memory
Jump to: navigation, search


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 >
<plist version='1.0'>

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 (, 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 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    =
# 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: freek@mickey

Now you can connect to 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