MySql server not recognizing correct password

Question

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.

Solution 3

I had this problem and I solved it for me by adding sudo, e.g. sudo mysql -u root -p and using the password set at installation.

Comments

[Sam]

Is this a safe method?

[geoffmcc]

Yes. Only option I really know of. All your doing in starting mysql without user passwords and then manually setting a new password

[Sam]

Don't mind. I don't know much, but are there any security issues with this?

[Anders]

@SameerThigale, no that I know of that you wouldn't get anyway if someone get their hands on the root account. Because if you get you computer hacked that much, you do have larger problems than someone hacking your MySQL installation. See the other solution which uses the computers root account.

[Anders]

@geoffmcc, any solution that works is right, but with this one you don't need to stop the MySQL server which could bee an advantage. And it is "The Debian Way™". ☺

[Anders]

The only "problem" is that you for a short time rum without passwords on the MySQL server. And that you need to stop the server twice.

[user3757405]

The MySQL docs (dev.mysql.com/doc/refman/5.7/en/…) also suggest running the server with --skip-networking.

[Marcelo Ágil]

Just a tip: in Debian before update I must select database: select database mysql;

[Shai]

I've been working on this for hours; none of the guides I saw for installing mysql and running mysql_secure_installation mentioned this. And mysql_secure_installation didn't mention it. And FYI, I'm a longtime Ubuntu (and varients) user who instinctively things to throw sudo in front of a command that isn't working and I hadn't thought of it. Thank you, thank you.