Change and Reset MySQL root Password

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 mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

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
Advertisements

~ by lordhendrix on March 13, 2009.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: