Setup MySQL on Ubuntu Droplet getting error ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

December 3, 2015 95.9k views
MySQL

Hi guys, I am very new to using a service like digitalocean (opposed to a one click setup of mediatemple or godaddy) and I am trying to setup and configure a new DB using MySQL but I keep getting the error

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

I have googled this, and searched the community for answers but cannot seem to solve this issue. Am I missing something? I have tried running it as root, and running it with sudo through another user and cannot get around it. If I offer no password it gives the same error but instead of using password: YES, it's NO.

Thanks.

5 comments
  • I faced like this error while i want check my database
    ERROR 1045 (28000): Access denied for user 'user02'@'localhost' (using password: NO)
    do you have solution about this

  • Same solution, just login as root and then change password of the user:

    update user set password=PASSWORD("YOURPASSWORDHERE") where User='user02';
    
  • Experienced the same problem.
    This particular explanation and solution made sense for me and helped as well: http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw

  • O/S: Ubuntu 16.04 64bit
    App: MySQL
    Command entered: sudo mysql -u root
    Error:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    I have reimaged 3 times following instructions on the installation, have given it a password and still get this error.

    When I try the step below: mysql -uroot
    I get this error:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

    any help would be greatly appreciated

  • Am using Ubuntu-16.04 : installed mysql - 5.7. I Had the same issue.

    Tried the below steps:

    1. dpkg --get-selections | grep mysql (to get the version of mysql).

    2. dpkg-reconfigure mysql-server-5.7

    3. mysql -u root -p

    Without -p that doesn't prompt you to ask password. Once you are in, you can create a user with a password by following steps :

    CREATE USER '-your_name-'@'your-hostname' IDENTIFIED BY 'your-password';

    GRANT ALL PRIVILEGES ON . to '-your_name-'@'your-hostname' WITH GRANT OPTION;

    Exit from the root and login from the you gave above.

    mysql -u -p

    For some reason still just typing mysql does not work. AT ALL. Please let me know if this works, else I have other steps.

8 Answers

Which one-click-setup did you use? You can check the configuration files probably and find the password.
If you can't find it, neither root without pass is working you can reset it:

Commands

sudo /etc/init.d/mysql stop

Now start up MySQL in safe mode, so you'll skip the privileges table:

sudo mysqld_safe --skip-grant-tables &

Login with root:

mysql -uroot

And assign the DB that needs to be used:

use mysql;

Now all you have to do is reset your root password of the MySQL user and restart the MySQL service:

update user set password=PASSWORD("YOURPASSWORDHERE") where User='root';
flush privileges;

quit and restart MySQL:

quit
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Now your root password should be working with the one you just set, check it with:

mysql -u root -p

Hope this will help you figure it out.

  • Thank you, this worked! I have never setup a server like this so despite my background in dev and light-hobby-like experience with linux, this is all new to me. Thank you!

  • Thanks, this is the right solution.

  • it's working. But after logout my mysql again it will be showing same ERROR ?
    So plzzzz Help ME !!!!

  • i did all the above and reset the password successfully but no luck again the same error coming
    "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"

On MySql 5.7 version you must replace query to:

 update user set authentication_string=PASSWORD("YOURPASSWORDHERE") where user="root"; 

everything else it's like CrypticDesigns described..

Use the right password.

  • The problem is I can't even get to where it's asking for a password now, or can't figure out how to get back there. Either way I received that error even while using the root password, and the mysql password that is displayed when I log into shell using root.

I was tripped up on a brand new installation of mysql. There will be a temporary root password created and logged in /var/log/mysqld.log check there and once mysql is started (as a service) you can then start mysqlsecureinstallation and use the password from the log file to setup mysql.

Thanks CrypticDesigns. It worked well for me.

I have the same problem as described above on my Ubuntu 16.04 LTS/MySQL 5.7.18/WordPress droplet, with 2 CPU, 40GB SSD and 16GB RAM. The droplet is only running one site and has hardly any traffic because it is still under development.

  1. I cannot connect via 'mysql -u root -p'.
  2. I cannot use the 'sudo mysqlsecureinstallation' script. Every time after entering the password I get the dreaded "Access denied for user 'root'@'localhost' (using password: YES)" error message.

This seems to be a very common problem. I followed all the usual recommendation, such as reinstalling MySQL, with and without prior uninstall, crossgrading to MariaDB and back to MySQL, all to none avail. What am I doing wrong?

Btw, the WordPress site is working fine. The problem only became apparent when I added Moodle to the installation and got stuck at the point where you need to create the moodle tables. Since I cannot log into the MySQL server, I'm now stuck.

Installed mysql - 5.7. I Had the same issue.

Tried the below steps:

1. dpkg --get-selections | grep mysql (to get the version of mysql).

2. dpkg-reconfigure mysql-server-5.7

3. mysql -u root -p

Without -p that doesn't prompt you to ask password. Once you are in, you can create a user with a password by following steps :

CREATE USER '-your_name-'@'your-hostname' IDENTIFIED BY 'your-password';

GRANT ALL PRIVILEGES ON . to '-your_name-'@'your-hostname' WITH GRANT OPTION;

Exit from the root and login from the you gave above.

mysql -u -p

For some reason still just typing mysql does not work. AT ALL. Please let me know if this works, else I have other steps.

Have another answer? Share your knowledge.