Getting STATA to connect to MySQL on Ubuntu isn't difficult so long as one critical step isn't missed out. I couldn't find this step on any guides so hopefully this will help others save some time on most 'NIX systems. This guide doesn't cover installing MySQL or STATA.

The steps are:

  1. Install libmyodbc
  2. Install unixODBC
  3. Configure ODBC
  4. Test the connection
  5. Connect STATA

Let's get started.

$ sudo apt-get install libmyodbc
$ sudo apt-get install unixodbc
$ sudo apt-get install unixodbc-dev # The critical step - without this STATA ODBC does not work
$ cat /etc/odbcinst.ini
[ODBC Drivers]
MySQL = Installed

[MySQL]
Driver = /usr/lib/odbc/libmyodbc.so
Setup  = /usr/lib/odbc/libodbcmyS.so
$ vi ~/.odbc.ini
...
[ODBC Data Sources]
database_name = MySQL # for simplicity I've used the database name here too

[database_name]
Description = A description
Driver      = MySQL
database    = database_name
server      = 127.0.0.1 # Connecting on the same machine
user        = your_mysql_username
password    = your_password # optional - this is plain text!
$ chmod 700 ~/.odbc.ini
$ isql database_name
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

You should now be able to run SQL commands try running:

SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_database_name                                         |
+-----------------------------------------------------------------+
|...                                                              |
|                                                              ...|
+-----------------------------------------------------------------+
SQL> quit
$ _
set odbcmgr unixodbc[, permanently]

Import the database into STATA by going to File -> Import -> ODBC data source

The database should be listed as an ODBC data source - make your selection. Done!


Anthony Thomas
MRC Centre of Epidemiology for Child Health
UCL, Institute of Child Health

https://wiki.ucl.ac.uk/x/sIaLAQ