mysql – ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

The Question :

207 people think this question is useful

I have been following a manual to install a software suite on Ubuntu. I have no knowledge of MySQL at all. I have done the following installations on my Ubuntu.

sudo apt-get update
sudo apt-get install mysql-server-5.5
sudo apt-get install mysql-client-5.5
sudo apt-get install mysql-common
sudo apt-get install glade
sudo apt-get install ntp

Then I do

me@ubuntu:~/Desktop/iPDC-v1.3.1/DBServer-1.1$ mysql -uroot -proot <"Db.sql"

I ended up with the following error message.

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

How may I fix it and continue?

The Question Comments :

The Answer 1

177 people think this answer is useful

Note: For MySQL 5.7+ please see answer from @Lahiru to this question. That contains more current information.

For MySQL < 5.7:

The default root password is blank (i.e. empty string) not root. So you can just login as:

mysql -u root

You should obviously change your root password after installation

mysqladmin -u root password [newpassword]

In most cases you should also set up individual user accounts before working extensively with the DB as well.

The Answer 2

116 people think this answer is useful

I was able to solve this problem by executing this statement

sudo dpkg-reconfigure mysql-server-5.5

Which will change the root password.

The Answer 3

103 people think this answer is useful

You have to reset the password! steps for mac osx(tested and working) and ubuntu

Stop MySQL using

sudo service mysql stop

or

$ sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

$ sudo mysqld_safe --skip-grant-tables --skip-networking

(above line is the whole command)

This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

$ mysql -u root

mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

As per @IberoMedia’s comment, for newer versions of MySQL, the field is called authentication_string:

mysql> UPDATE mysql.user SET authentication_string =PASSWORD('password') WHERE User='root';

Start MySQL using:

sudo service mysql start

or

sudo /usr/local/mysql/support-files/mysql.server start

your new password is ‘password’.

NOTE: for version of mysql > 5.7 try this:

update mysql.user set authentication_string='password' where user='root';

The Answer 4

65 people think this answer is useful

I know this an old Question but i feel this might help someone. I was recently faced with the same problem but in my case, i remember my password quite alright but it kept on giving me the same error. I tried so many solutions but still none helped then i tried this

mysql -u root -p 

after which it asks you for a pass word like this

Enter password: 

and then i typed in the password i used. That’s all

The Answer 5

44 people think this answer is useful

At the initial start up of the server the following happens, given that the data directory of the server is empty:

  • The server is initialized.
  • SSL certificate and key files are generated in the data directory.
  • The validate_password plugin is installed and enabled.
  • The superuser account ‘root’@’localhost’ is created. The password for the superuser is set and stored in the error log file.

To reveal it, use the following command:

shell> sudo grep 'temporary password' /var/log/mysqld.log

Change the root password as soon as possible by logging in with the generated temporary password and set a custom password for the superuser account:

shell> mysql -u root -p #Login to root user with some password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass5!'; 

The Answer 6

36 people think this answer is useful

It happens when your password is missing.

Steps to change password when you have forgotten:

  1. Stop MySQL Server (on Linux):

    sudo systemctl stop mysql
    
    
  2. Start the database without loading the grant tables or enabling networking:

    sudo mysqld_safe --skip-grant-tables --skip-networking &amp;
    
    

    The ampersand at the end of this command will make this process run in the
    background so you can continue to use your terminal and run #mysql -u root, it will not ask for password.

    If you get error like as below:

    2018-02-12T08:57:39.826071Z mysqld_safe Directory ‘/var/run/mysqld’ for UNIX
    socket file don’t exists. mysql -u root ERROR 2002 (HY000): Can’t connect to local MySQL server through socket
    ‘/var/run/mysqld/mysqld.sock’ (2) [1]+ Exit 1

  3. Make MySQL service directory.

    sudo mkdir /var/run/mysqld
    
    

    Give MySQL user permission to write to the service directory.

    sudo chown mysql: /var/run/mysqld
    
    
  4. Run the same command in step 2 to run mysql in background.

  5. Run mysql -u root you will get mysql console without entering password.

    Run these commands

    FLUSH PRIVILEGES;
    
    

    For MySQL 5.7.6 and newer

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
    
    

    For MySQL 5.7.5 and older

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
    
    

    If the ALTER USER command doesn’t work use:

    UPDATE mysql.user SET authentication_string = PASSWORD('new_password')     WHERE User = 'root' AND Host = 'localhost';
    
    

