MySQL allows connection without password although password is set

Question

I'm setting a MySQL server (actually a Percona server, but that shouldn't matter) and I'm setting a password to the root user. At the end, I have this:

mysql> select host, user, password from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *huge string here, no kidding             |
| localhost | debian-sys-maint | *another huge string here                 |
+-----------+------------------+-------------------------------------------+
2 rows in set (0.00 sec)

I thought this should not allow the root user to connect without a password. However, if I go to the command line, I can connect with mysql -u root or just mysql. If I do mysql -u root -p and hit enter for the password, then I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).

Could anyone explain to me how to make sure a user can only connect with a password?

Edit: if relevant, I set the password with SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somethinghere');

Edit: output of show grants, it indicates I used a password to login but I did not.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*huge string here, no kidding' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Did you do a `FLUSH PRIVILEGES;` in MySQL after setting the password? You can also try doing `mysqladmin -u root reload` and `mysqladmin -u root refresh` (no password since that's how it seems to want to work at the moment). — Mark Ormston, Apr 07 '13 at 18:42
yes, I do a `FLUSH PRIVILEGES` right after setting them. I ran these two commands you mentioned and nothing changed — Marcelo Diniz, Apr 07 '13 at 18:43
Hmm... if none of that is working, can you just restart MySQL and see if that makes a difference? Not sure what Percona uses for that, but in Debian you do a `sudo /etc/init.d/mysql restart` and in CentOS/RHEL you do `sudo service mysqld restart` — Mark Ormston, Apr 07 '13 at 18:45
Ok, let's look at how you tried to set the password then. The way I do it is with: `sudo mysqladmin -u root password 'test'` assuming I wanted a stupid password of just test - What did you use to set it? — Mark Ormston, Apr 07 '13 at 18:47
Try to understand which account is used, execute SHOW GRANTS query. — amk, Apr 07 '13 at 18:50
@MarkOrmston Edited the question with the way the password was set — Marcelo Diniz, Apr 07 '13 at 18:50
http://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-osx - Though this specifies OSX, it is relevant here as well. Just run through the process using `mysqladmin -u root password 'your password'` AND running the couple of queries they have again. Something's gotta stick, hopefully! — Mark Ormston, Apr 07 '13 at 18:54
@amk Edited the question with the output of `show grants`, it thinks I used a password when I did not. Any idea? — Marcelo Diniz, Apr 07 '13 at 18:55
@MarkOrmston tried everything there, including the `mysqladmin` command, still nothing — Marcelo Diniz, Apr 07 '13 at 19:01

score 14 · Accepted Answer · answered Apr 08 '13 at 09:18

Facepalm. It turned out there was a .my.cnf on /root with username and password, and so it was possible to login only with mysql when using the root account (that's what I was using). It was created by a Chef recipe (percona was installed via Chef) and I wasn't aware of it.

The hint was to look at the output of show grants. Even though I entered no password it still said I entered one, so there must be one somewhere!

MySQL allows connection without password although password is set

1 Answers1