How To Setup an ODBC Connection To a MySQL Database

Using an ODBC connection to query your MySQL database remotely can be quite useful for using third party database management softwares (like Microsoft Access) or reporting tools (like Crystal Reports).

But what exactly is ODBC?

ODBC is an abbreviation for Open Database Connectivity. It is an interface used to access databases using SQL queries (Structured Query Language).

How To Setup an ODBC Connection To MySQL Database

This tutorial has been performed on a Windows XP SP3 workstation. These steps might differ on a different operating system.

First of all you will the MySQL Connector/ODBC. At the time of this writing, the lastest version is 5.1.4. You can get it here. Personnally I’d recommend you to get the MSI version of the connector. Once downloaded, simply launch it, choose the “Typical” setup and go through the installation.

Before we try to create an ODBC connection, we need to make sure that our workstation’s IP address (or hostname) has the right to access the MySQL server remotely. There are several ways to do this but in this case I’ll use cPanel, a great web control panel, from my InMotion Hosting account.

Start logging in cPanel and click on the Remote MySQL icon from the Databases panel:

You will then be brought the Remote Database Access Hosts List. Enter your IP address or hostname in the textbox. You can use a wildcard to allow access from an entire domain. Click on Add Host.

Now back to our MS Windows desktop, let’s create our ODBC connection. Click on Start => Settings => Control Panel => Administrative Tools and double-click the Data Sources (ODBC) icon.

At this point, you have the choice of creating an ODBC connection that will be available only to the current logged on user (User DNS) or for all the user profiles on this workstation (System DSN). Unless there are some security risks, I always make the ODBC connections available to all.

Select the System DSN tab and click on the Add button. You will asked to select which ODBC driver to use. Select MySQL ODBC 5.1 Driver from the list and hit Finish.

You will now need to enter the configuration information for this ODBC connection:

• The Datasource Name is the name of the ODBC connection you will refer to within your 3rd party applications when using this connection.
• The Description field is optional. You may enter any other information you might find useful.
• The Server field contains the hostname (FQDN) or IP address of your MySQL server. If you’re unsure about this, ask your web hosting provider.
• The default port is 3306, so you can leave it as it is.
• Enter the username you’ve created for this database as well as its password.

If you’ve entered all the information correctly, you will see a list of available databases from the Database dropdown menu. Select the appropriate database from the list and click on OK. I’ve blurred out some fields for security reasons.

From there, you will have an available ODBC connection for any software on your computer (must support ODBC though). You can now use Microsoft Access to manage your database content or Crystal Report to create some reports from your computer for example.

Links

• MySQL 5.1 Reference Manual - ODBC Connectors
• InMotion Hosting Website

Bookmark It