Resetting the Root Password

I know it happens, because I've done it myself: You go to log in to MySQL as the root user and access is denied. You cannot remember the correct password or get a new password to work. Effectively you've locked yourself out of MySQL and all hope is lost. Luckily, there is an easy enough work-around when (or if) this does occur: You need to reset the root password.

Resetting an access password involves starting MySQL without the user/privilege system in use. Then anyone can access the database, make the alterations, and restart MySQL.

To reset the root password:

1.	Use the MySQL Administrator to disable the grant tables (Figure A.13). Figure A.13. Disabling the grant tables when starting MySQL allows you to circumvent the user/privilege system. This option tells MySQL to run without concern for user and access privileges. While this is an obvious security issue, it's the only way to reset the root password. This, of course, does assume that the MySQL Administrator has remembered your administrative password (although it's unrecoverable by you). See Chapter 13, "MySQL Administration," for more information on the MySQL Administrator, in case you're not familiar with it. Checking the box here will write the value to the proper option file. The new valuedisabling the grant tableswill take effect the next time MySQL is started. If you cannot use the MySQL Administrator to set this option, then you must start MySQL from the command line (in Step 3), using the --skip-grant-tables argument. See Chapter 2 for ways to start MySQL manually. Alternatively, you could manually edit a MySQL option file (see Chapter 13) to accomplish the same effect.
2.	Stop the MySQL server. The next step is to stop MySQL so that you can restart it without the grant tables. To do so: On Windows, where MySQL is running as a service, go into the Administrative Tools > Services panel to stop MySQL. You can see the specific steps in Chapter 2, if you need. On Mac OS X and Unix, issue the following command in the Terminal: sudo kill `cat /path/to/mysql/ data/hostname.pid` On these operating systems you'll likely need to use this strong-arm technique to stop a running MySQL server. You'll need to find the pid file on your computer and use that path and name here. It should be in your MySQL data directory. Note that those are backticks around the cat code, not single quotation marks.
3.	Start MySQL. If you were able to use the MySQL Administrator or an option file to disable the grant tables, you can start MySQL as you normally would. If not, then you must start MySQL with the --skip-grant-tables argument.
4.	Update the root password using mysqladmin. mysqladmin -u root password 'newpassword' This step is exactly the same as the one used to first establish a root password (see Chapter 2). No password is required to make this change, since the grant tables were skipped. You could also log in to the mysql client (without using a password to get in) and then run a SET PASSWORD command.
5.	Undo the effects of Step 1. Now that you've reset the password, it's important that you re-enabled the grant tables. If you used the MySQL Administrator in Step 1, you should uncheck that box (see Figure A.13). If you manually edited an option file, you should re-edit it. If you didn't do any of this but instead used the --skip-grant-tables flag when starting MySQL, you do not need to do anything here.
6.	Stop and restart MySQL. Since mysqld is currently running without verifying access, you'll need to stop and restart the server. Use the techniques taught in Chapter 2 to stop and restart it. You will not need to use a password to stop MySQL, as it doesn't currently require them.