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.