19

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?

  • 1
    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 '16 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 '16 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. – rathishDBA Apr 29 '17 at 19:20
11

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.

  • i got in but i still don't know how to change or chose a password – user253867 Mar 2 '14 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 '14 at 7:45
  • 7
    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'. – Blauhirn Aug 3 '16 at 19:46
  • 1
    sudo mysql -u root -p works for me, but mysql -u root -p does not – Ivan Black Dec 12 '18 at 8:15
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.

  • This solve my problem on Ubuntu 16 LTS, after trying everything.. permissions, group user. Thanx. – onalbi May 19 '16 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. – TheVillageIdiot Jun 15 '18 at 4:44
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

3

I solved my problem by 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
  • Doesn't work for me. – Ivan Black Dec 12 '18 at 8:11
2

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

Both sudo dpkg-reconfigure mysql-server-5.7 command and removing, purging and cleaning did not solve my problem.

I had to stop mysql service

# sudo service mysql stop

Start mysql daemon with no --no-grant-tables option

# sudo mysql_safe --no-grant-tables &

Then in another terminal, enter mysql (which do not have anymore authenfication) with command mysql, and update 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 mysql_safe command, restart mysql service and connect to mysql normaly

# sudo service mysql start
# mysql
1

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.

protected by Community Feb 8 '17 at 12:49

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.