menu

Questions & Answers

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

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?

Comments:
2023-01-17 00:50:09
Future visitors, also don't forget to check the several causes explained here: dev.mysql.com/doc/refman/8.0/en/problems-connecting.html
Answers(40) :

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

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

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

If you have access to a 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');
Comments:
2023-01-17 00:50:09
Gives : ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords
2023-01-17 00:50:09
It's normal. Your user is anonymous. You need to execute > mysql -u {your_username}

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

In recent MySQL versions there isn't any password in the 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 an init file (as the root or mysql user):

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

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 in AppArmor and MySQL.

By default, the password will be null, so you have to change the password by doing the 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

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.

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.

Comments:
2023-01-17 00:50:09
Thanks for the quick answer. Could you please explain what the -proot <"Db.sql part does?
2023-01-17 00:50:09
@FarticlePilter The -p flag specifies the password, so after you change your root password you would do like mysql -u root -p[newpassword]. The < [filename] is using std input to execute an SQL file at the path given via the user credential you provide.
2023-01-17 00:50:09
I tried as told mysqladmin -u root password abc1234, but I got mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'. Thank you so much!
2023-01-17 00:50:09
doesnt work at all if you already have password and forgot it like this question is about it but answer is about 1st time
2023-01-17 00:50:09
@Kanagaroo in this question, the OP states they just installed MySQL for the first time and then tried to access via MySQL command line client. That sounds like first time access to me.
2023-01-17 00:50:09
My ubuntu 14.04 server pre-installs LAMP(Linux, Apache, MySQL, and PHP). so i don't know the password of the root account. So, when I execute 'mysql -u root -p', it results in "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)", meanwhile, when I execute 'mysql -u root', it results in "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)". So I am so confused about the logging as root in mysql. so, do you guys have any resolutions? @MikeBrant, @SibbsGrambling
2023-01-17 00:50:09
Ps: when I execute 'mysqladmin -u root password [newpassword]', the command line shows "mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)", what on earth happened, guys? ---a beginner of Linux.
2023-01-17 00:50:09
Actually for mysql community server 5.7, the default root password is randomly generated when you install. Check your /var/log/mysqld.log for a line talking about a "temporary password". Saves hours of messing around.
2023-01-17 00:50:09
As of MySQL 5.7 this won't work. For the right way check answer by @Anshu
2023-01-17 00:50:09
I still get that error even when trying mysql -u root
2023-01-17 00:50:09
@BraianMellor This does solve the problem for MySQL < 5.7 in that the default (post-installation) root password on Ubuntu is blank (no password), where in the original question the poster was trying to use root/root. The question was not about lost password, changing password, or similar. For newer versions of MySQL the other answer I referenced is the correct one, as a user must look in the error logs to see the randomly-generated root password.
2023-01-17 00:50:09
I can't even run the first line of code you have. Same error.
2023-01-17 00:50:09
I installed mysql Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu) and followed Lahiru's suggestion to check the log file, which is in /var/log/mysql/error.log, the password is set to blank again. So 'sudo mysql -u root' should work again. Edited to add sudo because I can't get it to work without sudo.
2023-01-17 00:50:09
Did not work on 5.7.28-0ubuntu0.18.04.4. Was lacking IDENTIFIED WITH mysql_native_password. Found: digitalocean.com/community/questions/… and now applied ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pass_here'; which does work.
2023-01-17 00:50:09
@phil At first link and casually seeing your command saves my hours. fixed this within minutes. Thanks, dude

I am using mysql-5.7.12-osx10.11-x86_64.dmg on Mac OS X.

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

Follow the instructions:

  1. Go to cd /usr/local/mysql/bin/
  2. Enter the temporary password (which would look something like, "tsO07JF1=>3")
  3. You should get the 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 environment variable.

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

If the problem still exists, try to force changing the password:

/etc/init.d/mysql stop

mysqld_safe --skip-grant-tables &

mysql -u root

Set up a new MySQL root user password:

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

Stop the MySQL server:

/etc/init.d/mysql stop

Start the MySQL server and test it:

mysql -u root -p
Comments:
2023-01-17 00:50:09
Thank you. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) was also plaguing my server. 'mysqld_safe' also failed until I did a 'killall mysqld' AFTER your Step 1.
2023-01-17 00:50:09
Please, NOT that way ! @Divz's answer is more secure. Quoting the doc about --skip-grant-tables: This enables anyone to connect without a password and with all privileges [...] Because this is insecure, [...] use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting. oh, and also, this one didn't work for me. Thats why I came here :p
2023-01-17 00:50:09
Its giving me: ERROR 1054 (42S22): Unknown column 'password' in 'field list'
2023-01-17 00:50:09
Check answer by @Anshu it's more secure way of handling this
2023-01-17 00:50:09
For me, the mysql version is 5.6+, and the OS is centOS6.5, I should use the /etc/init.d/mysqld stop not /etc/init.d/mysql stop.
2023-01-17 00:50:09
@MD.MohiuddinAhmed You must be using newer version of mysql-server. Refer this answer : stackoverflow.com/a/31122246/6242649

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.

