7

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?

|improve this question
  • have you tried creating this user with a specific host to debug if that is the issue? – Max Vernon Mar 28 '13 at 19:54
  • I just tried dropping the user and creating with host localhost, and this worked. CREATE USER 'repl'@'localhost' IDENTIFIED BY '123'; But I do not want to restrict the user to some host, I would prefer to have any host. – davidjhp Mar 28 '13 at 20:02
  • without knowing anything about how to do this, I would say your syntax is incorrect. There must be some other way to specify ANY host. – Max Vernon Mar 28 '13 at 20:09
  • Looking at the canonical site for CREATE USER shows you are using the correct syntax. dev.mysql.com/doc/refman/5.1/en/create-user.html I wish I could be more help! – Max Vernon Mar 28 '13 at 20:29
5

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@'%'.

|improve this answer
  • 1
    I had already granted those privileges to the user. I do not see how this relates to the access denied problem. – davidjhp Mar 28 '13 at 21:29
4

You should issue for localhost specific to it. 

  GRANT USAGE ON *.* TO 'repl'@'localhost' IDENTIFIED BY '123';

And try connecting.

|improve this answer
0

The problem is these two accounts, added by default.

http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html

+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost |                  |                                           |
| server1   |                  |                                           |
+-----------+------------------+-------------------------------------------+

A blank user name is a wildcard, so no matter what account you use, it matches this user if MySQL thinks you're connecting from localhost or your local server name (server1 in this case)... since they have no password, any password you try is wrong. User authentication only tries the first match, so the user you created never gets noticed when your host is localhost (or your server name).

Delete these two from the mysql.user table and then FLUSH PRIVILEGES;.

Or, the mysql_secure_installation script can do this for you, although I tend to prefer doing things manually.

http://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html

|improve this answer
-1

This could be an issue with corruption of your mysql database. Tables inside mysql database like user table can get corrupt and may cause issued.

Please do a check on those

myisamchk /var/lib/mysql/mysql/ *.MYI

Usually while checking or fixing myisam tables we would like to take mysql down first. If this problem is still not solve please try this out aswell.

If they are corrupt then you can fix them using

myisamchk --silent --force --fast /path/table-name.MYI

Thanks,

Masood

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.