How to reset the MySQL root password

Wednesday, October 21, 2009 posted by Till

The following steps describe the procedure to reset the mysql root password on Linux.

1) Stop the mysql server

/etc/init.d/mysql stop

2) Start the mysql server manually without permission tables which allows us to login as root user without password:

mysqld_safe --skip-grant-tables &

3) Login into mysql as root user without a password and switch to the "mysql" database:

mysql -u root mysql

Then execute this SQL query to set a new password for the mysql root user:

update user set Password=PASSWORD('mynewpassword') WHERE User='root';

(Replace "mynewpassword" with the new root password in the above command).

Then logout from the mysql prompt by typing:

exit

4) Now bring back the running mysql instance into the foreground by typing:

fg

and then press [ctrl] + c to kill the mysql process.

5) Start the mysql server again:

/etc/init.d/mysql start

Be Sociable, Share!



5 Responses to “How to reset the MySQL root password”

  1. Farid S says:

    Thanks,

    Do we need to update “/usr/local/ispconfig/server/lib/mysql_clientdb.conf” too ?

  2. Till says:

    Yes, if you use ISPConfig 3.

  3. Kristian Feldsam says:

    Hello, easier is use debian sys user login located here /etc/mysql/debian.cnf

  4. Pedro Martins says:

    It did not worked for me.

    I had to use:
    mysqladmin -u root -p’oldpassword’ password ‘newpass’

    And changed the newpass in the file /usr/local/ispconfig/server/lib/mysql_clientdb.conf

    But I still am getting the same error at the monitor:
    Unable to connect to the database: Access denied for user ‘root’@’localhost’ (using password: YES)

    • Pedro Martins says:

      It updated now, I removed the error:
      Unable to connect to the database: Access denied for user ‘root’@’localhost’ (using password: YES) from the log

      And it updated successfully!

Leave a Reply