Blog Post

MySQL change root password On Ubuntu 16.04 / 18.04 LTS

February 25, 2020 Linux, Ubuntu by IJAZ ALI

Mysql change root password on Ubuntu if you forget don’t worry . We’re humans being, and we forget stuff when we do, we tend to go back in time to remember what was forgotten. The same is true with passwords. We do forgot a lot when it comes to password and that’s why there are countless password managers to help us manage our passwords.

Forgetting the root password to your MySQL database in production is totally another thing and not fun and if you did, then you’re in the right place. The given steps below will show you how to quickly reset the root password to your MySQL database server.

This brief tutorial is going to show you how to quickly regain access to MySQL databases by resetting the root password.

Are you ready to reset the root password for MySQL. If yes, then continue with the steps below

MySQL change root password

To reset the MySQL root password, logon to the Ubuntu server and run the command that given below to stop MySQL database service.

sudo /etc/init.d/mysql stop

Then run the command below to create a new directory with the name mysqld and give the MySQL user access to it and give the MySQL user access to it.

sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld/

After that, please run the commands below to start MySQL in safe mode by bypassing the standard authentication process in MySQL.

sudo mysqld_safe --skip-grant-tables &

You should see something like this and you may have to press the Enter key

ijaz@ubuntu:~$ 2020-02-25T16:49:30.551520Z mysqld_safe Logging to syslog.
2020-02-25T16:49:30.554646Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2020-02-25T16:49:30.578079Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2020-02-25T16:49:32.568746Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

[1]+  Done                    sudo mysqld_safe --skip-grant-tables

Next, run the command that given below to logon to the database server with the actual root account without typing a password.

sudo mysql -u root

you’re given root access to the database without entering the password yahoooo!!! it works.
Once you logged into the database. kindly, run the SQL command statement below to use the default MySQL database. This database keep the settings for root user and other server settings.

use mysql;

The output should look like the one below:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

Finally, run the MySQL command statement below to change the root default password

update user set authentication_string=PASSWORD("set_new_password") where User='root';

Save the change by running the commands below

flush privileges;
exit;

Finally, stop MySQL safe_mode and begin MySQL default service

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

If you probably did everything as described above, you should be ready to log back onto MySQL database using the root new password that you set recently.

sudo mysql -u root -p

Done. Enjoy!

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20-0ubuntu.18.04 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

This is how one resets MySQL root user.