Install and Create MySQL ODBC Connector on Windows 7 64 bit

bookmarks

Why install ODBC Connector for MySQL?

ODBC connector works as a standard gateway for programming languages, applications (such as Excel, Access, or OpenOffice) to communicate with database server with ease.

The database behind ODBC Connector could be MySQL, Microsoft SQL, PostgreSQL, Microsoft Access, or FileMaker, and more. You do not have to input the direct connection string into the code. You just need to know how to contact to ODBC Connector in a very simple way; especially, Delphi even makes it simpler with a few clicks.

If you want to see how to connect Delphi to mysql ODBC, click here to link to Connect Delphi to MySQL using ODBC.

You could see the usage of MySQL in these two cases.

The first case is to connect to a local MySQL Server. It means you have MySQL Server running on your local computer.

MySQL on localhost

In this case, the grant permission command is not necessary because MySQL allows localhost (127.0.0.1) to connect by default.

The second case is to connect to a remote MySQL Server. In this case, you want to connect to a remote machine where MySQL is running. Sometimes, we need to access to a remote MySQL to do some automatic tasks, such as maintaining database, checking database, or updating a WordPress blog using an auto web crawler application. If you want to run an application on one server and access database on another server, this case can be used.

Connect MySQL ODBC to a remote MySQL Server

In this case, the administrator on the machine 192.168.11.130 must execute the GRANT command. More about GRANT command in MySQL.
Back to top

Which version should I go?

As this time of writing, there are two public versions of MySQL Connectors, 3.51 and 5.1. Both versions support Windows from XP to 7 32 bit to 64 bit.

Summary steps

  1. Download MySQL Connectors
  2. Install MySQL Connectors on your Windows
  3. Setup MySQL Grant Permission to allow this computer to connect
  4. Setup an ODBC Connector from Windows machine.
  5. Test ODBC Connector

How to install ODBC Connector for MySQL in details

The easiest way to install MySQL ODBC Drivers on Windows is to use MSI Package. MSI package supports both Windows 32 bit and 64 bit.

Step 1. Download MySQL Connectors

  • Now, we start the first step is to go to this URL http://www.mysql.com/downloads/connector/odbc/3.51.html#downloads
  • Select Microsoft Windows as the platform.
  • Select Windows (x86, 64-bit), MSI Installer
  • Connector-ODBC  if you have 64 bit operating system, otherwise, select 32 bit.
  • Click Download.
  • Simply click “No thanks, just take me to the downloads!” if MySQL ask you to register an account.

Download from a trusted source
Warning – If you download the ODCB Connector from some other sources, make sure to check the MD5 hash key

Don’t know how the MD5 hash key works? See How to verify MD5 signature.

Back to top

Step 2. Install MySQL Connectors on your Windows

  • Double click on the file “mysql-connector-odbc-3.51.30-winx64.msi” that you just downloaded to run the setup process.
  • Click Next and accept the license agreement.
  • Select Custom to see what we will have after the installation
Custom Installation - MySQL
  • The package MySQL 64 bit allows you to install both 32 and 64 bit Connector. This is just an optional option, you could click Next now to continue another step.
MySQL ODBC supports both 32 and 64 bits
  • Click Install to let the Setup Wizard do the rest.

Back to top

Step 3. Setup MySQL Grant Permission to allow this computer to connect

Save your time - You do not have to complete this step if you are connecting to a MySQL Server on your local machine.

This task should be done on the remote MySQL Server in the second type. Click here to see the figure again.
On MySQL Server, login as root or any account with GRANT permission, issue this command

GRANT ALL ON *.* TO ‘newaccount’@’192.168.11.1′ IDENTIFIED BY “test123456″;

newaccount could be replaced by any usernames that you want to use. test123456 is the password for newaccount. The phase ‘192.168.11.1‘ means this account is only valid when connects from the server 192.168.11.1

Back to top

Step 4. Setup an ODBC Connector from Windows machine.

  • Go to Control Panel –> Administrative Tools and select Data Sources (ODBC), execute it.
