Steps to Recover MySQL Root Password

Introduction

It's not uncommon to lose or forget the MySQL root password, especially if you haven't accessed your server in a while. The following guide provides a step-by-step process for recovering the MySQL root password on an Ubuntu system. We'll also cover some common issues you might encounter during the process and how to address them.

Stop the MySQL service

The first step is to stop the currently running MySQL service. You can do this by running the following command in the terminal:

sudo systemctl stop mysql

Start MySQL

Next, you'll need to start the MySQL server without a password. The --skip-grant-tables option allows us to connect to MySQL without a password and with all privileges.

sudo mysqld_safe --skip-grant-tables &

Note: If you face the issue of "Directory '/var/run/mysqld' for UNIX socket file don't exist", you can resolve it by creating the directory:

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Log in to MySQL without a password

Now, you should be able to connect to MySQL as the root user without a password:

mysql -u root

Change the root password

After successfully logging into MySQL, use the following SQL command to change the root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'New-Password';

Replace 'New-Password' with your new root password. Then exit the MySQL shell by typing exit.

Stop the MySQL service

Now, stop the MySQL service which is running without password verification:

sudo mysqladmin -u root -p -S /var/run/mysqld/mysqld.sock shutdown

When prompted, enter the new MySQL root password that you set earlier.

Start the MySQL service

Finally, start the MySQL service as usual:

sudo systemctl start mysql