How to Change MySQL user password in Linux

MySQL and MariaDB both are the popular open source database systems meant for Linux based operating systems. Although the MariaDB is the fork of the MySQL,  the commands to operate it are similar to MySQL.

So, the purpose of this tutorial is to take out of the situation, where you forget the root or other MySQL users password. Yes, in case you have forgotten the MySQL root or want to just change the other users’ passwords then commands given here will help you to reset it.

The process given below is quite simple and work almost on all Linux operating systems such as Ubuntu, Debian, Centos and more.

Note: The MySQL default root password is empty that means there is no password assigned to when you installed the MySQL or MariaDB freshly. You just need to press enter after giving the username i.e root.

Let’s see how to reset MySQL root & other users passwords on Linux

For MySQL 5.7 version or older versions to reset root user password 

    1. Go to the terminal command line interface of your Linux system and type the below commands:
      sudo mysql -uroot
    2. Once it connected with the MySQL server then select the MYSQL database.
      use mysql;
    3. Use the below command to change the MySQL root user password. Note: Change the mynewpassword text with the password you want to set for the root user.
      update user set password=PASSWORD("mynewpassword") where User='root';
      flush privileges;
      Quit the MySQL
      quit
      Restart the mysql service
      systemctl restart mysql

For other MySQL 5.7 or above versions:

Everything will be the same as mentioned above except one command which is to change the password:

    1. Open the command terminal.
      sudo mysql -uroot
    2. Select the database.
      use mysql;
    3. Change the root password. Note: Replace the my-new-password with the password you want to set for the root user.
      UPDATE mysql.user SET authentication_string=PASSWORD('my-new-password') WHERE USER='root';
      FLUSH PRIVILEGES;
      Quite

An alternative method for both 5.7 and earlier version to reset the root user password

The below-given command will ask you a couple of questions to secure the MySQL installation including New password option.

sudo mysql_secure_installation

Mysql command to change a user password

MySQL-show-users:

To see the MySQL Database all users you can use using the below command, so you can get a clear idea which user’s password you want to change:

SELECT User FROM mysql.user;

select the users

1. Open the command terminal and connect to the MySQL user and enter the MySQL root user password to log in.

sudo mysql -u root  -p

2. Switch to MySQL database

use mysql;

3. Command to change the USER password in MySQL database server version 5.7.5 or older versions.

SET PASSWORD FOR 'user-name-here'@'localhost' = PASSWORD('new-password');

The screenshot just for reference:

reset mysql user password

To change the MySQL user password in the latest version of the MySQL database server 5.7.6 or above.

ALTER USER 'user-name-here'@'localhost' IDENTIFIED BY 'newPassword';

Screenshot for reference:

chnage mysql user password latest

Note: In above command replace the user-name-here with the user that’s password you want to change and newPassword text, of course, the password that you want to assign to that particular user.

Other Useful resources:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.