Reset the password of 'root' user in MySql in Ubuntu-16.0.4

Hi all, In this post we are going to see how to reset the password of root user in mysql

As we all know the root user of mysql is something like the root user of linux with full power to access anything in MySql database server.

Normally during the install, the installation would ask for password to set for the root user but there are occasions where as administrator or developer would pushed to reset the password.

This post talks about resetting the password in a precise manner.

This post assumes that you already have a valid MySql Server instance up and running and I am going to use the command line tool i.e the Terminal for connecting to the server and resetting the password.

Let's start it...

Open a terminal and check whether the MySql server is up and running....

> sudo systemctl status mysql

In my case I had the following response on the terminal

If the server is up and running, then you would have something like given below...

Stop the Server(MySql Server) using the command given below

> sudo systemctl stop mysql

Now we have the server stopped.

We have to start the MySql Server differently to have the passord reset for the user root, to do that you must edit mysqld.cnf file located in /etc/mysql/mysql.conf.d folder...

Now open mysqld.cnf file in your favourite editor and try to locate the group [mysqld], you can use the picture below to locate...

At the top of the group or just below the line which contains the content [mysqld]
add the line as said below

skip-grant-tables

Now the configuration file should appear something like the one shown below...

Save the file and exit from the text editor

Now try to start the server using the terminal as given below...

> sudo systemctl start mysql

Check the status of the mysql process

> sudo systemctl status mysql

you should have something like given below...

Connect to mysql server from the command using the command "mysql"

> mysql --user=root

Now you should be connected to the server and what you see on the server is the response connected to the server without the password for the user "root"

Change the password of the user "root" using the statement like the one given below...

Now stop the server and revert back the changes that we made in mysqld.cnf file and start the server...

Connect to the server now using the password switch..

> mysql --user=root --password

Which would ask you to provide the password, provide the one you updated the user with, now you have successfully changed the password of the user "root"

Hope you enjoyed the post, feel free to post your comments...

Thanks.