There will be times when you are setting up several development environments and you forget mysql root password.
In this guide I will give you steps on how to reset/ change MySQL root password if you have ssh access to your server(Ubuntu).
SSH into your server and in server terminal.
- Stop the MySQL Server:
sudo service mysql stop
sudo mysqld --skip-grant-tables &
- Login to MySQL as root:
mysql -u root mysql
- In the below commands replace
NEWPASSWORDwith your new password:
For MySQL < 8.0
UPDATE mysql.user SET Password = PASSWORD('NEWPASSWORD') WHERE User = 'root'; FLUSH PRIVILEGES;
If your MySQL uses new auth plugin, you will need to use:
update user set plugin="mysql_native_password" where User='root'; before flushing privileges.
Note: on some versions, if
passwordcolumn doesn’t exist, you may want to try:
UPDATE user SET authentication_string=password('NEWPASSWORD') WHERE user='root';
Note: This method is not regarded as the most secure way of resetting the password, however, it works.
For MySQL >= 8.0
FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD'; FLUSH PRIVILEGES;
5. Stop password less mysql process and restart normal mysql process
sudo killall -9 mysqld and then start normal process:
sudo service mysql start