MySQL – ERROR 1045 (28000): Access denied for user

MySQL

I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine. I logged into MySQL as root:

david@server1:~$ mysql -u root -p123

I created a new user called repl. I left host blank, so the new user can may have access from any location.

mysql> CREATE USER 'repl' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

I checked the user table to verify the new user repl was properly created.

mysql> select host, user, password from mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| server1   | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost |                  |                                           |
| server1   |                  |                                           |
| localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 |
| %         | repl             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.00 sec)

I then exit, try to login as user repl, but access is denied. 

david@server1:~$ mysql -u repl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ mysql -urepl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$

Why is access denied?

Best Answer

The reason you could not login as repl@'%' has to do with MySQL's user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.

Related Question