Jumbabox

In my tutorials on how to connect a Pivot Table to an Access database and an Oracle database I have gone through how you can set up an ODBC connection in great detail however I know a lot of people are starting to use MySQL a lot more and would like to use Office products such as Excel and Access with MySQL as well. This tutorial looks at how to configure an ODBC connection to a MySQL database which is either:

• Hosted locally
• or, hosted on a server on a LAN

Related Posts:

• Excel Pivot Table with a MySQL database
• Excel Pivot Table with an Oracle database
• Excel Pivot Table with an Access database

Download and install the ODBC driver for MySQL

The first step is to install the latest ODBC driver for your MySQL database. A list of the supported operating systems and installation files can be found here, or click here for a direct download of the Windows 32 bit MSI file (4.1 mb / Version 5.1).

Once you have finished downloading the driver, double click on it to start the installation process.

Once the installation wizard has loaded, click on the Next button.

Choose the Typical option, and then click the Next button again.

Check over your installation options (in my case no Destination Folder was blank) and then click the Next button once again.

The installation will then begin…

Once it is complete, click on the Finish button.

How to view the configuration menu for the ODBC MySQL Driver

To configure your ODBC MySQL driver click on Start > Settings > Control Panel to open up the control panel.

Double click on Adminstrative Tools.

Then double click on Data Source (ODBC).

A dialog box will show the current available ODBC drivers under the User tab, click on the Add… button to continue.

A wizard will now open and it will ask you to select a driver you want to setup a data source for, scroll down to the bottom and locate the MySQL ODBC 5.1 Driver, highlight this driver and then click the Finish button.

Once you have clicked on the Finish button, the MySQL Connector/ODBC Data Source configuration dialog box will pop up.

Here is a quick description of each option:

• Data Source Name : The name of your data source
• Description : A general description of your data source
• Server : The server location of your MySQL database
• Port : The port which the MySQL database uses (default: 3306)
• User / Password : The username and password to login into the database
• Database : You directly specify a database you want to connect to, always populate this!
• Details >> : A large amount of advanced options, I didn’t need to touch any of these options for this tutorial.

Pick your database setup

I have decided to go over two different setups, click on the one that best suits you.

• A MySQL database hosted locally
• A MySQL database hosted on a server located on a LAN

Configuration for a MySQL database hosted locally

Step 1 - Add a user

The first step is to add a user for the MySQL database, I will go through how to do this via phpMyAdmin and also show you the SQL statement.

First, open up your web browser and navigate to your phpMyAdmin page, click on the Privileges link at the bottom of the page.

The Privileges section will allow you to add, modify and remove users on your MySQL database, in this example I’m going to add a dummy user, first click on the Add a new User link found half way down the page.

After clicking this link you will be asked to provide some information regarding the new user you want to add, here is what I entered for the available options:

• Username : jumbabox
• Host : localhost
• Password : password
• Generate Password : blank
• Database for a user : Select ‘None’
• Global Privileges : Click Select All

To double check your options here is a screen shot of my Add a new User screen.

After this is done, click on the Go button to add your new user. You should then be notified off the new user being added successfully.

Here is the SQL statement to create the exactly same user and privileges.

CREATE USER 'jumbabox'@'localhost' IDENTIFIED BY '********';

GRANT ALL PRIVILEGES ON * . * TO 'jumbabox'@'localhost' IDENTIFIED BY '********'
WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Step 2 - Configure your MySQL ODBC connection

Now lets test if the user we just setup has access to the MySQL database which is hosted locally. Here is what I put in my configuration options.

Make sure you always specify a Database or choose one from the drop down box as this will cause headaches when using the ODBC driver with Excel, Access, Base or Calc.

If it is successful, you should receive this dialog box:

Hopefully that worked without any problems for you, if it didn’t leave a comment.

Configuration for a MySQL Database on a LAN

Step 1 - Add a user

This method is exactly the same as a database hosted locally, but with a little twist. The first step is to create a user for the MySQL database, I will go through how to do this via phpMyAdmin and also show you the SQL statement.

First, open up your web browser and navigate to your phpMyAdmin page, click on the Privileges link at the bottom of the page.

The Privileges section will allow you to add, modify and remove users on your MySQL database, in this example I’m going to add a dummy user, first click on the Add a new User found half way down the page.

After clicking this link you will be asked to provide some information regarding the new user you want to add, here is what I entered for the available options:

• Username : jumbabox
• Host : Your or the Users IP Address
• Password : password
• Generate Password : blank
• Database for a user : Select ‘None’
• Global Privileges : Click Select All

The main thing with this setup is that you must put the user’s IP address or accept all incoming IP addresses, in this example I will enter my own IP address (it’s recommended that you don’t give access to all incoming IP addresses as it leaves a hole in the security of your MySQL database).

To find out your IP, click on Start > Run > Type in ‘cmd’, at the command prompt enter ipconfig this should list your IP address.

To double check your options here is a screen shot of my Add a new User screen.

After this is done, click on the Go button to add your new user. You should then be notified off the new user being added successfully.

Here is the SQL statement to create the exactly same user and privileges.

CREATE USER 'jumbabox'@'192.168.1.5' IDENTIFIED BY '********';

GRANT ALL PRIVILEGES ON * . * TO 'jumbabox'@'192.168.1.5' IDENTIFIED BY '********'
WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Step 2 - Configure your MySQL ODBC connection

Now lets test if the user we just setup has access to the MySQL database which is hosted locally. Here is what I put in my configuration options.

Make sure you always specify a Database or choose one from the drop down box as this will cause headaches when using with Excel, Access, Base or Calc.

As you can see the server is the IP address of the MySQL database (not your IP address), if it is successful, you should receive this dialog box:

Hopefully that worked without any problems for you, if it didn’t leave a comment.

Using your MySQL ODBC driver

Once your test is successful click on the OK button and if you have a look in your ODBC Data Source Administrator dialog box you should see the new MySQL ODBC Driver.

So that is how you can go about configuring your MySQL ODBC driver.

Related Posts:

• Excel Pivot Table with a MySQL database
• Excel Pivot Table with an Oracle database
• Excel Pivot Table with an Access database

I have also had some success connecting to a MySQL server hosted on a web hosting account, if you would like to me create a tutorial on this let me know!

If you have any problems or questions please comment.

Enjoy!