As the root account has full privileges in MySQL, it’s recommended to create other accounts for specific use cases.
While this is good practice, a minor drawback is that people often forget the root password after not using it for a while.
Fortunately, resetting the root password is a simple task. We’ll cover the exact steps required to do this on Ubuntu in this article.
Set systemd Environment Variables
Starting the MySQL server by skipping the grant tables lets anyone connect with full privileges and without a password. This’ll let us log in and set a new password for the root account.
It does make your server insecure though, so we’ll also temporarily disable remote connections. First, shut down the MySQL server.
sudo systemctl stop mysql
Set the MYSQLD_OPTS
systemd variable as shown below.
sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables"
Now, start the MySQL server.
sudo systemctl start mysql
Login as Root and Set Password
Log in to MySQL. You won’t need to enter the password even if you log in as root.
sudo mysql -u root
We’ll use the ALTER USER statement to set a new root password. But such account management statements are currently disabled as we launched MySQL by skipping the grant tables. So, reload the grant tables to make such statements work first.
FLUSH PRIVILEGES;
Now, set a new password as shown below. Remember to replace test123
with your own password.
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'test123';
After setting the password, quit MySQL by entering
exit
Revert Configs and Test New Password
Unset the systemd environment variables set earlier and revert the MySQL configs to start MySQL normally again.
sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl revert mysql
Restart the MySQL server to apply these changes.
sudo systemctl restart mysql
Finally, try logging in as root and verify that your new password works.
sudo mysql -u root -p