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.

Answer 1

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...

Answer 2

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.

Answer 3

Try this command in terminal:

mysqladmin -u root -pcurrentpassword password 'newpassword'

Then, type:

mysql -u root -pnewpassword

Answer 4

The best answer is here

It explains what's happening. It explains why you shouldn't just disable the plugin. It explains how to get around it (login using sudo, create a new superuser account that's not root).

Note that this solution by david-leonardo doesn't survive a reboot.