Topic: Different Ways to Change Passwords in MySQL
Password change activity is also a critical task for
the database administrator. Why is this activity so critical? The reason is
that changing/resetting the password of the users (Accounts) is the single
command for the database Admin, even nowadays, some people are doing password
change activity through automation means, it is a simple or easy task for the
DBA. But the main task is after the password change activity; if the
application team doesn’t know which application and script are using the
current password, then it will cause the password lock issue. To avoid account
lock issues after password reset activity, later version 8.0.14 MySQL
introduces the feature called retain current password to hold the old and new
passwords until you discard the old password manually.
1.
Password change commands before the 8.0.14 version
-
Change using the Alter command
mysql> select current_user();
+----------------+
|
current_user() |
+----------------+
|
root@localhost |
+----------------+
1
row in set (0.00 sec)
mysql>
alter user test01@localhost identified by 'Admin_1234';
Query
OK, 0 rows affected (0.01 sec)
mysql>
exit
[mysql@mysql-se tmp]$ mysql -u test01 -p
Enter
password:
Welcome
to the MySQL monitor. Commands end with
; or \g.
Your
MySQL connection id is 18
Server
version: 8.4.5 MySQL Community Server - GPL
Copyright
(c) 2000, 2025, Oracle and/or its affiliates.
Oracle
is a registered trademark of Oracle Corporation and/or its
affiliates.
Other names may be trademarks of their respective
owners.
Type
'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select current_user();
+------------------+
|
current_user() |
+------------------+
|
test01@localhost |
+------------------+
1
row in set (0.00 sec)
mysql>
-
Reset password using the SET command
[mysql@mysql-se tmp]$ mysql -u test01 -p
Enter
password:
Welcome
to the MySQL monitor. Commands end with
; or \g.
Your
MySQL connection id is 19
Server
version: 8.4.5 MySQL Community Server - GPL
Copyright
(c) 2000, 2025, Oracle and/or its affiliates.
Oracle
is a registered trademark of Oracle Corporation and/or its
affiliates.
Other names may be trademarks of their respective
owners.
Type
'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> select current_user();
+------------------+
|
current_user() |
+------------------+
|
test01@localhost |
+------------------+
1
row in set (0.00 sec)
mysql>
set password for test01@localhost="Admin_5678";
Query
OK, 0 rows affected (0.01 sec)
mysql>
exit
Bye
[mysql@mysql-se tmp]$ mysql -u test01 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.4.5 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its
affiliates.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> select current_user();
+------------------+
| current_user()
|
+------------------+
| test01@localhost |
+------------------+
1 row in set (0.00 sec)
mysql>
INFORMATION: The SET and ALTER password commands can be
executed for the same user prompt and root password
2. New password change commands in the 8.0.14 version
In later version
8.0.14, version user account can hold two passwords. One old password and
another new password. which helps when you don’t know the exact count, where
you are using the user account and its password, and you want to change the
user password due to the password change policy. you can change the new
password in the application and scripts, which you know other applications and
scripts still use the old password utile you discard it. you can manually
discard it using the discard command. This method of changing passwords helps
to avoid the outages after the password change activity.
-
Connect to the user and change the password
using ‘Retain Current Password’
[mysql@mysql-se tmp]$ mysql -u test01 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.4.5 MySQL Community Server
- GPL
Copyright (c) 2000, 2025, Oracle and/or its
affiliates.
Oracle is a registered trademark of Oracle
Corporation and/or its
affiliates. Other names may be trademarks of
their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to
clear the current input statement.
mysql> select current_user();
+------------------+
| current_user() |
+------------------+
| test01@localhost |
+------------------+
1 row in set (0.00 sec)
mysql> alter user test01@localhost
identified by 'Weltest_8976'
RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[mysql@mysql-se tmp]$
