Skip to content
Tags

Reset mysql root password

December 3, 2010

So today I was setting up the rails environment on my friends computer and he did not know his mysql password. Reetting it was quite interesting.

Note that you should use ‘sudo’ in the commands in this post whenever required.

The official page to go to would be the MySQL page here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

I tried following the instructions for the mac but they did not work.  I decided to follow the general instructions.  The general instructions are on the last part of the mysql page. there are also summarized here:

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

As you can see the first thing to do is,

1. Stop MySQL server process. For this I had to locate the .pid file that the mysql page talks about.

Locate the .pid file that contains the server’s process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system’s host name.

I located it as /usr/local/mysql/data/Samara.local.pid

Then, do

shell>sudo cat /usr/local/mysql/data/Samara.local.pid

which will give you the process id for the sever proces

then do a

shell> sudo kill pid

with the right pid

this will stop the server.

Here are some pastes from the terminal

shell> sudo cat  /usr/local/mysql/data/Samara.local.pid

98261

shell> kill 98261

-bash: kill: (98261) - Operation not permitted

shell> sudo kill 98261

shell> mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Ok. so we have stopped the mysql server.

The next step is:

2. Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

This was not working. Here:

shell> mysqld start --skip-grant-tables

101202 19:25:12 [Warning] Can't create test file /usr/local/mysql-5.1.49-osx10.6-x86_64/data/Samara.lower-test

101202 19:25:12 [Warning] Can't create test file /usr/local/mysql-5.1.49-osx10.6-x86_64/data/Samara.lower-testmysqld: Can't change dir to '/usr/local/mysql-5.1.49-osx10.6-x86_64/data/' (Errcode: 13)

101202 19:25:12 [ERROR] Aborting

101202 19:25:12 [Note] mysqld: Shutdown complete

Neither did sudo work:

shell> sudo mysqld start --skip-grant-tables

Password:

101202 19:26:03 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.1.49-osx10.6-x86_64/data/ is case insensitive

101202 19:26:03 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
101202 19:26:03 [ERROR] Aborting
101202 19:26:03 [Note] mysqld: Shutdown complete

Finally what worked is this:

shell> sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables

Password:

Starting MySQL. SUCCESS!

(note just a mysql.server start –skip-grant-tables) was also not working. I got the tip from

http://www.linuxquestions.org/questions/linux-server-73/mysql-failed-to-start-database-670978/

3. Then I gave shell command mysql

shell> mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1Server version: 5.1.49 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates.All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Then i did

mysql> UPDATE mysql.user SET Password=PASSWORD('mysql')

-> WHERE User='root';

Query OK, 3 rows affected (0.00 sec)Rows matched: 3  Changed: 3  Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

Bye

Note, if while updating the password you get the error: ERROR 1142 (42000): UPDATE command denied to user ”@’localhost’ for table ‘user’

login with the explicit user as root. ie use the command mysql -u root

Then

shell> sudo /usr/local/mysql/support-files/mysql.server stop

Shutting down MySQL. SUCCESS!

shell> sudo /usr/local/mysql/support-files/mysql.server start

Starting MySQL. SUCCESS!

shell> mysql -u root -p

Enter password: #put in 'mysql' here as the new password

Welcome to the MySQL monitor.  Commands end with ; or \g.

DONE!!! password changed

Advertisements

From → Uncategorized

Leave a Comment

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: