Resetting the root password of a MySQL database is easy if you know the current password. If you don’t, then it’s a little harder.
If you already have access to the root account, then you can change the password by
name@name:~$ mysql –user=root –pass mysql
Enter password:
mysql> update user set Password=PASSWORD(‘new-password-here’) WHERE User=’root’;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
However, if you don’t have access to the root account because you don’t know the password, then you will need to start MySQL with a flag to tell it to ignore any username/password restrictions. You will then be able to change your password. Follow the steps below in order to reset the root password:
1. Login to MySQL server as root to the Windows or Unix-like machine.
2. Ensure that the MySQL server is stopped by using:
root@name:~# /etc/rc.d/init.d/mysql stop
or
root@name:~# /etc/rc.d/init.d/mysqlid stop
Check for running MySQL processes with this command:
ps waux
If any MySQL processes are still running, then stop the processes with the kill command
1. Once you have ensured that all processes are killed, open the mysql server startup script in order to start up the database. Start the server by adding –skip-grant-tables to the end of the line that contains the mysqld_safe command as its parameter. The server is started.
/usr/local/mysql/bin/safe_mysqlid -–skip-grant-tables
1. Once the server is running, you can connect to it to start it without a password. You will need to open a new terminal window.
/usr/local/mysql/bin/mysql myqsl
1. Run the update queries to change the MySQL password:
root@name:~$ mysql –user=root mysql
Enter password:
mysql> update user set Password=PASSWORD(‘new-password-here’) WHERE User=’root’;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
Note: You will need to replace newpassword with the new root password for MySQL server. Flush Privileges is needed to making the password change effect immediately.
1. Stop MySQL server.
/etc/rc.d/init.d/mysql stop
1. You can now start the server
root@name:~# /etc/init.d/mysql start
1. Once open, remove the –skip-grant-tables parameter that you had added.
2. Everything is now complete. You can now start the database with your new password to verify that it connects.
If resetting your MySQL root password on Windows then you will need to:
1. Log on to your system as Administrator.
2. Go to Services to stop the MySQL server if it is running
Start Menu -> Control Panel -> Administrative Tools -> Services
Alternatively you may use the Task Manager to force it to stop.
3. Create a text file and place the following command within it on a single line:
SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(‘MyNewPassword’);
4. Open a console window to get to the DOS command prompt:
Start Menu -> Run -> cmd
5. Execute the following command. Make sure the path is where you installed MySQL.
At the DOS command prompt, execute this command:
C:> C:mysqlinmysqld-nt –init-file=C:mysql-NAMEOFTEXTFILE.txt
After the server has started successfully, delete the text file.
6. Stop the MySQL server, then restart it in normal mode again or by using whatever command you normally use.
7. You can now connect using the new password.
For more information on resetting your mysql password, read
How do I Reset a MySQL Password
