5

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?

  • 3
    Hi, You can better reset the MySQL password by stopping the MySQL service first and start its services in safe mode. After that you can reset the MySQL password and then try login to MySQL with that password. – vembutech Jan 1 '15 at 5:05
  • 2 thing: user "root". Are you sure about that? It is not your normal user name? And mind the caps-lock. – Rinzwind Jan 1 '15 at 9:13
  • @rinzwind if not root then what? Because while installing it asked password for root, right? – Sam Jan 1 '15 at 10:22
  • 1
    @vembutech: helped and resolved. – Sam Jan 1 '15 at 10:52
  • 2
    We are glad to know the issue has resolved. Happy New Year ! – vembutech Jan 1 '15 at 10:53
4

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

| improve this answer | |
  • Don't mind. I don't know much, but are there any security issues with this? – Sam Jan 2 '15 at 14:15
  • 1
    @Anders I have had to reset a few mysql root passwords in my time and have always used the method I posted, since that's what I found with a search. I have never had any problems, but your answer is definitely the correct one. – geoffmcc Jan 3 '15 at 16:33
  • @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 Jan 5 '15 at 1:57
  • @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 Jan 5 '15 at 2:01
3

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.

| improve this answer | |
  • Is this a safe method? – Sam Jan 2 '15 at 3:54
  • Yes. Only option I really know of. All your doing in starting mysql without user passwords and then manually setting a new password – geoffmcc Jan 2 '15 at 3:56
  • 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. – Anders Jan 5 '15 at 2:07
  • The MySQL docs (dev.mysql.com/doc/refman/5.7/en/…) also suggest running the server with --skip-networking. – mwfearnley Nov 20 '16 at 11:53
  • Just a tip: in Debian before update I must select database: select database mysql; – Marcelo Ágil Feb 21 '19 at 5:06
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.