MySql server not recognizing correct password

I have installed MySql server on Ubuntu 14.04 LTS. I have prior experience to it too.

But whenever I login, using mysql -u root -p and after entering password it says:

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

I have entered the correct password. Same thing happened days back, but then I completely removed mysql and reinstalled it again.

Also I tried restarting the mysql server but no help!

Can anyone please help?

Solution 1:

Always read the contents of /usr/share/doc/package-name/ for packages you have installed. They contain important information for using and administrating packages.

So for mysql-server-5.5 there are important information in /usr/share/doc/mysql-server-5.5/README.Debian.gz. As it is gzipped, it is convenient to use zmore to read it.

There you can see that you can use the debian-sys-maint user to access the database for administration. Just as root use /etc/mysql/debian.cnf configuration file to login as that user. Notice that you NEVER should change that users password, unless you also change it in the file debian.cnf. If not, mysql will stop working.

So, to change password for the root user, try this.

$ sudo mysql --defaults-file=/etc/mysql/debian.cnf
mysql> UPDATE mysql.user SET Password=PASSWORD('*secret*') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;
$ mysql -u root -p

If you can use the *secret* password to login in as user root, you have managed to change the password for root.

Notice this use of the debian-sys-maint user only works for Debian based distributions.

Notice also that if you also have the package dbconfig-common installed and set to store the root password (see /etc/dbconfig/config), you need to reconfigure that package so it knows the password to administrat your databases for other packages.

Lastly, good information can also be read in Debians Wiki and in the free The System administrators Book

Solution 2:

First stop the mysql server

 sudo service mysql stop

Run the following command that will start the server again and skip the grant tables which store the passwords.

 mysqld_safe --skip-grant-tables

Mysql server should now start. Next we log into root without a password.

 mysql -u root 

And then press enter

Now we run the following commands in mysql to reset the password

 update user set 
 Password=PASSWORD('new-password')
 where user='root';
 flush privileges;
 exit;

Now restart the mysql server

 sudo service mysql restart 

You should now be able to log in with the password you just set

 mysql -u root -p

Press enter and you should get a prompt for root password and it should be accepted.