I have forgot the password of MySQL’s admin user – how to recover?

Uncategorized , , , 0 Comments

If you have accidentaly locked out yourself, or just forgot your password for the admin (root) user of the MySQL, here is a quick-and-dirty solution.

First you have to shut down the MySQL server, next you need to start it in safe mode with no priviliges table loaded, update the password to a new one, and restart the database server just as before.

Step by step

  1. Stop the database server:
    $ sudo /etc/init.d/mysql stop
    Stopping MySQL database server: mysqld.
  2. Start in safe mode in the background (remember the PID! (29789 in my case as you can see)):
    $ sudo mysqld_safe --skip-grant-tables &
    [1] 29752
    Starting mysqld daemon with databases from /var/lib/mysql
    mysqld_safe[29789]: started
  3. Now you can log in with the root user without password.
    $ mysql -u root
    Welcome to the MySQL monitor. Commands end with ; or g.
    Your MySQL connection id is 39
    Server version: 5.0.51a-6 (Debian)

    Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

  4. Set a new password, flush the privileges, and quit:
    mysql> UPDATE mysql.user SET password=PASSWORD('your-new-password') WHERE User='root';
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 3 Changed: 3 Warnings: 0

    Query OK, 0 rows affected (0.00 sec)

    mysql> QUIT

  5. Now shut down the mysqld_safe what you have used (kill its PID):
    $ sudo kill 29752
    STOPPING server from pid file /var/run/mysqld/mysqld.pid
    mysqld_safe[29869]: ended
    [1]+ Done sudo mysqld_safe --skip-grant-tables
  6. And you can start your mysql server normally again:
    $ sudo /etc/init.d/mysql start
    Starting MySQL database server: mysqld.

Original source is the Locked Out of MySQL topic on CentOS.org.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.