MySQL and Python on Lion

It’s been a long while since I’ve needed to run a database server on my own machine (mostly I use either remote servers or Linux VMs so that I can get rid of all the junk in one go), but today I felt whimsical and decided I’d have a go at running MySQL natively. Guess what, there’s still some assembly required.

So I got the 64-bit .dmg installer off the Oracle site, installed the server and the preference pane (I’d rather have the server down by default, thank you), and discovered, to my utter unbelieving shock, that the asinine defaults it ships with mean that you’ll be running the server bound to port 3306 on all network interfaces (rather than the much saner and safer localhost).

To remedy that, you need to create a my.cnf file with the right settings. You can start with one of the defaults like so:

sudo cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf

Then edit /my/cnf to have it include an explicit binding address like so:

[mysqld]
bind-address=127.0.0.1

Or, if you don’t need TCP networking at all, just uncomment skip-networking:

[mysqld]
skip-networking

…and restart the server.

Seriously, this is just stupid. Security-wise, it’s the worst possible scenario, and I can’t understand why it ships without this enabled by default (or with networking disabled altogether - I can use pure UNIX sockets, but like to have TCP available for tunnelling stuff).

Next came installing mysql-python. Natively, to work with the system Python, which I prefer for small, controlled deployments rather than messing about with third-party builds¹.

Thanks to the wonders of Xcode 4.3 and to the inscrutable ways in which Apple has decided to sandbox just about everything, to get perfectly ordinary, vanilla compiles going you have to dip into Xcode’s preferences, go into Downloads and install the command line tools as per the adjoining image².

To make good use of it all (or, rather, for it all to actually work), you need to edit your ~/.bashrc to include the following lines:

# make sure MySQLdb can load the runtime
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/
# make sure we can find the MySQL binaries and the developer CLI tools
export PATH=/usr/local/mysql/bin:$PATH:/Applications/Xcode.app/Contents/Developer/usr/bin

Then you can just do:

sudo easy_install mysql-python

…and you’re good to go. There are a few compile warnings, but it works fine:

$ python
Python 2.7.1 (r271:86832, Jun 25 2011, 05:09:01) 
[GCC 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.15.00)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> MySQLdb.get_client_info()
'5.5.21'

And now, back to our irregularly scheduled writing.