All-GUI MySQL on Mac

aka “How to use multiple MySQL Servers and Workbench in Snow Leopard without using Terminal… and live happily ever after”

The MySQL Community is a world of command-line aficionados. Many people, including myself, show their love to the simple-but-powerful interface of the mysql command-line client, but not everybody is keen to use a bash shell and give up its GUI, no matter how powerful the software is.

Until recently, GUI tools for MySQL were half baked solutions: in the end, there was always something that you had to do via the command line. Today, you can install, set up and use MySQL on your Mac with Snow Leopard without using Terminal, at all.

My Special Needs

Before digging into the details of the installation, let me describe what I need on my Mac. I use various versions of MySQL and I often need to run 2 or more instances at the same time. I constantly build, install and uninstall versions of MySQL and I need to find a way to quickly remove (or move) one version of MySQL with data, configuration files and all that is related to a specific instance.

Not everybody has complicated environments and multiple instances; for many users, a single installation would be enough. If you are one of these lucky users, you may skip some steps. In any case, everything presented in this article is applicable to 1 or more instances running on a single Mac.

The Ingredients

All the software is available on the MySQL Dev site. You will need 2 kits: MySQL Server and MySQL Workbench. I used the latest .dmg archive from http://dev.mysql.com/downloads and the latest version of Workbench, from here http://dev.mysql.com/downloads/workbench/5.2.html. At the time I am preparing this post, the latest versions are MySQL Server 5.6.0-m4 and MySQL Workbench 5.2.27.

Preparation

