Reset MySQL root password

How to Reset MySQL root password

Sometimes we loose or forget the root password of MySQL database. Following steps explain how to reset MySQL root password by resetting it.

Step # 1: Stop mysql service

 
# /etc/init.d/mysqld stop

Output: 
Stopping MySQL database server: mysqld.

Step # 2: Start mysqld daemon with skip-grant-tables option. This will start mysql without prompting for a password.

 
# mysqld_safe --skip-grant-tables & 

Output: 

[1] 5988
 Starting mysqld daemon with databases from /var/lib/mysql
 mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client and as root user.

 
# mysql -u root 

Output: 

Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 mysql>

Step # 4: Now Reset 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, 3 rows affected (0.00 sec)
 Rows matched: 3 Changed: 3 Warnings: 0 

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

mysql> quit

Step # 5: Stop mysql service