Update domain on an existing wordpress db

change-domain.sh

#!/bin/bash

# Prompt for MySQL credentials
read -p "Enter MySQL username: " MYSQL_USER
read -sp "Enter MySQL password: " MYSQL_PASS
echo
read -p "Enter MySQL database name: " DB_NAME
read -p "Enter old domain (e.g., old.example.com): " OLD_DOMAIN
read -p "Enter new domain (e.g., new.example.com): " NEW_DOMAIN

# Define the MySQL command
MYSQL_CMD="mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${DB_NAME}"

# Create SQL script
SQL_SCRIPT=$(mktemp)

cat <<EOF > "${SQL_SCRIPT}"
SET @old_domain = '${OLD_DOMAIN}';
SET @new_domain = '${NEW_DOMAIN}';

-- Update siteurl and home in wp_options
UPDATE wp_options
SET option_value = REPLACE(option_value, @old_domain, @new_domain)
WHERE option_name IN ('siteurl', 'home');

-- Update post_content in wp_posts
UPDATE wp_posts
SET post_content = REPLACE(post_content, @old_domain, @new_domain);

-- Update meta_value in wp_postmeta
UPDATE wp_postmeta
SET meta_value = REPLACE(meta_value, @old_domain, @new_domain)
WHERE meta_value LIKE CONCAT('%', @old_domain, '%');

-- Update meta_value in wp_usermeta
UPDATE wp_usermeta
SET meta_value = REPLACE(meta_value, @old_domain, @new_domain)
WHERE meta_value LIKE CONCAT('%', @old_domain, '%');

-- Update comment_content in wp_comments
UPDATE wp_comments
SET comment_content = REPLACE(comment_content, @old_domain, @new_domain);

-- Update link_url in wp_links
UPDATE wp_links
SET link_url = REPLACE(link_url, @old_domain, @new_domain);

-- Update name in wp_terms (if necessary)
UPDATE wp_terms
SET name = REPLACE(name, @old_domain, @new_domain);
EOF

# Run the SQL script
echo "Executing SQL commands..."
mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" "${DB_NAME}" < "${SQL_SCRIPT}"

# Clean up
rm "${SQL_SCRIPT}"

echo "Domain update completed successfully."