Popular Posts

Thursday, December 29, 2011

How to reset MySQL root password Option 1

How to reset MySQL root password. Did you know it is very easy to reset MySQL root password or any password for that matter.

Option 1.  Use MySQL --init-file parameter MySQL parameter

Why would someone what to do it - for very obvious reasons, someone may forget the password, dba on vacation and hundreds of other possibilities.This blog post will walk you though resetting/changing MySQL root or any other password.

In MySQL realm once you have read and execute permissions to use MySQL executables particularly mysqld and mysqld_safe you already have the capability to reset any password. So no matter how intricate are your MySQL password anyone with execute privileges can reset the passwords.

Here is how to do it:

Create reset password file. This file will be executed on MySQL daemon start up and will update passwords for the desired user in our case we are resetting MySQL root password:

UPDATE mysql.user SET Password=PASSWORD('WooHoo') WHERE User='root';
As you can see from the above we created a text file that simply updates USER table IN 
MYSQL database by setting the password to 'WooHoo'. You can name the file whatever you would like.
Shut down MySQL daemon:
Execute /etc/init.d/mysql stop 
Start MySQL daemon and use your reset password file you create above to reset the root password:
/usr/bin/mysqld_safe --init-file /root/changepwd.txt 
The key here is the MySQL --init-file parameter that instructs MySQL to execute/apply any commands
 that are in the --init-file. You must provide full path to the --init-file. 
There is also option 2 on how to reset MySQL password and I'll post in my next post. 

No comments: