How to Connect to Your MySQL Database Remotely Using ODBC
Posted onIt 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.
Got the odbc connection to remote database set up fine. But it looks like MS Access 2013 is not allowing a connection to mySQL. Looking for someone to confirm or a workaround. The newly setup connection is no where to be found in 2013.
It works fine with MS Access, I use it everyday. Let me get back to you later, I’m on my iPad right now.
Thanks Stephane. Had to reread all of your article. Was on a 64bit machine, but 32bit download of Office365. All is well now. Thanks!!
After pressing the ‘Test’ button, i received an error message “Connection Failed [HY000][MySQL][ODBC 5.2(w) Driver] Connection using old (pre-4,1,1) authentication protocol refused (client option ‘secure_auth’ enabled).” What can I do?
i have used ip address- getting error message cannot connect to mysql server
Great tutorial Stephane. Would the configuration be any different using prostage sql?
From the hosting side do we need to add anything for CPanel hosting customers to connect? For example in the firewall CSF or in WHM?
thanks.!