Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question

1 Answer 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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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