Search and replace values in serialized arrays in specific tables. Thanks to http://interconnectit.com/124/search-and-replace-for-wordpress-databases/ for sharing.

Serial Array Search and Replace WordPress plugin

<?php
/*
Plugin Name: Pew Serial Array Search and Replace
Description: Search and replace values in serialized arrays in specific tables. Thanks to http://interconnectit.com/124/search-and-replace-for-wordpress-databases/ for sharing.
Version: 1.0
Author: Russell Heimlich
Author URI: http://www.russellheimlich.com
*/

/********************/
/* Helper functions */
/********************/

function icit_srdb_form_action( ) {
  $step = isset( $_REQUEST[ 'step' ] ) ? intval( $_REQUEST[ 'step' ] ) : 1;
	echo '?page=array-search-replace&nonce=' . wp_create_nonce('array-search-replace') . '&step=' . intval( $step + 1 );
}

function icit_srdb_submit( $text = 'Submit', $warning = '' ){
	$warning = str_replace( "'", "\'", $warning ); ?>
	<input type="submit" class="button" value="<?php echo htmlentities( $text, ENT_QUOTES, 'UTF-8' ); ?>" <?php echo ! empty( $warning ) ? 'onclick="if (confirm(\'' . htmlentities( $warning, ENT_QUOTES, 'UTF-8' ) . '\')){return true;}return false;"' : ''; ?>/> <?php
}

function esc_html_attr( $string = '', $echo = false ){
	$output = htmlentities( $string, ENT_QUOTES, 'UTF-8' );
	if ( $echo )
		echo $output;
	else
		return $output;
}

function recursive_array_replace( $find, $replace, &$data ) {
    if ( is_array( $data ) ) {
        foreach ( $data as $key => $value ) {
            if ( is_array( $value ) ) {
                recursive_array_replace( $find, $replace, $data[ $key ] );
            } else {
                // have to check if it's string to ensure no switching to string for booleans/numbers/nulls - don't need any nasty conversions
                if ( is_string( $value ) )
					$data[ $key ] = str_replace( $find, $replace, $value );
            }
        }
    } else {
        if ( is_string( $data ) )
			$data = str_replace( $find, $replace, $data );
    }
}


function recursive_unserialise_replace( $from = '', $to = '', $data = '', $serialised = false ) {

	if ( is_string( $data ) && ( $unserialised = @unserialize( $data ) ) !== false ) {
		$data = recursive_unserialise_replace( $from, $to, $unserialised, true );
	}

	elseif ( is_array( $data ) ) {
		$_tmp = array( );
		foreach ( $data as $key => $value ) {
			$_tmp[ $key ] = recursive_unserialise_replace( $from, $to, $value, false );
		}

		$data = $_tmp;
		unset( $_tmp );
	}

	else {
		if ( is_string( $data ) )
			$data = str_replace( $from, $to, $data );
	}

	if ( $serialised )
		return serialize( $data );

	return $data;
}

function icit_srdb_replacer( &$connection, $db = '', $search = '', $replace = '', $tables = array( ) ) {

	$report = array( 'tables' => 0,
					 'rows' => 0,
					 'change' => 0,
					 'updates' => 0,
					 'start' => microtime( ),
					 'end' => microtime( ),
					 'errors' => array( ),
					 );

	if ( is_array( $tables ) && ! empty( $tables ) ) {
		foreach( $tables as $table ) {
			$report[ 'tables' ]++;

			$columns = array( );

			// Get a lit of columns in this table
		    $fields = mysql_db_query( $db, 'DESCRIBE ' . $table, $connection );
			while( $column = mysql_fetch_array( $fields ) )
				$columns[ $column[ 'Field' ] ] = $column[ 'Key' ] == 'PRI' ? true : false;

			// Count the number of rows we have in the table if large we'll split into blocks, This is a mod from Simon Wheatley
			$row_count = mysql_db_query( $db, 'SELECT COUNT(*) FROM ' . $table, $connection );
			$rows_result = mysql_fetch_array( $row_count );
			$row_count = $rows_result[ 0 ];
			if ( $row_count == 0 )
				continue;

			$page_size = 50000;
			$pages = ceil( $row_count / $page_size );

			for( $page = 0; $page < $pages; $page++ ) {

				$current_row = 0;
				$start = $page * $page_size;
				$end = $start + $page_size;
				// Grab the content of the table
				$data = mysql_db_query( $db, sprintf( 'SELECT * FROM %s LIMIT %d, %d', $table, $start, $end ), $connection );

				if ( ! $data )
					$report[ 'errors' ][] = mysql_error( );

				while ( $row = mysql_fetch_array( $data ) ) {

					$report[ 'rows' ]++; // Increment the row counter
					$current_row++;

					$update_sql = array( );
					$where_sql = array( );
					$upd = false;

					foreach( $columns as $column => $primary_key ) {
						$edited_data = $data_to_fix = $row[ $column ];

						// Run a search replace on the data that'll respect the serialisation.
						$edited_data = recursive_unserialise_replace( $search, $replace, $data_to_fix );

						// Something was changed
						if ( $edited_data != $data_to_fix ) {
							$report[ 'change' ]++;
							$update_sql[] = $column . ' = "' . mysql_real_escape_string( $edited_data ) . '"';
							$upd = true;
						}

						if ( $primary_key )
							$where_sql[] = $column . ' = "' . mysql_real_escape_string( $data_to_fix ) . '"';
					}

					if ( $upd && ! empty( $where_sql ) ) {
						$sql = 'UPDATE ' . $table . ' SET ' . implode( ', ', $update_sql ) . ' WHERE ' . implode( ' AND ', array_filter( $where_sql ) );
						$result = mysql_db_query( $db, $sql, $connection );
						if ( ! $result )
							$report[ 'errors' ][] = mysql_error( );
						else
							$report[ 'updates' ]++;

					} elseif ( $upd ) {
						$report[ 'errors' ][] = sprintf( '"%s" has no primary key, manual change needed on row %s.', $table, $current_row );
					}

				}
			}
		}

	}
	$report[ 'end' ] = microtime( );

	return $report;
}

