How to Find and Replace a Domain in a WordPress Database

If you’ve recently changed your domain name, you may need to update your WordPress database to reflect the new domain. This can be a tedious process if you have a large site with many pages and posts. However, with a simple SQL query and a PHP function, you can easily find and replace the old domain with the new one throughout your entire database. Here’s how to do it.

Before you begin, it’s important to back up your database to ensure that you can restore it if anything goes wrong. You can use a plugin like UpdraftPlus to create a backup of your database and files.

Once you have a backup, you can proceed with the following steps:

Step 1: Open your database in phpMyAdmin

First, open your WordPress database in phpMyAdmin. You can access phpMyAdmin through your web hosting control panel or by logging in to your web server via SSH.

Step 2: Run the SQL query

Next, run the following SQL query to find and replace the old domain with the new domain:

SET @old_domain = 'old-domain.com';
SET @new_domain = 'new-domain.com';

SELECT CONCAT('UPDATE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` SET `', COLUMN_NAME, '` = REPLACE(`', COLUMN_NAME, '`, \'', @old_domain, '\', \'', @new_domain, '\')') AS `sql`
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND DATA_TYPE IN ('char','varchar','text','mediumtext','longtext');

Replace old-domain.com with your old domain name, and new-domain.com with your new domain name. Also, replace your_database_name with the name of your WordPress database.

This query generates a list of all columns in the database that have a text-like data type, and generates a series of UPDATE statements for each matching column that replaces the old domain with the new domain.

Step 3: Execute the generated SQL statements

The query doesn’t actually run the UPDATE statements, but instead generates them as output. To execute the generated SQL statements, copy the output and run it as a separate query.

Step 4: Use the PHP function

If you prefer to use a PHP function to find and replace the domain, you can use the following function:

function find_replace_domain_in_database($old_domain, $new_domain) {
    global $wpdb;

    $columns_query = "
        SELECT CONCAT(
            'UPDATE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` SET `', COLUMN_NAME, '` = REPLACE(`', COLUMN_NAME, '`, \'', %s, '\', \'', %s, '\')'
        ) AS `sql`
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = %s
        AND DATA_TYPE IN ('char','varchar','text','mediumtext','longtext')
    ";

    $columns = $wpdb->get_col($wpdb->prepare($columns_query, $old_domain, $new_domain, DB_NAME));

    foreach ($columns as $column) {
        $wpdb->query($column);
    }
}

This function takes two parameters: $old_domain and $new_domain, which are the old domain and the new domain, respectively. You can call this function in your WordPress theme or plugin to update your database.

Note that this function will make changes to your WordPress database, so be sure to backup your database before running it to ensure that you can restore it if anything goes wrong.

To use the function, simply call it and pass in the old domain and the new domain, like this:

find_replace_domain_in_database('old-domain.com', 'new-domain.com');

This will update all instances of the old domain with the new domain in the WordPress database.

It’s crucial to back up your WordPress database before making any changes, including running the SQL query or using the PHP function provided in this post. Any mistakes or errors during the process can cause irreversible damage to your database, which could potentially result in the loss of data. To avoid any such issues, be sure to create a backup of your database, so you can restore it if anything goes wrong. You can use a plugin like UpdraftPlus or a similar backup tool to create a backup of your database and files. By taking this precautionary step, you can ensure that your data is safe and protected throughout the process of updating your domain in your WordPress database.

Here is an example plugin for the code, which allows users to replace an old domain with a new one in the WordPress database. This plugin includes an admin page with a form for entering the old and new domain, and a button for running the domain replacement function. Please make sure to backup your database before using this plugin.

https://gist.github.com/devuri/ccd0a5085afb10f5522516b51e9ffdd5