How to Create New WordPress Admin User via MySQL

With the freedom and open-source nature of the WordPress content management system, taking over online content publishing is easy, flexible, and manageable.

It is important for WordPress database administrators to have a grip on all database user contributions and interactions within such platforms. There are several reasons that will force a database administrator to create users with different privileges via the MySQL client or shell.

The first reason is the flexibility and straightforward nature of a MySQL client shell. You only need to adhere to the required MySQL command syntax for your database queries to execute successfully. The second reason is preference. It is relatively faster to create and execute MySQL database queries from a MySQL client shell than from a GUI interface like phpMyAdmin.

If you are using MariaDB, the MySQL shell commands are still applicable to it since MariaDB is an open-source fork of the MySQL RDBMS.

Listing WordPress MySQL Database and Tables

In order to create any WordPress user; normal user or admin user, through the MySQL client shell, your WordPress site needs to be mutually configured with the targeted MySQL database.

To achieve this, access the MySQL command shell as a root user.

$ mysql -u root -p

Make sure the database associated with your WordPress site exists and switch to that database as we will be creating a WordPress Admin user associated with it.

MariaDB [(none)]> SHOW DATABASES;  
MariaDB [(none)]> USE wordpress;     

WordPress comes with a default MySQL user table called wp_users. This table automatically exists under the database name you created for your WordPress site. We need to familiarize ourselves with the column details of this table as it will help us comfortably relate while creating a new WordPress Admin user.

MariaDB [(none)]> Describe wp_users;

One WordPress Admin user called tutor@linuxshelltips already exists. When we create another Admin user, their credentials should be displayed on this wp_users MySQL database table.

MariaDB [(none)]> SELECT * FROM wp_users;

Checking WordPress MySQL Database Connection Settings

In a second Linux terminal environment, we need to make sure that your WordPress site configuration permits WordPress to securely connect to the MySQL database. The needed step here is to counter-check your WordPress database configuration file and make sure everything is in order.

$ sudo nano /srv/www/wordpress/wp-config.php
Or
$ sudo vi /srv/www/wordpress/wp-config.php

This file is used to hold important WordPress configuration details like the database name (DB_NAME), database user (DB_USER), database password (DB_PASSWORD), and database host (DB_HOST).

Creating the New WordPress Admin User via MySQL

You will need to add another user (editorlinuxshelltips) row entry to the MySQL database table wp_users.

MariaDB [(none)]> INSERT INTO wordpress.wp_users 
(user_login,user_pass,user_nicename,user_email,user_url,user_registered,user_activation_key,user_status,display_name) 
VALUES ('editor@linuxshelltips',MD5('Id@editor254'),'editorlinuxshelltips','[email protected]','http://localhhost','2021-08-27','',0,'editor@linuxshelltips');

After adding a new user, list the database table wp_users again to confirm.

MariaDB [(none)]> SELECT * FROM wp_users;

The new user (editor@linuxshelltips) has been added to the WordPress database table (wp_users).

Restart the MySQL service to apply changes.

$ sudo systemctl restart mysql

Login to WordPress with New Admin User Credentials

Login to your WordPress site with the new user credentials:

As you can see the user can’t do much except edit and delete a post or Log Out.

Sorry, you are not allowed to access this page.

Assign Admin Privileges to WordPress User via MySQL

To assign this user (editor@linuxshelltips) the needed Admin privileges, first take note of the auto-generated ID (3) in table wp_users. Another pre-existing table in our wordpress database is wp_usermeta. The values on this table are responsible for graduating a normal WordPress user to an Admin user.

Let us Identify these values for reference.

MariaDB [(none)]> SELECT * FROM wp_usermeta;

We need to assign this user (editor@linuxshelltips) the same wp_capabilities and wp_user_level entries as the already existing and privileged Admin user tutor@linuxshelltips.

Implement the following MySQL commands.

MariaDB [(none)]> INSERT INTO wordpress.wp_usermeta (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '3', 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}'), (NULL, '3', 'wp_user_level', '10'), (NULL, '3', 'show_welcome_panel', '1'); 

Again, list the table wp_usermeta.

MariaDB [(none)]> SELECT * FROM wp_usermeta;

Note the new values for editor@linuxshelltips from the screenshot above under user_id 3. The user_id in this wp_usermeta table should be equivalent to ID in the wp_users table.

Restart the MySQL service again.

$ sudo systemctl restart mysql

Log out and re-login into your WordPress site with the new user credentials.

The user, editor@linuxshelltips, can now do more to the WordPress site other than just edit or delete a post and Log Out. This user is now a Superuser or an Admin user.
​
It is now possible for your WordPress site to have more than one Admin user especially when you have too much content to manage and the traffic on your site is off the charts.