Linux Admin Zone

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 prompt)# use mysql;
(mysql prompt)# update user set password=PASSWORD("newpassword") where user="root";
(mysql prompt)# quit;

You can also set password of any user here.

Step 3. Restart MySQL server and use new password.

Related posts in this blog on MySQL:
1. Setup MySQL Cluster in RedHat based Linux
2. Setup Multiple MySQL server instances in a single Linux Server.
3. Optimize a large installation of MySQL.
4. Fix MySQL running slow without any known reason.
5. Ignore MySQL error coming while executing bulk statements.

***

PostgreSQL:

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

 # vi /var/lib/pgsql/data/pg_hba.conf
Old Line:
local all postgres password
Change it to:
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
 
Old Line:
local all postgres trust
Change it to:
local all postgres password

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

 # service postgresql restart

database mysql postgresql