Recover or Reset root password of MySQL and PostgreSQL Servers

by jagbir on April 24, 2008

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.

Few related and helpful posts in this blog on MySQL:
* Quick and simple php based script to check replication status of multiple MySQL Servers
* Setup multiple MySQL database servers in a single Linux host
* Optimize MySQL on a large Database Server
* How to setup MySQL Cluster 7.0 in Redhat based Linux
* Optimize and fix MySQL Server running slow without any load
* How to find out the clients connecting to your MySQL server

***

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

You may also like:
* 5 steps to secure your Linux Server
* Ensuring secure access to production Linux Servers
* Bash script to backup essential log files in Linux
* Quickly change your ssh port from defualt 22 to something higher
* SSH port forwarding from remote to local machine
* Save root or user history to check later
* Install and configure denyhost to prevent brute force attacks

  • http://www.pohlidame.cz/ Ondra

    psql: FATAL: Ident authentication failed for user “postgres”

  • http://www.pohlidame.cz/ Ondra

    Solved: Step 3 is needed to be done as user postgres.

  • http://linuxadminzone.com jagbir

    Thanks Ondra for comment.

  • Vijay

    Thank you so much for this nice write up.

  • http://linuxadminzone.com jagbir

    Thanks Vijay for comment.

  • csuggs

    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.

    • http://linuxadminzone.com jagbir

      Thanks csuggs for comment and helpful info.

  • Pingback: script nulled

  • Felipe Marques

    Congratulations!! Works very well for me!

  • miguel

    Hi i did that but when i execute that command:

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

    appears the next message:

    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

Previous post:

Next post: