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

Question

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

cd ~/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?

Answer 1

This solution was tested in the year 2020

For those whom the current answers didn't work can try this (tested on macOS):

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 get into MySQL you can run:

select Host, User from mysql.user;

And you should see:

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

Answer 2

For Mac users, I've solved this issue

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

by clicking on the "Initialize Database" that you can find in System Settings → MySQL, after clicking there you will need to insert the password again.

Once you are done, if you try to access MySQL from the command line with mysql -u root -p and inserting the previously typed password, it works.

Answer 3

This is a silly answer but, if you fresh install it to your Mac Apple laptop, you need to initialize the database first.

BTW, this will delete all your database data!! If you do this multiple times!

Then you can get access to MySQL using.

/usr/local/mysql/bin/mysql -u root -p

Answer 4

This error can occur when you already have other versions of MySQL installed on your computer. The quickest way to resolve this issue is to uninstall older versions of MySQL before installing the newer one.

Answer 5

This Error can be caused by Special Characters in your Password

If you remove them the issue is solved.

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

The special characters in my case were "$§"

You also have to fix all other issues that cause this error like in windows adding the mysql Installation path to windows path under windows environment Variables. Edit System Environment variables > Advanced > Environment variables > doubl click path > New > enter path to mysql bin folder

C:\Program Files\MySQL\MySQL Server 8.0\bin

Answer 6

The following 3 steps worked for me:

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';
mysql -u root -p

And you're completely done!

Answer 7

You have to reset the password! Steps for Mac OS X (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

(the 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";

Answer 8

Year 2021.

Answer for Ubuntu 20.04 (Focal Fossa) (maybe other distributions as well).

After days of wandering around... and having none of those answers working for me, I did this and it worked!

Always in a Bash shell:

sudo systemctl disable mysql

In order to stop the daemon from starting on boot.

sudo apt purge mysql-server

and

sudo apt purge mysql-community-server*

There, it warns you you'll erase configuration files... so it's working! Because those are the ones making trouble!

sudo apt autoremove

The command sudo apt autoremove deletes all the left behind packages.

Then (maybe it's optional, but I did it) reboot. Also, I downloaded mysql-server-8.0 from the official MySQL webpage:

sudo apt install mysql-server

A signal that it's working is that when you enter the command above, the system asks you to enter the root password.

Finally:

mysql -u root -p

And the password you entered before.

Answer 9

I know it's way to late.. I got this issue in a spring-boot application.

I solved this problem by using defaults, let me explain.

When I used hibernate, I use the property

<property name="connection.user">root</property>

i.e connection.user

now, I tried using the same in spring-boot application.

spring.datasource.user

I get this error.

Solution is spring.datasource.username

Answer 10

I would like to add one more hint to the answers that suggest to reinstall MySql: in my case reinstalling wasn't enough, I also had to remove /etc/mysql folder (MySql 8, Ubuntu 20).

Answer 11

Because your error message says "PASSWORD: YES" this means you are are using the wrong password. This happened to me also. Luckily I remembered my correct password, and was able to make the DB connection work.

Answer 12

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

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

Answer 13

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

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

Answer 14

Add the following two lines at the bottom of your my.cnf file:

[mysqld]    
skip-grant-tables    

This should work.

Answer 15

For WSL2 I entered the following command

sudo mysql -u root -p

It prompted me to enter a password, I assume this step is for setting the password. I typed a random string and I was able to access the MySQL prompt. This may not be the correct answer, but at least it will help you get started

Answer 16

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 0x2D code but the Unicode 0xE2 0x80 0x93 UTF-8 sequence (codepoint U+2013 EN DASH).

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.

Answer 17

In my case I was trying to pass a shell command to a docker container. In which case only the first word was interpreted. Ensure that you're not running:

mysql

as opposed to:

mysql -uroot -ppassword schemaname

perhaps try quoting:

'mysql -uroot -ppassword schemaname'

Answer 18

In my case, I found my root password in the log file "mysqld.log", path "/var/log".

After I run the command "mysql -u root -p" and I enter my root password which I find in /var/log/mysqld.log.

Answer 19

Copied from this link, I had the same problem and this solved the problem. After we add a password for the database, we need to add -p (password-based login), and then enter the password. Otherwise, it will return this error:

mysql -u root -p

Answer 20

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, 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 the Configure button.

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

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

Answer 21

While the top answer (with mysqladmin) worked on macOS v10.15 (Catalina), it did not work on Ubuntu. Then I tried many of the other options, including a safe start for MySQL, but none worked.

Here is one that does:

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 Ubuntu 20.04 (Focal Fossa) LTS comes out).

I found 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.

Answer 22

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 address fixes the issue:

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

Answer 23

I also came across the same problem. I did:

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. It 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 the Windows platform.

Answer 24

I had this problem with Ubuntu 18.04 (Bionic Beaver) 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

Answer 25

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 the password. But we are just going to use(GUx0RblkD3sPhHL5) that in the prompt.

2. `mysql -u debian-sys-maint -p

   Enter password: `

   Now provide the password (GUx0RblkD3sPhHL5).

3. Now exit from MySQL and log in again as:

   `mysql -u root -p

   Enter password: `

Now provide the new password. That's all. We have a new password for further uses.

It worked for me.

Answer 26

In the terminal, just enter:

mysql -u root -p

Then it will ask the password from you.

Answer 27

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

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

All of these are not required. If you are installing on Mac OS using Homebrew:

brew install mysql

Answer 28

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

For MySQL < 5.7:

The default root password is blank (i.e., an empty string), not root. So you can just log in 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 database as well.

Answer 29

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 the 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 &

# 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 password regardless of its host

FLUSH PRIVILEGES;
EXIT;

# Kill the mysqld_safe process
sudo service mysql restart

# Now you can use your new password to log in 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

Answer 30

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 log in with the initial password using this command:

mysql -uroot -p

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