ODBC Connector in Control Panel

This tool allows you to create new ODBC Connector for you or your computer.

The ODBC Data Source Administrator appears, where you could create new DSN for you, or for your computer.

Tips: User DSN is Adds, deletes, or sets up data sources with user data source names (DSNs). These data sources are local to a computer and accessible only by the current user (User level).

System DSN is Adds, deletes, or sets up data sources with system data source names (DSNs). These data sources are local to a computer but not user-dedicated; any user with privileges can access a system DSN (Computer level).

In this case, I would like to setup an DSN (ODBC Connector) that is available to every user account on my computer, I will click the tab System DSN.

  • Click Add to add a new ODBC Connector
  • Select MySQL ODBC 3.51Driver
Select MySQL ODBC 3.51
  • Click Finish to enter another dialogue where you can setup some necessary parameters.
Complete MySQL ODBC Form
Leave field Database empty
Stay out of trouble – just leave the field Database empty. If you select some database here, you might run into some weird problems with MySQL ODBC Connector. You must specify database name in your SELECT command. Example: SELECT * FROM plaintutdb.table_employees;

Data Source Name – The name that your application will use to communicate with this ODBC Connection.

Description – Anything that for your understanding (useful in case you have so many ODBC Connectors on your machine)

TCP/IP Server – IP address or DNS Name of the MySQL Server. In this case, my remote MySQL Server has the IP of 192.168.11.130.

User – Username on the MySQL box. This is the username in the GRANT command at Step 3. More about GRANT command on MySQL.

Back to top

Step 5. Test ODBC Connector

Click Test button and wait for the success message pop-up. You should expect a pop-up message like this.

Connect to MySQL ODBC successfully

If you have some error at this point, go back to Step 3 and make sure you have enough permission.