I have my own way of keeping multiple instances on a single mac, and I am sure you can find many others, perhaps they would make even more sense for you. There are some good tools and scripts that may help you (just google a bit or visit confidently Giuseppe Maxia’s blog: http://datacharmer.blogspot.com).

In a nushell, my way of keeping everything separated can be described as:

• Select a parent directory: in my case, I choose /usr/local. For example, the new version of the server will be available on /usr/local/mysql-5.5.6-m4-osx10.6-x86_64. Everything related to this version is in there.
• Select a data directory: I simply use the data sub-directory in the base directory. For an instance on a laptop, used for basic tests, that would be just fine.
• Keep the config files in the base directory: For example, the configuration file, by default, would be /etc/my.cnf. I will use /usr/local/mysql-5.5.6-m4-osx10.6-x86_64/my.cnf instead.
• Select a socket file: in this case, I keep the socket file in the /tmp directory. My suggestion is to use a naming convention, such as mysql-<version>.sock. For example, the socket file for this version would be mysql-5.6.0-m4.sock.
• Identify a specific TCP port for your version: avoid the default port (3306) and start from 3307 ore any other port. In this way, your client tools will not accidentally connect to a server because you forget to specify the TCP port.

Cooking Instructions

Once you have downloaded the software, you are ready to install it. You should start with MySQL Server. When you open the DMG archive, you should see something this:

MySQL Server Package

Just doubleclick on the main package and follow the default instructions, and you are done in literally 1 minute.

By default, the package creates a symbolic link to the base directory. You should see a base dir created under /usr/local. Finder does not allow you to browse /usr/local, but if you go do Go To Folder in the Go menu, you can insert /usr/local in the folder text box and see this window:

Go To Folder

As you can see, there is a mysql sym link (/usr/local/mysql) and a new mysql-5.6.0-m4-osx10.6-x86_64, which is the base directory that you have just installed.

Local Dir

My recommendation is to remove the mysql link, in order to avoid any confusion with multiple instances. Since the server has been installed with the superuser, you need to retype the superuser password again to remove the link.

Another important point to consider is that the installer has created a base directory owned by the superuser, with Read/Write access. Standard users have ReadOnly access. You should open the Info dialog (right click on the dir and select Get Info), then you should grant Read/Write access to everyone.

MySQL Base Dir Info

Change to RW

Now it’s time to install MySQL Workbench. This is an even easier task, since you just need to open the DMG archive to expose the application:

MySQL Workbench Package

The next step is to drag and drop the Workbench icon into your favourite application folder or subfolder.

Easy as 1..2..3, done. Just double click on Workbench and you will see the main window:

MySQL Workbench Home Page

The next step is to create a new server instance. Click on the New Server Instance item on the right side of the main window and you will see this dialog:

Create New Server Instance

The instance will run on your local Mac, so leave the radio button on localhost selected. By clicking the Continue button, you will see the next page:

Create New Server Instance

As Connection Name, I used the version of the server (mysql-5.6.0.-m4) and I have selected Local Socket as Connection Method. Now you need to specify the socket file and path. I usually leave the file in the /tmp directory, therefore the file and path would be /tmp/mysql-5.6.0-m4.sock.

I do not have any relevant information in my instances, so a root access with simple or no password is enough. I use to store it in the Keychain, in order to avoid the request every time I need to open a new connection.

Store Password for Connection

By clicking Continue on the main dialog window, you will move to a testing page. Here Workbench will try to connect to the server. Since the server is down, you will see a set of errors, they are absolutely fine at this stage.

Testing the DB Connection

By clicking Continue, you will be able to specify the operating system and the package you are using. These combos are already selected for you as MacOS X and MySQL Package. When you will open the profile to change some parameters, you will notice that the Installation Type will be different: this is absolutely normal, since Workbench identifies the fact that we have overridden some defaults as a custom installation.

Specifying the OS

When you click Continue, you will test the host settings and again, you will see some errors, due to the fact that the instance is not running (see image below).

Testing the Host Settings

Another click on the very same button brings you to the MySQL Config Page. In this page you can specify the version of the server and the path to the configuration file. Personally I am very lazy and with fat fingers, so I prefer to see the software to provide the path for me. I will use the same trick adopted in Finder, i.e. I will type the first or first two directories in the path, then I will click “…” to select the rest of the path:

Info About MySQL Conf

Specify Path to the Conf File

Here you can select the base directory and from the base directory you can select a file. My recommendation is to select the README file:

Select README

When you click the Open button, the README file and path will fill the Path text box. Now you can change README with my.cnf:

Change README to my.cnf

You are ready to click Continue again. The next page shows you the commands used to administer the server instance. You can leave these commands as is at the moment, you will change them in a minute.

Commands to Manage the Server

The last click on Continue brings you to the final page, where you can give a name to the profile. Again, I used the version and the location to name the profile.

Create Instance Profile

You can finally create the Profile by clicking the Finish button. The result is a new instance in the Server Administration area.

New Instance Created

Now you may want to change the way you administer the instance, by selecting more details that were not present in the wizard. You can do so by clicking the Manager Server Instances item.

The output window shows the connection parameters that have been set in the wizard.

Manager Server Instances - Connection

By clicking System Profile, you will see a new set of parameters. Some of them were not present in the previous wizard, but you can review and alter them now. You may have noticed that the Installation Type is now Custom, because the path of the configuration file has changed. Now what you should do is to update the start and stop commands in this page, since they still refer to the symbolic link that I removed.

Manage Server Instances – System Profile

This is probably the trickiest part of the configuration. In general, the start and stop commands require the privilege of superuser (or you must play more with the owneship and the grants of files and directories). I had lots of problems with the sudo commands and in the end I decided to use a trick. Again, this trick is not great, since it exposes my user password on the local machine, but I do not have any problem with that, so I decided to adopt it. Basically, I added the sudo command to the start and stop lines and I unchecked the checkbox underneath. The result is that I do not rely on the use of two commands combined in one action, but I can confidently test and execute a command in one go.

The final start and stop commands are something like this:

Start:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqld_safe --defaults-file=my.cnf"

Stop:

echo mypwd | sudo -S /bin/sh -c "cd /usr/local/mysql-5.6.0-m4-osx10.6-x86_64; bin/mysqladmin -uroot --socket=/tmp/mysql-5.6.0-m4.sock shutdown"

The concept is pretty simple: you type a password and you pass it to a sudo command. The command then executes a shell and passes the command string between quotes to the shell. If this explanation does not make sense to you, don’t worry, just replace mypwd with your machine password. Just remember, if you are using special characters, i.e. non digits or letters, you should use the \ symbol as prefix. Hence, the password “Good Job!” should be “Good\ Job\!”. You may notice that I have also removed the sudo command completely, just to avoid any misunderstanding.

Change Start-Stop

When you click the Close button, you are finally ready to go! Now you can doubleclick the new instance profile and Workbench will try to connect to the instance. Again, MySQL is not running, so you should see a dialog that alerts you and you should click Continue Anyway.

Open a MySQL Instance

After few seconds, you will see the main server instance window:

Server Instance Window

The MySQL Server is stopped and you need to set few more parameters before you can have it up and running. Click the Configuration Item and start with the General tab. Here you should change the TCP port (as advised, so you will not accidentally connect to any instance by default), the base directory and the data directory. The same trick to select a root directory first is applicable here, since the two directories should be respectively /usr/local/mysql-5.6.0-m4-osx10.6-x86_64 and /usr/local/mysql-5.6.0-m4-osx10.6-x86_64/data.

Instance Configuration - General

Once you have set the directories, you need to change a network parameter. Click the Networking tab and specify the socket file as we have previously defined (/tmp/mysql-5.6.0-m4.sock).

Instance Configuration - Networking

Now you can click the Apply button and create the my.cnf file for the very first time. The dialog that appears is just a confirmation of the actions to take. You will create the file by clicking Apply again.

Conf File Changes

You can finally select the server instance again and click on Start Server. The first time you start the server, allow your Mac to work for 40-50 seconds or even a minute or so. MySQL needs to create the InnoDB files and it will take a while.

Open Instance

Finally, you will be rewarded with this screen:

Instance Running

Congratulations!

Now, you may argue it’s overcomplicated, but again, everything is self contained and you can run as many instances as you like at the same time, without touching the Terminal.

Enjoy!

As last bit, you should check the connection parameters set by the wizard, by selecting the new connection on the left and clicking the Manage Connection option. Here, I would just recommend to give a good name to the connection.

Manage Connection

Now you can close the window and doubleclick the new connection. The query window will appear:

Open Connection

You can now start using the connection, by executing queries or creating objects with the SQL editor or with the data modeler.

But the beauty of Workbench is material for another post!