In this article I will describe how you can manually reset WordPress administrator password from mysql hosted on AWS RDS or EC2 instance. Normally, you use the wordpress forget password feature to reset wordpress administrator password, but in a condition where email on your site is not working then you cannot use that. You can reset the password in the database manually, this will let you login to wordpress admin then.
Resetting the administrator password in the mysql database
Please follow below steps to manually reset wordpress administrator password from mysql database. You can follow these steps for shared hosting as well, by navigation to phpmyadmin.
STEP 1: Connecting to Database
The very first stepp in this process is to connect to database server. If the mysql database is hosted to AWS EC2 server and if you have configured database to be accessed remotely from any system or from your system, then you can connect to mysql from your local machine using below command or you can use tool like heidisql or mysql workbench.
mysql -u username -p databasename
This will prompt you for database password, enter the password and you are connected.
If your db is hosted on AWS RDS and you allowed public access to it from any ip or your your ip, then again you can connect through command line or heidisql or mysqlworkbench.
Similar process you can adapt for shared hostings, if you configured shared hosting database to be accessible from remote ips, then you can use command line approach or you can use phpmyadmin from control panel and enter to your mysql database.
STEP 2: Update Password
The next step is to update the wordpress password, you can use below command to update the password. This command you can run from command line or phpmyadmin.
UPDATE 'wp_users' SET 'user_pass' = MD5('new_password') WHERE 'wp_users'.'user_login' = "adminuser";
You can replace new_password with the new password that you want to put and adminuser with the user whose password you want to update. Above SQL command creates an MD5 hash of the password you specify. WordPress requires that passwords should be stored in database in MD5 format.
Note: Some version of wordpress may use wp_users table while some version might use wpe6_users, so please check before applying.