How set mysql root password?

This article describes how to reset the MySQL root password. You may need to do this, for example, if you have forgotten the password.

Table of Contents Show

Step 1: Check the version of MySQL on Ubuntu 20.04
Step 2: Stop the MySQL server
Step 3: Skip Grant Tables & Networking
Step 4: Start the MySQL service
Step 5: Confirm the status of the MySQL Server
Step 6: Sign In to the MySQL shell
Step 7: Alter the root password
Step 8: Revert Database Server to Normal settings
Step 9: Kill all MySQL processes & Restart the MySQL service
Step 10: Log in with the newly set Password
About the author
How do I find MySQL root password?
How set MySQL root password in Linux?
How do I change the root password in MySQL 8.0 27?
How set MySQL root password in Ubuntu?

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:
```
mysql
```
At 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 shutdown
```
Start 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.

Step 1: Check the version of MySQL on Ubuntu 20.04

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 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

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

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.

How do I find MySQL root password?

How to retrieve MySQL root password.

Log in as root into your server through SSH (eg: puTTY/terminal/bash). Alternatively, run the commands that follow as su or sudo as root user. ... .

Navigate to /etc/mysql /cd /etc/mysql..

View the file my. cnf either using the command cat or use any text editing software (vi/vim/nano)..

How set MySQL root password in Linux?

mysql> FLUSH PRIVILEGES; mysql> use mysql; mysql> update user set plugin="mysql_native_password" where User='root'; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password_here'; mysql> FLUSH PRIVILEGES; mysql> quit; Finally, shut down the MySQL service and start it back up.

How do I change the root password in MySQL 8.0 27?

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; In the above change “new_password” with the password that you wish to use. This will start the MySQL service and during the process it will execute the init-file that you have created and thus the password for the root user will be updated.