Multisite Blog URL Replacement using bash

Here’s an example of how you might use a Bash script to do that. The script assumes that you have the MySQL/MariaDB client installed on your system, that you can log into the database without a password (or that you’re OK with entering it manually), and that the database user has the necessary privileges to make these changes.

In this script, it will look for all tables with the format wp_{id}_options and then update the siteurl and home options accordingly.

Replace “yourDatabaseName”, “yourNewSiteURL”, and “yourNewHomeURL” with your actual database name, new site URL, and new home URL.

#!/bin/bash

databaseName="yourDatabaseName"
newSiteURL="yourNewSiteURL"
newHomeURL="yourNewHomeURL"

# Get all wp_{id}_options tables
optionsTables=$(mysql -Nse "SHOW TABLES LIKE 'wp_%_options'" $databaseName)

for table in $optionsTables; do
    echo "Updating $table..."

    # Update siteurl
    mysql -e "UPDATE $table SET option_value = '$newSiteURL' WHERE option_name = 'siteurl'" $databaseName

    # Update home
    mysql -e "UPDATE $table SET option_value = '$newHomeURL' WHERE option_name = 'home'" $databaseName
done

echo "All tables updated successfully."

Save the above script as update_wp_options.sh, make it executable with chmod +x update_wp_options.sh and run it with ./update_wp_options.sh.

Remember to back up your database before running the script, as it directly modifies the database, and any mistake could lead to data loss. This script also does not handle database errors, so if there’s a problem with a query, the script will move on to the next one.

If your MySQL/MariaDB client requires a password to connect, you may have to modify the script to include the password option -p in the MySQL command, but remember that this will prompt for the password for each table updated. For automatic scripts like this, it would be more secure and convenient to use MySQL’s password configuration file .my.cnf to automatically supply the password.

Will this script work if there are multiple blogs?

Yes, the script will do exactly that.

Here’s how it works:

  • The script starts by getting a list of all tables in the specified database that match the pattern wp_%_options, where the % is a wildcard that will match any sequence of characters. So this will match wp_1_options, wp_2_options, wp_3_options, …, wp_20_options, and any other tables that match the pattern.
  • It then loops over each of these tables one by one.
  • For each table, it executes two SQL UPDATE commands: one to update the siteurl option, and one to update the home option.
  • It uses the $table variable to specify which table to update, so each command will only affect the current table in the loop.

So as long as all your wp_{id}_options tables are in the specified database, and the database user has the necessary privileges. This script should update the siteurl and home options for all of them.

What if MySQL is running in Docker?

If your MySQL database is running inside a Docker container, you would need to connect to it using the Docker command.

The updated script will use the docker exec command to run the mysql commands inside your container. This command executes a command inside a running Docker container.

First, you need to find the name or ID of your running MySQL Docker container. You can do this by running the docker ps command which will list all running containers.

Once you have the container name or ID, replace yourDockerContainer in the script below with the name or ID of your running MySQL container:

#!/bin/bash

databaseName="yourDatabaseName"
newSiteURL="yourNewSiteURL"
newHomeURL="yourNewHomeURL"
containerName="yourDockerContainer"

# Get all wp_{id}_options tables
optionsTables=$(docker exec $containerName mysql -Nse "SHOW TABLES LIKE 'wp_%_options'" $databaseName)

for table in $optionsTables; do
    echo "Updating $table..."

    # Update siteurl
    docker exec $containerName mysql -e "UPDATE $table SET option_value = '$newSiteURL' WHERE option_name = 'siteurl'" $databaseName

    # Update home
    docker exec $containerName mysql -e "UPDATE $table SET option_value = '$newHomeURL' WHERE option_name = 'home'" $databaseName
done

echo "All tables updated successfully."

If your MySQL container requires a username and password to connect, you need to include the -u (for user) and -p (for password) flags in the mysql command, like so:

docker exec $containerName mysql -u yourUsername -p yourPassword -e "UPDATE $table SET option_value = '$newSiteURL' WHERE option_name = 'siteurl'" $databaseName

Again, replace yourUsername and yourPassword with your actual MySQL username and password.

Don’t forget to back up your database before running the script. If you’re running this in a production environment, it’s also a good idea to test it in a development or staging environment first.

Will this replace all the URLs needed?

No.

Despite implementing this script, it is essential to execute the WP-CLI command wp search-replace <old> <new>. During my experience, I encountered scenarios where the wp search-replace command did not execute as expected, even when supplemented with the --network or --all-tables flags. This led me to develop the script I’ve shared with you. 🙂

So, all together now
wp search-replace 'old.com' 'new.com' --network --all-tables