MySQL Windows Password Reset

If you receive a 1045 error, you may need to reset your password.  Here are the official instructions for resetting the password: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html

If you find these instructions confusing, here’s another method.  

1.  Find the my.ini file (my.cnf for linux).  On windows, the my.ini file is typically in your install directory: c:\Program Files\MySQL\MySQL Server 5.1\my.ini   Open the file with notepad (or any text editor) and add the following entry under the [mysqld] section:

skip-grant-tables

Then, save the file and restart the MySQL service.  This has disabled the grant tables so you should be able to log into mysql without any password ( no more 1045 errors ).  Now, we need to reset the password.  

2.  Log into MySQL with the MySQL command-line utility.  This can be found in the MySQL bin directory.  No user or password parameters are required since grant tables have been disabled.  Execute the following statement:

UPDATE mysql.user SET PASSWORD = PASSWORD(”) WHERE user = ‘root’ and host=’localhost’;

This example will replace the root password with an empty string or no password.  Feel free to substitute the appropriate password in the PASSWORD(”) function or leave it blank and change the password later.

3.  Remove “skip-grant-tables’ from the my.ini file and restart the MySQL service.

4.  Log into mysql.  Example: shell>mysql -u root

I hope this helps.

MySQL Windows Install Troubleshooting

If you’re having trouble installing MySQL 5.1 on a Windows server, I’d recommend the following process.  Don’t skip the first steps that describe the uninstall and removal of the previous install as this seems to clear up most issues.   For the uninstall, I’d recommend manually deleting data files.  Caution: deleting the data files will delete any data previously entered into MySQL.

1.  Remove previous MySQL installs (and remnants)

a.  Stop the MySQL service ( Start | Control Panel | Administrative Tools | Services)

b.  Remove MySQL using Windows Add or Remove Programs (Start | Control Panel | Add or Remove Programs)

c.  Clean up (delete) the data directory  (assuming that you don’t have data that you need) c:\documents and settings\all users\application data\mysql
 

 

d.  Clean up (delete) any remaining directories under program files c:\program files\MySQL\MySQL Server 5.1 (or whatever version you’re using)

 

————–

That’s it for the removal and clean up.  Now, we’ll begin the install.

 

2.  The MySQL install is a two step process: running the installer (.MSI) and then running the MySQL configuration utility (MySQLInstanceConfig.exe).  Theoretically, the first step should install the data files and program files and the second step should set up the service, set the root password and start the service.  The installer is designed to call the configuration utility automatically.   Instead, I recommend running each process separately.   

 

a.  Launch the installer from a command-line.  Here’s an example shell>msiexec /log c:\install.txt /i mysql-5.1.34-win32.msi 

 

Note: You’ll need to change the name of the .msi file to match the name of the version that you’re installing.  If you’re not used to working at the command-line, you’ll either need to change the directory to the location of the mysql installation file or specify the full path.  For example, you may need to execute:  msiexec /log c:\install.txt /i “c:\Documents and Settings\Administrator\Desktop\mysql-5.1.34-win32.msi”

 

Continue through the wizard until you reach the final screen which says, “Wizard Completed”  There’s a checkbox to “Configure the MySQL Server now”.  Uncheck the box and select Finish.

 

 

Uncheck the Configure box.

 When the install has finished, please check c:\documents and settings\all users\application data\mysql\mysql server 5.1\data .  You should see a mysql and test directory.  

 

Also, verify that files have been installed in c:\program files\MySQL\MySQL Server 5.1 

You should have a bin, Docs and share folder.  

 

b.  Navigate to c:\program files\MySQL\MySQL Server 5.1\bin and launch the MySQLInstanceConfig.exe from a command-line.  Here’s an example.

shell>MySQLInstanceConfig.exe “-lC:\mysql_install_log.txt” “

 

I suggest launching it from a command-line so that you can pass the -l parameter, which will create an install log in case you need to troubleshoot the problem.

 

Step through the wizard.  Make sure to remember the password that you assign in the wizard.

 

The install should complete successfully.

 

TROUBLESHOOTING

What if it didn’t work?

 

1.  Check the service from the control panel.  Is it running?  If so, try to log into MySQL.  I’ve seen instances where an error was received, but it completed successfully.  If it’s not running, try to start it and please post the complete error message.

 

2.  Check the error log.  It’s in C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data   It has an .err extension and you should be able to open it with notepad.  Ignore any errors about the innodb plugin not loading.  It’s a symptom, not the problem.  Look for an error about missing data files or a mismatch in file sizes.

 

3.  1045 error?  It should indicate Password: No or Password:yes.  If “yes”, it’s not a port issue, not a firewall issue — it’s a password issue.  It’s usually caused by not deleting out the data files from an old install.  The password is kept in the data directory and it’s likely that the password you entered during the previous install doesn’t match what you’re entering now.  You can either re-install after deleting all the files or try to reset your root password.  (resetting root password: http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html )

 

4.  Still stuck?   In the instructions above, I specified c:\install.txt and c:\mysql_install_log.txt  Please check those and post the contents.