How to Reset MySQL Root Password in Linux: 5 Easy Steps

Forgetting or losing access to your MySQL root password can be frustrating, especially when managing critical databases. Fortunately, resetting it is a straightforward process if you follow the right steps. Whether you’re a system administrator or a developer, knowing How to Reset MySQL Root Password in Linux can save you from potential downtime and security risks.

In this guide, we’ll walk you through a simple and effective way to regain access to your MySQL database. We’ll cover stopping the MySQL service, restarting it in safe mode, updating the root password, and ensuring security best practices. Whether you’re using MySQL or MariaDB, these steps will help you reset your credentials quickly and securely.

Prerequisites

Before starting, ensure you have:

• Root or sudo access to your Linux server.
• The MySQL or MariaDB service is installed and running.

Step-by-Step Guide to Reset MySQL Root Password in Linux

Step 1: Stop the MySQL Service

The first step in resetting the MySQL root password is to stop the MySQL service. This can be done using the following command:

For Ubuntu/Debian:

sudo systemctl stop mysql

For systems running MariaDB, run:

sudo systemctl stop mariadb

For CentOS/RHEL:

sudo systemctl stop mysqld

For systems running MariaDB, run:

sudo systemctl stop mariadb

Step 2: Start MySQL in Safe Mode

Starting MySQL in safe mode allows you to bypass the normal authentication process. This is done by running the MySQL server with the `–skip-grant-tables` option, which disables the authentication system.

To do this, start MySQL with the following command:

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

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

The `&` at the end of the command runs the process in the background, allowing you to continue using the terminal.

Step 3: Log into MySQL

Since the authentication system is disabled(–skip-grant-tables), you can log into the MySQL server without a password:

mysql -u root

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

Step 4: Change the Root Password

Now that you are logged in, you can reset the root password. Execute the following commands to update the root password. Replace `new_password` with your desired password.

For MySQL 5.7.6 and newer or MariaDB 10.1.20 and newer:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

For older versions:

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

Replace `new_password` with your desired password.

Exit the MySQL command prompt by typing:

QUIT;

Step 5: Stop and Restart MySQL

After resetting the root password, you need to exit the MySQL command prompt and restart the MySQL service to apply the changes.

Exit the MySQL Command Prompt: To exit the MySQL command prompt, type exit or quit, then press Enter:

exit

Terminate the Running mysqld_safe Process: The mysqld_safe process was started to run MySQL in safe mode. Now, you need to stop this process to restart MySQL in its normal mode. Use the following command to kill the running mysqld_safe process:

sudo killall mysqld_safe

Restart the MySQL Service: Depending on your Linux distribution, the command to restart the MySQL service may vary.

For Ubuntu/Debian

If you are using an Ubuntu or Debian-based distribution, restart the MySQL service with:

sudo systemctl start mysql

For CentOS/RHEL

If you are using a CentOS or Red Hat-based distribution, restart the MySQL service with:

sudo systemctl start mysqld

Step 6: Verify the New Password

Finally, verify that the new root password works by logging into the MySQL server:

mysql -u root -p

Enter your new password when prompted. If you can log in without any issues, the password reset was successful.

Troubleshooting Tips

Permission Denied: If you encounter permission issues when starting or stopping MySQL, ensure you have sufficient privileges by using `sudo`.

Service Names: Depending on your distribution, the service name might differ (e.g., `mariadb` instead of `mysql`).

Configuration File: Ensure that no other configuration file is causing issues by checking the/etc/mysql/my.cnf or /etc/my.cnf.

Conclusion

Resetting the MySQL root password in Linux is a crucial skill for database administrators and developers. By carefully following the steps outlined in this guide, you can successfully regain access to your database without compromising security.

Now that you know How to Reset MySQL Root Password in Linux, be sure to store your credentials securely and follow best practices for database security. Regular maintenance and access control can prevent similar issues in the future. If you encounter any difficulties, checking MySQL logs and permissions can help troubleshoot potential errors.

Check out the official MySQL documentation to learn about queries, optimization, security, indexing, transactions, backups, authentication, performance tuning, and more.

How to Reset MySQL Root Password: FAQs

sudo systemctl stop MySQL

sudo systemctl stop mysqld

sudo mysqld_safe --skip-grant-tables &

mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

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

exit

quit

sudo killall mysqld_safe

sudo systemctl start mysql

sudo systemctl start mysqld