How to Reset MySQL Root Password on Ubuntu
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.
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
Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.
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.
Ensure the directory
/var/run/mysqld exists and correct owner set.
chown mysql /var/run/mysqld
Now start MySQL with the
--skip-grant-tables option. The
& is required here.
You should see something similar:
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';
Replace your_password_here with your own.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
Flush privileges again.
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
update user set plugin="mysql_native_password" where User='root';
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.
Log in to MySQL again and you should now be prompted for a password.
mysql -p -u root
Enter your MySQL root new password.
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