WordPress Password Resets via MySQL

I have not completed the switch away from WordPress completely, (as this site and the justCheckers site show). I might switch to using something like Django CMS or Wagtail. Unfortunately I have not had as much time as I’d like to, to work on Rookeries, my own Flask based CMS

Still in the meantime, I managed to accidentally lock myself out of my own WordPress sites. Thankfully there are a few ways to reset passwords in WordPress. The most surefire way I found was to reset the password using MySQL. (Using the wp-cli tools looks interesting, but I didn’t feel like setting up get another PHP tool.) Most of the time you don’t need to do this. However if you’ve managed to forget your only admin password… well this will get you out of that problem. So what does resetting a WordPress password via MySQL, you ask?

  1. Log into your server via SSH (or to MySQL via a SSH tunnel if you have that enabled)
  2. Login into MySQL: mysql -u $MY_DB_USER -p
  3. Get the username if you have not already. You can look those up in the ${MY_WP_SUFFIX}_wp_users table.
  4. Finally reset the password, using the MD5() function and an update SQL statement: UPDATE "wp_users" SET "user_pass" = MD5('new_password') WHERE "wp_users"."user_login"= "username";
  5. Log in with the user using the regular WordPress login (e.g. https://my-wordpress.example.com/wp-login.php). Remember to change the password, as that will use a stronger hash than MD5 internally and is more secure.

And there you go!