Losing access to the WordPress administrator account can be a real headache. Sometimes, standard password recovery methods (via email) don’t work, especially if:

• You forgot both your login and password.
• The site’s mail sending function (wp_mail) is broken.
• The WordPress admin panel is broken or unavailable.
• The only access left is to the database (via MySQL client, phpMyAdmin, WP-CLI, etc.).

In this article, we’ll walk through a reliable and secure way to find the WordPress administrator directly in the database and reset their password using SQL. We’ll follow DevSecOps best practices to avoid breaking serialized data and minimize risks.

1. How to Find the WordPress Administrator in the Database

Before resetting the password, we need to find the administrator account ID.

Step 1: Connect to the Database

First, connect to your MySQL database. If using the command line, this is done as follows:

mysql -u root -p -D wordpress_db

Important: Replace root with your actual database username, and wordpress_db with your real WordPress database name. You can find the database name in the wp-config.php file under the DB_NAME parameter.

Step 2: Run an SQL Query to Find the Administrator

Now run the following SQL query to find users with the administrator role:

SELECT
    u.ID,
    u.user_login,
    u.user_email,
    m.meta_value AS role
FROM
    wp_users u
JOIN
    wp_usermeta m ON u.ID = m.user_id
WHERE
    m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%administrator%';

What does this query do?

• It joins the wp_users (users) and wp_usermeta (user metadata) tables.
• It searches wp_usermeta rows where meta_key is 'wp_capabilities' (the key where user roles are stored) and meta_value contains 'administrator'.
• It returns the ID, login (user_login), email (user_email), and the actual user role.

Note: If your WordPress uses a custom table prefix (not wp_), be sure to replace wp_users, wp_usermeta, and wp_capabilities with your actual prefix. You can find it in wp-config.php in the $table_prefix variable. For example, if $table_prefix = 'custom_', then wp_users becomes custom_users.

2. How to Reset the Administrator Password via SQL

WordPress stores passwords in encrypted form using secure algorithms (typically bcrypt). However, for temporary recovery, you can set the password using the MD5 function. On the next login, WordPress will automatically rehash the password using a more secure method.

Run the following SQL query to reset the password:

UPDATE wp_users
SET user_pass = MD5('YourNewSecurePass123')
WHERE user_login = 'admin'; -- Or use user ID: WHERE ID = 1;

Important:

• Replace 'YourNewSecurePass123' with a strong, unique password.
• Replace 'admin' with the actual administrator login found in the previous step. If you have multiple administrators or want to be more precise, use WHERE ID = [admin_ID], where [admin_ID] is the ID from the first query.

After running this query, you’ll be able to log into the WordPress admin panel using the specified login and new password.

⚠️ EXTREMELY IMPORTANT: Do not leave an MD5 password in place! MD5 is outdated and insecure, vulnerable to rainbow table attacks. After successfully logging into WordPress, IMMEDIATELY change your password via the user interface (Profile -> Change Password). WordPress will automatically rehash it using bcrypt, making it secure.

3. Potential Issues and Limitations of This Method

When using direct database access, there are always risks:

• MD5 is outdated: As mentioned, MD5 is insecure for storing passwords. Use it only as a temporary fix.
• Broken serialized data: The wp_usermeta table (which stores roles) contains serialized PHP arrays. Do not attempt to manually edit the meta_value field to change roles or other settings, as this can corrupt data and break the site. To change roles, use wp-cli or a PHP script.
• No audit trail: Changes made directly via SQL are not logged by WordPress. This makes it harder to track security incidents or debug issues.
• Custom prefix complications: Don’t forget to adjust SQL queries if your WordPress uses a non-standard table_prefix.

4. Benefits of This Recovery Method

Despite the limitations, this method is very useful in critical situations:

• Works without web access: Allows access recovery when the WordPress admin panel is down.
• Minimal dependencies: All you need is MySQL command line or phpMyAdmin access.
• Quick control recovery: Lets you quickly regain control of the site during outages, data loss, or even after a compromise.
• Automation-friendly: Can be part of deployment or recovery scripts (e.g., using Bash, Ansible).

5. Recommendations After Successful Access Recovery

After you’ve successfully logged into the WordPress admin panel:

• Immediately change the password via your user profile: This rehashes it securely (via bcrypt).

• Make sure the user has the correct role:

  SELECT meta_value FROM wp_usermeta
  WHERE user_id = (SELECT ID FROM wp_users WHERE user_login = 'YOUR_ADMIN_LOGIN')
    AND meta_key = 'wp_capabilities';
  

  (Usually something like a:1:{s:13:"administrator";b:1;}. Do not edit this manually!).

• Ensure the user_email is correct: This is critical for future password recovery through standard WordPress mechanisms.

6. Bonus: WP-CLI Method (If Available)

If you have access to WP-CLI (the WordPress command-line interface), this is the most secure, simple, and recommended way to reset a password, as WP-CLI works properly with internal WordPress mechanisms, including password hashing and serialized data.

1. Check users with the administrator role:

   wp user list --role=administrator
   

   This will show a list of administrators, their IDs, and logins.

2. Reset the specific user’s password:

   wp user update admin --user_pass='YourNewSecurePass123'
   

   Replace admin with the login of the intended administrator and 'YourNewSecurePass123' with your new password. WP-CLI will handle secure hashing for you.

Conclusion

Restoring administrative access to WordPress via direct SQL queries is a powerful and effective method for emergencies. It lets you quickly regain control of your site when other methods are unavailable. The key is to act cautiously, understand the risks of MD5, and always treat this method as a temporary fix — promptly change your password via the WordPress UI after logging in. If possible, always prefer WP-CLI as a safer and more predictable tool.