Skip to content

Tag: WordPress Admin

  • Reset WordPress Password Directly in Database

    My current job has found me in a sea of WordPress sites. Since we don’t allow (or want, at this time) users to register, the PHP mail() function is not enabled along with the security risks that PHP mail(); brings along with it. So what happens when a user needs their password reset? Currently, an admin has to manually set a new password. That’s fine and dandy but what happens if you only have one admin account and you’re locked out?

    The answer to that question is simple – reset the password directly in the database. Before you do that however, the new password needs hashed. You can use a simple MD5 hash to insert into your database which WordPress will run through its own wordpress_generate_password() function to create the proper form or you can use this tool that I built in order to create a ready-to-go WordPress password hash and example SQL query to run against your target database.

    Once you’ve got your hash, go into your WordPress database, and then find the ‘wp_users’ table. Inside of that table is a column called ‘user_pass’ – this is the field to enter your hash into.

    Execute the following SQL command (adjusted for your specific parameters):

    UPDATE wordpress.wp_users t SET t.user_pass = 'your-generated-hash' WHERE t.ID = 1;

    Replace “wordpress” with your database name and make sure you have the correct ID if you have multiple users otherwise, someone else is getting a new password.

    This can also be handy when installing WordPress within MAMP or MAMP Pro. I’ve found that in verisons 5 and 6 of MAMP Pro, when adding WordPress as an extra, the password you enter before MAMP installs WordPress never works. So, I’ve used this method to fix several WordPress installs when done through MAMP.

  • Replace Your Old Site URL with a New Site URL In Your WordPress Database

    I use the following SQL queries regularly to move databases between local, staging and development environments in my current role and have found them to be invaluable. There are plugins that can do this but a simple SQL query is going to be faster and the output from the SQL console will give you clear and concise feedback when executing. The following WordPress database queries are my “Swiss army knife” of tools when migrating databases across environments.

    First, to replace the Site Address (URL) and the WordPress Address (URL), use this query:

    UPDATE wp_options SET option_value = replace(option_value, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

    After that, we need to replace the URL’s contained within your post content, which is stored in the “post_content” column contained in the “wp_posts” table. To do that, use this query:

    UPDATE wp_posts SET post_content = replace(post_content, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

    while we’re at it, we’ll also update instance of the old URL in the “guid” column. We’ll do that like this:

    UPDATE wp_posts SET guid = replace(guid, 'https://www.your-old-domain.com', 'http://www.your-new-domain.com');

    Your site will almost certainly work perfectly fine if you don’t update the “guid” column but if you rely on RSS then your feed will malfunction. Each guid is also a valid URL and each URL contained in the “guid” column will redirect you to the corresponding post so while this may not seem like a big deal if nothing is obviously broken, it is a significant piece in the overall health and proper function of your site.

    It’s also important to not overlook the “wp_postmeta” table. This table stores all of the data related to each post in your site. Any hardcoded instances of your old site URL can have a negative affect on the functionality of your site. To update the post meta_value, use this query:

    UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

    Last, and this only applies if you use or have used the comments feature, is that we need to replace the URL’s in any comments made on your site. Top do that, use this query:

    UPDATE wp_comments SET comment_content = replace(comment_content , 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

    When the queries run successfully, you should see an output like the following:

    Query OK, 2 rows affected (0.03 sec)
    
    Rows matched: 2 Changed: 0 Warnings: 0

    Depending on how many rows the query will check, this may take some time. Updating the “meta_value” on one of my sites with almost 19,000 posts returned this console output:

    sitedb_wp> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'https://www.my-old-url.com', 'https://www.my-new-url.com')
    [2023-10-02 10:23:42] 231,918 rows affected in 1 s 27 ms

    And likewise, updating the “post_content” column returned this console output:

    sitedb_wp> UPDATE wp_posts SET post_content = replace(post_content, 'https://www.my-old-url.com', 'https://www.my-new-url.com')
    [2023-10-02 10:26:03] 86,156 rows affected in 38 s 573 ms

    Keep in mind that I’m executing these queries on an AWS RDS instance that is size db.t3.medium so take your particular database infrastructure into account when running these commands. I can personally confirm that these queries work on WordPress versions 4.8.x up to 6.3.x. I don’t see this changing in future WordPress versions but who knows. Also, these queries do not take into account any third-party plugins or data that said plugins may be storing in your site’s database.