Monday, 17 Jun 2019
Applications Technology

Resetting MySQL Root Password on Ubuntu 17.04 / 17.10

Once in a while we forget stuff… and when we do, we tend to go back in time to remember what was forgotten. The same is true with passwords. We humans do forgot when it comes to passwords… and that’s why there are password managers to help us with passwords.

The day you forget MySQL database root password, the steps below will help you regain access to your database easily… This tutorial is going to show students and new users how to quickly regain access to MySQL databases by resetting the root password.

When you’re ready to reset the root password for MysQL, continue with the steps below

Setting MySQL Root Password

To reset MySQL root password, logon to the server and run the commands below to stop MySQL database service

sudo /etc/init.d/mysql stop

Then run the commands below to create a new mysqld directory

sudo mkdir /var/run/mysqld/

and give mysql user access to it.

sudo chown mysql /var/run/mysqld/

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

sudo mysqld_safe --skip-grant-tables &

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

richard@ubuntu1710:~$ 2017-12-25T16:49:30.551520Z mysqld_safe Logging to syslog.
2017-12-25T16:49:30.554646Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-12-25T16:49:30.578079Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-12-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 commands below to logon to the database server with the root account without typing a password

sudo mysql -u root

Then change to the mysql database by running the SQL command below

use mysql;

Finally, run the SQL statement below to change the root password

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

Save the change by running the commands below

flush privileges;
exit;

Finally, stop MySQL safe_mode and start MySQL default service

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

If you did everything as described above, you should be able to log back onto MySQL database using the root new password.

sudo mysql -u root -p

Enjoy!

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

Copyright (c) 2000, 2017, 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.

You may also like the post below:

Fix MariaDB Plugin ‘unix_socket’ is not loaded Error on Ubuntu 17.04 / 17.10