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

Question

I know that some users may say that this is a duplication but seriously I have combed the answers and those on the internet for over half a day and I am so frustrated i am going to explode.

Now that therapeutic explanation is done, I have installed MySql Server and MariaDb 10 from the Ubuntu repositories on Ubuntu 12.04 LTS and input the root password when it is installing[When the pink screen appears and prompts for the root password] and the installation goes through as smoothly as a knife through butter.

Now the real frustration begins in either of the below 2 scenarios:

1. When I enter the sudo /usr/bin/mysql_secure_installation command nd am prompted to enter the root password

or

2.When I enter the mysql -u root -p command and am prompted for the password.

Please I really need help on this - I have lost count of the number of times I have had to uninstall and reinstall either MySQL or MariaDb because this pain in neck erf an error.

Solution 1

I think I may have figured out the issue - It has to do with the long password.I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.

So glad I figured this out after so much pain - surprising really that this has not been identified as a bug considering how many years MySQL has been in production.

So basically one resolution is to go through the following steps to resolve the above issue:

1. Start with a blank password on a non-production server and is if it works

2. If you are successful with step 1 then proceed with securing your mysql installation by typing the follwing command: sudo /usr/bin/mysql_secure_installation and try entering a more complex password - comination of lowercase letters, numbers, symbols and uppercase letters of at least 8 characters should be fine.

3. I recommend starting with 8 and move upwards till you reach a ceiling with the number of characters you can enter.

The above should be a step in the right direction to getting your MySQL server secured.

Solution 2

Check the file: /etc/mysql/mariadb.conf.d/mysqld.cnf

verify if this line exists:

plugin-load-add = auth_socket.so

then go to MariaDB:

sudo mysql -u root

and make these modifications:

MariaDB [(none)]>use mysql;
MariaDB [(none)]>update user set plugin=' ' where User='root';
MariaDB [(none)]>flush privileges;
MariaDB [(none)]>exit

I didn't have access to mysqlworkbench, but after I applied this solution, everything ruan well...

Solution 3

Try this command in terminal:

mysqladmin -u root -pcurrentpassword password 'newpassword'

Then, type:

mysql -u root -pnewpassword

Comments

[chetwynd2014]

Hi Jobin, I have tried the first command:mysqladmin -u root -pcurrentpassword password 'newpassword' but the system just hangs.Is there a limit to the number of characters we can use as a password coz mine is over 100 characters long.I didnt have any issues when setting the password so I dont see why that should be an issue now.Thanks.

[chetwynd2014]

I think I may have figured out the issue - It has to do with the long password.I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.So glad I figured this out after so much pain - surprising really that this has not been identified as a bug considering how many years MySQL has been in production.

[chetwynd2014]

This is really strange coz I havnt found any definite documentation or answers on the maximum MySQL password length - not hash mind you.This is crazy coz the longer the password the harder it will be to crack.

[terdon]

Have you actually enabled the root account? You're not giving your regular user's password are you? Also, I assume you know that root's password and root's mysql password are not the same right?

[chetwynd2014]

Hello @terdon and to answer your questions, the root account is enabled and I am not giving anyone any passwords for now.I also do know the difference between the Linux Server root password and MySQLs root password.I have answered the question I asked - please look at my below after a painful and lengthy process.I do hope that this helps anyone out there who faces a similiar issue.Cheers.

[terdon]

Yes, it sounded like you knew what you were doing but you never know, sometimes we make obvious mistakes. I meant no disrespect :). Glad you figured it out. If you remember to come back and accept your answer (I think you need to wait two days) that would be great.

[salsbury]

I was having a similar issue, but seems like it was caused by special characters instead of length. I tried quoting the password as suggested here (superuser.com/questions/123928/…), but that didn't solve it. I eventually just used a special-character-free password.

[Aukhan]

MAN ! Fabby and David please take a moment to come back here and shed some light on whath the "plugin" thing is here for MariaDb, this solution worked for me. My issue started on an abnormal system shutdown and mariadb stopped allowing access for root.

[Conor]

Thanks David! I was able to set a root password for MariaDB by running sudo /usr/bin/mysql_secure_installation but unable to get in anywhere else (CLI or MySQL Workbench) until I followed these steps. What role does this plugin-load-add = auth_socket.so play in resolving this issue?

[Faizan Akram Dar]

I wonder why does it expect default user to be of root privilege

[xcvbn]

still not solved by mariadb devs a year later. your fix works though!

[julian-alarcon]

After installing MariaDB on Windows v10.2.12 I got this error, ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) After changing the password I got the same error, even after reinstallation. But I tried your finding, change the password to a shorter one and now it's working fine! Thank you!

[Admin]

Thanks alot! Fixed it for me. 5.4.0-107-generic #121-Ubuntu SMP , +1 vote