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.

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

Tagged as: mysql postgresql