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.

<code>service mysqld stop</code>

Start the service with

--skip-grant-tables

(two ways):

<code>./bin/mysqld_safe --skip-grant-tables --user=root &amp;</code>

or, if

mysqld_safe

is unavailable, run

mysqld

with the same effect.

<code># 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
</code>

<code># 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</code>

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.

<code># 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;</code>

Scenario 3: Change login password

Method 1 – Update the

mysql.user

table directly (requires a MySQL session and a privilege flush):

<code># 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;</code>

Method 2 – Use

SET PASSWORD

(automatically flushes privileges):

<code>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');</code>

Method 3 – Use

ALTER USER

(automatically flushes privileges):

<code>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';</code>

Method 4 – Use

GRANT

(creates the user if it does not exist and automatically flushes privileges):

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

Method 5 – Use

mysqladmin

without logging into MySQL (automatically flushes privileges, but transmits the password in plain text):

<code>mysqladmin -uroot -pOldPassword password NewPassword</code>

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