/*****************/
/* Main Function */
/*****************/
add_action('admin_menu', 'serial_array_search_and_replace_menu');
function serial_array_search_and_replace_menu() {
	add_submenu_page( 'tools.php', 'Serial Array Search Replace', 'Array Search Replace', 'install_plugins', 'array-search-replace', 'serial_array_search_and_replace');
}

function serial_array_search_and_replace() {

/*
 Check and clean all vars, change the step we're at depending on the quality of
 the vars.
*/
$errors = array( );
$step = isset( $_REQUEST[ 'step' ] ) ? intval( $_REQUEST[ 'step' ] ) : 1; // Set the step to the request, we'll change it as we need to.

if( $step >= 2 ) {
	if ( !wp_verify_nonce($_REQUEST['nonce'], 'array-search-replace') ) {
		$errors[] = 'Security problem: Couldn\'t verify the request (Nonce didn\'t validate)';
	}
}

// Search replace details
$srch = isset( $_POST[ 'srch' ] ) ? stripcslashes( $_POST[ 'srch' ] ) : '';
$rplc = isset( $_POST[ 'rplc' ] ) ? stripcslashes( $_POST[ 'rplc' ] ) : '';
// Tables to scanned
$tables = isset( $_POST[ 'tables' ] ) && is_array( $_POST[ 'tables' ] ) ? array_map( 'stripcslashes', $_POST[ 'tables' ] ) : array( );

// Check the db connection else go back to step two.
	global $wpdb;
	$connection = mysql_connect( $wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword );
	if ( ! $connection ) {
		$errors[] = mysql_error( );
		$step = 1;
	} else {
		// Do we have any tables and if so build the all tables array
		$all_tables = array( );
		$all_tables_mysql = mysql_db_query( $wpdb->dbname, 'SHOW TABLES', $connection );
		if ( ! $all_tables_mysql ) {
			$errors[] = mysql_error( );
			$step = 2;
		} else {
			while ( $table = mysql_fetch_array( $all_tables_mysql ) ) {
				$all_tables[] = $table[ 0 ];
			}
		}
	}

// Check and clean the tables array
$selected_tables = array();
foreach( $tables as $table ) {
	if( in_array( $table, $all_tables ) ) {
		$selected_tables[] = $table;
	}
}

if ( $step >= 2 && empty( $selected_tables ) ) {
	$errors[] = 'You didn\'t select any tables.';
	$step = 1;
}

// Make sure we're searching for something.
if ( $step >= 3 ) {
	if ( empty( $srch ) ) {
		$errors[] = 'Missing search string.';
		$step = 2;
	}

	if ( empty( $rplc ) ) {
		$errors[] = 'Replace string is blank.';
		$step = 2;
	}

	if ( ! ( empty( $rplc ) && empty( $srch ) ) && $rplc == $srch ) {
		$errors[] = 'Search and replace are the same, please check your values.';
		$step = 2;
	}
}


/*
 Send the HTML to the screen.
*/
?>
<style>
#tables {
overflow:auto;
height:20em;
width:500px;
margin-bottom:1em;
}
#tables label {
display:block;
cursor:pointer;
padding:0.15em 0;
}
#tables label input {
margin-right:0.5em;
}
</style>
<div id="container"><?php
		if ( ! empty( $errors ) && is_array( $errors ) ) {
			echo '<div class="error">';
			foreach( $errors as $error )
				echo '<p>' . $error . '</p>';
			echo '</div>';
		}?>


	<h1>Safe Search Replace</h1>
	<?php
