If you've forgotten the root password for MySQL on your CentOS 7 server, it can cause serious complications in managing your database. This article will guide you through the steps to reset the forgotten MySQL root password so you can regain access to your databases.

Preparation

Before you begin, make sure you have full access to the CentOS 7 server as a user with root privileges. The password reset process involves restarting the MySQL service and manipulating the configuration, which requires elevated permissions.

Step 1: Stop the MySQL Service

The first step is to stop the MySQL service. This can be done using the following command in your terminal:

sudo systemctl stop mysqld

Step 2: Start MySQL in Insecure Mode

To reset the root password, we need to start MySQL in a mode that allows us to log in without a password. This can be achieved by setting the --skip-grant-tables flag. This requires editing the MySQL configuration file.

1. Start MySQL in safe mode:

   sudo mysqld_safe --skip-grant-tables &
   ​

This command will start MySQL in safe mode, ignoring permission tables, allowing us to access the database without needing a password.

Step 3: Log in to MySQL as root

Now that MySQL is running in safe mode, you can log in as the root user without a password:

mysql -u root

Step 4: Set a New Root Password

After logging in to MySQL, you can reset the password using the following command. Remember to replace new_password with your new password:

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

Step 5: Restart the MySQL Service

After successfully setting the new password, it's time to restart the MySQL service to bring it back to normal mode. This involves stopping the safe mode and restarting the service:

sudo systemctl stop mysqld
sudo systemctl start mysqld

You should now be able to log in to MySQL with the new root password:

mysql -u root -p

Resetting a forgotten MySQL root password on CentOS 7 can be a challenging process, but by following these steps, you can successfully reset the password and regain control over your databases. Remember, with great power comes great responsibility, so be cautious when manipulating permissions and passwords.