Moving a WordPress database from one domain to another can be quite a challenging task. If you would like to move a copy of a WordPress database from a production environment to a local development environment for example, you would probably like to change the domain names used in that database.
The straight forward approach for simple installations would be to just create an SQL-dump of the database and then perform a replace on it. This requires the new domain name and current domain name to have the same length in order not to break PHP- and JSON-encoded data. However, if you have a multi-site or even a multi-network installation with parts of the same domain name used here and there, it immediately becomes a bit more complicated. Do I have to mention multi-byte characters?
In order for a database dump to be moved easily and at the same time change the domain name of any amount of domains in that database I wrote a PHP-script. This script actually connects to an existing DB and checks every column in every table and rewrites data according to a defined set of rewrite rules. It is actually not WordPress specific, so it can be used on any database to rewrite data.
It support rewriting of:
- Regular text data.
- PHP-encoded data.
- JSON-encoded data.
- Export a WordPress (or any other) database.
- Import the file into a new database.
- Add connection info and some rewrite rules to the PHP-file.
- Run the PHP-file.
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'my_new_db');
/** MySQL database username */
define('DB_USER', 'username');
/** MySQL database password */
define('DB_PASSWORD', 'password');
/** MySQL hostname */
define('DB_HOST', 'localhost');
/** Rewrites to perform */
$rewriter = new DatabaseRewrites( DB_HOST, DB_NAME, DB_USER, DB_PASSWORD );
$rewriter->addRewrite( 'blogg1.domain.com', 'blogg1.new-domain.local' );
$rewriter->addRewrite( 'blogg2.domain.com', 'blogg2.new-domain.local' );
$rewriter->setEchoProgress( true );
$rewriter->execute();
$rewriter = null;It is recommended that you run this file using PHP-CLI without a timeout set so you can follow progress and make sure the entire DB is rewritten in one execution. If it were to timeout and you run it again, no harm will be done unless you add conflicting rewrite rules.
The entire script can be copy/pasted from below:
<pre>
<?php
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'webartisan-se');
/** MySQL database username */
define('DB_USER', 'webartisan-se');
/** MySQL database password */
define('DB_PASSWORD', 'webartisan-se');
/** MySQL hostname */
define('DB_HOST', '127.0.0.1');
/** Rewrites to perform */
$rewriter = new DatabaseRewrites( DB_HOST, DB_NAME, DB_USER, DB_PASSWORD );
$rewriter->addRewrite( 'wordpress.wennerberg.biz', 'webartisan.hawe' );
$rewriter->setEchoProgress( true );
$rewriter->execute();
$rewriter = null;
class DatabaseRewrites {
private $columnTypes = array( 'VAR_STRING', 'STRING', 'BLOB' );
private $echoProgress = false;
private $rewrites = array();
private $db;
private $curTableName;
private $counter = array(
'tables' => 0,
'columns' => 0,
'cells' => 0
);
public function __construct( $dbHost, $dbName, $dbUser, $dbPass ) {
// Connect to database directly.
$this->db = new PDO( 'mysql:host=' . $dbHost . ';dbname=' . $dbName, $dbUser, $dbPass, array( PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8" ) );
}
public function __destruct() {
// Close connection to database.
$db = null;
}
public function addRewrite( $find, $replace ) {
$this->rewrites[] = array( 'find' => $find, 'replace' => $replace );
}
public function execute() {
// Rewrite all tables.
$this->rewriteTables();
if ( $this->echoProgress === true ) {
echo "n";
echo "Parsed {$this->counter['tables']} tables.n";
echo "Parsed {$this->counter['columns']} columns.n";
echo "Updated {$this->counter['cells']} cells.n";
}
}
private function getWhere( &$column ) {
$find = array();
foreach ( $this->rewrites as $rewrite ) {
$find[] = $column['name'] . " LIKE '%" . $rewrite['find'] . "%'";
}
return 'WHERE ' . implode( ' OR ', $find );
}
private function nestedReplace( &$array, $find, $replace ) {
if ( !is_object( $array ) && gettype( $array ) === 'object' ) {
return $array;
}
if ( is_array( $array ) || is_object( $array ) ) {
foreach ( $array as $key => &$value ) {
if ( !is_object( $array ) && gettype( $value ) === 'object' ) {
continue;
}
if ( is_array( $value ) === true ) {
$this->nestedReplace( $value, $find, $replace );
} else {
$value = str_replace( $find, $replace, $value );
}
}
}
return $array;
}
private function rewriteColumn( &$column, &$primaryKey ) {
if ( is_null( $primaryKey ) === true ) {
$primaryKey = $column;
}
$where = $this->getWhere( $column );
$rows = $this->db->prepare( "SELECT {$primaryKey['name']}, {$column['name']} FROM {$column['table']} {$where}" );
$rows->execute();
if ( $rows->rowCount() > 0 ) {
while ( $row = $rows->fetch() ) {
$value = $row[1];
$unserialized = @unserialize( $row[1] );
$jsonDecoded = json_decode( $row[1], true );
if ( $unserialized !== false ) {
// Serialized data.
foreach ( $this->rewrites as $rewrite ) {
$unserialized = $this->nestedReplace( $unserialized, $rewrite['find'], $rewrite['replace'] );
}
$value = serialize( $unserialized );
} else if ( is_null( $jsonDecoded ) !== true ) {
// JSON data.
foreach ( $this->rewrites as $rewrite ) {
$jsonDecoded = $this->nestedReplace( $jsonDecoded, $rewrite['find'], $rewrite['replace'] );
}
$value = json_encode( $jsonDecoded );
} else {
// Non-encoded data.
foreach ( $this->rewrites as $rewrite ) {
$value = str_replace( $rewrite['find'], $rewrite['replace'], $value );
}
}
if ( in_array( $primaryKey['native_type'], $this->columnTypes ) === true ) {
$updateWhere = "WHERE {$primaryKey['name']} = '" . mysql_escape_string( $row[0] ) . "'";
} else {
$updateWhere = "WHERE {$primaryKey['name']} = " . mysql_escape_string( $row[0] );
}
$this->counter['cells']++;
$this->db->query( "UPDATE {$column['table']} SET {$column['name']} = '" . mysql_escape_string( $value ) . "' " . $updateWhere . "n" );
}
}
$rows = null;
}
public function rewriteTable( &$tableName ) {
$this->curTableName = $tableName;
$columns = $this->db->prepare( "SELECT * FROM {$tableName} LIMIT 0" );
$columns->execute();
$columnMeta = array();
$primaryKey = null;
for ($columnIndex = 0; $columnIndex < $columns->columnCount(); $columnIndex++ )
{
$meta = array();
$meta = $columns->getColumnMeta( $columnIndex );
if ( isset( $meta['native_type'] ) === true && in_array( $meta['native_type'], $this->columnTypes ) === true ) {
$columnMeta[] = $meta;
} elseif ( in_array( 'primary_key', $meta['flags'] ) === true && is_null($primaryKey) === true ) {
$primaryKey = $meta;
}
}
foreach ( $columnMeta as $column ) {
$this->counter['columns']++;
$this->rewriteColumn( $column, $primaryKey );
if ( $this->echoProgress === true ) echo ".";
}
}
private function rewriteTables() {
$tables = $this->db->prepare( 'SHOW TABLES');
$tables->execute();
while ( $row = $tables->fetch() ) {
$this->counter['tables']++;
if ( $this->echoProgress === true ) echo "Rewriting table {$row[0]} (";
$this->rewriteTable( $row[0] );
if ( $this->echoProgress === true ) echo ") done.n";
}
}
public function setEchoProgress( $value ) {
if ( is_bool( $value ) === true ) {
$this->echoProgress = $value;
}
}
}
?>
</pre>