How to reset MySQL root password
Forgetting passwords happens to the best of us. If you forget or lose the root password to your MySQL database, you can still gain access and reset the password if you have access to the server.
Step 1 — Stop MySQL service
First of all, you need to stop the MySQL service on your system. You can do this using the following command.
- For
SysVinit
systems# service mysql stop
or# /etc/init.d/mysql stop
- For S
ystemd
systems# systemctl stop mysql.service
Step 2 — Start MySQL in Safe Mode
Now connect to MySQL in safe more using skip grant and run this command in background.
# mysqld_safe --skip-grant-tables &
Step 3 — Login to MySQL and Change Password
After starting MySQL in safe more connect to MySQL with root user and without any password. After that run following command to change the root user password.
# mysql -u root mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> UPDATE user SET authentication_string = PASSWORD("NeW_pAsSw0rD") where User='root'; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> FLUSH PRIVILEGES; mysql> quit;
Step 4 — Stop and Restart MySQL
After successfully changing the 'root' password, stop and restart the service in normal mode using following commands:
- For
SysVinit
systems:# service mysql stop # service mysql start
or# /etc/init.d/mysql stop # /etc/init.d/mysql start
- For
Systemd
systems# systemctl stop mysql.service # systemctl start mysql.service
Step 5 — Login with New password
At this stage you have successfully updated you root MySQL password, Lets connect to MySQL using new password.
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.20-0ubuntu0.17.10.1 (Ubuntu) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
You have restored administrative access to your MySQL server. Make sure the new password you chose is strong and secure and keep it in a safe place.
For more information on user management, authentication mechanisms, or ways of resetting database password for other version of MySQL, please refer to the official MySQL documentation.