Comments:
2023-01-17 00:50:09
It isn't silly. It is a common occurrence. It can also happen when copying from web pages (not on this site, though), e.g. from blog posts.
2023-01-17 00:50:09
It isn't necessary to retype. In the appropriate text editor those can be positively identified/searched for and replaced (taking the guesswork out of it). In this case, regular expression search for \x{2013} (works in at least Notepad++ and Geany). It can also be used for search/replace for invisible characters like ZERO WIDTH SPACE (search for \x{200B}).
2023-01-17 00:50:09
A sort of canonical question for this is Compilation error: stray ‘\302’ in program, etc.

I was able to solve this problem by executing this statement

sudo dpkg-reconfigure mysql-server-5.5

Which will change the root password.

Comments:
2023-01-17 00:50:10
Just what I needed... but not everyone has version 5.5 of server. use dpkg --get-selections | grep sql to get your version
2023-01-17 00:50:10
@Balmipour what should be the equivalent command to check the sql version in Rhel 6.x
2023-01-17 00:50:10
@Divz what should be the command to reconfigure mysql server in Rhel/centos
2023-01-17 00:50:10
@PrathamN I never used red had, but googling for "red hat check packages versions" gives me commands like this one : yum list installed add | grep sql to filter only SQL packages.
2023-01-17 00:50:10
When I run this command in Mac OS X, I get this error: sudo: dpkg-reconfigure: command not found. Any suggestion?
2023-01-17 00:50:10
For a distro independent way check answer by @Anshu
2023-01-17 00:50:10
As said here, it does not seem to work with version 5.7...
2023-01-17 00:50:10
This does not ask for a new password. Change it to what?

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
Comments:
2023-01-17 00:50:10
For All Remote Address: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf -> bind-address=0.0.0.0

If the problem still exists, try to force changing the password.

Stop MySQL Server (on Linux):

/etc/init.d/mysql stop

Stop MySQL Server (on Mac OS X):

mysql.server stop

Start the mysqld_safe daemon with --skip-grant-tables:

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

Set up a 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 the MySQL server service and test to log in by root:

mysql -u root -p

In the terminal, just enter:

mysql -u root -p

Then it will ask the password from you.

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';
Comments:
2023-01-17 00:50:10
Please, NOT that way ! @Divz's answer is more secure. Quoting the doc about --skip-grant-tables : This enables anyone to connect without a password and with all privileges [...] Because this is insecure, [...] use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting. oh, and also, this one didn't work for me. Thats why I came here :p
2023-01-17 00:50:10
so your saying use this 'sudo mysqld_safe --skip-grant-tables --skip-networking'
2023-01-17 00:50:10
If you go that way, yes. It didn't work for me, and following @Divz's answer seems way easier to me, anyway -- What I would suggest is using dpkg --get-selections | grep mysql-server- to get your exact MySQL version, then go for sudo dpkg-reconfigure mysql-server-5.x (replace 5.x with your server version, btw). I commented @Divz's answer with this precision, but it's masked by the several "thanks" comments.
2023-01-17 00:50:10
Gives : ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
2023-01-17 00:50:10
update user set authentication_string=password('1111') where user='root';
2023-01-17 00:50:10
I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) when I try that. Same deal if I add a -p: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
2023-01-17 00:50:10
I've to change the command sudo mysql restart for sudo service mysql restart, and it worked like a charm.'I've edited your post.
2023-01-17 00:50:10
Since MySQL 5.7.6 the Password column was renamed "authentication_string" Here you can read more about it: bugs.mysql.com/bug.php?id=76655
2023-01-17 00:50:10
NOTE: for version of mysql > 5.7 try this: update mysql.user set authentication_string='password' where user='root';;
2023-01-17 00:50:10
When I try this, I get the following error: Failed to stop mysql.service: Unit mysql.service not loaded. What am I doing wrong?
2023-01-17 00:50:10
is your Mysql running at all?
2023-01-17 00:50:10
+1K the damn authentication_string column is definitely what crashed my script!!! Thanks a lot @IberoMedia !

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.

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

  1. Follow the instructions from 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. We need to edit AppArmor permissions.

  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 file /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.

  5. Shutdown mysqld_safe by sudo mysqladmin -u root -p shutdown

  6. Start mysqld the normal way by sudo systemctl start mysql

