How to Install the MySQL ODBC Driver on Ubuntu 16.04?
The seemingly easy task to install the MySQL ODBC driver on Ubuntu 16.04 has turned out to be a challenge. It has resulted from the driver upgrade problem that Ubuntu developers couldn’t have handled.
If you have the latest version of Ubuntu 16.04 installed on your system, you may have already hit a snag while attempting to locate the ODBC driver.
# apt-get install libmyodbc Reading package lists. Done Building dependency tree Reading state information. Done Package libmyodbc is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is only available from another source E: Package ‘libmyodbc’ has no installation candidate
Prior to that MySQL could be accessed via ODBC-based applications, after having the ODBC driver installed using a simple Terminal command:
As for the latest version, all efforts to search the Ubuntu repository for the ODBC driver are in vain: of all the repositories listed in the software sources, none of them include the libmyodbc package. According to the Ubuntu version history, the package used to exist but has been deleted due to a certain bug.
MySQL 5.7, as opposed to other versions, restricts symbols available in libmysqlclient20. It looks like MyODBC uses some of these symbols, so it cannot be installed from libmysqlclient-dev.
In fact, maintaining MyODBC in Ubuntu is a disputable question, since there are no hard dependencies on it, and it is a default alternative for libreoffice-canzeley-client.
Thus, MyODBC that uses libmysqlclient functions incompatible with MySQL 5.7 is not supported by Ubuntu 16.04 at the moment. Once the work on MySQL 5.7 is finished, Ubuntu developers may get it back into the release. However, there is a chance it is never going to happen.
If you have software that absolutely requires the MySQL ODBC driver, it is possible to set one up and get it working on Ubuntu 16.04. To do it, take the following steps:
- Download the ODBC driver for Ubuntu 15.10 (mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit.tar.gz) from this page.
- Extract the files: tar -xvf mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit.tar.gz
- Copy the driver to the ODBC folder: sudo cp mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit/lib/libmyodbc5* /usr/lib/x86_64-linux-gnu/odbc/
- Install it: sudo mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit/bin/myodbc-installer -d -a -n «MySQL» -t «DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so;»
- Create DSN: sudo mysql-connector-odbc-5.3.6-linux-ubuntu15.10-x86-64bit/bin/myodbc-installer -s -a -c2 -n «test» -t «DRIVER=MySQL;SERVER=127.0.0.1;DATABASE=mysql;UID=root;PWD=123456»
- Сonnect the database and query it:
[email protected]:~$ isql test +—————————————+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +—————————————+ SQL> show tables +——————————————————————+ | Tables_in_mysql | +——————————————————————+ | columns_priv | | db | | engine_cost | | event | | func | ………. +——————————————————————+
[email protected]:~$ cat /etc/odbc.ini [test] Driver=MySQL SERVER=127.0.0.1 UID=root PWD=123456 DATABASE=mysql PORT=3306 [email protected]:~$ cat /etc/odbcinst.ini [MySQL] Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so UsageCount=1
The ODBC driver for MySQL is installed. Your software now has an opportunity to establish connection with MySQL directly via TCP/IP.
Next, does your MySQL or other database contains a sensitive data that has to be secured and protected? Or do you need to be in compliance with GDPR, SOX or HIPAA? Check DataSunrise database security and data masking software or download the trial.
Подготовка ODBC драйвера для подключения к MySQL
Для корректного подключения к базам данных MySQL необходима установка драйверов ODBC. Поддерживается выполнение только запросов, команд СУБД. Создание и использование таблиц, представлений, процедур и функций поддерживается в зависимости от возможностей конкретной СУБД и её соответствия стандартам SQL.
В данной статье приведен пример создания базы данных, таблицы и пользователя для подключения к MySQL с помощью драйвера Connector/ODBC на Ubuntu 18.04.
Предварительно необходимо установить клиентскую и серверную части MySQL:
sudo apt-get install mysql-server
sudo apt-get install mysql-client
Установить unixodbc и odbcinst, если их нет:
sudo apt-get install unixodbc
sudo apt-get install odbcinst
В Ubuntu 18.04 из официального репозитория была удалена библиотека libmyodbc. В связи с этим установите Connector/ODBC вручную.
Совет . Для MySQL 5.7 рекомендуется скачать MySQL Connector/ODBC 5.3.13.
После подготовки ODBC драйвера установите или перезапустите BI-сервер, если он был установлен ранее.
Установка Connector/ODBC
Рассмотрим пример установки Connector/ODBC на Ubuntu 18.04.
Для установки Connector/ODBC выполните действия:
tar xvf mysql-connector-odbc-5.3.13-linux-ubuntu18.04-x86-64bit.tar
myodbc-installer –a –d –n “MySQL ODBC 5.3 Unicode Driver” –t “Driver=/usr/local/lib/libmyodbc5w.so”
myodbc-installer –a –d –n “MySQL ODBC 5.3 ANSI Driver” –t “Driver=/usr/local/lib/libmyodbc5a.so”
В результате компиляции в терминале должно быть выведено имя зарегистрированных драйверов:
MySQL ODBC 5.3 Unicode Driver
MySQL ODBC 5.3 ANSI Driver
Примечание . Информация о зарегистрированных драйверах хранится в конфигурационном файле /etc/odbcinst.ini.
- Отредактируйте файл настроек /etc/mysql/my.cnf или /etc/mysql/mysql.conf.d/mysqld.cnf для подключения к удаленному серверу MySQL. В одном из файлов конфигурации хранится строка:
Для подключения к базе данных с любого IP-адреса замените строку на:
sudo systemctl restart mysql
Создание базы данных, таблицы и пользователя
- Создайте базу данных в MySQL с идентификатором test_database.
- Создайте таблицу с идентификатором test_table в базе данных и заполните ее значениями от 1 до 10.
- Создайте пользователя и предоставьте ему права для подключения к базе данных:
create user ‘test_user’@’%’ identified by ‘123’;
grant all privileges on *.* to ‘test_user’@’%’ identified by ‘123’;
% — означает, что пользователь может подключаться с любого IP-адреса.
sudo myodbc-installer –s –a –c2 –n “TestDSN” –t “DRIVER=MySQL ODBC 5.3 Unicode
При успешном подключении к созданной DSN будет получено следующее сообщение:
Примечание . Созданные DSN хранятся в конфигурационном файле /etc/odbc.ini.
MySQL Connector/ODBC Developer Guide
This manual describes how to install and configure MySQL Connector/ODBC, the driver that enables ODBC applications to communicate with MySQL servers, and how to use it to develop database applications.
MySQL Connector/ODBC 8.0 is recommended for use with MySQL Server 8.0, 5.7, and 5.6. Please upgrade to MySQL Connector/ODBC 8.0.
For notes detailing the changes in each release of Connector/ODBC, see MySQL Connector/ODBC Release Notes.
For legal information, see the Legal Notices.
For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL users.
Licensing information. This product may include third-party software, used under license. MySQL Connector/ODBC 8.0 Community License Information User Manual has information about licenses relating to Connector/ODBC community releases in the 8.0 release series. MySQL Connector/ODBC 8.0 Commercial License Information User Manual has information about licenses relating to Connector/ODBC commercial releases in the 8.0 release series. MySQL Connector/ODBC Community License Information User Manual has information about licenses relating to Connector/ODBC community releases up to and including version 5.3. MySQL Connector/ODBC Commercial License Information User Manual has information about licenses relating to Connector/ODBC commercial releases up to and including version 5.3.
Document generated on: 2023-06-05 (revision: 75805)
Table of Contents
- Preface and Legal Notices
- 1 Introduction to MySQL Connector/ODBC
- 2 Connector/ODBC Versions
- 3 General Information About ODBC and Connector/ODBC
- 3.1 Connector/ODBC Architecture
- 3.2 ODBC Driver Managers
- 4.1 Installing Connector/ODBC on Windows
- 4.1.1 Installing the Windows Connector/ODBC Driver Using the Zipped DLL Package
- 4.1.2 Installing the Windows Connector/ODBC Debug Packages
- 4.2.1 Installing Connector/ODBC Using the MySQL Yum Repository
- 4.2.2 Installing Connector/ODBC from a Binary Tarball Distribution
- 4.2.3 Installing Connector/ODBC from a DEB Distribution
- 4.2.4 Installing Connector/ODBC from an RPM Distribution
- 5.1 Overview of Connector/ODBC Data Source Names
- 5.2 Connector/ODBC Connection Parameters
- 5.3 Configuring a Connector/ODBC DSN on Windows
- 5.3.1 Configuring a Connector/ODBC DSN on Windows with the ODBC Data Source Administrator GUI
- 5.3.2 Configuring a Connector/ODBC DSN on Windows, Using the Command Line
- 5.3.3 Troubleshooting ODBC Connection Problems
- 5.9.1 Enabling ODBC Tracing on Windows
- 5.9.2 Enabling ODBC Tracing on macOS
- 5.9.3 Enabling ODBC Tracing on Unix
- 5.9.4 Enabling a Connector/ODBC Log
- 6.1 Basic Connector/ODBC Application Steps
- 6.2 Step-by-step Guide to Connecting to a MySQL Database through Connector/ODBC
- 6.3 Connector/ODBC and Third-Party ODBC Tools
- 6.4 Using Connector/ODBC with Microsoft Access
- 6.4.1 Exporting Access Data to MySQL
- 6.4.2 Importing MySQL Data to Access
- 6.4.3 Using Microsoft Access as a Front-end to MySQL
- 6.7.1 Using Connector/ODBC with Visual Basic Using ADO, DAO and RDO
- 6.7.2 Using Connector/ODBC with .NET
- 7.1 Connector/ODBC API Reference
- 7.2 Connector/ODBC Data Types
- 7.3 Connector/ODBC Error Codes
- 8.1 Connector/ODBC General Functionality
- 8.1.1 Obtaining Auto-Increment Values
- 8.1.2 Dynamic Cursor Support
- 8.1.3 Configuring Catalog and Schema Support
- 8.1.4 Connector/ODBC Performance
- 8.1.5 Setting ODBC Query Timeout in Windows
- 8.2.1 Using Connector/ODBC with Microsoft Applications
- 8.2.2 Using Connector/ODBC with Borland Applications
- 8.2.3 Using Connector/ODBC with ColdFusion
- 8.2.4 Using Connector/ODBC with OpenOffice.org
- 8.2.5 Using Connector/ODBC with Pervasive Software DataJunction
- 8.2.6 Using Connector/ODBC with SunSystems Vision
- 9.1 Connector/ODBC Community Support
- 9.2 How to Report Connector/ODBC Problems or Bugs