How to change forgotten/lost Mysql root user password?


Question: How to change forgotten/lost Mysql root user password?


Below instructions apply to both Windows and Linux servers:

1)          1)  Windows : Open my.ini file located in Mysql installation folder at C:\Program Files\MySQL\MySQL Server 5.x
           At end of page make the following entry:

              Linux: Open my.cnf at location /etc/mysql or /etc/ , add line

2)   Restart Mysql service.

3)   Now connect to Mysql command line using

            Mysql –u root –p

It will prompt for password. Press enter only (as blank password is set by following above steps )

4)   Execute following command to reset the password

        mysql> use mysql;

             mysql> UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';

                If the above query does not work or return the error of coulmn 'Password' then run the below query to reset the password:

               mysql> UPDATE user SET authentication_string=PASSWORD('new_password') WHERE user='root';

              mysql> FLUSH PRIVILEGES;

5)   Remove skip_grant_table= 1   from my.ini or my.cnf , save and exit.

6)   Restart Mysql service.