Q. How to reset MySQL root password in Linux? How do I recover MySQL root password? I forgot MySQL root password, steps to change MySQL root password? How to reset MySQL root password using the command line. How to reset forgotten MySQL root password on Linux? How to reset MySQL 5.7 root password?
Advertisement
MySQL is an open source database software widely used for data storage. Sometimes we forgot MySQL root password. So don’t be panic, This tutorial will help you to reset MySQL root password with simple steps.
Reset MySQL Root Password
- Start MySQL in Safemode – First of all, you are required to stop running MySQL server. Use one of the following commands to stop MySQL server on your Linux system.
systemctl stop mysql.service
- Start MySQL in safe mode – Now start MySQL server in safe mode using withe the
--skip-grant-tables
option. Use the following command to start MySQL in safe mode. In safe mode, MySQL does not prompt for login password.mysqld_safe --skip-grant-tables &
- Reset MySQL root Password – Next, login to MySQL server as root user and change password using the following set of commands. This will reset MySQL root password on your system.
For MySQL 5.6 or Below
mysql -u root
mysql> USE mysql;mysql> UPDATE user SET password=PASSWORD("NEW-PASSWORD") WHERE User='root';mysql> FLUSH PRIVILEGES;mysql> quitFor MySQL 5.7 or Above
mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD("NEW-PASSWORD");mysql> FLUSH PRIVILEGES;mysql> quit - Restart MySQL Service – Once you change the password. Terminate the current mysqld process, and then start it again as regular service.
sudo pkill mysqld && sudo pkill mysqld_safe
systemctl start mysql.service
Verify New Password
After resetting MySQL root account password and restarting, just verify new password by login.
mysql -u root -p
Enter password:********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1308 Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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>
2 Comments
Hi, I am getting below error while resetting mysql root password.
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
Please help
Thanks dear. It helps me a lot to quickly reset mysql 5.7 password.