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 stopverify whether mysqld process stopped, if not you can go ahead to kill it:
# ps aux | grep mysqlKill all processes shows by above command.
OR
# killall mysqldStep 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-tablesStep 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 restartStep 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