How to change root password on MySQL via command line

MySQL is a great relational database and its widely used by millions of people out there. And beside providing tutorial about Linux, I am trying to start creating some posts about MySQL. I hope you enjoy it and I will start with How to change user password on MySQL via command line.

On this article you will learn few things. First, you will be able to change non root MySQL user’s password, change root password and reset root password. In case you forgotten your root password, you can reset the password in few simple steps.

A. Change non root user’s password

In this example, we are going to change password for user ‘dhani’. Login to MySQL using root and then we can change user’s password from MySQL console.

Login to MySQL

sudo mysql -u root -p

Change user’s password

UPDATE mysql.user SET PASSWORD=PASSWORD('12345') WHERE user="manjaro";

The command above will attempt to change password for user manjaro to 12345.

B. Change root user’s password

In this example, we are going to change MySQL root password. In this case we still able to login to MySQL console with current root password. If you forget your MySQL root password, skip this step and jump to the next section. In order to change MySQL root password, we need to do it safely.

First, shut down MySQL service

sudo systemctl stop mysql

Start MySQL with –skip-grant-table option

sudo mysqld_safe --skip-grant-table &

Login to MySQL

mysql

You won’t be asked to enter root password.

UPDATE mysql.user SET Password=PASSWORD('your-new-password') WHERE User='root';

You should see the following output on your screen:

MariaDB [(none)]> update mysql.user set password=password('12345') where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now continue with this command

flush privileges;
exit;

Shut down MySQL Server. You will be asked to enter root password. Use your new root password to continue.

sudo mysqladmin -u root -p shutdown

Output

dhani@ubuntu-server:/etc/netplan$ sudo mysqladmin -u root -p shutdown
Enter password: 
[1]+ Done sudo mysqld_safe --skip-grant-table

Now let’s start MySQL Server again.

sudo systemctl start mysql

Done.

C. Reset MySQL root password

In case you forgotten your MySQL root password, follow these steps below to reset your MySQL root password. The following trick works on Linux system only. For Windows system, you can follow this explanation.

Step 1. Stop the MySQL Server

sudo systemctl stop mysql

Step 2. Create a text containing password-assignment on a single line

We need to create a text file that contains the following line. Save it to a safe directory where no other user can open it because it contains your next MySQL root password.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Off course do not forget to change MyNewPass with your actual password. For you who don’t know how to create this file, I usually use nano to create this text file. Open Terminal and paste this command. Change it accordingly.

nano /home/dhani/Documents/mysql-password

Step 3. Start MySQL Server with –init-file option

Now we need to start the MySQL Server with –init-file option. Point to the file we created earlier on step 2.

sudo mysqld --init-file=/home/dhani/Documents/mysql-password &

After you execute the command, you should now be able to login to MySQL using your new password.

sudo mysql -u root -p

Now stop and restart the MySQL server normally.

sudo systemctl restart mysql

Done.