39

In my server, everytime I try to access MySQL I get the error:

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

when I try mysqladmin -u root -p password I get

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

How can I access MySQL in my Ubuntu server 12.04?

3
  • 2
    Try to avoid typing your password as a parameter on the command line, other users can find it via process listings. Much better to type it direct into the program or use a key
    – Amias
    May 17, 2016 at 8:41
  • The syntax -p password is wrong, it has to be -ppassword but is not recommended. More info: stackoverflow.com/questions/5131931/…
    – Melebius
    Dec 14, 2016 at 11:12
  • Check this page for possible causes of this error and workarounds to fix this error. rathishkumar.in/2017/04/…. It may help you. Apr 29, 2017 at 19:20

7 Answers 7

18

If you're running n localhost, just type the command below in terminal:

mysql -u root -p

If you're using an external server, enter the host IP (xxx.xxx.xxx.xxx) as well:

mysql -hxxx.xxx.xxx.xxx -uroot -p

You will be prompted for your password, enter it and you will be able to access your MySQL prompt.

You can also look at this answer on how to reset your MySQL password.

5
  • 1
    i got in but i still don't know how to change or chose a password
    – user253867
    Mar 2, 2014 at 7:39
  • In the answer I linked to, you have to replace the 5.x at the end with the version of the MySQL server you're using. I'm using 5.5 so my command will be sudo dpkg-reconfigure mysql-server-5.5
    – Parto
    Mar 2, 2014 at 7:45
  • 20
    so, the problem is that OP sees Access denied for user 'root'@'localhost'. You suggest calling mysql -u root -p. The result of this command is: Access denied for user 'root'@'localhost'.
    – phil294
    Aug 3, 2016 at 19:46
  • 9
    sudo mysql -u root -p works for me, but mysql -u root -p does not
    – Ivan Black
    Dec 12, 2018 at 8:15
  • "...and you will be able to access your MySQL prompt." No, we're not!
    – Rodrigo
    Jul 21, 2021 at 15:56
7

I get the same symptoms when I update, but for me the fix (after killing mysql and restarting with --skip-grant-tables to get in) is to execute

update mysql.user set plugin = '';

The update process likes to set this column to "unix_socket". I don't know what that is supposed to accomplish, but for me it breaks everything.

2
  • This solve my problem on Ubuntu 16 LTS, after trying everything.. permissions, group user. Thanx.
    – onalbi
    May 19, 2016 at 10:26
  • yes, I had run hardening script and it changed the plugin to mysql_socket so whatever I was set to using PASSWORD('xxxxxx'), was not working. Jun 15, 2018 at 4:44
6

I solved my problem with this:

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt install mysql-server

Be extremely responsible. With this solution, you are removing MySql server and all of its data.

2
  • 1
    Doesn't work for me.
    – Ivan Black
    Dec 12, 2018 at 8:11
  • 2
    !! Be extremely responsible. With this solution you are removing MySql server and all of it's data.
    – Matiss
    Apr 17, 2019 at 14:24
5

The same as Ulrich Metzger, after upgrading my machine to Ubuntu 16.04 and Mysql-server-5.7, I couldn't log in with root because the plugin column changed.

My problem was not solved with sudo dpkg-reconfigure mysql-server-5.7 + removing + purging + cleaning.

I had to stop the mysql service:

# sudo service mysql stop

Then restart the mysql daemon with the --no-grant-tables option:

# sudo mysqld_safe --no-grant-tables &

Then in another terminal, enter the mysql console (which now doesn't need authentication) with the command mysql, and update the password and plugin columns via a SQL UPDATE:

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

Finally, kill the mysqld_safe command, restart mysql service and connect to mysql normally:

# sudo service mysql start
# mysql
1
  • This was the only solution which worked for me! However, there's a typo, I had to use --skip-grant-tables instead of --no-grant-tables Mar 29, 2022 at 18:14
4

Set a password for mysql

sudo dpkg-reconfigure mysql-server-5.x

Now open the terminal and type

mysql -uroot -p

give password and press enter

0
4

After completely reinstalling mysql (thinking that I was using the incorrect password) I ended up with the same problem, but eventually found Ivan Black's comment above which did the trick:

sudo mysql -u root -p

Why I should need elevated privileges to log in with the root user is beyond me.

1
  • My guess is because the user without elevation doesn't have access to the MySQL socket, but the root user does. Connecting directly to the socket means you don't need to enter a password.
    – user426293
    Aug 3, 2023 at 12:23
3

After upgrading from ubuntu 14.4LTS to 16.4LTS the mysql root user could no more login due a wrong entry in the mysql user table's plugin column. The upgrade sets the plugin value for the root@localhost user to auth_socket but the correct entry must be mysql_native_password, if you used mysql native password encryption before.

1
  • This is the one that's worked for me, and seems to be the root of the problem. Uninstalling and reinstalling won't do anything to fix the "problem". May 21, 2019 at 16:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .