How to change the root password in MySQL server

Zyxware default image3

Suppose you forgot mysql password in the middle of a project and you tried almost all passwords and failed. So the only option left is to change the password. Then you can follow the below steps. Please note that you have to login as admin for doing this process.

  1. First you have to stop the mysql server process. For doing this open a terminal and execute the below command.

    $ sudo /etc/init.d/mysql stop
  2. Secondly you have to start the the mysqld daemon process with the --skip-grant-tables option. After doing this, it will not prompt for password. For this process execute the command.

    $ sudo mysqld_safe --skip-grant-tables

  3. Open another terminal (CTRL+shift+t)
  4. Then you have to connect to the mysql server as the root user.

    $ mysql -u root
  5. Change the root account password of mysql server.
    mysql>use mysql;
    mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
    mysql> flush privileges;
    mysql> quit
    
  6. Kill the sql process which is running in the background. For doing this use the below commands.
    $ ps aux|grep mysql
    $ sudo kill processid. (Take each process id which you will get through the above command)
  7. Finally you have to exit and restart the MySQL server.
    $ /etc/init.d/mysql stop
    $ /etc/init.d/mysql start
    $ mysql -u root -p

Hope you will be able to change the password. And please note that this only one method to accomplish this task.