I recently locked myself out of my own wiki while changing passwords. For some reason, my browser didn’t store the new one, and I hadn’t yet written it down. Oops!

MediaWiki does, like all other CMSs, have a “reset password” option. But sometimes that doesn’t work (which was the case here—I need to update that install, which is why I was changing passwords), and sometimes you need to reset a password for a user, rather than yourself. Fortunately, there’s an easy way using phpMyAdmin.

(I’m going to assume that you know how to log in to your instance of phpMyAdmin. If you don’t, consult your host’s knowledge base for instructions.)

MediaWiki stores user information in a table called “user”. Navigate to that table and you’ll see something like this:

This is from a localhost install that I created to test things out, so it only has one user. Chances are you’ll have multiple users. Note that MediaWiki stores both the user_name, user_real_name, and user_email as binary values, so you can’t actually tell which user you are looking at. You have two options here:

First, you can actually click on any of those values I just mentioned and download the .bin file for that value. Open it in a text editor and you’ll see its actual value. If you only have a handful of users, this is not a bad way to go.

The second option requires that you know at least one of those actual values. Go to the SQL tab and enter one of the following commands:

SELECT user_name FROM user WHERE user_name = 'username';

or

SELECT user_name FROM user WHERE user_real_name = 'Joe User';

or

SELECT user_name FROM user WHERE user_email = 'user@example.com';

You should be able to get their user name from the history of any page they’ve edited, or from their user page if they’ve created one. Once you execute one of those three queries, you should see something like this:

As you can see in the above screenshot, the binary version of our user’s name is 4b656e. Click on the “Browse” tab, which takes us back to our first screenshot.  We can see that our user’s user_id has a value of 1.

Let’s go back to the SQL tab. Enter the following sql command:

UPDATE user SET user_password=md5(concat(1,'-',md5('local'))) where user_id=1;

Change ‘local’ to whatever you want the password to be, and adjust the user_id value as required. Your user’s password is now reset.

Troubleshooting

  1. If no values are returned when you are searching for a user name, real name, or email, check for case. ken is not the same as Ken.
  2. Your database may have been set up to use a table prefix. If so, your users table may be actually be something like mw_users. Be sure to alter the SQL queries and commands above appropriately.

References