MySQL – How to Recover When MySQL Loses Its Root Password

MySQL

I am having a strange problem, my mysql loses/resets the root password automatically. By which I mean that it resets it to none. It has happened twice this month. I am worried that it can be a security issue as data is open waiting only for someone trying no password!

Here is the version:

mysql --version

mysql  Ver 14.14 Distrib 5.1.56, for redhat-linux-gnu (i386) using readline 5.1

Any help would be appreciated.

Update:

Output of select user, host, password from mysql.user; how it looks after the password got reset

| root    | localhost                                     |

| root    | 127.0.0.1                                    |

|         | localhost          |                                           |
| admin   | localhost          | ################################# | (I have removed the actual output with #)

So all the passwords are blank, except for for another user named 'admin'

Best Answer

This answer is in connection with Ruby on Rails framework where this behavior occurred

  1. have mysql with user root and password
  2. bootstrap new rails project using with mysql "rails new projectname
    -d mysql"
  3. do not modify config/database.yml - defaults credentials for server are name: root, password is blank
  4. rake db:create prompt show up - asking for root password and then when you provide root password

the answer is in GRANT command on line 68 in here

basically it executed GRANT command with credentials matches your current database.yml

Related Topic