This article describes how to reset the MySQL root password. You may need to do this, for example, if you have forgotten the password.
You must have root access on the server to reset the MySQL root password.
Changing the MySQL root password
To reset the root password for MySQL, follow these steps:
- Log in to your account using SSH.
You must run the commands in the following steps as the root user. Therefore, you can either log in directly as the root user (which is not recommended for security reasons), or use the su or sudo commands to run the commands as the root user.
- Stop the MySQL server using the appropriate command for your Linux distribution:
- For CentOS and Fedora, type: service mysqld stop
For Debian and Ubuntu, type:
service mysql stop
Restart the MySQL server with the —skip-grant-tables option. To do this, type the following command:
mysqld_safe --skip-grant-tables &- Make sure you type the ampersand (&) at the end of the command. This runs the command in the background and allows you to type the commands in the following steps.
- Running MySQL with the —skip-grant-tables option enabled is highly insecure, and should only be done for a brief period while you reset the password. The steps below show you how to stop the mysqld_safe server instance safely and start the MySQL server securely after you have reset the root password.
Log into MySQL using the following command:
mysqlAt the mysql> prompt, reset the password. To do this, type the following command, replacing new-password with the new root password:
UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';At the mysql> prompt, type the following commands:
FLUSH PRIVILEGES; exit;Stop the MySQL server using the following command. You will be prompted to enter the new MySQL root password before the MySQL server shuts down:
mysqladmin -u root -p shutdownStart the MySQL server normally. To do this, type the appropriate command for your Linux distribution:
- For CentOS and Fedora, type: service mysqld start
For Debian and Ubuntu, type:
service mysql start
Replace the MySQL default root password and make it configurable in your
DX NetOps Spectrum
environment.You can change the default root password for the MySQL root user.
Export the configuration file using the following command:
export MYSQL_TEST_LOGIN_FILE=/$SPECROOT/mysql/data/mylogin.cnf
Run the following command to change the existing password:
./mysql_config_editor set --login-path=myclient --host=localhost --user=root --password
At the prompt, enter the new password.
Enter password:
Run the following command to log in to MySQL with the default password and update the new password:
ALTER USER 'root'@'localhost' IDENTIFIED BY '<new password>';
Verify the new password using the following command:
./my_print_defaults -s --defaults-file=$SPECROOT/mysql/my-spectrum.cnf myclient
The MySQL root user password is changed.
From 21.2.8, use
./update_mysql_user.pl -u root
from the$SPECROOT/mysql/bin
location to update the MySQL root user password. For more information, see Update MySQL User Passwords.If the root user password is updated using mysql_config_editor, ensure that you update it in both the files: mylogin.cnf and myspectrumlogin.cnf.
Passwords are hard to remember, so if you have forgotten the MySQL root password, luckily, there is a way to change it. This post has been written for you, and by the end of this post, you will have successfully changed the password of MySQL. Before getting straight to the solution, it is assumed that you are using the latest version of the MySQL database on Ubuntu 20.04 LTS system. This post will provide a step-by-step guide on
how to change the MySQL root password in Ubuntu 20.04. So, without wasting any time, let’s start. First of all, check the version of your MySQL because this post contains the solution of changing the root password on version 8 or higher. If the version of your MySQL is lower than 8, then the solution will be different. The command for checking the version of MySQL is given below:Step 1: Check the version of MySQL on Ubuntu 20.04
Step 2: Stop the MySQL server
To change the MySQL root password, you first need to shut down the MySQL server, and you can do so using the command:
$ sudo systemctl stop mysql.service
Check the status of the MySQL server to verify using the command:
$ sudo systemctl status mysql.service
Step 3: Skip Grant Tables & Networking
To start the MySQL server without granting the tables and networking check, set the environment variable “MYSQLD_OPTS” which MySQL uses on startup:
$ sudo systemctl set-environment MYSQLD_OPTS="--skip-networking --skip-grant-tables"
Alright, the environment variable is set, and we can log in to the MySQL shell without providing any password.
Step 4: Start the MySQL service
After setting the environment variable “MYSQLD_OPTS”, start the MySQL service now using the command:
$ sudo systemctl start mysql.service
Step 5: Confirm the status of the MySQL Server
Confirm the status of the MySQL service, whether it is running or not:
$ sudo systemctl status mysql.service
Step 6: Sign In to the MySQL shell
Now, you need to log in as a root user to the MySQL server and for signing in to the MySQL shell, type the command:
Without providing any password, you will log in to the MySQL shell.
Step 7: Alter the root password
Now, flush the privileges first:
Select the MySQL database:
And set the new password for the root user using the ALTER command by typing the following statement:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'the-new-password';
At the place of “the-new-password” provide your new password. After successfully changing the MySQL root password, log out from the MySQL shell:
Step 8: Revert Database Server to Normal settings
For restarting the database server in “normal” mode, it is required to “revert” the changes we have made so that the networking is enabled and grant tables are loaded.
To do so, firstly unset the “MYSQLD_OPTS” environment variable which you have set previously:
$ sudo systemctl unset-environment MYSQLD_OPTS
After doing so, remove the modified system configuration by reverting “MySQL”:
$ sudo systemctl revert mysql
Step 9: Kill all MySQL processes & Restart the MySQL service
Now, kill all the processes of MySQL before restarting the MySQL server:
After having the output as shown in the picture, hit “Enter” and restart the MySQL server using the command given below:
$ sudo systemctl restart mysql.service
Step 10: Log in with the newly set Password
Log in to the MySQL shell with the newly set password:
Provide the recently set password in the ALTER command:
This is it. MySQL’s root password is successfully changed, and you are logged back into the MySQL shell.
Wrap Up
In this step-by-step guide, you have learned to change the MySQL root password on the Ubuntu 20.04 LTS system. This post contains a profound and easy-to-understand solution on resetting the root password of MySQL on Ubuntu 20.04.
About the author
A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.