Update (8/7/2012): After setting up a new OS X (Lion), I had installed MySQL via MacPorts and just moved my db files over to the case sensitive partition. That works fine (been using it this way for a month). With exception to the note below, I didn’t have to go through the trouble of moving the entire MySQL install. So in my case, I moved /opt/local/var/db/mysql5 to my other partition /Volumes/Macintosh HD 2/mysql5. Then created a symbolic link pointing from the old to the new location. That’s it.
MySQL Workbench doesn’t play nice with case insensitive file systems when you try to use the db synchronization feature on schemas with mixed case table names. …And the lower_case_table_names option in MySQL doesn’t quite fix it.
Ultimately, I decided to copy and move MySQL over to a case sensitive partition that was created. The result… MySQL Workbench db synchronization now works as expected.
Also, I didn’t have to worry about crapping up anything since I was just making a copy of MySQL and pointing the “mysql” sym link to it’s new location; plus it only takes 10 minutes to setup.
To get started (this may vary slightly on your OS version):
- Create a partition using Applications>>Utilities>>Disk Utility. Make sure under “Format” you select a “Case-sensitive” format. I went with “Mac OS Extended (Case-Sensitive, Journaled)”.
- Stop MySQL if it’s running:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
- Copy your current MySQL install to the new partition:
$ sudo cp -rp /usr/local/mysql /Volumes/Macintosh/NewPartition
- Point the symbolic link “mysql” in /usr/local/mysql to the new location:
$ sudo ln -sf /Volumes/Macintosh/NewPartition/mysql /usr/local/mysql
- Start MySQL back up:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
- You’re finished. If it doesn’t work for you, you can always point your “mysql” sym link back to your original install and restart.
If you run $ mysqladmin -u root -p variables …you’ll see that “lower_case_table_names” automatically sets to zero (case sensitive setting).
Note: You may have to drop and rebuild any schemas/tables that you copied over. This seems to only apply to schemas with linked tables (the “information_schema.REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAMES.” column has case insensitive values which MySQL may choke on).