How to Reset MySQL Root Password and Manage User Accounts Safely

Scenario 1: Resetting the root password

MySQL stores passwords using the password() algorithm, which is difficult to reverse; the usual workaround is to start MySQL with privilege checks disabled, modify the user table, then restart the service.

Step 1: Stop the running MySQL service service mysqld stop Step 2: Start MySQL with --skip-grant-tables (two options)

Method 1 – one‑time launch:

./bin/mysqld_safe --skip-grant-tables --user=root &

Method 2 – edit my.cnf to add skip-grant-tables (affects service/systemctl starts). Use sudo mysql --help | grep my.cnf or mysql --help | grep 'Default options' to locate the correct file.

Step 3: Log in without a password and modify the password fields

mysql -uroot
use mysql;
select version();
-- View current password columns (varies by version)
select user, host, authentication_string, password from user where user='root';
-- For MySQL 5.7+
update user set authentication_string = password('new_password') where user='root';
-- For MySQL 5.7- (or older)
update user set password = password('new_password') where user='root';
-- If both columns exist, update both
update user set authentication_string = password('new_password'), password = password('new_password') where user='root';
flush privileges;
quit;
service mysqld restart

Warning: the MySQL service runs without authentication during this process, so perform it during a maintenance window.

Scenario 2: Adding a user account and granting privileges

When MySQL runs in normal mode, you can create new accounts and assign rights.

mysql -u root -p
use mysql;
grant all privileges on *.* to "root"@"<ip_address>" identified by "password" with grant option;
flush privileges;
quit;

Scenario 3: Changing a login password

Multiple methods are available, depending on MySQL version and preferred syntax.

Update the user table directly (requires a subsequent flush privileges).

-- Pre‑5.7
update user set password = password('123456') where user='root' and host='localhost';
-- 5.7 and later
update user set authentication_string = password('123456') where user='root' and host='localhost';
flush privileges;

Use SET PASSWORD (auto‑flushes).

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

Use ALTER USER (auto‑flushes).

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

Use GRANT to create/modify a user and set its password.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.11.31' IDENTIFIED BY 'Mysql.pass.123' WITH GRANT OPTION;

Use mysqladmin (does not require logging into MySQL, but transmits the password in plain text).

mysqladmin -uroot -pOldPassword password NewPassword

Consider using an SSL connection to protect the password.

These commands cover the most common ways to reset, create, or modify MySQL user credentials across different versions.