Restoring root Access to MySQL on Ubuntu 22.04
If we don't establish a standard for storing and protecting our passwords, sooner or later, you'll lose access to critical services. But don't worry, there's always a solution.
When you’re faced with a situation where you cannot locate the root password for your production database, the challenge might be more common than you think. A colleague once reached out urgently for help because he needed to provision a new service and lacked the credentials for his production database.
This is a scenario that can occur repeatedly in any IT department if there isn’t a standardized process for securely storing business-critical passwords. Personally, I recommend using a password manager like 1Password (no affiliation with them, for now, and welcome 😅; it’s simply the tool I trust the most for various reasons that I’ll detail in a future article) for managing and securely storing all our access credentials to digital services.
In this particular case, I had to assist in resetting the root password for a MySQL 8.0 service running on an Ubuntu 22.04 server. For this task, I required root access to the server in order to have the necessary permissions.
Recovery Procedure
To reset the root password for a MySQL database, follow these steps:
-
Log in as the root user on the server where you need to regain access to the MySQL service.
-
Verify that the installation created the file
debian.cnf. You can check for the file’s existence using the following command:
test -f "/etc/mysql/debian.cnf" && echo true || echo falseIf the command returns “true,” this tutorial applies to your case. This file is specific to MySQL installations configured with Ubuntu’s package manager (apt or apt-get).
-
If the file exists, you’ll be able to connect to the MySQL service with administrative privileges. Execute the following command:
mysql --defaults-file=/etc/mysql/debian.cnfIf your configuration is correct, you should now have administrative access to the database service.
-
Finally, update the MySQL root user’s password by executing this command:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';Remember to replace MyNewPass with the actual new password you wish to set for the root user.
And that’s it! You’ve successfully restored root access to the MySQL service.
I hope this time you remember to store the password securely.
See you next time!