How to add wordpress admin user to the database using phpMyAdmin (mySql)

1. My site got hacked, I cannot login into my wordpress admin dashboard. How do I gain access to my wordpress admin?
2. Guy maintaining my wordpress website has left. He used his email address on the administrator login. I’m locked out, how do I reset my wordpress admin password?
3. Forgot my admin password, how do I reset the password using mySql?

All the above queries can be solved by adding a new admin user via phpMyAdmin. In this tutorial I’m going to guide you on how to add a new wordpress admin user via mySql.

Requirements to add new user using phpMyAdmin

• Access to either WHM or Cpanel for your website hosting. (you can access your database from cpanel).
• Access to phpMyAdmin for your website.
• Ask your hosting provider if you don’t have access rights for either of the above.
• Though not mandatory, but having knowledge of SQL queries is an added advantage

Video Tutorial

If the above video does not solve you’re problem, you can continue reading for step by step instructions.

Step by step guide to add new wordpress admin user using phpMyAdmin (mySql)

Step 1. Access your mySql database through cPanel / WHM

• Login to your website control panel/cpanel. You can also get to your cpanel using WHM login. (Usually the cpanel login url is https://yoursite.com/cpanel).
• Locate the DATABASES section and click on phpMyAdmin.

Below is a screenshot from a siteground hosting cPanel.

Step 2. Access wordpress “Users” table using phpMyadmin

• Once you are in to the phpMyAdmin interface, click on the database name (DB of your site) on the left.
• Go to wp_users table on the left and Click on “Insert” link on top.

Note: Based on the table prefix you chose during wordpress installation. The wp_users table can have a different name. (e.g. wp_users can be mysite_users).

Screenshot of wp_users table on clicking insert tab

Step 3. Enter the form fields to insert a new record in wp_users table

Screenshot of new record entry on wp_users table

Fill the form fields as below to enter a new record.

ID – Leave this field empty to avoid duplicate entry (Automatic unique row id is assigned if left empty)

user_login –  Enter a username you want to use wordpress administrator login

user_pass – Enter a password for above username. From the function dropdown select MD5 (It’s important to encrypt password to avoid hacking and lockout again).

user_nicename – You can leave this empty for now. Or pick a nickname you want yourself to refer as.

user_email – add an email address you want to associate with this account.

user_url – You can leave this field empty for now. Or add your website url.

user_registered – Select a Date and time when the user is registered. (Don’t miss the calendar icon)

user_status – set the user status to 0.

display_name – leave this field empty for now. Or enter a display name for the user.

Click on the Go Button.

You will see a success message (e.g. 1 row inserted, Inserted row id: 3). Take a note of the row id. We will need this row id in our next step. In our case row id is 3.

You can also find the row id by clicking on wp_users link. See the image below to note the row id.

Step 4. Add user meta values to wp_usermeta table

To insert user meta values locate wp_usermeta on left. Click on Insert on top, this is similar to what we did in our previous step. Only the table changes.

Add the below information on the form which appears after clicking Insert.

unmeta_id – leave this field empty. (This will be automatically generated)

user_id – Enter 3 here. This is same id we noted on our previous step. The user_id should be same on both wp_users and wp_usermeta table.

meta_key – Enter wp_capabilities here. (change wp_capabilities to match your table prefix. All tables would have a certain prefix change wp to the prefix you have for your table. (Refer to the note above in step 2).

meta_value – insert the below value for this field:

a:1:{s:13:"administrator";s:1:"1";}

On the second row in wp_usermeta table add the below information:

unmeta_id – leave this field empty. (This will be automatically generated)

user_id – Enter 3 here. This id is same id we entered on our previous step.

meta_key – Enter wp_user_level here.

meta_value – 10

Click on GO

You can now login into your wordpress admin dashboard. Use the Username and Password you entered on step 3 to access your WP dashboard.

SQL Query to add new wordpress user using phpMyAdmin (mySql)

Advanced user and developers having knowledge of sql queries can use the below code.
Simply add the below query, click on SQL tab on top on phpMyAdmin (Refer the image below)

INSERT INTO `DBname`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('3', 'newuser', MD5('password@123'), 'Your NickName', 'youremail@yoursite.com', 'http://www.yoursite.com/', '2019-01-17 00:00:00', '', '0', 'Your Display Name');

INSERT INTO `DBname`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '3', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');

INSERT INTO `DBname`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '3', 'wp_user_level', '10');

On the above query change the DBname to your database name, also change the wp prefix if you’ve changed it on your wordpress installation.

That’s it.

Don’t forget to use complex and encrypted passwords. I would also recommend to use a wp security plugin. So that you don’t get locked out again from your wordpress dashboard.

Your comments and feedback is valuable. Do share your thoughts on how can we improve this blog.

Till then Happy Blogging.