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.
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.
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.
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:
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:
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:
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:
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:
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:
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.
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).
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:
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:
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:
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.
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.
You can finally create the Profile by clicking the Finish button. The result is a new instance in the Server Administration area.
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.
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"
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"
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.
After few seconds, you will see the main 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.
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).
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.
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.
Finally, you will be rewarded with this screen:
Now you can close the window and doubleclick the new connection. The query window will appear: