For any reason, you may have forgotten MySQL root password and need to reset it. You can reset it if you have access to the server and a user having sudo privileges. Follow this simple guide and know how to reset MySQL root user password in Ubuntu.
In this guide, we use Ubuntu version 18.10. It should work with any modern Linux distribution like Ubuntu 18.04 and CentOS 7.
First of all, make sure you are logged in with sudo user and have a console window opened. Now, we need to stop MySQL service in case it is running.
> sudo service mysql stop
Create MySQL Service Directory
Next, we need to make service directory which is used by MySQL process to access and store socket file.
> sudo mkdir /var/run/mysqld
Now, we need to give “mysql” user the ownership of this service directory, so it can write to it.
> sudo chown mysql: /var/run/mysqld
Start MySQL Without Grant Tables
Next, start the service manually using “mysqld_safe” command. Here, we start it without permission checks or networking. Using “–skip-grant-tables” flag, anyone can log in to database server with all privileges and without the password.
> sudo mysqld_safe --skip-grant-tables --skip-networking &
The & ampersand which closes the command above allows the program to operate in the background and grant us access to the database server shell where we can change the password.
Now, leave this console window open to keep the process running and open another console. Here, log in to MySQL without the password using this command:
> mysql -uroot mysql
Reset the Root Password
Once, you are logged in, you can run the following command to reset the password:
> mysql> UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='localhost'; Output: Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
The password is changed. Now, exit from it.
> mysql> EXIT;
Stop And Start Service Normally
Next, turn off the running service.
> sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
And finally, start the service normally.
> sudo service mysql start
To verify if the root password has changed, you can log in to your database server with new password using the command:
> mysql -u root -p
Now, enter the new password press ENTER to log in to your MySQL database server.