MySQL in VMWare Fusion

Whatever your development platform of choice, if you work with large amounts of data, you should be running a database management system. In the modern day, high quality DBMSs are available for whatever platform you’re probably using, and some are even available for free. For an important project on which my team is currently working, we have implemented MySQL, in part because it runs on all the same platforms that Mathematica does. Mathematica can interface with it easily, and it comes with decent administration tools. This means that we can program on Windows or Mac and run on either one, transparently.

All of our Macs also have Windows partitions, and we have Mathematica licenses for both. While it is possible to install MySQL under both operating systems, to do so would almost inevitably lead to inconsistencies between the datasets on the two sides. In development, this can complicate debugging, and it’s completely unacceptable in production.

A nice solution is to run the MySQL server under OSX, and to set up the Windows virtual machine as a client to it. This is not hard to do with a virtualization tool like VMWare Fusion, though it is not expressly documented.

To allow the windows account to see the mySQL server on the Mac side, you need to do three things

1. Create permissions on the MySQL server under OSX. For example, create a new account for root, limiting connectivity to hosts matching whatever the windows partition identifies itself as in its network control panel (with wildcards, this often comes to something like win-qae% or win-OFT%).

2. Create a server connection from the client (windows) side. When establishing the connection from windows, you can refer to the Mac by IP address or by its local network name, as appears at the top of the Sharing control panel (Mac-Pro.local in the example below).

sharing control panel

finding the computer's name (also, optionally turning on SSH to allow remote administration)

If you use DHCP, it may be necessary to use a wildcarded version of the laptop’s IP address (e.g. 192.168.%), though this obviously reduces security.

3. In Workbench under Windows, create a connection to a remote MySQL server. This can be pure TCP/IP (which requires you open port 3306 or any other you have set for your MySQL server) or TCP/IP over SSH (which only requires port 22 and encrypts the communication).

This is set up in the Connection Manager (left column, last entry on the WB home screen). Alternatively you can use the New Connection Wizard (click the “New Connection” link in that column).

If you want to be able to administer the database from Windows, including making database backups, you’ll need a couple more steps.

4. Go to the Mac Sharing control panel and enable Remote Login, which turns on SSH.

5. In Workbench under Windows, create a remote “command connection.” This is set up in the Server Instance Manager, which can be reached via the last link in the right column on the workbench home screen. Alternatively you can also use the New Server Instance Wizard.

When you open the server instance manager for an existing server instance, you will see a block of settings titled “Remote Adminstration” or “Remote Management” (select your instance in the left-hand list first). There are the 3 possible type settings (none, win-based, ssh-based) and you need ssh-based for this technique. The SSH hostname is your computer’s name:SSH port number (Mac-Pro.local:22 for example). SSH username is your account name on in OSX.

identify the host for the command connection

set the database connection values

set up remote administration

For the sake of your sanity, do not use the same username for your computer account and your database account. When MySQL asks for the password for user ‘fred’, you need to know which one it’s asking about.

If you have done all these things correctly, it will be possible to query the OSX database from within the Windows partition on the same machine, and to administer the database from either operating system.