/*
 The bit that does all the work.
*/
switch ( $step ) {
	case 1:
		// Ask which tables to deal with ?>
		<h2>Which tables do you want to scan?</h2>
		<form action="<?php icit_srdb_form_action( ); ?>" method="post">

			<fieldset>
            		<label for="keyword_filter">Filter tables by keyword</label>
                    <input id="keyword_filter" name="keyword_filter" type="text">
                    <div id="tables">
					<?php foreach( $all_tables as $table ) { ?>
						<label><input type="checkbox" name="tables[] "value="<?=esc_html_attr( $table );?>"><?=$table?></label>
					<?php } ?>
                    </div>
				<?php icit_srdb_submit( 'Continue' );	?>
			</fieldset>
		</form>
		<script type="text/javascript">
        jQuery(document).ready(function($) {
			$("#keyword_filter").keyup(function () {
        		var filter = $(this).val(), count = 0;
        		$("#tables label").each(function () {
            		if ($(this).text().search(new RegExp(filter, "i")) < 0) {
                		$(this).hide();
            		} else {
                		$(this).show();
                		count++;
            		}
        		});
			});
		});
        </script>
		<?php
		break;


	case 2:
		// Ask for the search replace strings. ?>
		<h2>What to replace?</h2>
		<form action="<?php icit_srdb_form_action( ); ?>" method="post">
			<fieldset>
			<?php
				foreach( $tables as $i => $tab ) {
					printf( '<input type="hidden" name="tables[%s]" value="%s" />', esc_html_attr( $i, false ), esc_html_attr( $tab, false ) );
				} ?>

				<p>
					<label for="srch">Search for (case sensitive string):</label>
					<input class="text" type="text" name="srch" id="srch" value="<?php esc_html_attr( $srch, true ) ?>" />
				</p>

				<p>
					<label for="rplc">Replace with:</label>
					<input class="text" type="text" name="rplc" id="rplc" value="<?php esc_html_attr( $rplc, true ) ?>" />
				</p>

				<?php icit_srdb_submit( 'Submit Search and Replace', 'Are you REALLY sure you want to go ahead and do this?' ); ?>
			</fieldset>
		</form>	<?php
		break;


	case 3:

		@ set_time_limit( 60 * 10 );
		// Try to push the allowed memory up, while we're at it
		@ ini_set( 'memory_limit', '1024M' );

		// Process the tables
		if ( isset( $connection ) ) {
			$report = icit_srdb_replacer( $connection, $wpdb->dbname, $srch, $rplc, $tables );
		}

		// Output any errors encountered during the db work.
		if ( ! empty( $report[ 'errors' ] ) && is_array( $report[ 'errors' ] ) ) {
			echo '<div class="error">';
			foreach( $report[ 'errors' ] as $error )
				echo '<p>' . $error . '</p>';
			echo '</div>';
		}

		// Calc the time taken.
		$time = array_sum( explode( ' ', $report[ 'end' ] ) ) - array_sum( explode( ' ', $report[ 'start' ] ) ); ?>

		<h2>Completed</h2>
		<p><?php printf( 'In the process of replacing <strong>"%s"</strong> with <strong>"%s"</strong> we scanned <strong>%d</strong> tables with a total of <strong>%d</strong> rows, <strong>%d</strong> cells were changed and <strong>%d</strong> db update performed and it all took <strong>%f</strong> seconds.', $srch, $rplc, $report[ 'tables' ], $report[ 'rows' ], $report[ 'change' ], $report[ 'updates' ], $time ); ?></p> <?php
		break;


	default: ?>
		<h2>No idea how we got here.</h2>
		<p>Something strange has happened.</p> <?php
		break;
}

if ( isset( $connection ) && $connection ) {
	mysql_close( $connection );
}


// Warn if we're running in safe mode as we'll probably time out.
if ( ini_get( 'safe_mode' ) ) {
	echo '<h4>Warning</h4>';
	printf( '<p style="color:red;">Safe mode is on so you may run into problems if it takes longer than %s seconds to process your request.</p>', ini_get( 'max_execution_time' ) );
}
/*
 Close out the html and exit.
*/ ?>
		<div class="help">
			<p>This developer/sysadmin tool helps solve the problem of doing a search and replace on a
			WordPress site when doing a migration to a domain name with a different length.</p>

			<p style="color:red"><strong>WARNING!</strong> Take a backup first, and carefully test the results of this code.
			If you don't, and you vape your data then you only have yourself to blame.
			Seriously.  And if you're English is bad and you don't fully understand the
			instructions then STOP.  Right there.  Yes.  Before you do any damage.</p>
	</div>
<?php } ?>