Holy permanent domain names Batman!

Once in a while, I discover something about wordpress that makes me wonder how it ever got so popular. The most recent discovery is that wordpress stores your site’s domain name into the mysql database. Worst of all, it doesn’t just store it in one place but litters it throughout several tables and records. And of course wordpress does not come with any way to migrate the site to a different domain name.

This is a problem because I want to be able to setup a pre-production site on a pre-production domain and then move it to a production domain once it’s ready.  For example, I recently wanted move this blog to wordpress 3.2 but I wanted to see it in action on my test url first.

After doing some research, there were no official list of where the domain name may be stored and there were just too many tables and records to look through manually.  In addition, I knew I had to change the domain a few times so I put in some time and wrote a php script to look through all the tables, records, fields, etc for the existing domain name.  When it finds one in a particular field, it updates the value to the new domain.

<?php

$longopts = array(
  "db_name:",
  "db_user:",
  "db_pw:",
  "find_string:",
  "replace_string:",
  "replace_guid_field"
);

$options = getopt("", $longopts);
print "Found arguments:\n";
print_r($options);

$database = $options["db_name"];
$database_user = $options["db_user"];
$database_pw = $options["db_pw"];
$find_string = $options["find_string"];
$replace_string = $options["replace_string"];
$replace_guid = array_key_exists("replace_guid_field", $options);

$link = mysql_connect("localhost", $database_user, $database_pw);
mysql_select_db($database);

$query = "SHOW TABLES FROM $database";
$result = mysql_query($query);

while ($arr = mysql_fetch_array($result, MYSQL_NUM)) {
  foreach ($arr as $table) {
    process_table($table, $find_string, $replace_string, $replace_guid);
  }
}
mysql_close($link);

print "done";

function process_table($table, $find_string, $replace_string, $replace_guid) {
  print "\nprocessing $table....";

  $field_arr = array();
  $query = "SELECT * FROM $table";
  $result = mysql_query($query);
  while ($row_arr = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($row_arr as $key => $value) {
      if (strpos($value, $find_string) !== false) {
        // Create a list of fields which contain the found string.
        $field_arr[$key] = true;
        //print_r($row_arr);
      }
    }
  }

  if (count($field_arr) == 0) {
    print " string not found.";
  } else {
    print " string found in field(s): " . implode(', ', array_keys($field_arr)) . "\n";
    foreach (array_keys($field_arr) as $field) {
      if ($field != "guid") {
        $query = "UPDATE $table SET $field = replace($field, '$find_string', '$replace_string') WHERE $field LIKE '%$find_string%'";
        update_table($query);
      } else {
        if ($replace_guid) {
          $query = "UPDATE $table SET $field = replace($field, '$find_string', '$replace_string') WHERE $field LIKE '%$find_string%'";
          update_table($query);
        } else {
          print "\nString found in guid field but not replacing. Override this by using the --replace_guid_field flag.\n";
        }
      }
    }
  }
}

function update_table($query) {
  print "\n$query\n";
  $result = mysql_query($query);
  $num_updated = mysql_affected_rows();
  print "... updated $num_updated records\n";
}
?>

The script is designed to be run from the command line.  Run the script by passing in the following options:

php change-domain.php --db_name=<your_db_name> --db_user=<db_login> --db_pw=<db_password> --find_string=<original_domain> --replace_string=<new_domain> --replace_guid_field

You can download the script but use at your own risk. If you do use it, I suggest you backup your database first.

Your php may not have the command line interpreter installed.  You’ll need the php5-cli package in order to run php from the shell.

apt-get install php5-cli

>> dpkg --list |grep php
ii  libapache2-mod-php5              5.3.2-1ubuntu4.9                  server-side, HTML-embedded scripting languag
ii  php5-cli                         5.3.2-1ubuntu4.9                  command-line interpreter for the php5 script
ii  php5-common                      5.3.2-1ubuntu4.9                  Common files for packages built from the php
ii  php5-gd                          5.3.2-1ubuntu4.9                  GD module for php5
ii  php5-mysql                       5.3.2-1ubuntu4.9                  MySQL module for php5