Michael's Blog

Manually create a Wordpress admin user from the mysql command line

If you need admin access to a wordpress install you can easily create a new admin user by running a few SQL commands on the database. This has been tested and verified to work on Wordpress 3.5.

To do this you will first need to identify what database the site is actually using. Check wp-config.php for the database name and mysql host info. Once you have that connect to mysql and run the following statements.

INSERT INTO wp_users (user_login,user_pass,user_email,user_registered,user_status) VALUES("user_name",md5('password'),"username@example.com",NOW(),0);

Find user ID from wp_users table:

SET @user_id = (SELECT ID FROM wp_users where user_login = 'user_name');

INSERT INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (@user_id,"wp_user_level","10");

INSERT INTO wp_usermeta (user_id,meta_key,meta_value) VALUES (@user_id,"wp_capabilities",'a:1:{s:13:"administrator";s:1:"1";}');

SQL Query Optimization

I've been updating the code for my site a bit to make things load a bit faster and one of the things I fixed was the random query generator.

SELECT quote, name FROM quotes ORDER BY RANDOM() LIMIT 1

The query above works great for small tables but consider the issue when you have a table with millions of rows, the server must read every row, sort them, and then throw out all the results but the first. This takes a lot of CPU time and is slow, the optimal solution is to use create a sequence as the primary key and then generate a random number using that. For example:

idx = select last_value FROM quotes_idx_seq

SELECT quote_text, name FROM quotes WHERE quote_id = '%s' %idx

It requires two queries instead of one but there is much less disk I/O required.