Here is a quick Step-by-Step “how to” which helps restoring MySQL root password that was lost/forgotten.
It happens to everybody, especially if several distributed (different) systems are maintained, where the password is not the same. Here is what needs to be done to restore it:
Step 1: Stop MySQL daemon if it is currently running
Depending on the operating system MySQL is installed on, the daemon can be checked/stopped differently. Here is an example on how to do it in Unix-like systems.
[ NOTE ]: You might need to run it as a Unix System superuser (root) - depending on how the system is configured, and what permissions your Unix account is granted)
Here is how to stop/kill the existing mysql daemon, in case it is running:
ps -ef | grep mysql - checks if mysql/mysqld is one of the running processes. pkill mysqld - kills the daemon, if it is running.
Note: if pkill (’process kill’) is not on a particular Unix system, use kill -9 ‘pid’, where ‘pid’ corresponds to processes that were found with ps -ef | grep mysql
Step 2: Run MySQL safe daemon with skipping grant tables
mysqld_safe --skip-grant-tables &
Step 3: Login to MySQL as root with no password
mysql -u root mysqlStep 4: Run UPDATE query to reset the root password
In MySQL command line prompt issue the following two commands:
UPDATE user SET password=PASSWORD("ualue=42") WHERE user="root"; FLUSH PRIVILEGES;
“ualue=42” is a common password for “The Hitchhiker’s Guide to the Galaxy” people which reads “Ultimate Answer to Life, the Universe, and Everything=42“
Step 5: Stop MySQL safe daemon
Follow the first two steps, but this time kill (pkill) “mysqld_safe” instead of “mysqld”
Step 6: Start MySQL daemon
Depending on the operating system (Unix-like examples):
/etc/rc.d/rc.mysql start
OR
/etc/init.d/mysql start
OR
/etc/rc.5/mysql start
etc.. check existing MySQL configuration
Step 7: Root password is reset and ready to use
Password is reset. Privileges are flushed. Start MySQL and login as root with the password set in step 4:
mysql -u root -p mysql
Note: sometimes (most of the time) ‘root user’ privileges are required for the system (OS) in order to stop/start processes
what is next? Reset Lost Password in Sun Application Server…
great guide, thanks so much =)
Thank you! I’ve been futzing with the crap howto on the mysql main site with no luck.. Maybe you can suggest including your guide on mysql’s page?
@QED,
Sure, very welcome! If you believe the guide is useful feel free to suggest mysql site to reference to it. You (people who find this guide helpful) are the only true measure of whether it is indeed useful or not, I can’t really claim it is
/Toly
A simple way for the new root password (if MySql daemon is stoped):
#mysqld_safe –skip-grant-tables mysqladmin -u root flushprivileges password “new_password”
$mysql -u root -p
Enter password:
mysql>
It’s simple.
Thanks for this post. One suggestion: for the Fedora users, Step 6 should be replace by service mysqld start
Thank you very much, it really saved my life
Descriptive, Fabulous and just what I wanted..!
where to write all these commands…in CMD or MYSQL
Dear Toly,
I really would like to solve it but I can’t! in my case there’s no way to stop/kill the existing mysql daemon. Neither with “pkill mysqld” nor with “sudo kill -9 9470″ I succeed, infact when I run:
sudo mysqld_safe –skip-grant-tables
it always says:
“120109 23:15:40 mysqld_safe A mysqld process already exists”
it’s very sad, I know I’m wrong in something but what?
someone can help me?
thans to all
@Lore,
Search for mysql process(es):
which, in case mysql(d) is running, should give you:
where “22211″ is the mysql’s PID (process ID) you’d need to kill ( in your case the number will be different ).
And kill it with “kill -15 22211″ or with “kill -9 22211″ if 15 does not work.
/Toly