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'; FLUSH PRIVILEGES;
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.