Recover or Reset root password of MySQL and PostgreSQL Servers

Forgot password of MySQL/PostgreSQL? Here is quick howto to recover/reset the password of root user in both Servers:

MySQL:

Step 1. Stop mysql server or Kill it:

$ service mysqld stop

verify whether mysqld process stopped, if not you can go ahead to kill it:

$ ps aux | grep mysql

Kill all processes shows by above command.
OR

$ killall mysqld

Step 2. Start mysqld process manually without using grant table (to skip requirement of user/password):

$ /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --skip-grant-tables

Step 3. Reset root password:

$ mysql
mysql> use mysql;
mysql> update user set password=PASSWORD("newpassword") where user="root";
mysql> quit;

You can also set password of any user here.

Step 3. Restart MySQL server and use new password.

***

PostgreSQL:

Step 1. Edit PostgreSQL config file to establish trust relationship to login without password:

$ vi /var/lib/pgsql/data/pg_hba.conf
# <em>Old Line:</em>
local all postgres password
# <em>Change it to:</em>
local all postgres trust

Step 2. Restart PostgreSQL Server:

$ service postgresql restart

Step 3. Change password:

$ psql -U postgres template1 -c alter user postgres with password 'newpassword';

Step 4. Password has been updated. Revert back the original settings of config file:

$ vi /var/lib/pgsql/data/pg_hba.conf
# <em>Old Line:</em>
local all postgres trust
# <em>Change it to:</em>
local all postgres password

Step 5. Restart server and use your new password to access PostgreSQL Server.

$ service postgresql restart

This entry was posted on Thursday, April 24th, 2008 at 5:59 pm and is filed under database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

8 Responses to “Recover or Reset root password of MySQL and PostgreSQL Servers”

Ondra on March 11th, 2011 at 4:48 am

psql: FATAL: Ident authentication failed for user “postgres”
Ondra on March 11th, 2011 at 4:59 am

Solved: Step 3 is needed to be done as user postgres.
jagbir on March 11th, 2011 at 10:27 am

Thanks Ondra for comment.
Vijay on August 2nd, 2011 at 9:20 pm

Thank you so much for this nice write up.
jagbir on August 2nd, 2011 at 9:33 pm

Thanks Vijay for comment.
csuggs on August 28th, 2011 at 2:08 am

If you’re wanting PG to store your password encrypted, then you should issue

ALTER USER postgres WITH ENCRYPTED PASSWORD ‘new-password’;

Afterwards, if you want to be able to log in through through the command line without being prompted for a password, then in the .pgpass file you must put the plain-text of the password, not the md5 hash.
jagbir on August 28th, 2011 at 7:44 am

Thanks csuggs for comment and helpful info.
script nulled on January 6th, 2012 at 2:02 am

script nulled…

[...]Recover or Reset root password of MySQL and PostgreSQL Servers | Linux Admin Zone[...]…

Linux Admin Zone