The Geek Diary

CONCEPTS | BASICS | HOWTO

MySQL : how to set (change) user password

By

There are several ways to set a MySQL user password:
– CREATE USER…IDENTIFIED BY
– GRANT…IDENTIFIED BY
– SET PASSWORD
– mysqladmin password
– UPDATE grant tables (not recommended)

For all methods, the privileges required to change a password are:
1. All users can change their own password.
2. Users with UPDATE on mysql database can update passwords for other users using SET PASSWORD or in MySQL 5.7 and later using ALTER USER.
3. If the read_only option is enabled, in addition to the privileges above, SUPER is required.

SET PASSWORD

The most common way to change an existing account’s password without changing any of its privileges is to use the SET PASSWORD statement. For example, to set the password for tom on the local host to new_password, use the following statement:

mysql> SET PASSWORD FOR tom@localhost = PASSWORD('new_password');
Query OK, 0 rows affected (0.00 sec)

If you are logged in as a non-root user and your user does not have the UPDATE privilege for the mysql database, you can change only your own password. Do this by using the SET PASSWORD statement without the FOR clause:

mysql> SET PASSWORD = PASSWORD('new_password');

MySQL 5.7 and later (SET PASSWORD)

In MySQL 5.7 and later it is not necessary to use the PASSWORD() function:

MySQL 5.7> SET PASSWORD FOR myuser@localhost = 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

To change the password for the current user:

MySQL 5.7> SET PASSWORD = 'my_new_password';
Query OK, 0 rows affected (0.00 sec)

mysqladmin

The mysqladmin utility allows you to change the password for a user you know the existing password for. The usage is:

$ mysqladmin --defaults-file=etc/my.cnf --user=tom --password my_new_password
Enter password:
New password:
Confirm new password:

ALTER USER

The ALTER USER statement can also be used to change the password of an existing user:

MySQL 5.7> ALTER USER tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)

ALTER USER also supports manipulating several users with one statement, for example:

MySQL 5.7> ALTER USER myuser@localhost, tom@localhost IDENTIFIED BY 'my_new_password';
Query OK, 0 rows affected (0.01 sec)
Note : It is generally recommended to use different passwords for each account!

You May Also Like