63

When I'm working locally, I don't really need to enter my password to access my database. I changed my root password when I first installed MySQL, but I don't know how to change my password back. What should I do?

0

7 Answers 7

60

To change the root password to newpassword:

 mysqladmin -u root -p'oldpassword' password 'newpassword'

To change it so root doesn't require a password:

 mysqladmin -u root -p'oldpassword' password ''

Note: I think it matters that there isn't a space between the -p and 'oldpassword' but I may be wrong about that

3
  • 3
    i keep getting error: access denied for 'root'@localhost . how can i fix this please?
    – t q
    Apr 4, 2012 at 20:15
  • works for me: mysqladmin -u root -p'oldpassword' password '' @t q: you must type extactly oldpassword
    – Bảo Nam
    Jan 7, 2016 at 7:02
  • I found the above post very helpful but Windows warning - doing this exactly as written will actually create a password of two double quotes. The correct way is: mysqladmin -u root -p'oldpassword' password "" (use double quotes not single quotes).
    – Jon Vote
    Nov 1, 2022 at 2:56
20

Rather than removing the password (which may have unpleasant consequences in the future if you happen to expose that server to the wilds), put the current password (and your username) into ~/.my.cnf (or presumably some equivalent location in Windows) that looks like this:

[client]
user = root
password = s3kr1t

This gives MySQL the awesome ability to autologin using the credentials provided, without leaving you wide open for unpleasantness in the future.

0
10
  1. Stop mysqld and restart it with the --skip-grant-tables option.
  2. Connect to it using just mysql.
  3. Change the root password:
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

FLUSH PRIVILEGES;

For reference: the official mysql docs.

2
  • 1
    This works but is a brute force way of doing it. This is more for when you don't know the password.
    – David
    Jan 16, 2010 at 21:38
  • Note, as of MySQL Server 5.7 and up, the column Password is now called authentication_string. stackoverflow.com/a/31122246/1016891 Dec 11, 2021 at 2:30
9

Note that starting with MySQL 5.7, the validate_password plugin is active by default, and prevents you from using an empty password.

You need to disable this plugin to allow for an empty password:

UNINSTALL PLUGIN validate_password;
SET PASSWORD FOR root@localhost = PASSWORD('');

Be careful that unless you don't care about security, you should follow @womble's advice and use a password, along with a .my.cnf file for convenience.

Check my article Removing the MySQL root password on this topic!

2
  • For versions > 5.6 this is the only way that works. I suspect that it should work on previous versions too.
    – Diego
    Aug 10, 2018 at 22:02
  • 1
    UNINSTALL PLUGIN validate_password; leads to: ERROR 1305 (42000): PLUGIN validate_password does not exist, and the version is: mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
    – Palo
    Nov 13, 2021 at 13:44
3

In newer versions

UPDATE mysql.user SET authentication_string=PASSWORD('MyNewPass') WHERE User='root'

and this will remove password

UPDATE mysql.user SET authentication_string=PASSWORD('') WHERE User='root'
1
  • 2
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('') WHERE User='root'' at line 1
    – Palo
    Nov 13, 2021 at 13:46
1

I also created a root password at installation and wanted to change back to using unix authentication once I understood that if I run commands or launch apps as root they can connect to the database without any password which is much simpler than having another password.

I connected as root using the password then ran:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

To test open a new Terminal and do sudo su then mysql and check if it connects with no password (this didn't work when a password was set).

I also created a MySQL account with the same username as my unix account, again with no password, and using the root account I granted it access to the tables I needed, then I could also connect from apps from my user account without changing to root.

This is beyond the scope of the question but next I would be looking into hooking the unix user creation process to automatically creating a matching MySQL user account.

More info and other ways to do it in MySQL Docs How to Reset the Root Password

-2

For the latest MySQL 5.7.23:

mysqladmin -u root password '' -p

You must log in to answer this question.

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