[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:

[[email protected]]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

[[email protected]]# mysqld_safe --skip-grant-tables &
[1] 4721
[[email protected]]# Starting mysqld daemon with databases from /var/lib/mysql
[[email protected]]#
[[email protected]]# 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
[[email protected]]# /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
[[email protected]]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[[email protected]]# 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.

Share on Facebook1
Share on Google+3Tweet about this on TwitterShare on StumbleUpon0Share on LinkedIn0Share on Tumblr0Digg thisPin on Pinterest0Share on Reddit0Email this to someonePrint this page
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…

Review Overview

User Rating: Be the first one !

About Abhijit Sandhan

Abhijit Sandhan
Loves Linux, Blogging, Traveling, Hiking and sharing knowledge!

One comment

  1. Works perfect, thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA