MySQL 8: 2 Ways to Reset Root Password in Ubuntu

Introduction

Occasionally, you may find yourself in a position where you need to reset the root password for MySQL 8 on Ubuntu. This could be due to a forgotten password or inherited server management responsibilities without credential handover. Fortunately, there are multiple ways to resolve this issue. Here we’ll discuss different methods to reset the root password, each with their steps and considerations.

Method 1: Using mysqld_safe

This method involves starting MySQL in a safe mode that does not require authentication.

1. Stop the MySQL service using the command:

sudo systemctl stop mysql

2. Start mysqld_safe with the –skip-grant-tables option:

sudo mysqld_safe --skip-grant-tables &

3. Login to MySQL without a password:

mysql -u root

4. Flush privileges and set a new root password:

FLUSH PRIVILEGES;
 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

5. Exit MySQL and restart the service:

exit;
 sudo systemctl start mysql

Notes: While this method is straightforward, it involves running MySQL without any security checks, which can be risky. You should revert back to normal operation as quickly as possible to maintain server security.

Method 2: Init File Method

This method uses a SQL script executed at server startup to reset the password.

1. Create an init file with the SQL statement to reset the password:

echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';" > /var/tmp/mysql-init

2. Stop the MySQL service:

sudo systemctl stop mysql

3. Start MySQL with the init file:

sudo mysqld --init-file=/var/tmp/mysql-init

4. After MySQL starts and processes the init file, stop MySQL, and start the service normally:

sudo systemctl restart mysql

Notes: This method is more secure than the first as it does not require running MySQL in an insecure state. However, you need to ensure that the init file is protected and deleted after use to prevent unauthorized password changes.

Conclusion

Resetting the MySQL root password on Ubuntu can be achieved through various methods. Whether you decide to use mysqld_safe or an init file, both methods require caution and understanding of MySQL’s authentication process. It is essential to protect your database to prevent unauthorized access, so always remember to handle root credentials securely and remove temporary configurations promptly after use. After completing the password reset, testing your access and ensuring your databases respond as expected is recommended.