Reset the MySQL root password on CentOS
Reset the MySQL root password by starting the server without its grant tables, then restart it normally.
The root password of a MySQL server can be reset by starting the server with its grant tables skipped, setting a new password, and restarting the server normally. The steps below use systemd, as on CentOS.
Find the temporary password
If MySQL was installed recently, the temporary root password is written to the error log:
grep 'temporary password' /var/log/mysqld.log
Reset the password
-
Stop the server:
systemctl stop mysqld -
Set the environment option that skips the grant tables:
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" -
Start the server with this option:
systemctl start mysqld -
Connect as root, without a password:
mysql -u root -
Change the password of the root user:
UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') WHERE User = 'root' AND Host = 'localhost'; FLUSH PRIVILEGES; QUIT; -
Stop the server:
systemctl stop mysqld -
Remove the environment option, so that the server starts normally:
systemctl unset-environment MYSQLD_OPTS -
Start the server:
systemctl start mysqld -
Check the new password:
mysql -u root -p
Notes
- For MySQL 5.7.6 and later, the original author recommends
ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASSWORD>';in step 5 instead of the UPDATE statement, which otherwise produces a warning. - While the option is set, the server does not check passwords or privileges, so anyone who can connect to it has full access. Run the procedure only on a server you control, and remove the option right after.
- Reference: Spiceworks, reset MySQL root password on CentOS.