How to Reset MySQL Root Password and Manage User Grants Safely

Scenario 1: Reset root password

MySQL stores passwords using the password() function, which is costly to reverse; the following generic method uses MySQL's special startup mode that skips the grant tables.

Principle: Start MySQL with --skip-grant-tables so authentication is bypassed; then modify the user table to set a new password and reload privileges.

Warning: The server is insecure while running in this mode; execute at an appropriate time.

Stop the running MySQL service: service mysqld stop Start MySQL with --skip-grant-tables (two ways):

Method 1 – one‑time startup:

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

Method 2 – edit my.cnf to add skip-grant-tables (affects all future starts). Locate the active my.cnf with:

sudo mysql --help | grep my.cnf
mysql --help | grep 'Default options' -A 1

Login without a password:

mysql -uroot
use mysql;
select version();
select user,host,authentication_string,password from user where user='root';

Set the new password (adjust for version):

# For MySQL 5.7+ (authentication_string)
update user set authentication_string=password('new_password') where user='root';
# For MySQL 5.7- (password column)
update mysql.user set password=password('new_password') where user='root';
# Optionally set both fields
update user set authentication_string=password('new_password'), password=password('new_password') where user='root';
flush privileges;
quit;
service mysqld restart

Scenario 2: Add account and grant privileges

When MySQL runs with --skip-grant-tables, privileges cannot be granted, so restart MySQL normally before adding accounts.

Login with password and grant access to a specific IP:

mysql -u root -p
use mysql;
grant all privileges on *.* to 'root'@'ip' identified by 'password' with grant option;
flush privileges;
quit;

Scenario 3: Change login password

Four common methods to change a MySQL account password:

Update the user table directly (different columns for versions).

Use SET PASSWORD command (auto‑flushes).

Use ALTER USER command (auto‑flushes).

Use GRANT statement (creates user and sets password).

Use mysqladmin without logging into MySQL (password sent in plain text).