A common error message could happen is Connection Failed: [HY000] [MySQL][ODBC 3.51 Driver][Host ‘your-ip-address’ is not allowed to connect to this MySQL server.

MySQL ODBC Error

This problem happen because you do not have permission to connect to the database from the IP address 192.168.11.1, or you entered wrong password. The password is test123456 which I entered on the GRANT command at step 3.

OR, you might have an error message like this: "Connection Failed: [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user ‘newaccount’@’192.168.11.1′ (using password: YES)“. The root causes of this error message are: you type wrong password, OR the password is too complex.

MySQL ODBC 3.51 No-Complex Password
Warning – You might have a serious headache with MySQL ODBC 3.51 if the password in your GRANT command contains special characters, such as ! @ # $ % ^ ?. MySQL ODBC 3.51 ODBC Driver does not support these special characters in the password box. The only error message you would receive is “Access denied” (using password: YES)

Wrong password or too complex password

To this point, you could connect your database application to MySQL Server using the new ODBC Connector created from this tutorial.

Back to top

No related posts.

About The Author

Hao Nguyen

Hao Nguyen

Hello! I'm Hao Nguyen and I'm currently working as a Network Engineer for a small firm in Houston. I enjoy writing technical documents and blog, such as PlanTutorials.com Contact me on Google+

28 Comments

  1. Diana says:

    Wow, this is the most helpful tutorial for beginner like me! I like that you mention about local and remote server. Thanks!

  2. sola says:

    hey guy, this is cool. Pls i need free download of mysql connector and access driver for windows 7 64bits

  3. ahmed says:

    hi all ,but i use complex pass with this odbc 3.51 in previous week ,but in this week not work ??? please help due to i need to use complex pass

  4. Chandan says:

    Hello , I am using ODBC to connect mysql on a windows host which works fine .. but after some time getting error on windows host and while running test its stating that your host.domain.com is blocked because of many connection errors;unblock with ‘mysqladim flush-hosts

    Can anyone help me out here…

    Thanks

  5. Bernard says:

    Just wanted to leave a big thanks for sharing your knowledge in a well written manner.

  6. syarif says:

    thanks a lot..it works :)
    best regard.

  7. AIR MAX 95 says:

    Hi i am kavin, its my first time to commenting anywhere, when
    i read this post i thought i could also
    make comment due to this brilliant piece of writing.

  8. Srini says:

    Thanks for the post. It was really helpful. My password contains a special character “#” hence I’m getting the “”[ODBC 3.51 Driver] Access Denied error for user ‘*’@'*’ “. Since it is a remote server, and it is used by many people, I cannot change the password. But however, if I try this odbc 3.51 connection using a windows xp it works like a charm, but it does not work on Windows 7. Is there a fix?

    • Hao Nguyen says:

      Srini,

      It’s good to know it works for you on Windows XP. I’really would like to use special characters for my ODBC password, but I didn’t try it on my Windows 7 yet. I don’t either see a fix on mysql site yet.

      Hao

  9. GARCIA says:

    Xclnt, Thank you. Three years ago i had a headache with this. Cheers.

  10. GARCIA says:

    What version of mysql connector 32bits for windows 7 can you recommend?

  11. bani says:

    really helped a lot…thanx!!!
    Sir
    I am new to sql..can u plz help me to carry out sql work in cmd.Please…

  12. Diogo Bernardino says:

    Hi Hao,
    Straight and helpful explanation.
    Thanks!

  13. alex says:

    hello – great explanation here – thanks. Wonder if you’ve ever come across this: On my dev machine I setup odbc connector using the latest 64/32 drivers, connected up fine and setup a mailmerge in a word doc using ms query to lookup a view. on the client’s dev machine I can only connect with the Unicode version of the driver – ‘General ODBC error’ with the ANSI driver. I can then setup up the mailmerge and see all my data in ms query – but then only numerical columns get returned to Word, varchar columns come out blank. I’ve tried changing the collation and encoding of the original table, casting the varchar columns to char and wrapping them in a CONCAT function (some random advice on google). I see that even in 2012 you were using the 3.5 connector – I’ll give that a try. apologies that this is slightly off topic but you seem to know your stuff so I’m hoping you’ll get me out of the bottomless pit I’m in with this one! a German developer raised a bug at mysql with the same issue a couple of years ago but the mysql team couldn’t replicate so they stopped investigating.

    cheers for the article – wish I’d found this a week ago before I had to learn all the connection voodoo the hard way! nice work dude!

  14. D7UN says:

    hao nguyen incredible man, I was looking for my head dizzy from my exe sync apps do not work, thank you brother Greetings

  15. Remarkable! Its really awesome post, I have got much
    clear idea on the topic of from this paragraph.

  16. Cindy says:

    This was extremely useful and well written! After hours of searching for a solution to my problem, I stumbled upon this and within minutes I had it loaded and running. Excellent job! Thanks so much!

  17. shine says:

    hi,
    Please can you explain how to connect to a remote MySQL server which is in different network using odbc?

    • Hao Nguyen says:

      Hi Shine,

      To connect to a remote MySQL server using ODBC, the process should be the same. You might check with your hosting provider to see if they allow remote ODBC or not. Most of the time, they don’t.

      Hao

  18. Bob Lewis says:

    Thanks soooo much. The special character in my password (!) was stopping me from making the connection. Thank you!

  19. Mark says:

    I get the “Connection Failed: [HY000][MySQL][ODBC 5.1 Driver] Access Denied for user ‘me@servername.domain.local’ (using password: YES) error when connecting via an internal domain. But I do not get it when connecting via my laptop over VPN. I’m using the same 5.1.08 driver, username and password. Ideas? Like I said, my username works on one but not the other.

  20. Shobhit says:

    Hi Hao,

    Big Help this Article. Question – I am facing the password problem,i have a specia character in my password for MySQL 5.2 database placed on a Windows Server 2008 R2 Server. I am unable to connect from Windows 7 or an XP machine. Windows 7 is understandable, but should i be facing similar issues from an XP machine?

    Thanks,
    Shobhit

Leave a Reply