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
Top comments (0)