Resetting and Managing MySQL Root Password

As a database administrator, it’s essential to manage your MySQL root password securely. However, there may be situations where you need to reset or recover the root password. In this tutorial, we’ll explore how to reset the MySQL root password, understand the changes in different MySQL versions, and discuss best practices for managing passwords.

Understanding MySQL Password Storage

MySQL stores user passwords, including the root password, securely using a one-way hash. This means that even if you gain access to the database, you won’t be able to retrieve the original password. The only option is to reset it.

Resetting the Root Password in MySQL 5.7 and Earlier

To reset the root password in MySQL 5.7 and earlier versions, follow these steps:

1. Stop the MySQL service: sudo service mysql stop
2. Start MySQL in safe mode with the --skip-grant-tables option: sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
3. Open a new terminal and log in to MySQL without a password: mysql -u root
4. Update the root password using the following query: UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
5. Flush privileges to apply the changes: FLUSH PRIVILEGES;
6. Quit the MySQL safe mode and start the MySQL service again: mysqladmin shutdown followed by sudo service mysql start

Resetting the Root Password in MySQL 8

In MySQL 8, the PASSWORD() function is deprecated, and you need to use a different query to update the root password:

1. Stop the MySQL service: sudo service mysql stop
2. Start MySQL in safe mode with the --skip-grant-tables option: sudo mysqld_safe --skip-grant-tables --skip-syslog --skip-networking
3. Open a new terminal and log in to MySQL without a password: mysql -u root
4. Update the root password using the following query: UPDATE user SET authentication_string=CONCAT('*', UPPER(SHA1(UNHEX(SHA1('new_password'))))), plugin='mysql_native_password' WHERE User='root' AND Host='localhost';
5. Flush privileges to apply the changes: FLUSH PRIVILEGES;

Alternative Method for Ubuntu 18 and Later

On Ubuntu 18 and later, MySQL uses a privileged socket for authentication by default. To reset the root password, use the following method:

1. Log in to MySQL using the privileged socket: sudo mysql
2. Update the root password using the following query: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

Finding the Temporary Password

In some cases, MySQL may generate a temporary password during installation. You can find this password in the MySQL log file:

1. Use the following command to search for the temporary password: sudo grep 'temporary password' /var/log/mysqld.log

Best Practices for Managing MySQL Root Password

• Always use strong and unique passwords for your root account.
• Limit access to the root account using privileges and roles.
• Regularly update and rotate your passwords to maintain security.
• Consider using a password manager to securely store your database credentials.

By following these steps and best practices, you can securely manage your MySQL root password and maintain the integrity of your database.