Create WordPress administrator via MySQL CLI

Sometimes you need to create a new WordPress administrator however you don't have access to WordPress administrator or PHPMyAdmin, thankfully you can create a WordPress administrator using MySQL command line interface using the steps outlined below.

To complete this guide, please ensure you have SSH Access to your hosting server. Serversaurus hosting relies on key based authentication to login via SSH/SFTP. To set up SSH access you can generate a SSH key pair and configure it in cPanel before proceeding with this guide.

1. SSH to your hosting server
2. Login to MySQL using the below command (update mysql_username to your MySQL user and enter your password when prompted:

   mysql -u mysql_username -p;

3. Change into your database:

   use database_name;

4. Update the below command to include your preferred user details and email address, then run the command:

   insert into wp_users (user_login,user_pass, user_nicename,user_email,user_url,user_registered,user_activation_key,user_status,display_name) values ('username','','username','your@emailaddress.com','','2020-02-11 09:43','',0,'Username');

5. Locate your user in the wp_users table and note the number displayed in the ID column:

   SELECT * FROM wp_users;

6. Update the following command with your user ID, in our example, the user ID is the number 4:

   insert into wp_usermeta (user_id, meta_key, meta_value) values (4,'wp_capabilities','a:1:{s:13:"administrator";b:1;} ');

7. The final step is to reset your password by navigating to the WordPress login page (eg https://yourdomain.com.au/wp-admin), then select Lost your password? and enter your username and Get New Password.

You will now receive a password reset email and reset your password to access WordPress dashboard!