How to Reset the MySQL Root Password on Ubuntu 18.04

Before attempting to reset the MySQL root password, be aware that a fresh install of Ubuntu & MySQL will often give you full access to all databases if you login as a user with the sudo privilege:

sudo mysql -u root

If you still need to reset the MySQL root password, this post will guide you through the process.

First, check what version of MySQL you have installed. The steps in this guide are for MySQL version 5.7 and higher:

mysql --version

Stop the MySQL service:

sudo systemctl stop mysql

Ensure that the /var/run/mysqld directory exists and that mysql is the owner:

sudo mkdir /var/run/mysqld

sudo chown mysql /var/run/mysqld

Start MySQL in the background, skipping grant tables. This will give all users full access to all tables.

sudo mysqld_safe --skip-grant-tables &

MySQL will start. You may need to press enter to return to the bash prompt.

Login as the root user, and access the mysql database

sudo mysql -u root mysql

Reset the root password using the following SQL query:

UPDATE user SET AUTHENTICATION_STRING=PASSWORD('password') WHERE USER='root';

Change the authentication plugin. When MySQL is installed without a root password it uses the auth_socket plugin for authentication. Because we are setting the root password, we need to change to the mysql_native_password plugin, which will store the password as a hash in the database.

UPDATE user SET PLUGIN="mysql_native_password" WHERE USER='root';

Exit MySQL

quit

Make sure all MySQL processes have been terminated:

sudo killall -u mysql

Restart MySQL:

sudo systemctl start mysql

Verify that you can login to MySQL using the new root password:

mysql -u root -p

Sources

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

https://devanswers.co/how-to-reset-mysql-root-password-ubuntu/