Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL root password change

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
FLUSH PRIVILEGES;

Have a look at this from the MySQL reference manual:

First log in to MySQL:

mysql -u root -p

Then at the mysql prompt, run:

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

Then

FLUSH PRIVILEGES;

Look at this page for more information: Resetting the Root Password: Unix Systems

UPDATE:

For some versions of mysql, the password column is no longer available and you'll get this error:

ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

In this case, use ALTER USER as shown in the answer below.


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

You can find Resetting the Root Password in the MySQL documentation.


This is the updated answer for WAMP v3.0.6 and up.

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

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

FLUSH PRIVILEGES;

In MySQL version 5.7.x there is no more password field in the MySQL table. It was replaced with authentication_string. (This is for the terminal/CLI.)

In the MySQL command-line client, phpMyAdmin or any MySQL GUI:

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

FLUSH PRIVILEGES;

Please follow the below steps.

  1. sudo service mysql stop
  2. sudo mysqld_safe --skip-grant-tables
  3. sudo service mysql start
  4. sudo mysql -u root
  5. use mysql;
  6. show tables;
  7. describe user;
  8. update user set authentication_string=password('1111') where user='root';
  9. FLUSH PRIVILEGES;

Log in with password "1111".


I searched around as well and probably some answers do fit for some situations,

my situation is Mysql 5.7 on a Ubuntu 18.04.2 LTS system:

(get root privileges)

$ sudo bash

(set up password for root db user + implement security in steps)

# mysql_secure_installation

(give access to the root user via password in stead of socket)

(+ edit: apparently you need to set the password again?)

(don't set it to 'mySecretPassword'!!!)

# mysql -u root

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> set password for 'root'@'localhost' = PASSWORD('mySecretPassword'); 
mysql> FLUSH PRIVILEGES;
mysql> exit;

# service mysql restart

Many thanks to zetacu (and erich) for this excellent answer (after searching a couple of hours...)

Enjoy :-D

S.

Edit (2020):

This method doesn't work anymore, see this question for future reference...


I found it! I forgot to hash the password when I changed it. I used this query to solve my problem:

update user set password=PASSWORD('NEW PASSWORD') where user='root';

I forgot the PASSWORD('NEW PASSWORD') and just put in the new password in plain text.


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!