A WordPress user can belong to any of the six pre-defined roles.
• Super Admin – Has access to all features including site network administration.
• Administrator – Has access to all the administration features within a single site.
• Editor – Can publish and manage posts including the posts of other users.
• Author – Can publish and manage their own posts.
• Contributor – Can write and manage their own posts but cannot publish them.
• Subscriber – Can only manage their profile.
Upon installing WordPress, an Administrator account is automatically created. Thus, every WordPress installation comes with an admin user.
In case a WordPress admin user loses their password, they can use the Lost your password? link and the related Email account for resetting the password.
But in some case, we may not have access to this Admin account, or may not want to reset the password, but would like another Admin account created for maintenance or troubleshooting purpose.
If you ever face such a scenario, we have laid out the necessary steps required to create a WordPress Admin user from MySQL commandline.
First, let us get the necessary information about the WordPress database.
This information is stored in the WordPress configuration file, which is named wp-config.php. Identify the location of wp-config.php in the server.
And in terminal, switch to the directory containing this configuration file.
List the Database Host, Database Name, Database User, Database Password, and WordPress table prefix from WordPress configuration file.
# grep ‘DB_HOST\|DB_NAME\|DB_USER\|DB_PASSWORD\|$table_prefix’ wp-config.php
Next, login to the MySQL Server as WordPress Database user.
# mysql -h $DB_HOST -u $DB_USER -p
NOTE:
⇾ Replace $DB_HOST and $DB_USER with values of Database Host and Database User acquired from output of previous command
⇾ Upon receiving password prompt, use value of $DB_PASSWORD acquired from output of previous command
Perform the following tasks in the MySQL prompt…
Use the WordPress database.
mysql> use $DB_NAME;
NOTE: Replace $DB_NAME with name of database
Execute the following MySQL commands to create a WordPress user.
mysql> insert into `$DB_NAME`.`$table_prefix_users` (`id`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) values (‘$WP_ID’, ‘$WP_USER’, md5(‘$WP_PASSWORD’), ‘$WP_USER_NICE’, ‘$EMAIL’, ‘$WEBSITE’, ‘$DATE&TIME’, ”, ‘0’, ‘$WP_USER_DISPLAY’);
NOTE:
⇾ Replace $DB_NAME with value of Database Instance acquired from output of previous command.
⇾ Replace $table_prefix with value of WordPress table prefix acquired from output of previous
command.
⇾ Replace $WP_ID with the ID of to be created WordPress user.
⇾ Replace $WP_USER with value of to be created WordPress admin user.
⇾ Replace $WP_PASSWORD with a new strong and complex password.
You may use the LastPass Password Generator
⇾ Replace $WP_USER_NICE with URL sanitized version of $WP_USER. But if $WP_USER‘s
value does not have any special characters, then that value could be used for this.
⇾ Replace $EMAIL with the Email address of to be created WordPress admin user.
⇾ Replace $WEBSITE with the website user. This could be the main URL of WordPress website.
Example: http://example.com/
⇾ Replace $DATE&TIME with the Date and Time(UTC) on which user is being created.
Example: 2023-09-19 10:25:47
⇾ Replace $WP_USER_DISPLAY with the name of $WP_USER that will be displayed on their
profile page and next to their posts. Value of $WP_USER could be used for this.
Now, assign the Administrator capability for the newly created WordPress user.
mysql> insert into `$DB_NAME`.`$table_prefix_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) values (null, ‘$WP_ID’, ‘$table_prefix_capabilities’, ‘a:1:{s:13:”administrator”;s:1:”1″;}’);
NOTE:
⇾ Replace $DB_NAME with value of Database Instance.
⇾ Replace $table_prefix with value of WordPress table prefix.
⇾ Replace $WP_ID with the ID of to be created WordPress user.
And then set the User Level as 10 for the newly created WordPress user. This user level pertains to the Administrator role.
mysql> insert into `$DB_NAME`.`$table_prefix_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) values (null, ‘$WP_ID’, ‘$table_prefix_user_level’, ’10’);
NOTE:
⇾ Replace $DB_NAME with value of Database Instance
⇾ Replace $table_prefix with value of WordPress table prefix
⇾ Replace $WP_ID with the ID of to be created WordPress user.
Exit from MySQL command line.
mysql> exit
Finally, login in to the WordPress Dashboard using the new password, so that WordPress will automatically update the MD5 hash of password to a salted one.
Done.