[How To] Reset MySQL root password

MySQL root password is important if you want to access phpMyAdmin, secure MySQL command prompt access, etc.

In case one forgets the MySQL root password or wants to set root password, following steps should help to achieve the same.

1) Stop the MySQL Server:

/etc/init.d/mysql stop

or

/etc/init.d/mysqld stop

2) Start the MySQL configuration:

mysqld --skip-grant-tables &

or

mysqld_safe --skip-grant-tables &

3) Login to MySQL as root and load database mysql:

mysql -u root mysql

4) Execute MySQL command to set/update the password

update user set password=PASSWORD("NEWPASSWORD") where User='root'; FLUSH PRIVILEGES; exit;

Note: In above query, replace YOURNEWPASSWORD with your new password:

5) Stop the MySQL server:

/etc/init.d/mysql stop

or

/etc/init.d/mysqld stop

6) Start the MySQL server:

/etc/init.d/mysql start

or

/etc/init.d/mysqld start

7) Try logging in MySQL prompt:

mysql -u root -pNEWPASSWORD

Example:

[root@localhost]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

[root@localhost]# mysqld_safe --skip-grant-tables &
[1] 4721
[root@localhost]# Starting mysqld daemon with databases from /var/lib/mysql
[root@localhost]#
[root@localhost]# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, 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> update user set password=PASSWORD(password") where User='root';FLUSH PRIVILEGES; exit;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Bye
[root@localhost]# /etc/init.d/mysqld stop
STOPPING server from pid file /var/run/mysqld/mysqld.pid
130730 15:46:25  mysqld ended

Stopping mysqld:                                           [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@localhost]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@localhost]# mysql -u root -ppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, 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> exit

The above method works on Ubuntu, Fedora, CentOS, RedHat.