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 mysql |
Step 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…