It is sometime necessary to be able to connect remotely to a MySQL database in order to generate reports, statistics or simply to manipulate the data. Most of the time, you will need to do so using popular software applications such as Microsoft Office or specialized solutions like Crystal Reports.
In order to query a MySQL database remotely, you will need to use an interface called “ODBC” which stands for “Open Database Connectivity”. The ODBC driver is basically acting as a translator between an application and the database management system.
Installing the MySQL ODBC Driver
MySQL has made multiple versions of its Connector/ODBC driver for various operating systems. You can download the latest MySQL ODBC drivers here.
If you are using a 64-bit version of Windows, I strongly recommend you download and install both 32 and 64 bit versions of the driver. The reason being that even though you are running Windows 64-bit, you won’t be able to use the 64-bit driver with the 32-bit applications.
I will not go into details about installing the ODBC driver as it is pretty straightforward: simply download the MSI installer and proceed with the installation with the default settings.
Allowing Remote Access to Your MySQL Database
For security reasons, remote access to MySQL databases is often blocked by default. In order to allow remote connections, you will need to enable it through your hosting control panel. If you are using cPanel, click on “Remote MySQL” from your control panel and add your IP address:
Note that some web hosting companies do not allow remote MySQL access to their database servers no matter what. If you’re unsure about this, contact your provider’s technical support team.
Creating an ODBC Connection
On your local computer, go to “Control Panel > System and Security > Administrative Tools”. On 64-bit versions of Windows, you will see two (2) icons: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit). Use the 64-bit ODBC Data Source Administrator only if you plan to use a 64-bit software application to access your MySQL database.
Click on the “Add” button to create an ODBC connection:
Select the MySQL ODBC Unicode Driver from the list and click on “Finish”. You can also choose the ANSI driver if you wish. It’s faster but it supports only a limited range of character sets.
Enter the connection information as well as the login credentials to access your MySQL database:
If the connection to the MySQL server is established properly, you should be able to select a database from the listbox. Select the database you wish to connect to and click the “Test” button to ensure the connection is successful. Click on “OK” when you’re done. You should now be able to use this ODBC connection within your software applications.
p style=”text-align: left;”>In some cases, the IP address of the MySQL server may be different from the IP of your website. The MySQL port may also be different than the default one (3306). If you’re unsure about this, contact your hosting provider.