Just one line and it solved my issue.

sudo dpkg-reconfigure mysql-server-5.5

It happens when your password is missing.

Steps to change the password when you have forgotten it:

  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 &
    

    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 (as root). It will not ask for a 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 the MySQL console without entering a 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

  6. To stop the instance started manually:

    sudo kill `cat /var/run/mysqld/mysqld.pid`
    
  7. Restart MySQL

    sudo systemctl start mysql
    
Comments:
2023-01-17 00:50:10
In my case, I have to modify the Step 6 as sudo kill `sudo cat /var/run/mysqld/mysqld.pid`
2023-01-17 00:50:10
This is the one that worked for me. In step 5, do 1. use USER 2. To change password use --> update user set authentication_string = PASSWORD("password") where User='root';
2023-01-17 00:50:10
This is the best solution, I could find out. Using "5.7.26 MySQL Community Server (GPL)" on macOS Mojave on a MBP '15 model. I had to stop Mysql from System Preference, and then followed step 2 and step 5. And then I had to kill the MySQL process by looking up the PID using ps -ef | grep mysql.
2023-01-17 00:50:10
After hours and days of hitting my head on this and too lazy to find cause of error in step 2. Thank you so much for this answer. Finally I can see mysql -u root -p working with my new password

For those for 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:

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.

Comments:
2023-01-17 00:50:10
Thanks alot, this was the only solution that worked for me

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 reinstall it. On reinstall 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 don't take any credit for it. But it works. To install MySQL after uninstalling it, I think DigitalOcean has a good tutorial on it. Checkout my gist for this.

How to install MySQL on Ubuntu (which works)

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.

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.

Comments:
2023-01-17 00:50:10
Re "typed in the password when the password prompt was blinking": In contrast to what? Why would it be blinking or not blinking? What caused it to change state wrt. to blinking? Was it a time-based thing? Can you elaborate? Preferably by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).

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

Solution is spring.datasource.username

Solution

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.

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 password like this

Enter password:

And then I typed in the password I used. That's all.

Comments:
2023-01-17 00:50:11
Worked for me! My mysql-server version is: Server version: 8.0.13 MySQL Community Server - GPL
2023-01-17 00:50:11
What if you don't know your password?

In my case I was trying to pass a command to a 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'
Comments:
2023-01-17 00:50:11
What kind of container?
2023-01-17 00:50:11
@PeterMortensen Sorry, I tried to clarify it in the answer.

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.

I am using Ubuntu 16.04 (Xenial Xerus) and installed MySQL 5.7.

I had the same issue

Login denied for root user.

I 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 log in from the <name> 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.

Comments:
2023-01-17 00:50:11
Without the dpkg commands, this worked for me between a WSL install and a MySQL installed in the root. Oddly, my IP changed in WSL, and I could not log into mariaDB from WSL, but I could from Windows. Even more weird is I did have a root@'%' in the users table. Creating a new root and doing the GRANT ALL worked.

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.

XAMPP control panel

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

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!'; 
Comments:
2023-01-17 00:50:11
This looked promising, but it doesn't work for me. When I run sudo grep 'temporary password' /var/log/mysqld.log I get /var/log/mysqld.log: No such file or directory
2023-01-17 00:50:11
Try sudo grep 'temporary password' /var/log/mysql/error.log. @Lahiru solution worked for me.
2023-01-17 00:50:11
I tried sudo grep 'temporary password' /var/log/mysql/error.log but it returns nothing.
2023-01-17 00:50:11
Try shell> sudo grep 'password' /var/log/mysql/error.log. Worked for me, but I get: root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. My mysql version is: Ver 8.0.13 for Linux on x86_64 (MySQL Community Server - GPL)
2023-01-17 00:50:11
According to this tutorial digitalocean.com/community/tutorials/… for For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; and for older systems SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); . In my case Alter has done the job.
2023-01-17 00:50:11
@EnginYilmaz why do you suggest a mysql command when the problem is that mysql is not even accessible?
2023-01-17 00:50:11
Sorry you right @R-obert . I mention to forget that full tutorial is about accessing mysql without password with system root privileges. After logging in without password as root easily other passwords can be set.
2023-01-17 00:50:11
@learner use grep 'A temporary password' /var/log/mysqld.log |tail -1

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

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

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 autoremove

To delete 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.

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

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

[mysqld]    
skip-grant-tables    

This should work.

Comments:
2023-01-17 00:50:11
but not a permanent fix

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

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.

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!';

Comments:
2023-01-17 00:50:11
While necromancy can be good - even a badge for it for questions with no good answers - this case, there is already an accepted answer and many upvoted ones. I would suggest answering questions without good answers instead.