3
mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql -uroot -p
Enter password:       <-- leave blank, hit enter without entering anything

mysql>                <-- i am logged in

NOTE: This is a new mysql instance installation

So if the password is blank, why won't it log me in without a -p flag?

For a little clarification. I am running into this issue when attempting to change the password using a script:

We're using a bash script to do that.

  • mysqladmin -u root password abc wouldn't work (access denied)
  • mysqladmin -u root -p password abc cannot be used because it prompts for a password and we need to automate this.
  • mysqladmin -u root -p'' password abc is not working either
gAMBOOKa
  • 999
  • 6
  • 19
  • 34

3 Answers3

2

it's a default behavior for blank password.There is nothing wrong with it.

To allow yourself to log in to MySQL without having to type a password at the prompt, add your password to a file called .my.cnf in your home directory.

[client]
user=root
password=

Now change the permission of .my.cnf file to either 400 or 600.

chmod 400 .my.cnf

OR

chmod 600 .my.cnf

Now 

mysql

command will automatically log you in.

kaji
  • 2,528
  • 16
  • 17
  • Please beware about security implications for usernames with nonblank password. Nevermind for blank ones ;) – kaji Jan 25 '12 at 07:06
  • The issue would show up when changing the password using mysqladmin, we're using a bash script to do that. `mysqladmin -u root password abc` wouldn't work (access denied) and `mysqladmin -u root -p password abc` cannot be used because it prompts for a password and we need to automate this. `mysqladmin -u root -p'' password abc` is not working either. – gAMBOOKa Jan 25 '12 at 07:19
  • @gAMBOOKa myqladmin -u root password "abc" should work for blank password. Try with quotes – kaji Jan 25 '12 at 07:41
  • Disregard the comment if you read it: It's not working... I was connected to incorrect server. So it's still not working... even with quotes - it's prompting for a password – gAMBOOKa Jan 25 '12 at 07:54
  • @gAMBOOKa it will only work for the blank password like i already mentioned. For nonblank passwords, try mysqladmin -u root -pyourpassword "newpassword" for your automation bash script – kaji Jan 25 '12 at 07:59
  • The password is blank. I can do a `mysql -uroot -p` and simply hit enter when prompted for password, it works. – gAMBOOKa Jan 25 '12 at 08:02
  • well that is strange. I just did all the steps in my laptop and it worked as expected. sorry friend we could not solve your issue – kaji Jan 25 '12 at 08:06
  • It works on another server for me as well. – gAMBOOKa Jan 25 '12 at 08:10
  • 1
    then something funny is going on with mysql. Are you running multiple instances mysql on same machine ??? When running multiple instances of mysql, client (eg mysql, mysqladmin) program gets confused as to which server the command is expected to execute for !! – kaji Jan 25 '12 at 08:18
  • Yes, i am running multiple instances, but I am also adding in the -S flag for selecting the right socket. Any details on this confusion? Related articles? This might be it. – gAMBOOKa Jan 25 '12 at 08:25
  • how about passing socket path to mysqladmin command itself too !! – kaji Jan 25 '12 at 08:31
  • I do that, of course – gAMBOOKa Jan 25 '12 at 08:32
0

It's sending a password, despite not being given one; it's getting that from somewhere.

Check for a file at ~/.my.cnf.

But, set a password for your root user ASAP - unless you're perhaps being logged in anonymously (select current_user();)?

Shane Madden
  • 114,520
  • 13
  • 181
  • 251
0

Because MySQL is set to use password auth, and if you don't provide the password on the command invocation (leaving it at "-p"), it would prompt you to then enter your MySQL password.

It may be easier to think of your password as being a carriage-return rather than being blank

gWaldo
  • 11,957
  • 8
  • 42
  • 69