Disclaimer:

This post is for educational purposes only and no responsibility will be taken if you execute any of the commands. You mess it, you fix it!

Replacing a password for a user on MySQL can be done in at least four ways. Three ways at least.

1. set password for ‘user’@’host’=password(‘abc’);

2. grant usage on *.* to ‘user’@’host’ identified by ‘abc’;

3. update mysql.user set password=password(‘abc’) where user=’user’ and host=’host’;

mysql Wed Mar  9 14:27:17 2011 > set password for 'dc'@'%' = password('d');
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:27:39 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*84869AED8A7235127BFD0AD4A55E335B29ADE3AD' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:27:40 2011 > grant usage on *.* to 'dc'@'%' identified by 'y';
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:27:59 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:01 2011 > set password for 'dc' = password('d');
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:28:20 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*84869AED8A7235127BFD0AD4A55E335B29ADE3AD' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:22 2011 > grant usage on *.* to 'dc' identified by 'y';
Query OK, 0 rows affected (0.00 sec)

When updating MySQL privilege tables manually remember to flush privileges as follows. Otherwise any changes will not be noticed by MySQL when authenticating users until the instance is actually restarted.

mysql Wed Mar  9 14:28:29 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:28:30 2011 > update mysql.user set Password=PASSWORD('xyz') where User='dc' and Host='%';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql Wed Mar  9 14:30:01 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*7446F64EFCFB1294A6DE20CAE7E49C2377A9AA25' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql Wed Mar  9 14:30:04 2011 > flush privileges;
Query OK, 0 rows affected (0.04 sec)

mysql Wed Mar  9 14:30:27 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dc'@'%' IDENTIFIED BY PASSWORD '*39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2' |
| GRANT SELECT ON `test`.* TO 'dc'@'%'                                                              |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Another way is to use:

4. mysqladmin

[preacher /sandboxes/msb_5_1_51/data/mysql 14:51:47]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:51:57]$ mysqladmin -uroot -poldpass -h127.0.0.1 -P5151 password "newpass"
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:05]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:07]$ mysql -uroot -pnewpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:16]$ mysqladmin -uroot -pnewpass -h127.0.0.1 -P5151 password "oldpass"
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:27]$ mysql -uroot -poldpass -h127.0.0.1 -P5151
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[preacher /sandboxes/msb_5_1_51/data/mysql 14:52:33]$

The last way I can think of using just command line (there are so many tools you can use):

5. vi data/mysql/users.MYD (don’t do it!)

As a friend of mine put it, it’s for the brain dead, but hey, its yet another way to do it!! :)

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 2 votes)

  1. shantanuo says:

    Very useful post.
    The only problem I had is that when I tried the set password for ‘dc’ = password (‘d’) syntax, it changed the password of only ‘%’ and not ‘localhost’ as well.

  2. @shantanuo: The reason for that is that MySQL authentication is based on user@host and as such if you have multiple user entries for ‘dc’, like ‘dc’@’%’, ‘dc’@’127.0.0.1′, ‘dc’@’%.domain.com’ and you set password for ‘dc’ you will only effect ‘dc’@’%’. dc user can have different password for the different hosts it has access from and therefore in such a case, changing all the passwords for ‘dc’ would be easier doing an update user set Password=PASSWORD(‘newpass’) where User=’dc’; flush privielges;

    Good luck

  3. aaa says:

    Excellent post. I was checking constantly this blog and I’m impressed!
    Very useful information particularly the last part :) I care for such info a lot.
    I was seeking this certain information for a very long time.
    Thank you and best of luck.

  4. aaa says:

    Helpful info. Fortunate me I discovered your site by chance, and I’m surprised why this coincidence did not happened in advance!

    I bookmarked it.

  5. aaa says:

    Your style is really unique compared to other people I’ve read stuff
    from. I appreciate you for posting when you have the opportunity, Guess
    I will just bookmark this web site.

  6. aaa says:

    Tremendous issues here. I am very happy to peer your post.
    Thanks a lot and I am taking a look ahead to touch you. Will you kindly drop me a e-mail?

  7. aaa says:

    I’m amazed, I have to admit. Rarely do I encounter a blog that’s equally educative and interesting, and without a doubt, you’ve hit the nail
    on the head. The issue is something which too few folks are speaking intelligently about.
    Now i’m very happy I came across this in my search for something relating to this.

*