Changing User Passwords – MySQL Change Root Password

In this post, we look at a step by step guide to MySQL change root password if you are working on Linux. We will also look at changing other users’ passwords and get to understand the procedure for MySQL change user password.

If you have used a MySQL database before, you know that setting and changing MySQL root passwords is inevitable. If you haven’t had to change, set, or you have never forgotten your MySQL password, you are one of the lucky ones.

If you are running a MySQL server anywhere, chances are that you will need to change user password or change your root password. The scenarios that would need you to change your MySQL root password vary from wanting to boost your security to forgetting your password.

Like with many MySQL commands, the MySQL change root password command needs you to be in the command line to execute it. Regardless of your installation – MySQL or MariaDB, the steps we will look out will work on either. If you are running Linux, you only need to have admin access – su or sudo – and you can change your root password.

Ideally, when you are running an important database, you want to use a strong password to secure your databases. The best practice is to generate a random password using a password generator and then storing your password in a password manager.

MySQL Set A Root Password

When first installing your MySQL database or MariaDB, you will typically be prompted to set your initial password. While that is one process that nearly always happens, in the instance that you did not get the prompt to set your password during installation, you will have to set your MySQL root password for the first time.

To start setting your root password, open a terminal session and run the command:

myslqadmin -u root password MYPASSWORD

On the field, ‘MYPASSWORD’ enter the new password that you will be using to sign in to your server moving forward. The next time you log into your MySQL server using the command mysql –u root –p, the system will prompt you to enter a password – use the one you set. 

There is also an alternative MySQL set root password syntax. While with the previous method you only set the root password, with the alternative command, you secure your database even more. The command below allows you to set a root password, purge anonymous users, prohibit root users from login in remotely, and delete the test database.

The command to do all this is:

Once you enter the command above, follow the prompts and by the end of it, you will have set a password and secured your database.

Now that our password is all set and good, how do you change it when the time comes? 

Changing MySQL Root Password

The MySQL change root password process is a little more complicated than the initial set root password process. For starters, the MySQL change root password procedure involves creating a new file first. In the new file, enter the details – ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newpassword’ – then save the file as ~/mysql-pwd. 

Remember to input your new password in the ‘newpassword’ field before saving the file.

In the next step, use the command sudo sytemctl stop mysql to stop your MySQL server session. Once your session is terminated, call the script file you created and saved earlier using the command sudo mysqld –init-file=~/mysql-pwd. 

After executing the file, you will get the command prompt from which you can restart your server session by entering the command sudo systemctl start mysql. In the MySQL daemon, log back into your database using the command mysql –u root –p. The engine will prompt you to enter the new password and voila, you are good to go.

So, now you know how MySQL change root password and MySQL set root password work. But, how do you recover a password you have forgotten it?

How To Recover The Root Password In MySQL

If you forgot your root password, what alternative do you have for recovery? Is there a way you can recover a forgotten password? The short answer is yes, the root and user passwords in MySQL are recoverable. 

To recover a forgotten password, you start by stopping the server process using the command:

sudo service mysql stop

The second step is restarting the server using the command below which keeps the engine from asking for a password when you are connecting to the server:

sudo mysqld_safe –skip-grant-tables –skip-networking &

Once the server is up, connect to it as root using the command MySQL –u root. Notice how we don’t specify the password at this instance. Once you connect to the server as root, run the following commands respectively:

mysql -u root

After this is done, restarting your MySQL daemon will enable you to log in as the root user with the new password you set above. The command to restart the server is sudo service MySQL restart. 

The only thing left now is changing other users’ passwords which we will look at below.

MySQL Change User Password Procedure

In MySQL, one of the more common errors is when a MySQL change user password query is run. Below we look at how you can change users’ password using the proper commands and acceptable syntax.

Just like we have done while changing the root password, we are going to change the user password on Linux/UNIX system. For starters, to change user passwords in MySQL, you have to be logged in as root or have all the privileges to alter and update all databases.

The first step is opening a bash shell and connecting to the server as the root user using the command:

After you are logged in, switch to MySQL database before you can start altering user passwords. To switch to the MySQL database, use the command:

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

If you are using a MySQL version that is older than 5.7.5, use the following command to set a user password:

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

If your MySQL is a newer version than 5.7.6, the syntax to change users’ password is:

ALTER USER 'user'@'hostname' IDENTIFIED BY 'newPass';

Alternatively, you can use both UPDATE and SET simultaneously in one command to change the user password:

UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';

‍

Assuming we have a use named John whose password we want to change – there are two ways to do it. We can use either:

SET PASSWORD FOR 'john'@'localhost' = PASSWORD('foobar');

Or

UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE USER='tom' AND Host='localhost';

Once you reset the passwords to your liking, you need to make sure that the database keeps all the records you have updated and marks them as the right ones. To do this, you need to run a commit command as follows:

FLUSH PRIVILEGES;

The above command reloads the privileges to store the updates in the database. Once that is done, leave the MySQL database and try to login in again with the user you have just created and enter the users’ password once prompted. In this instance, to log in, you would enter the command:

mysql -u root -p

You would then hit enter and the engine will prompt you to enter a password, once you enter the password, you will be logged in as the new user.

And that is how you keep your MySQL passwords updated.