247

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
  • 325,700
  • 82
  • 523
  • 502
nectar
  • 9,525
  • 36
  • 78
  • 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
  • A common reason to want to remove the root password is so that you don't have to type it all the time. Another way to achieve that is to put the password in a mysql config file. (Figuring out the filepaths for mysql config files is another question... `~/.my.cnf` might work.) `[client]\npassword = root` (replace `\n` with a newline i.e. line break) – David Winiecki Feb 21 '23 at 21:37

2 Answers2

452

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
  • 6,333
  • 7
  • 41
  • 59
Dario
  • 5,203
  • 1
  • 23
  • 26
  • 1
    how can I reset password for a perticular database? – nectar Jun 13 '10 at 13:18
  • 6
    mysqladmin -u root -pcurrent_password password '' is another way to do it. – crackity_jones Mar 21 '13 at 23:31
  • 15
    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
27

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
  • 15,374
  • 13
  • 103
  • 121
Faiz Akhtar
  • 279
  • 3
  • 2