Reset MySQL Root Password From The Command Line

The following tutorial describes, how to reset the MySQL root password from the command line.

To do so, stop the MySQL service

sudo systemctl stop mysql

After that, start the MySQL server without loading the grant tables, so that anyone can connect to the database server without a password and with all privileges:

sudo mysqld_safe --skip-grant-tables &

Now, login to the MySQL shell as root without password:

mysql -u root

Set a new root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

If the ALTER USER command does not work, try this command instead:

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD') WHERE User = 'root' AND Host = 'localhost'; 
FLUSH PRIVILEGES;

If you use an oder version of MySQL (5.7.5 and before), you have to use this command instead:

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

If everything works, the result should be:

Query OK, 0 rows affected (0.00 sec)

Then, stop and start the database server normally. We start with stopping the server:

mysqladmin -u root -p shutdown

The server will ask you for the newly set root password. After the server has been stopped, start the server again:

sudo systemctl start mysql

You can verify the password by running:

mysql -u root -p

If everything works fine, the root password has been resetted.