Installing, configuring and using ODBC on ubuntu

I wanted to use an ODBC-MySQL bridge on my linux box. Here are the steps I followed.

• First of all, unixodbc comes installed on ubuntu. Otherwise you can install the package called unixodbc.
• I downloaded and installed the ODBC connector from the MySQL website. I downloaded the rpm package, converted it to a deb with alien and installed it with dpkg.
• I also found and installed these packages in the ubuntu repositories: mysql-connector-odbc and libmyodbc. There is probably some redundancy in this step and the previous one, but at least I didn't have any conflict issues and everything worked in the end.
• Now, unixodbc relies on two files: /etc/odbcinst.ini and ~/.odbc.ini. The first one holds a registry with ODBC drivers for different database systems. The installer for the MySQL driver (the one from the MySQL website) registers the driver with this file, so you don't need to do that manually. The file should look like this:

  [MySQL]
  DRIVER          = /usr/lib/libmyodbc3.so
  SETUP           = /usr/lib/libmyodbc3S.so
  UsageCount              = 1
  

  Make sure the location of the drivers is the correct one.
• The .odbc.ini is used for declaring datasources. Actually this file declares datasources on a per-user basis. Global datasources can be configured in the file /etc/odbc.ini. An entry on these files should look like this:

  [test]
  DRIVER = MySQL
  SERVER = localhost
  DATABASE = mysql
  USER        =root 
  PASSWORD    =
  PORT        = 3306
  SOCKET = /var/run/mysqld/mysqld.sock
  

  Needless to say that the DRIVER name should must the name of the driver, as it was declared on /etc/odbcinst.ini.
• As you can see I also had to explicitly define the mysql socket. This was because the location of the mysql socket in ubuntu is different than in other linux systems. By default, unixodbc uses the location /tmp/mysql.sock, so you can either explicitly specify the socket location as above, or just create a link:
  sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
• Now you can test the connection using
  isql -v test
  You should get a message like this:

  +---------------------------------------+
  | Connected!                            |
  |                                       |
  | sql-statement                         |
  | help [tablename]                      |
  | quit                                  |
  |                                       |
  +---------------------------------------+

  followed by an SQL prompt.
• Finally this is some java code that uses this datasource:

  // There are two ways we can address a datasource: 
  // either with the DNS of a correctly configured ODBC datasource, 
  // or by providing all the database information.
       Connection conn = java.sql.DriverManager.getConnection(
               //"jdbc:odbc:;DRIVER={MySQL};SERVER=localhost;DATABASE=mysql;",
               "jdbc:odbc:test",
               "root",
               "");
     

mysql and external files

A mysql database can be dumped to a file with the mysqldump command.

mysqldump -uusername -ppassword database_name > file.sql

This is particularly useful for backup purposes. The dumped database can then be reloaded with this command:

mysql -uusername -ppassword database_name < file.sql

These two commands can be combined in order to replicate a database to a remote mysql server.

mysqldump -ulocal_username -plocal_password local_database_name | mysql -uremote_username -premote_password remote_database_name

If only some of the database tables need to be dumped, the --tables option can be used.

mysqldump -uusername -ppassword database_name --tables table1 table2 > file.sql

This, however, dumps not only the table data, but also the table creation commands and other information. If we just need to write a table into a CSV file or read table data from a CSV file, we can use the SELECT ... INTO OUTFILE and LOAD DATA INFILE commands.

SELECT columns into outfile 'file_name' FIELD TERMINATED BY 'char' FROM table_name; LOAD DATA INFILE 'file_name' INTO TABLE table_name FIELD TERMINATED BY 'char';

Note that the file name should be placed in quotes and it is preferable to use absolute paths, otherwise mysql will look for the file in the mysql directory (eg /var/lib/mysql/database_name). There is also a nasty complication in this point with the permissions that I haven't figured out yet.

Other interesting options for these two commands are
FIELDS TERMINATED BY 'char' OPTIONALLY ENCLOSED BY 'char'
and
LINES TERMINATED BY 'char'.

The full documentation can be found here.