How to install and configure the latest ODBC driivers for both MYSQL & PostgreSQL in 18.04

Question

I'm currently trying to get access to some MYSQL and PostgreSQL databases via an ODBC connection via an ODBC Administrator GUI as I had on Windows. After searching around, I have only found scattered and old tutorials for installing and setting up ODBC connections on Ubuntu.

Can someone help me with a more up to date instructions as I am working in a delicate production environment so the last thing I need a screwup? Thank in advance.

Solution 1

This answer only explains how to install MySQL ODBC drivers. I guess for Postgres, you will have to ask the drivers from Postgres. See this page for more info on that: http://www.unixodbc.org/, under the theme 'Drivers'.

1. Install UnixODBC

sudo apt install unixodbc

2. Get and install the MySQL drivers from MySQL here: https://dev.mysql.com/downloads/connector/odbc/. That will create the files libmyodbc5X (where the X depends on which driver has been installed) and libodbcmy.so in /usr/lib/x86_64-linux-gnu/odbc. The first one is the driver, the second is the managing driver (not very useful).

3. Create two files in /etc: odbcinst.ini and odbc.ini The first one contains the specification of the available drivers. In your case, there will be two of them, one for MySQL, the other one for Postgres. The second one is the collection of database source name. Each one specifies at least a name, between brackets [], and a driver name.

For example, my odbcinst.ini contains:

[MySQL]
Description= MySQL ODBC Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so    
Usagecount=1

and my odbc.inicontains two entries, one for the production database, one for the test

[Prod]
Description = Production DB
Driver = MySQL
SERVER = 127.0.0.1
USER = youruser
PASSWORD = yourpassword
PORT = 3306
DATABASE = prodDB

[TestDB]
Description = Test DB
Driver = MySQL
SERVER = 127.0.0.1
USER = youruser
PASSWORD = your password
PORT = 3306
DATABASE = test

From that point on, you can connect just using the DSN name (Prod or TestDB). Of course, it might be wise to put your username/password elsewhere. The exact way to connect to DSN depends on the programming language/development tool that you use.

Solution 2

You should always periodically copy your production environment to a test environment for user training and a development environment for yourself. You don't want your trials and errors effecting production (the company's bottom line) or user training (they are already enough challenge day to day without messing up their world).

That said, here are some newer ODBC (Open Database Connection) tutorials for you:

• April 11, 2019 Connect to PostgreSQL Database on Linux, Windows
• August 16, 2019 MySQL show status: How to show open database connections

Comments

[Tojamismis]

Updated the question I want to set up the ODBC connections to the databases on Ubuntu using something like an ODBC gui manager where I can manage all the ODBC database connections.