Now exit

  1. To stop instance started manually

    sudo kill `cat /var/run/mysqld/mysqld.pid`
    
    
  2. Restart mysql

    sudo systemctl start mysql
    
    

The Answer 7

35 people think this answer is useful

if the problem still exists try to force changing the pass

/etc/init.d/mysql stop

mysqld_safe --skip-grant-tables &amp;

mysql -u root

Setup new MySQL root user password

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit;

Stop MySQL Server:

/etc/init.d/mysql stop

Start MySQL server and test it:

mysql -u root -p

The Answer 8

15 people think this answer is useful

If none of the other answers work for you, and you received this error:

mysqld_safe Logging to '/var/log/mysql/error.log'.
mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
[1]+  Exit 1                  sudo mysqld_safe --skip-grant-tables

Follow the below commands step by step until you reset your password:

# Stop Your Server First
sudo service mysql stop

# Make MySQL service directory.
sudo mkdir /var/run/mysqld

# Give MySQL permission to work with the created directory
sudo chown mysql: /var/run/mysqld

# Start MySQL, without permission and network checking
sudo mysqld_safe --skip-grant-tables --skip-networking &amp;

# Log in to your server without any password.
mysql -u root mysql


# Update the password for the root user:
UPDATE mysql.user SET authentication_string=PASSWORD('YourNewPasswordBuddy'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';

#if you omit (AND Host='localhost') section, it updates the root pass regardless of its host

FLUSH PRIVILEGES;
EXIT;

#kill mysqld_safe process
sudo service mysql restart

#Now you can use your new password to login to your Server
mysql -u root -p

#take note for remote access you should create a remote user and then grant all privileges to that remote user

The Answer 9

13 people think this answer is useful

Am using Ubuntu-16.04 : installed mysql – 5.7. I Had the same issue : Login denied for root user.

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_new_username'@'your-hostname' IDENTIFIED BY 'your-password';

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

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

mysql -u <your_new_username> -p

For some reason still just typing mysql does not work. AT ALL. I suggest to make it a habit to use mysql -u <name> -p.

The Answer 10

12 people think this answer is useful

I came across this very annoying problem and found many answers that did not work. The best solution I came across was to completely uninstall mysql and re-install it. On re-install you set a root password and this fixed the problem.


sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

I found this code elsewhere so I take no credit for it. But it works. To install mysql after uninstalling it I think digital ocean has a good tutorial on it. Checkout my gist for this.
https://gist.github.com/JamesDaniel/c02ef210c17c1dec82fc973cac484096

The Answer 11

11 people think this answer is useful

just enter the terminal

mysql -u root -p . than it will ask password to you

The Answer 12

8 people think this answer is useful

I installed MySQL as root user($SUDO) and got this same issue
Here is how I fixed it-

  1. $ sudo cat /etc/mysql/debian.cnf

This will show details as-

# Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = GUx0RblkD3sPhHL5 socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = GUx0RblkD3sPhHL5 socket = /var/run/mysqld/mysqld.sock

Above we can see password just we are going to use(GUx0RblkD3sPhHL5) that in the prompt-

  1. mysql -u debian-sys-maint -p Enter password:
    now provide password(GUx0RblkD3sPhHL5).

  2. Now exit from MySQL and login again as-

    mysql -u root -p Enter password:
    Now provide new password. That’s all, we have new password for further uses.

It worked for me, hope help you too!

The Answer 13

7 people think this answer is useful

Please read the official documentation: Mysql: How to Reset the Root Password

If you have access to terminal:

MySQL 5.7.6 and later:

$ mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

$ mysql
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

The Answer 14

4 people think this answer is useful

The answer may sound silly, but after wasting hours of time, this is how I got it to work

mysql -u root -p

I got the error message

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

Even though I was typing the correct password(the temporary password you get when you first install mysql)

I got it right when I typed in the password when the password prompt was blinking

The Answer 15

3 people think this answer is useful

I am using mysql-5.7.12-osx10.11-x86_64.dmg in Mac OSX

The installation process automatically sets up a temporary password for root user. You should save the password. The password can not be recovered.

Follow the instruction

  1. Go to cd /usr/local/mysql/bin/
  2. Enter the temporary password (which would look something like, “tsO07JF1=>3”)
  3. You should get mysql> prompt.
  4. Run, SET PASSWORD FOR 'root'@'localhost' = PASSWORD('{YOUR_PASSWORD}'); If you wish to set your password: “root” then the command would be, SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
  5. Run ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
  6. Run exit
  7. Run ./mysql -u root -p
  8. Type your password. In my case I would type, “root” (without quote)
  9. That’s all.

For convenience, you should add "/usr/local/mysql/bin" to your PATH

Now from anywhere you can type ./mysql -u root -p and then type the password and you will get mysql> prompt.

Hope it helps.

The Answer 16

2 people think this answer is useful

if the problem still exists try to force changing the pass

Stop MySQL Server (on Linux):

/etc/init.d/mysql stop

Stop MySQL Server (on Mac OS X):

mysql.server stop

Start mysqld_safe daemon with –skip-grant-tables

mysqld_safe --skip-grant-tables &amp;
mysql -u root

Setup new MySQL root user password

use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit;

Stop MySQL Server (on Linux):

/etc/init.d/mysql stop

Stop MySQL Server (on Mac OS X):

mysql.server stop

Start MySQL server service and test to login by root:

mysql -u root -p

The Answer 17

2 people think this answer is useful

If you have MySQL as part of a Docker image (say on port 6606) and an Ubuntu install (on port 3306) specifying the port is not enough:

mysql -u root -p -P 6606

will throw:

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

as it’s trying to connect to localhost by default, specifying your local IP fixes the issue:

mysql -u root -p -P 6606 -h 127.0.0.1

The Answer 18

1 people think this answer is useful

BY default password will be null, so you have to change password by doing below steps.

connect to mysql

root# mysql

Use mysql

mysql> update user set password=PASSWORD(‘root’) where User=’root’; Finally, reload the privileges:

mysql> flush privileges; mysql> quit

The Answer 19

1 people think this answer is useful

Just one line and it solved my issue.

sudo dpkg-reconfigure mysql-server-5.5

The Answer 20

1 people think this answer is useful

In Ubuntu 16.04 (MySQL version 5.7.13) I was able to resolve the problem with the steps below:

  1. Follow the instructions from the in section B.5.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems MySQL 5.7 reference manual

  2. When I tried #sudo mysqld_safe –init-file=/home/me/mysql-init & it failed. The error was in /var/log/mysql/error.log

    2016-08-10T11:41:20.421946Z 0 [Note] Execution of init_file ‘/home/me/mysql/mysql-init’ started. 2016-08-10T11:41:20.422070Z 0 [ERROR] /usr/sbin/mysqld: File ‘/home/me/mysql/mysql-init’ not found (Errcode: 13 – Permission denied) 2016-08-10T11:41:20.422096Z 0 [ERROR] Aborting

The file permission of mysql-init was not the problem, need to edit apparmor permission

  1. Edit by #sudo vi /etc/apparmor.d/usr.sbin.mysqld

    ....
      /var/log/mysql/ r,
      /var/log/mysql/** rw,
    
    
    # Allow user init file
      /home/pranab/mysql/* r,
    
      # Site-specific additions and overrides. See local/README for details.
      #include <local/usr.sbin.mysqld>
    }
    
    
  2. Do #sudo /etc/init.d/apparmor reload

  3. Start mysqld_safe again try step 2 above. Check /var/log/mysql/error.log make sure there is no error and the mysqld is successfully started

  4. Run #mysql -u root -p

    Enter password:

Enter the password that you specified in mysql-init. You should be able to log in as root now.

  1. Shutdown mysqld_safe by #sudo mysqladmin -u root -p shutdown

  2. Start mysqld normal way by #sudo systemctl start mysql

The Answer 21

1 people think this answer is useful

While the top answer (w/ mysqladmin) worked on mac 10.15, then it did not work on ubuntu. Then tried many of the other options including safe start for mysql, none worked. Thus adding a new response.

At least for the version I got 5.7.28-0ubuntu0.18.04.4 answers were lacking IDENTIFIED WITH mysql_native_password. 5.7.28 is the default on the current LTS and thus should be the default for most new new systems (till 20.04 LTS comes out).

Found: https://www.digitalocean.com/community/questions/can-t-set-root-password-mysql-server and now applied

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here';

which does work.

The Answer 22

1 people think this answer is useful

For those whom the current answers didn’t work can try this: (Tested on mac os)

mysql -h localhost -u root -p --protocol=TCP

After this a password will be asked from you and you should use your OS user password. then when you got into MySQL you can run:

select Host, User from mysql.user;

and you should see

MySQL [(none)]> select Host, User from mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

and from here you can change the configurations and edit the password or modify the grants.

The Answer 23

0 people think this answer is useful

In recent MySQL versions there is no password in mysql.user table.

So you need to execute ALTER USER. Put this one line command into the file.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

And execute it as init file (as root or mysql user)

mysqld_safe --init-file=/home/me/mysql-init &amp;

MySQL server need to be stopped to start mysqld_safe.

Also, there may be a problem with apparmor permissions to load this init file. Read more here https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql

The Answer 24

0 people think this answer is useful

If you haven’t set password yet, then run mysql -uroot, it works for me.

The Answer 25

0 people think this answer is useful

On Mac, If you have a problem in logging in with the first password you were given in installation, maybe you can just simply kill the mysql process and then try.

So: 1- run the following command to find the PID of mysql:

ps -aef | grep mysql | grep -v grep

2- kill the process:

kill -15 [process id] 

Then you can login with the initial password using this command:

mysql -uroot -p

Which ask you to enter your password. Just enter the initial password.

The Answer 26

0 people think this answer is useful

I had a similar issue:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

But in my case, the cause was really silly. I copied the command from a Word document, and the problem was that an hyphen did not have the ASCII 2D code but the Unicode E28093.

Wrong way:

mysql -u root –pxxxx

Right way:

mysql -u root -pxxxx

Both look the same but aren’t the same (try it, copy and paste replacing your password).

Faced with this type of error, the recommendation is to try typing the command instead of copying and pasting.

The Answer 27

0 people think this answer is useful

I tried with the correct answer by @Lahiru, but did not work with MySQL server version 8.0.16 (Community) on macOS Mojave.

Followed the instructions by Sameer Choudhary above and with some adjustments, I was able to change root password and enable root access from localhost.

Update: All of these are not required, if you are installing on Mac OS using homebrew: “brew install mysql”

The Answer 28

0 people think this answer is useful

I had this problem with Ubuntu 18.04 LTS and Mysql Server version 5.7.27-0ubuntu0.18.04.1 (Ubuntu).

My solution was (running as root with sudo -i)

mysql <<-EOSQL
  use mysql;
  update user set plugin="mysql_native_password" where User='root';
  FLUSH PRIVILEGES ;
EOSQL

mysqladmin -u root password new_pw

The Answer 29

0 people think this answer is useful

I also came across the same problem, what I did was

1) Open your cmd

2) Navigate to C:\Program Files\MySQL\MySQL Server 8.0\bin> (where MySQL Server 8.0 may be different depending on the server you installed)

3) Then put the following command mysql -u root -p

4) I will prompt for the password… simply hit enter, as sometimes the password you entered while installing is changed by to blank.

now you can simply access the database

This solution worked for me on windows platform

The Answer 30

0 people think this answer is useful

The error that I faced was

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

it was a problem with the port running on.

By default the mysql is running on port 3306.

You can check that on by running

  • in a 32-bit system:

    sudo /opt/lampp/manager-linux.run

  • in a 64-bit system:

    sudo /opt/lampp/manager-linux-x64.run

and click on configure button

xampp control panel

In my case the port was running on 3307 and the command i used was

mysql -u root -p -P 3307 -h 127.0.0.1

Add a Comment