emmby

Turns out that it's fairly simple, really. The basic idea is that you edit your /etc/my.cnf to switch to using the MySQL Instance Manager and then to set up a second instance using a different socket, port, and data directory.

The following steps assume that you've got a standard MySQL 5.1.x for Mac OS X installation. In other words, you probably installed the mysql-*.pkg and the MySQLStartupItem.pkg from the dmg available on the MySQL website.

1. Verify everything is working in your existing instance, however you want to do it. For me, I do a quick check by doing:
   

   echo "show databases" | mysql -u root -p

   
   
2. Stop your mysql server (sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop) then, switch to using MySQL's instance manager by adding the following two lines to the top of your /etc/my.cnf:
   

   [mysql.server]
   use-manager

3. Start your mysql server (... MySQLCOM start), then verify everything is working and that you can still connect to your databases.
   
   
4. Stop the server again, then add a second instance to my.cnf, being sure to set socket, port, and datadir. You can pick whatever you want, as long as it's not the values used by your other instance (which default to 3306, /tmp/mysql.sock, and /usr/local/mysql/data, respectively):
   

   [mysqld2]
   socket=/tmp/mysql.sock2
   port=3307
   datadir=/usr/local/mysql/data2
   

5. Set up the second data directory. If you already have one then you can skip this step. Otherwise, make sure you specify the same user that owns your /usr/local/mysql/data dir when creating your second data directory ( _mysql in my case, and probably in yours)
   

   cd /usr/local/mysql
   sudo scripts/mysql_install_db --user=_mysql --datadir=/usr/local/mysql/data2

6. Start it up (... MySQLCOM start) and test it out!
   

   echo "show databases" | mysql -u root -p
   echo "show databases" | mysql --sock=/tmp/mysql.sock2

   
   

If everything went according to plan, you should now have two mysql instances that are each accessible on their own socket or port.

Here's my complete my.cnf file for my two instances:

[mysql.server]
use-manager

[mysqld]

[mysqld2]
socket=/tmp/mysql.sock2
port=3307
datadir=/usr/local/mysql/data2

References
1. http://dev.mysql.com/doc/refman/5.0/en/instance-manager-startup-process.html