How To Change MySQL User Passwords Using MySQL Change Password Syntax

In this post, we are going to look at MySQL change password commands and syntax for changing both the root password and other users’ passwords.

How To Change The Root Password In MySQL

There are people who have never assigned a root password for their MySQL server, and that enables them to access the server without entering a password. However, that opens up the server to manipulation by anyone and everyone who can access it.

To secure your server from the word go, you need to secure your MySQL account which makes it important that you understand the MySQL change password process.

In case you have forgotten your root password, there are ways you can reset it. In this section, will be changing the root password for the account ‘root’@’localhost’. If you have a different hostname, you can modify the account hostname appropriately.

MySQL Set Root Password On Windows

The first step of the MySQL change password process is to log into your system as admin and stop your MySQL server if it is already running. You can stop your server by finding the MySQL service in Start>Control Panel>Administrative Tools>Services.

You can also force stop your server from the Task Manager if it is not running as a service.

Create and save a .txt file with the password-assignment statement on one line as follows:

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

where MyNewPass should be the password you want to use moving forward.

On the command prompt, restart your MySQL server using –init-file (use a double backslash before the init as shown). Below is how the commands will look in Window’s cmd.

C:\> cd "C:\Progam Files\MySQL\MySQL Server 8.0\bin"
C:\> mysqld -init-file=C:\\mysql.init.txt

You can adjust the cd depending on where your MySQL server is installed. With this simple move, the contents of the file named by the –-init-file will execute at startup and effectively change the account’s password.

If you want the server output to show up in the console window, add the option –console to the mysqld command. Otherwise, without adding the console option, the server output will appear in a log file.

And that is all there is to MySQL change password for root on windows. Once you are through with this process and the server starts successfully, delete the .txt file:

Moving forward your root password will change and you should be able to use it to connect to your server as root. Stop the server and restart it again, as usual, to ascertain that your new password is active.

Changing Non-Root MySQL User Passwords

Now that we are done with MySQL set root password commands, how do you set or change passwords for other users? There are three MySQL change password statements for changing non-root user passwords. These statements are SET PASSWORD, ALTER USER, and UPDATE.

But before getting into the syntax and commands of changing MySQL user password, there are two things you should consider.

• What are the account details for the user whose password you want to change?
• Is there an application using the user account whose password you are changing? If there is, you should also change the applications connection string. Otherwise, the application will be locked out of the database server.

Using SET PASSWORD To Change MySQL User Password

The account you are using to change the password of another user should at the very least have update privileges. For the users whose password you want to change, their account format should be ‘user@host’.

Assuming the user ID is user1, the statement to change their password is:

SET PASSWORD FOR 'user1'@'localhost' =newpass;

Using ALTER USER

Unlike the SET PASSWORD statement, our second statement is accompanied by another clause – ‘IDENTIFIED BY’.

The ALTER USER statement to change a MySQL user’s password is:

ALTER USER user1@localhost IDENTIFIED BY 'newpass';

Using UPDATE Statement

The third and last way of changing a MySQL database user password is using the UPDATE statement. Other than just changing the password, this statement also alters the user table in the MySQL database.

Also, it precedes the use of the FLUSH PRIVILEGES statement which you should execute immediately after executing the UPDATE statement.

The FLUSH PRIVILEGES statement reloads the privileges in MySQL databases’ grant table. The syntax for this method of changing user password is longer than the rest as shown below:

USE mysql;
UPDATE user
SET authentication_string = PASSWORD('newpass')
FLUSH PRIVILEGES;

Apart from the commands and statements we have looked at above, there are other MySQL set root password syntaxes you can use to change a forgotten root password depending on your operating system.