Why you can not connect to your fresh installed MySQL database.
By Sveta Smirnova on May 06, 2009
I read in Russian the talk which I presented at MySQL Conference & Expo this year. Really it was not exactly same, but some parts existed in both. And there was one of my friends who heard the talk in Russian at the conference. He didn't attend English version, but when it finished he came and asked: "Did you say the most important?" "The most important what?" "Why people can not connect to MySQL"
So here is most important part of my talk :)
And if go through our bugs database you will see enormous quantity of bug reports with similar synopsis: "I can not connect to my fresh installed MySQL". Why does it happen?
Major reason is localhost has special meaning. This problem can look different, but has same route.
Favorite problem of Windows users, especcially ones who love GUI Tools is default host. Default host in MySQL is %, but not localhost, neither 127.0.0.1. So if you CREATE USER `abc` IDENTIFIED BY 'xyz'; you really create user `abc`@`%`. Af first glance it looks like you are safe for "Access denied" errors, but MySQL sorts its privilege tables in such a way what more exact address is first and less exact is last and if you have default anonymous user (and you have unless you had run script mysql_secure_installation or manually edited privilege tables) this user would be chosen if you connect as abc from localhost
Favorite problem of UNIX users, especcially ones who use Windows as developer's machine and UNIX on production, is mix of localhost and 127.0.0.1. Assuming you have user test@'127.0.0.1' identified by 'password'; and trying to connect from the same machine MySQL is installed on. Again you will get "Access denied" error, because localhost on UNIX has special meaning and used by default even if you specify --port option. Workaround here is force mysql to use protocol TCP/IP with option --protocol=TCP/IP or --host=127.0.0.1
And last but not least popular reason for "Access denied" errors is mixing name-based and IP-based hostnames for the same username, then specifying different privileges for these users. Which privilege MySQL chooses depends from sort order of mysql.\* tables, but in such cases this can be unpredictable and can create bad headaches, especcially in case of upgrade. Better just don't mix them.