00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL User Password Change

Last updated:

Passwords need changing: on a schedule, when someone leaves the team, or after a suspected leak. MySQL changes them with ALTER USER (or SET PASSWORD), and adds features that make it safe: dual passwords for zero-downtime rotation, random passwords, expiry policies, and a recovery procedure for a forgotten root password.

1. ALTER USER … IDENTIFIED BY

syntax.sql 
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';
alter-user.sql 
ALTER USER 'priya'@'localhost' IDENTIFIED BY 'Pr1ya#New2026';
SELECT user, DATE(password_last_changed) = CURDATE() AS changed_today FROM mysql.user WHERE user = 'priya';

Result: 1 row

userchanged_today
priya1

The new password works immediately; the old one is rejected:

as priya (new password) 
SELECT CURRENT_USER() AS logged_in_as;

Result: 1 row

logged_in_as
priya@localhost
Administrators need the CREATE USER privilege (or UPDATE on the mysql schema) to change other accounts' passwords.

2. Changing Your Own Password

Any user can change their own password. MySQL can require the current password first (PASSWORD REQUIRE CURRENT):

query.sql 
ALTER USER 'priya'@'localhost' PASSWORD REQUIRE CURRENT;
Output 
Query OK, 0 rows affected
as priya 
ALTER USER USER() IDENTIFIED BY 'Pr1ya#Third26';
Output 
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
as priya 
ALTER USER USER() IDENTIFIED BY 'Pr1ya#Third26' REPLACE 'Pr1ya#New2026';
Output 
Query OK, 0 rows affected

SET PASSWORD = 'new' REPLACE 'current'; does the same.

3. Random Passwords

Let MySQL generate a strong password and show it once:

query.sql 
ALTER USER 'priya'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

Result: 1 row

userhost
priyalocalhost
The generated password appears only in this result (we hide it here). Copy it straight into a secrets manager.

4. Zero-Downtime Rotation with Dual Passwords

Rotating an application password with no downtimeStep 1: ALTER USER with RETAIN CURRENT PASSWORD adds a new password while the old one still works. Step 2: update and restart application servers one by one. Step 3: DISCARD OLD PASSWORD removes the old one.1. Add new passwordRETAIN CURRENT PASSWORDold ✔ new ✔2. Roll outupdate app config, restart serversold ✔ new ✔3. Remove oldDISCARD OLD PASSWORDold ✘ new ✔applications keep working at every step
Figure 1: MySQL's dual passwords let you change an application's password without a moment of failed logins.
step1.sql 
ALTER USER 'stock_app'@'localhost' IDENTIFIED BY 'St0ck#New2026' RETAIN CURRENT PASSWORD;
Output 
Query OK, 0 rows affected
login with OLD password 
SELECT 'old password still works' AS status;

Result: 1 row

status
old password still works
login with NEW password 
SELECT 'new password works' AS status;

Result: 1 row

status
new password works

After every application server uses the new password:

step3.sql 
ALTER USER 'stock_app'@'localhost' DISCARD OLD PASSWORD;
Output 
Query OK, 0 rows affected

5. Password Expiry Policies

expiry.sql 
ALTER USER 'priya'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'stock_app'@'localhost' PASSWORD EXPIRE NEVER;
SELECT user, password_lifetime FROM mysql.user WHERE user IN ('priya', 'stock_app');

Result: 2 rows

userpassword_lifetime
priya90
stock_app0
OptionEffect
PASSWORD EXPIREExpire now: user must set a new password at next login
PASSWORD EXPIRE INTERVAL n DAYExpire every n days
PASSWORD EXPIRE NEVERNever expire (common for service accounts using rotation)
PASSWORD EXPIRE DEFAULTFollow the global default_password_lifetime
PASSWORD HISTORY n / PASSWORD REUSE INTERVAL n DAYPrevent reusing recent passwords

6. From the Command Line

Terminal 
# change your own password interactively
mysqladmin -u priya -p password
# MySQL Shell
mysqlsh priya@localhost --sql -e "ALTER USER USER() IDENTIFIED BY 'New#Pass2026' REPLACE 'Old#Pass2026'"

7. Resetting a Forgotten root Password

If nobody knows the root password, restart MySQL with a one-time init file:

  1. Stop the MySQL service (sudo systemctl stop mysqld or net stop MySQL84).
  2. Create a text file, for example /tmp/reset.sql, containing ALTER USER 'root'@'localhost' IDENTIFIED BY 'N3w#Root2026';
  3. Start the server once with mysqld --init-file=/tmp/reset.sql (as the mysql OS user).
  4. Log in with the new password, then stop the server and start it normally as a service.
  5. Delete the init file.
The older --skip-grant-tables method also works but leaves the server open to anyone while it runs; prefer --init-file.

8. Good Practice

  • Use long, random passwords and store them in a secrets manager.
  • Rotate service passwords with RETAIN CURRENT / DISCARD OLD.
  • Enable the validate_password component to enforce strength.
  • Never put passwords in shell history or scripts committed to Git; use option files with strict permissions (~/.my.cnf, mode 600) or mysql_config_editor.

9. Summary

  • ALTER USER 'u'@'h' IDENTIFIED BY 'new'; changes a password; users can change their own with ALTER USER USER() … REPLACE 'current'.
  • IDENTIFIED BY RANDOM PASSWORD generates a strong password.
  • Dual passwords allow rotation with no downtime.
  • Expiry, history and reuse policies enforce good habits; reset root with --init-file.