211

I want to remove the password for user root in localhost. How can I do that? By mistake I have set the password of root user. That's why phpmyadmin is giving an error:

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

OMG Ponies
  • 300,587
  • 73
  • 490
  • 482
nectar
  • 8,567
  • 34
  • 76
  • 100
  • 4
    Why not configure your phpMyAdmin to use the root password instead? – sisve Jun 13 '10 at 11:27
  • 2
    The MySQL documentation contains instructions on [how to reset the root password](http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html) in case you have forgotten it. – Michael Madsen Jun 13 '10 at 12:03
  • 1
    Note that starting with MySQL 5.7, a random root password is set by default, and you cannot remove it without disabling the `validate_password` plugin first. See my article [Removing the MySQL root password](https://medium.com/@benmorel/remove-the-mysql-root-password-ba3fcbe29870) or [this gist](https://gist.github.com/BenMorel/3aa86d9db6c6751b6ab77b3a939938fc) directly. – BenMorel Sep 07 '17 at 12:15
  • For all non-root users `SET PASSWORD FOR root@\`%\`=PASSWORD('');` , the percentage sign must be enclosed by the backticks – FantomX1 Oct 09 '20 at 12:19

2 Answers2

397

You need to set the password for root@localhost to be blank. There are two ways:

  1. The MySQL SET PASSWORD command:

    SET PASSWORD FOR root@localhost=PASSWORD(''); -- MySQL 5.x
SET PASSWORD FOR root@localhost=''; -- MySQL 8.x

  2. Using the command-line mysqladmin tool:

    mysqladmin -u root -pType_in_your_current_password_here password ''
Pyves
  • 5,405
  • 6
  • 35
  • 53
Dario
  • 4,671
  • 1
  • 21
  • 26
  • 1
    how can I reset password for a perticular database? – nectar Jun 13 '10 at 13:18
  • 5
    mysqladmin -u root -pcurrent_password password '' is another way to do it. – crackity_jones Mar 21 '13 at 23:31
  • 11
    I needed to do `mysqladmin -u root -p password ''` then enter the password. – crizCraig Apr 13 '14 at 21:30
  • 2
    @crizCraig probably because your password wasn't `CURRENTPASSWORD`, the password argument (unlike other arguments) doesn't have a space after it. If you omit a password then it will prompt you for one ([doc](http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html#option_mysqladmin_password)) – Jason Sperske May 27 '14 at 22:04
  • Using a password on the command line is not good practice. – davegallant Mar 08 '16 at 15:59
  • @Dario: I forgot my mysql temporary password, how could I login now? :| I can't even change?:(( – mOna Mar 11 '16 at 15:41
  • 3
    @mOna try this way: 1- turn off mysql (shutting down the service or killing it); 2- create a file containing `SET PASSWORD FOR root@localhost=PASSWORD('');` called `restore`; 3- call `mysqld_safe --init-file=path/to/restore`; Finally log in and change again the password with what you prefer. – Dario Mar 12 '16 at 08:39
  • @Dario: I couldn't start with mysqld_safe, then I tried with this command `/usr/local/mysql/support-files/mysql.server start` but I got this error: `. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld.pid). ` – mOna Mar 14 '16 at 12:40
  • @Dario: by the way, regarding mysqld_safe, I found that there is no mysqld in `/var/run`. also, when I typr mysql in terminal, I receive this error: `ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)` – mOna Mar 14 '16 at 12:48
  • Answer #1 works. #2 does not. At least for me. – Elliptical view Oct 04 '16 at 02:33
  • @Dario C:\Program Files (x86)\MySQL\MySQL Server 5.7\bin>mysql.exe -u root -p -e "SET PASSWORD FOR root@localhost=PASSWORD('');"---->ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) – Dr.jacky Mar 12 '17 at 11:37
  • 1
    SET PASSWORD FOR root@localhost=''; worked for me. – Darush Nov 28 '18 at 12:12
  • 4
    The command in answer is deprecated. Use the below instead. SET PASSWORD FOR root@localhost=''; – Prateek Bhuwania Apr 02 '20 at 11:54
  • According to MySQL 8.0 docs: "Rather than using SET PASSWORD to assign passwords, ALTER USER is the preferred statement for account alterations, including assigning passwords." https://dev.mysql.com/doc/refman/8.0/en/set-password.html To remove root password: ALTER USER 'root'@'localhost' IDENTIFIED BY ''; – whbogado Nov 20 '20 at 13:47
25

I have also been through this problem,

First i tried setting my password of root to blank using command :

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

But don't be happy , PHPMYADMIN uses 127.0.0.1 not localhost , i know you would say both are same but that is not the case , use the command mentioned underneath and you are done.

SET PASSWORD FOR root@127.0.0.1=PASSWORD('');

Just replace localhost with 127.0.0.1 and you are done .

akjoshi
  • 14,589
  • 13
  • 94
  • 116
Faiz Akhtar
  • 259
  • 3
  • 2