While installing the MySQL for the first time on your server, you have to set the password for the root user. The user root is kind of a superuser in MySQL. The root user can perform all the tasks and has all the privileges. It can create new users and grant them privileges on a specific database or multiple databases. As we do not use root account frequently, sometimes we forget the password for the root account. And it’s pretty hard to reset the root password in MySQL. In this tutorial, I am going to show you a method that you can use to reset the MySQL root password in Ubuntu.
This method will work for you especially if you are using MySQL version 5.7 or higher. Majority of the tutorials available on the internet to reset the MySQL root password on Ubuntu does not work if you are using MySQL version 5.7 or higher.
Finally, Let’s get started with the actual tutorial. You must have root access or the sudo privileges on the server. It is because we have to start and stop the services to reset the password.
Reset MySQL root password on Ubuntu
The first thing we have to do is to stop the MySQL service. If any of your application is dependent on this particular MySQL installation, I recommend you to put them on maintenance mode.
Once you are ready to turn off the MySQL service, execute the following command.
$ sudo service mysql stop
It might take a few seconds to stop the service depending on the load you normally have on your server. Once it is stopped, the real work starts!
First of all, we have to create a directory under which MySQL socket file is normally stored. We need to create this directory manually as we will start MySQL without grant tables so that we can log in without a password.
$ sudo mkdir /var/run/mysqld $ sudo chown mysql: /var/run/mysqld
Now, It’s time to start MySQL without loading permissions and networking. So that we can log in as a root user without using the password. To start MySQL manually, execute the following command.
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
Just after executing the above-given command, Log in to MySQL using the command given below as a root user.
mysql -uroot
Once you are in, It’s time to update the table and enter the new password for our root user. Execute the following query to update the password for root user in mysql.user table.
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('NEW_PASSWORD') WHERE User='root';
mysql> exit;
Once done, It’s time to stop the MySQL service we manually started. And then we have to start MySQL normally. First of all, to stop the manually started process, execute the following command.
$ sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
Now, Start the MySQL service normally using the following command.
$ sudo service mysql start
Once the MySQL service is active, your new MySQL root password is set! You can now log in to your MySQL server using the new root password.