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 easy.run the following command to change the mysql root password

sudo /etc/init.d/mysql reset-password
New MySQL root password:
Verify:
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.

Incoming search terms:

Related posts

11 thoughts on “Reset the root password on MySQL

  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

    [Reply]

  2. 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;
    exit;

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

    [Reply]

  3. 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';

    [Reply]

  4. @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?

    [Reply]

  5. 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:
    /etc/mysql/debian.cnf

    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';
    FLUSH PRIVILEGES;

    [Reply]

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

    [Reply]

  7. 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
    UPDATE user SET Password=PASSWORD(‘NEWPASSWORD’) WHERE User=’root'; FLUSH PRIVILEGES; exit;

    [Reply]

Leave a comment

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