How to Reset the MySQL Root Password on Ubuntu

1. Confirm MySQL version

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

mysql -V

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

Keep note of your “Distrib”. In the above example, we are on MySQL 5.7. Keep note of this for later.

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.

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
[email protected]:~$ 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

You may need to 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.

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

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

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

mysql> update user set Password=PASSWORD('your_password_here') where user='root';

When resetting a root password, you must also change the auth plugin to mysql_native_password

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

Flush privileges.

mysql>  flush privileges;

Exit MySQL.

mysql> exit

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

sudo killall -u mysql

Start MySQL again.

sudo /etc/init.d/mysql start

4. Test New Password

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

sudo mysql -p -u root

Enter your password. If correct, you should see

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

You’re all done!

Let me know in the comments if this helped. Follow me @DevAnswers or read more.

5.00 (2 votes)