Reset MySQL 8.0 User Password Efficiently with the --init-file Option

When managing a MySQL 8.0 instance, resetting a user password can be streamlined by leveraging the --init-file server startup option, which executes a specified SQL script automatically during startup.

What is the --init-file option?

The --init-file flag tells MySQL to run all SQL statements found in a given file as soon as the server starts. This file can contain any valid SQL, such as creating users, changing configurations, or, as demonstrated here, resetting a password.

Steps

1. Create the init file

First, write a file that contains the password‑reset statement. For example, to change the password for the root user:

cat /opt/mysql/tmp/init
ALTER USER 'root'@'::1' IDENTIFIED BY ')xK0f_Mg076>b__s)4,Z8P6U&x<,3,2E';

The file is saved at /opt/mysql/tmp/init and contains a single ALTER USER command that sets a strong, complex password.

2. Restart MySQL with the init file

Launch the MySQL server, pointing it to the init file so the statement runs automatically: mysqld --init-file=/opt/mysql/tmp/init Upon startup, MySQL reads the file and executes the ALTER USER command, completing the password reset without entering the interactive client.

3. Delete the init file

After the server has started and the password is changed, remove the init file to eliminate any security risk: rm -f /opt/mysql/tmp/init This step ensures that sensitive credentials are not left on disk where other users could read them.

Method Advantages

Simple and efficient : No need to log into the MySQL prompt; the entire operation runs from the command line, making it ideal for automated workflows.

Minimal downtime : Compared to entering safe mode, using --init-file reduces service interruption, keeping the database available for most of the reset process.

Automation‑friendly : The approach fits naturally into DevOps or CI/CD pipelines, allowing password resets to be scripted and executed without manual intervention.

Precautions

File permissions : Ensure the init file is readable only by privileged users to prevent unauthorized access to the password.

Security : Because --init-file runs arbitrary SQL at startup, verify the file’s contents carefully and avoid using it in untrusted environments.

Not for frequent use : While convenient, this method should not replace a comprehensive password‑management strategy; use it sparingly for emergency or scripted resets.

Conclusion

Using MySQL 8.0’s --init-file option to reset a user password provides a fast, low‑downtime solution that works well in automated contexts. Properly securing the init file and limiting the technique’s frequency are essential to maintain database stability and security.