ODBC Driver for MySQL: How to Install and Set up Connection (Step-by-step)

Total: 2 Average: 5

This article focuses on the step-by-step processes of installing and configuring the ODBC driver for MySQL. In particular, it will cover the following issues:

  1. Installing the ODBC driver on Windows 10.
  2. Configuring the ODBC data source with ODBC driver for MySQL.
  3. Creating a basic list report with PowerBI that uses the ODBC driver for the data population from the MySQL database.
CodingSight - Install and Configure ODBC Drivers for MySQL

ODBC/Connector Installation on Windows

First, let’s clarify the process of installing MySQL ODBC drivers.

Using MySQL community installer, you can view, install, update, configure, and remove various MySQL products. That’s why we install ODBC drivers from the MySQL Community installer – it is available for download here. Once the process is complete, open the installer. On the initial screen, you see the list of MySQL software installed on the computer.

To install the ODBC driver, click Add.

MySQL Installer - Add MySQL software

On the new Select Products and a Features screen, you will see the list of MySQL products in various categories. ODBC drivers are available under the MySQL Connectors category.

Expand MySQL Connectors > Expand Connector/ODBC

MySQL Installer - Expand MySQL Connectors > Expand Connector/ODBC

Two versions of ODBC drivers are available. We want to install the latest driver.

Expand Connector/ODBC 8.0 and choose the most recent version of the ODBC driver. Click on the arrow sign.

MySQL Installer - Expand Connector/ODBC 8.0 and choose the most recent version of the ODBC driver. Click on the arrow sign

The ODBC driver will move from Available Products to Products to be installed. Click Next.

Select Products and Features

On the Installation screen, you can view the list of MySQL products we are installing. Click Execute.

On the Installation screen, you can view the list of MySQL products we are installing. Click Execute.

The installation starts. Wait for its completion, and once it’s done, click Next.

The installation starts. Wait for its completion, and once it’s done, click Next.

Now you can see the ODBC driver in the list of DSN.

ODBC Connection Configuration

Open Control Panel > Administrative tools > ODBC Data Sources (64-bit) > System DSN tab > Add.

Configure ODBC Driver for MySQL Open Control Panel > Administrative tools > ODBC Data Sources (64-bit) > System DSN tab > Add

This will launch the Create New Data Source wizard.

Select MySQL ODBC 8.0 ANSI driver from the list and click Finish.

Create New Data Source wizard - Select MySQL ODBC 8.0 ANSI driver from the list and click Finish

The configuration wizard opens. There, specify the following connection parameters:

  1. Data Source name: the desired data source name.
  2. TCP/IP Server: the hostname or IP Address of the server where the MySQL database server is installed.
  3. Port: the appropriate port number of the server where the MySQL database server is installed.
  4. Named Pipe: If this parameter to connect to the database server is configured, choose it and specify its appropriate name.
  5. User: the username to connect to the MySQL database server.
  6. Password: the appropriate password for the user specified in the User field.
  7. Database: the MySQL database you want to connect.

When all parameters are set, click Test to check the connectivity to the MySQL database server:

MySQL Connector/ODBC Data Source Configuration Connection Parameters - Test Connection

If the configured parameters are correct, the connection will be established successfully.

If the configured parameters are correct, the connection will be established successfully

Click OK to save the configuration and close the ODBC configuration wizard.

You will see the configured MySQL ODBC data source under the System DSN:

The configured MySQL ODBC data source under the System DSN:

Generate a PowerBI report using MySQL ODBC driver

At this stage, we are going to create a PowerBI report using the MySQL ODBC driver. For demonstration purposes, I have installed MySQL 8.0 on my workstation and created a database named eltechDB on MySQL. Then, I created a table named tblemployee in that database. We will use the database view to generate the report, and I have also created a view named vwEmployees.

The following script creates the eltechDB database:

Create database eltechDB

To create the tblEmployees table, run this script:

CREATE TABLE `tblemployees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) 

This script creates the vwEmployees view:

CREATE 
VIEW `eltechdb`.`vwemployees` AS
    SELECT 
        `eltechdb`.`tblemployees`.`emp_no` AS `emp_no`,
        `eltechdb`.`tblemployees`.`birth_date` AS `birth_date`,
        `eltechdb`.`tblemployees`.`first_name` AS `first_name`,
        `eltechdb`.`tblemployees`.`last_name` AS `last_name`,
        `eltechdb`.`tblemployees`.`gender` AS `gender`,
        `eltechdb`.`tblemployees`.`hire_date` AS `hire_date`
    FROM
        `eltechdb`.`tblemployees`

Once the database and objects are ready, download the PowerBI desktop version, install it, and open. Note: I don’t have a PowerBI license or subscription, but if you own it, log in your account with your credential.

On the Welcome screen, click Get data:

Power BI Desktop - sign-in to use benefits of Power BI

The new window that will open will display the list of various data sources, supported by the PowerBI report is here. To find the necessary data source, you can type the initials of its name. We are using ODBC drivers, so, type ODBC in the search field, select ODBC, and click Connect:

Type ODBC in the search field, select ODBC, and click Connect

Select the EltechDB database from the Data Source Name (DSN) drop-down menu, and click OK.

Select the EltechDB database from the Data Source Name (DSN) drop-down menu, and click OK

On the Navigator screen, you can view the list of MySQL databases under the ODBC connection string. We are going to generate a PowerBI report to display the list of the employees from vwEmployees.

To select the necessary view, expand ODBC data source > EltechDB. Under the EltechDB database, you will see the list of available tables and views.

To select the necessary view, expand ODBC data source > EltechDB. Under the EltechDB database, you will see the list of available tables and views

When you click on vwEmployees, you can preview the data in the right pane. Click Load to load the data into the PowerBI report:

Navigator - when you click on vwEmployees, you can preview the data in the right pane. Click Load to load the data into the PowerBI report:

After you’ve loaded the data in the report, you can view it by clicking on the table icon located on the left pane. If needed, you can add more fields from the report. For that, drag and drop the data fields located on the right pane of the report:

Power BI Desktop - drag and drop

Summary

Thus, we have explored the process of installing the MySQL ODBC driver and learned to configure the data source using the MySQL ODBC driver. We’ve also created a basic list report in the PowerBI using the ODBC data source. Hope that this article was of help to you.

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.