A Step-by-Step Guide to Resetting Your MySQL Root Password on Linux Systems

Losing or forgetting the root password for MySQL can be a frustrating experience. However, fear not, as this guide will walk you through the process of resetting your MySQL root password on Linux systems step by step. By following these instructions, you’ll be back in control of your MySQL server in no time.

Prerequisites

Before we begin, make sure you have:

• Access to the Linux server running MySQL with root privileges or sudo access.
• MySQL or MariaDB installed on the server.

Step 1: Stop the MySQL Service

The first step is to stop the MySQL service to perform maintenance tasks. Run the following command, depending on your Linux distribution:

• For Ubuntu/Debian:

  sudo systemctl stop mysql 
  

• For CentOS/RHEL:

  sudo systemctl stop mysqld 
  

Step 2: Start MySQL in Safe Mode with Skip-Grant-Tables

Skip-Grant-Tables is an option that disables the authentication and authorization checks, allowing you to access MySQL without a password. To start MySQL with this option, use the following command:

sudo mysqld_safe --skip-grant-tables --skip-networking & 

The --skip-networking option will prevent remote connections to the MySQL server during this process.

Step 3: Connect to MySQL

Now that MySQL is running with the Skip-Grant-Tables option, you can connect to it without a password. Run the following command:

mysql -u root 

You should now be connected to the MySQL server as the root user.

Step 4: Set a New Root Password

To set a new root password, follow these steps:

1. Select the mysql database:
   
   USE mysql;

   1	USE mysql;

2. Update the root user’s password:
   • For MySQL 5.7 or newer:
     ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

     1	ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

   • For MySQL 5.6 or older:
     UPDATE user SET Password=PASSWORD('your_new_password') WHERE User='root' AND Host='localhost';

     1	UPDATE user SET Password=PASSWORD('your_new_password') WHERE User='root' AND Host='localhost';

     Replace your_new_password with your desired new password.

3. Flush the privileges to apply the changes:
   FLUSH PRIVILEGES;

   1	FLUSH PRIVILEGES;

Step 5: Exit MySQL and Restart the Service

1. Exit the MySQL command prompt:
   QUIT;

   1

   QUIT;

2. Kill the running mysqld_safe process:

   sudo killall mysqld_safe 
   

3. Restart the MySQL service:
   • For Ubuntu/Debian:

     sudo systemctl start mysql 
     

   • For CentOS/RHEL:

     sudo systemctl start mysqld 
     

Step 6: Test the New Root Password

To ensure your new root password works, try connecting to the MySQL server using the new password:

mysql -u root -p 

Enter the new password when prompted. If you can access the MySQL server, the password reset was successful.

Conclusion

In this step-by-step guide, you learned how to reset the MySQL root password on Linux systems. Remember to keep your new password safe and secure. It’s also a good practice to limit remote access to the root user and create separate users with limited privileges for application-specific purposes.