Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: set mysql root password to password=""

    hi
    i wanted to to change my sql root password from linux cli. so i enter these command in command line:
    Code:
    mysql>use mysql;
    mysql>update user set password=PASSWORD("") where User='root';
    as you see i forgot to set the new password in update statement, so i think root password became null. & now i cant login into mysql! i tried space, "", nothing, but neither of them worked out.
    i know one solution is reset mysql password, but it seems weird that i cant log in.
    anyone have any idea?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Are you running mysql under Windows or UNIX? You will need to perform some system admin type operations to reset this password. If you can let me know the environment in which your database is running and whether you have permissions to start and stop the database?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    May 2004
    Posts
    144
    it Linux OS, & yeah, i have permision for restarting database

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi, you will need to do the following.

    As the Linux prompt type the following command:

    $ ps -ef | grep mysqld

    The first column should return the user under which the mysql server is running. You may get two entries, one executed by root calling the mysqld_safe and the other is the actual server.

    Keep a note of the number value in the second column as this is the process identifier or pid. We will need this to stop your mysql server.

    Login in to Linux using the user found above.

    Execute the following command to stop the mysql server

    $ kill pid

    where the pid is the process id found above.

    Now we need to create a file with the following contents:

    UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
    FLUSH PRIVILEGES;

    Make sure you know the full pathname to this file as we will need this in the next step.

    From the Linux shell prompt execute the following:

    mysqld_safe --init-file=<full pathname to file above> &

    That should be it to restore your password. Be sure to remove the file that has the new root password.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •