Last updated on April 21st, 2022 | 146 replies
In this article we will reset the MySQL root password in Ubuntu by starting MySQL with the --skip-grant-tables
option.
Introduction
We’ve all been there. You’ve forgotten the MySQL root password or it has
mysteriously changed 🤔. Thankfully there is a way around this using the --skip-grant-tables
option.
Before you begin, if you are having problems logging into phpMyAdmin and getting an error Access denied for user ‘root’@’localhost’ , but you’re certain your root password is correct, please refer to this article first: Can’t log into phpMyAdmin: mysqli_real_connect[]: [HY000/1698]: Access denied for user ‘root’@’localhost’
1. Confirm MySQL version
Firstly, you must confirm which version of MySQL on Ubuntu you are running as commands will be different.
If on MySQL version 8, you will see something like:
mysql Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 [[Ubuntu]]
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
2. 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.
sudo /etc/init.d/mysql stop
Ensure the directory /var/run/mysqld
exists and correct owner set.
sudo mkdir /var/run/mysqld
sudo chown mysql /var/run/mysqld
Now start MySQL with the --skip-grant-tables
option. The &
is required here.
sudo mysqld_safe --skip-grant-tables&
You should see something similar:
[1] 1283
user@server:~$ 2019-02-12T11:15:59.872516Z mysqld_safe Logging to syslog.
2019-02-12T11:15:59.879527Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2019-02-12T11:15:59.922502Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Now press ENTER
to return to the
Linux BASH prompt.
3. Change MySQL Root Password
You can now log in to the MySQL root account without a password.
sudo mysql --user=root mysql
Once logged in, you will see the mysql>
prompt.
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. [Generate a strong password
here]
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
Flush privileges again.
Exit MySQL.
Now skip to Step 4 below.
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.
[Generate a strong password here]
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.
Exit MySQL.
Now skip to Step 4 below.
MySQL 5.6 – Reset Root Password
For
MySQL 5.6 on Ubuntu, run this command to change the root password. Replace your_password_here
with your own. [Generate a strong password here]
update user set Password=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.
Exit MySQL.
Now skip to Step 4 below.
4. Test New Root Password
Make sure all MySQL processes are stopped before starting the service again.
If you see a message similar to below, press ENTER
to continue.
2020-05-30T07:23:38.547616Z mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended
Start MySQL again.
sudo /etc/init.d/mysql start
Log in to MySQL again and you should now be prompted for a password.
Enter your MySQL root password. If correct, you should see something like:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-0ubuntu0.20.04.1 [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.
mysql>
You’re all done!
Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.
p.s. I increased my AdSense revenue by 200% using AI 🤖. Read my Ezoic review to find out how.