How to Reset MySQL Root Password and Manage User Accounts Safely

Scenario 1: Reset root password

MySQL stores passwords using the password() algorithm, which is costly to reverse; the following is a generic method.

Principle: MySQL can be started with a special mode that skips the privilege tables, allowing login without a password.

After logging in, modify the user table, reset the password, flush privileges, and restart the MySQL service. Warning: The MySQL service will be highly exposed; execute this only at an appropriate time. service mysqld stop Start the service with --skip-grant-tables (two ways):

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

or, if mysqld_safe is unavailable, run mysqld with the same effect.

# Edit my.cnf and add skip-grant-tables
# Find the loaded my.cnf order with:
#   sudo mysql --help | grep my.cnf
#   mysql --help | grep 'Default options' -A 1

# Login as root without a password
mysql -uroot

# Switch to the mysql database where accounts are stored
use mysql;

# Check MySQL version
select version();

# View current account information (fields differ by version)
# For 5.7+ the password field is authentication_string; for earlier versions it is password
select user, host, authentication_string, password from user where user='root';

# Set password for 5.7+ (authentication_string)
update user set authentication_string = password('new_password') where user='root';
--and Host='localhost';

# Set password for pre‑5.7 (password)
update mysql.user set password = password('new_password') where user='root';
--host='localhost';

# For 5.7+ you can set both fields simultaneously
update user set authentication_string = password('new_password'), password = password('new_password') where user='root';
--and Host='localhost';

# Flush privileges to apply changes
flush privileges;

# Exit MySQL
quit;

# Restart MySQL service
service mysqld restart

Scenario 2: Add account and grant privileges

The --skip-grant-tables mode cannot be used to add accounts, so start MySQL normally before creating a new user.

# Login to MySQL after a normal restart
mysql -u root -p

# Switch to the mysql database
use mysql;

# Grant all privileges to a new account from a specific IP
grant all privileges on *.* to "root"@"ip" identified by "password" with grant option;

# Flush privileges
flush privileges;

# Exit
quit;

Scenario 3: Change login password

Method 1 – Update the mysql.user table directly (requires a MySQL session and a privilege flush):

# For MySQL 5.7 and earlier
update user set password = password('123456') where user='root' and host='localhost';

# For MySQL 5.7 and later
update user set authentication_string = password('123456') where user='root' and host='localhost';

flush privileges;

Method 2 – Use SET PASSWORD (automatically flushes privileges):

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

Method 3 – Use ALTER USER (automatically flushes privileges):

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

Method 4 – Use GRANT (creates the user if it does not exist and automatically flushes privileges):

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

Method 5 – Use mysqladmin without logging into MySQL (automatically flushes privileges, but transmits the password in plain text):

mysqladmin -uroot -pOldPassword password NewPassword

Note: Transmitting passwords in plain text is insecure; consider using SSL connections.