2
mysql> select user, password, host from user;
+------+----------+---------------------------------------------+
| user | password | host                                        |
+------+----------+---------------------------------------------+
| root |          | localhost                                   | 
| root |          | host-1.iluruco.clients.pmedia.com | 
| root |          | 127.0.0.1                                   | 
|      |          | localhost                                   | 
|      |          | host-2.iluruco.clients.pmedia.com | 
+------+----------+---------------------------------------------+

but I can log in the MySQL server on my machine using

$ mysql

mysql> select user();
+----------------+
| user()         |
+----------------+
| qaz@localhost | 
+----------------+
1 row in set (0.00 sec)

So why qaz doesn't exist in the user table?

1

MySQL has a funny authentication style

Actually, you need to use two functions

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL

You must be logging as an anonymous user. CURRENT_USER() will tell you what mysqld allowed you to come in as.

My guess is that qaz is the OS user and it was chosen by the mysql client as the user name to login.

Your set up is not secure. Please run this:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE user='root' AND host<>'localhost';
UPDATE mysql.user SET password=PASSWORD('whateverpassword')
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;

This does three(3) things:

  1. Blocks anonymous users
  2. Keeps only root@localhost
  3. Assigns a password to root@localhost

If you cannot log back into mysql, do the following

service mysql restart --skip-grant-tables --skip-networking
mysql -e"UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='root' AND host='localhost';"
service mysql restart 

I wrote earlier posts about this

  • If I give a password to root and forget it. What should I do? – qazwsx May 3 '12 at 20:16
  • I read somewhere that service is no longer a morden way of restarting mysql server. Is it true? – qazwsx May 8 '12 at 19:25

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.