How to Reset MySQL Root Password on Ubuntu

Estimated reading: 3 minutes 141 views

MySQL is one of the most popular database systems out there. It’s an open-source relational database management system that follows a client-server model. MySQL is at the core of many of the online services we enjoy on a regular basis

In the MySQL ecosystem, root is the default user created during installation. Similar to Linux, it’s the user with the highest privilege. By default, root access is protected with a password. What to do when you forgot the root password of MySQL?

In this guide, we will show you how to reset MySQL root password on Ubuntu.

Prerequisites

An Ubuntu 20.04 server properly configured with MySQL. The following guide will demonstrate setting up an Ubuntu server. Follow this tutorial to install MySQL

Database Version

Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.

mysql -V

If you are on MySQL version 5, you will see something similar to:

mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper

Restart MySQL with skip-grant-table

In order to skip the grant tables and reset the root password, we must first stop the MySQL service. Enter your Linux password if prompted.

/etc/init.d/mysql stop

Ensure the directory /var/run/mysqld exists and correct owner set.

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

Now start MySQL with the --skip-grant-tables option. The & is required here.

mysqld_safe --skip-grant-tables&

You should see something similar:

Now press ENTER to return to the Linux BASH prompt.

Change MySQL Root Password

You can now log in to the MySQL root account without a password.

mysql --user=root mysql

MySQL 8 – Reset Root Password

For MySQL 8 on Ubuntu, run following commands.

UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;

Replace your_password_here with your own.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

Flush privileges again.

flush privileges;

Exit MySQL.

exit

MySQL 5.7 – Reset Root Password

For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace your_password_here with your own.

update user set authentication_string=PASSWORD('your_password_here') where user='root';

Change the auth plugin to mysql_native_password.

update user set plugin="mysql_native_password" where User='root';

Flush privileges.

flush privileges;

Exit MySQL.

exit

Test New Root Password

Make sure all MySQL processes are stopped before starting the service again.

killall -u mysql

If you see a message similar to below, press ENTER to continue.

Start MySQL again.

/etc/init.d/mysql start

Log in to MySQL again and you should now be prompted for a password.

mysql -p -u root

Enter your MySQL root new password.

Conclusion

The administrative access to MySQL is now restored. The new password should be strong to prevent unwanted access to administrative powe. This is all we guide you in this article, good luck

Leave a Comment