Have you ever forgotten your MySQL root password? Chances are, you have at least a dozen passwords for your server, computers, phones, email, social network, and bank account. It is tough enough to remember commonly-used passwords without even thinking about your MySQL password. It’s one of those things that just take place despite the numerous precautions one might take. As a result, you are locked out of your database server. You can’t create new databases and are left with little control over the state of your database server. In such situations knowing how to regain root access to your database server comes in handy. So here’s what you can do to reset the password for the root user in MySQL on both Windows and Linux.
Linux Users:
1. Log into your server via SSH and then become root:
$ su
2. Stop the current MySQL instance (note that this will halt any sites that depend on it, so make sure you do this during the lowest possible traffic time).
3. Start MySQL in safe mode and skip privileges checks:
# mysqld_safe –skip-grant-tables &
4. Next, log into MySQL without a privileges check:
# mysql –user=root mysql
5. Finally, reset the password by updating the user table of the default mysql database:
UPDATE user SET Password=PASSWORD(‘newrootpwd’) WHERE user=’root’;
FLUSH PRIVILEGES;
(Note: replace “newrootpwd” with your actual password, preferably one you can remember)
6. Restart MySQL to get out of safe mode:
# /etc/init.d/mysql restart.
Windows Users:
Log on to your server as the Administrator. Kill the MySQL server if it’s running. To do this you need the Windows Services Manager, so click on the Start Menu, then go to the Control Panel, then to the Administrative Tools, and select Services. Here look for the MySQL server and stop it. If it’s not listed there and MySQL is till running it means that MySQL is not running as a service. In that case you need to load the Task Manager which you should be able to access using the key combination of Ctrl+Alt+Del. Now kill the MySQL process.
With the MySQL process stopped you need to force a change of passwords on MySQL using a combination of the UPDATE and FLUSH options. So launch your favorite text editor and create a new file. Enter the following text into the file replacing “NewMySQLPassword” with your new password:
UPDATE mysql.user SET Password=PASSWORD(”NewMySQLPassword”) WHERE User=’root’;
FLUSH PRIVILEGES;
What the first line does is that it updates the value of the field “Password” in the table mysql.user for the user “root” to “NewMySQLPassword”. The second line flushes the old set of privileges and makes sure your new password is used everywhere. Save this text as C:\mysql_reset.txt.
Next, you need to start your MySQL server passing this file as a configuration parameter. Launch a terminal by going to the Start Menu, then to Run, and then type cmd and hit Enter. Now enter the following command:
C:\mysql\bin\mysqld-nt –init-file=C:\mysql_reset.txt
Once the server is done starting delete the file C:\mysql_reset.txt. Your MySQL root password should be reset now. Now restart your MySQL server again. Go back to the Windows Services Manager again to do that. Your new MySQL root password should work for you now.
