Reset the root password on MySQL

Sponsored Link
MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). First released in January, 1998, MySQL is now one component of parent company MySQL AB's product line of database servers and development tools.

Yes, it really is this the following command to change the mysql root password

sudo /etc/init.d/mysql reset-password
New MySQL root password:
Setting new MySQL root password

But how? Debian (and by inheritance, Ubuntu) have a special user called ‘debian-sys-maint' on each mysql install that has admin privileges on the server. It's used to shutdown the server gracefully, automatically check for corrupt tables, and resetting your password. Its password is randomly generated, and stored in /etc/mysql/debian.cnf. Handy if you want to do some admin scripts yourself.

You may also like...

13 Responses

  1. I can reset my password. ‘reset-password’ command is not on my debian-etch. it says me:
    Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status

  2. buck says:

    Same here… did you ever find an answer to why the ‘reset-password’ does not work?

  3. admin says:

    try the following procedure

    Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

    mysqld_safe --skip-grant-tables

    You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

    mysql --user=root mysql

    update user set Password=PASSWORD(‘new-password’);
    flush privileges;

    Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.

  4. mdmbkr says:

    The above advice will change ALL of the mysql users’ passwords! You only want to change the password for the user named ‘root’, so add that constraint:

    update user set Password=PASSWORD(’new-password’) WHERE User=’root’;

  5. cspr says:

    @mdmbkr lol!;) just tell me how to run that query without knowing your password to connect to the server!

  6. uzair says:

    @Admin uhhh, that little command gave me the following error when I started it the server up again:
    uzair@ubuntu:~$ sudo /etc/init.d/mysql restart
    * Stopping MySQL database server mysqld [ OK ]
    * Starting MySQL database server mysqld [ OK ]
    * Checking for corrupt, not cleanly closed and upgrade needing tables.
    /usr/bin/mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)’
    uzair@ubuntu:~$ ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)

    Is there anyway to reverse the effects if mdmbkr is correct about it changing all the passwords.
    What’s the default debian-sys-maint account’s password?

  7. uzair says:

    Please ignore this. Forgot to check the little checkbox to notify me of any replies 🙂

  8. uzair says:

    nevermind, a bit of google-ing solved the issue.
    For those of you also running into the same problem here’s what you can do:

    Your debian-sys-maint user’s info is located in:

    copy the password that is there and do the same procedure as above, but this time make sure you change the password only for ‘debian-sys-maint’ using the command mdmbkr suggested:
    mysqld_safe –skip-grant-tables
    mysql –user=root mysql
    update user set Password=PASSWORD(”) WHERE User=’debian-sys-maint’;

  9. piar says:

    i have change password in mysql but unfortunately now i am unable to log in in mysql.please help me as soon as possible.

  10. gauraw says:

    You can reset the password as follows (At least it worked for me):
    sudo /etc/init.d/mysql stop
    sudo mysqld –skip-grant-tables &
    mysql -u root mysql

  11. cloud says:

    thanks! this solution works!

  12. Anvesh says:

    Nice Article !
    I have also worked around this and prepared my own step, please visit my blog:

  13. pooyan says:

    thanks! this solution works!

Leave a Reply

Your email address will not be published. Required fields are marked *