Author: Stephen VanDyke
Date: 18-10-00 19:48
I had to do this a while ago and thougt it would make a nice howto on the site :)
Here's a complete howto on what to do if the root password is ever lost/changed and it has to be changed back.
You must be either the Unix root user or the same user the server runs as to do this.
Take down the mysqld server by sending a kill (not kill -9) to the mysqld server.
The pid is stored in a .pid file which is normally in the MySQL database directory:
kill `cat /mysql-data-directory/hostname.pid`
Restart mysqld with the --skip-grant-tables option
Connect to the mysqld server with mysql -u root
MySQL should drop you straight to the sql command line without prompting for a password
connect to the mysql database with "connect mysql"
Update priveledges on the user you need to regain access to, you may need
to perform a "select * from user" to confirm what accounts they have set-up
"UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Flush the priveledges cache that was NOT initially loaded
Log out of MySQL, and attempt to relog in.
mysql -u root -p password newpassword
10. If everything works, kill the current running mysql daemon and restart as you would normally.