Connecting to a mySQL database with R on a Mac

I had some difficulty accessing a mySQL db using the RODBC package on my Mac – I’m documenting the steps to follow below:

1) Download the mySQL ODBC connector from http://dev.mysql.com/downloads/connector/odbc/ (the tarball is best)

2) Unpack the archive and copy libmyodbc5w.so to your preferred location (/usr/local/lib in my case)

3) Create ~/Library/ODBC if it doesn’t already exist

4) In the directory above create a file named odbc.ini with the parameters for your data source(s):

[ODBC Data Sources]
 TESTDB = MyODBC
[TESTDB]
 Driver = /usr/local/lib/libmyodbc5w.so
 DATABASE = your_db
 DESCRIPTION = your_description
 SERVER = your_server
 UID = your_username
 PASSWORD = your_password

5) In the same directory, create odbcinst.ini with parameters for your driver(s):

[ODBC Drivers]
 MyODBC = Installed
 /usr/local/lib/libmyodbc5w.so = Installed
[MyODBC]
 Driver = /usr/local/lib/libmyodbc5w.so
 Description = mySQL driver
 Threading = 0
[/usr/local/lib/libmyodbc5w.so]
 Driver = /usr/local/lib/libmyodbc5w.so
 Description = mySQL driver
 Threading = 0

6) Test that you can access your data source(s) using Mac OS X’s built-in command line tool iodbctest:

monolith:~ nicolas$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008
Enter ODBC connect string (? shows list): ?
DSN | Driver 
------------------------------------------------
TESTDB | MyODBC
Enter ODBC connect string (? shows list): DSN=TESTDB
Driver: 05.02.0004 (libmyodbc5w.so)
SQL>

7) Connect to your data source in R through RODBC – Voilà!

library(RODBC)
cn <- odbcConnect("TESTDB")
sqlTables(cn)
close(cn)