Recover/Change/ Reset MySQL root password

This article will guide you through the steps required to Recover/Change/Reset MySQL root password.

The procedure mentioned in this tutorial is tested on:

OS	Ubuntu 12.04
MySQL Server	5.5.32
MySQL Client	5.5.32

1) Stop the MySQL demon process using command:

   # /etc/init.d/mysql stop

     Rather than invoking init scripts through /etc/init.d, use the service(8)
     utility, e.g. service mysql stop

     Since the script you are attempting to invoke has been converted to an
     Upstart job, you may also use the stop(8) utility, e.g. stop mysql
     mysql stop/waiting

2) Start the MySQL demon process using "--skip-grant-tables"option so that it will not prompt for password and "--skip-networking"option to disable mysql networking functionality.

   # /usr/sbin/mysqld --skip-grant-tables --skip-networking &
     [1] 27946

3) Start the MySQL client process on another terminal using this command:

   # mysql -u root
     Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 1
     Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

     Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

     Oracle is a registered trademark of Oracle Corporation and/or its
     affiliates. Other names may be trademarks of their respective
     owners.

     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

     mysql>

4) Setup new MySQL root account password:

   # mysql> use mysql
     Reading table information for completion of table and column names
     You can turn off this feature to get a quicker startup with -A

     Database changed
   # mysql> update user set password=PASSWORD("NEW-PASSWORD") where User='root';
     Query OK, 4 rows affected (0.00 sec)
     Rows matched: 4  Changed: 4  Warnings: 0

   # mysql> flush privileges;
     Query OK, 0 rows affected (0.00 sec)

   # mysql> quit
     Bye

Note: You have to type the sql statement specified in RED color. 5) Stop/kill the MySQL Server process:

   # ps -ef | grep -i mysql
     mysql    27946 27863  0 10:10 pts/1    00:00:00 /usr/sbin/mysqld --skip-grant-tables --skip-networking
   # kill -9 27946

6) Exit and restart the MySQL server daemon.

   # /etc/init.d/mysql start
     Rather than invoking init scripts through /etc/init.d, use the service(8)
     utility, e.g. service mysql start

     Since the script you are attempting to invoke has been converted to an
     Upstart job, you may also use the start(8) utility, e.g. start mysql
     mysql start/running, process 28231

7) Verify that the root password is changed using following command:

   # mysql -u root -p
     Enter password:
     Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 71
     Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

     Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

     Oracle is a registered trademark of Oracle Corporation and/or its
     affiliates. Other names may be trademarks of their respective
     owners.

     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

     mysql> quit
     Bye