Question

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

Posted December 3, 2015 189.5k 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.

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

  • Show 1 more comments

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
10 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)”

  • I was getting an error after running “sudo mysqld_safe –skip-grant-tables &”:

    mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists
    

    So before starting with this procedure, make sure you run

    mkdir -p /var/run/mysqld
    chown mysql:mysql /var/run/mysqld
    

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.

It worked for me. Thanks

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.

I used mysql -u root -p and enter password and got working.

I am also new in DigitalOcean and was following this article to setup a LEMP server: https://www.digitalocean.com/community/tutorials/how-to-install-linux-nginx-mysql-php-lemp-stack-on-ubuntu-20-04

I was having trouble on the last step. I was unable to connect to the database using the php script. It was showing the following error:

“Error!: SQLSTATE[HY000] [1045] Access denied for user”

I was able to log-in from the terminal using the same credential and was able to see the records on the table, but the php script was showing the connection error for the same credential, which was weird!

After searching all the posts on stackoverflow, found no solution for this. Finally don’t know why, I ran the php file from the terminal like this instead of the browser:

‘php /var/www/yourdomain/todolist.php’

which was showing me an error on line 3, which was the line with the password. It was telling me something like an empty value and indicating the last 2 characters of the password. I then noticed that in my password, before these last two characters, I have a “$” sign, which is the reserved symbol in php to indicate a variable!

So I changed my mysql user password with the following command:

“ALTER USER ‘userName’@'localhost’ IDENTIFIED BY 'New-Password-Here’;”

And updated the php script accordingly. Voila! All worked perfectly afterwards!

Lesson learned: Do not have “$” sign on your password.

Hope it will save others an hour of tension on my same situation!

by Erika Heidi
The LEMP software stack is a group of software that can be used to serve dynamic web pages and web applications written in PHP. This is an acronym that describes a Linux operating system, with an Nginx (pronounced like “Engine-X”) web server. The backend data is stored in a MySQL database, and dynamic processing is handled by PHP. In this guide, we'll set up a LEMP stack on an Ubuntu 20.04 server.
Submit an Answer