Sometimes, you may need to reset MySQL root password without existing root password on your web hosting server. You can reset the password by following the below steps:
[1] Stop the MySQL service from the server.
[2] Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.
[3] Connect to mysql server as the root user. You can connect with GUI tool or from SSH or command line.
[4] Update MySQL root password using the following query:
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
Flush the privileges using the following query:
FLUSH PRIVILEGES;
[5] Exit and restart the MySQL service.
For Linux servers, following are the complete steps with commands:
[1] Stop MySQL service:
# /etc/init.d/mysql stop
[2] Start MySQL with –skip-grant-tables:
# mysqld_safe --skip-grant-tables
[3] Connect to MySQL server:
mysql -u root
[4] Set new MySQL root user password:
mysql> use mysql; mysql> update user set password=PASSWORD("MyNewPass") where User='root'; mysql> flush privileges; mysql> quit
[5] Stop and start MySQL service:
# /etc/init.d/mysql stop # /etc/init.d/mysql start
That’s it. Now, you should be able to access MySQL server using the root password specified in steps 4.