MySQL – Page 2 – Nicovs' Blog

Migrating wordpress sites to another host

When migrating wordpress sites, from one server/host to another… you might encounter some problems with 2 things:

Absolute vs Relative paths for e.g. images
Serialized data.

This is how to fix both:

Absolute paths: can be fixed by my previous post here

Serialized Data: by modifying your absolute paths for URL’s of Image paths, your serialized data is no longer valid… I have found this script to be very usefull to fix the serialized data: BangHeadOnWall

How to use fix_serialization.php:

1. Download the script on the machine that has the db you need to fix
2. Change a couple of things in the script:

‘db1′ to the actual database name
username
password
and maybe the table/column and index column (e.g. when using multisite)

3. type ‘php fix_serialization.php’ on the command line

The only real reason why I need to run this is because migrating the site can change the tite’s url length or absolute image paths. For example ‘hard coded’ paths to images in some plugins:

'/var/www/vhosts/nicovs.be/httpdocs' -> '/var/www/nicovs/be/wwwroot'

1	'/var/www/vhosts/nicovs.be/httpdocs' -> '/var/www/nicovs/be/wwwroot'

The length of this string changes, and therefor the serialized string in the wp_options (or any other table using serialized string with url/data paths) is not longer valid.

The script goes through all that data and corrects the math in those couple of places automagically and you’re back online!

Mysql: Replacing string in all tables of a database

Yes, of course you can replace a string in all fields of a specific table in a specific databases by using the REPLACE function.
No, you can not easily do this on an entire database… you have to script this.

I have found a Brilliant script @BrillianSheep.com which I modified a little bit for readability…

Copy/Paste the script in a php file, replace parameters (strings to replace: yourDbUser, dbP4sswd and yourDbName and run it on your server!

<!--?php     // Setup the associative array for replacing the old string with new string     $replace_array = array( '/var/www/vhosts/' => '/var/www/wwwroot', '/home/username/user1' => '/home/user1');

    $mysql_link = mysql_connect( 'localhost', 'yourDbUser', 'dbP4sswd' );
    if( ! $mysql_link) {
        die( 'Could not connect: ' . mysql_error() );
    }

    $mysql_db = mysql_select_db( 'yourDbName', $mysql_link );
    if(! $mysql_db ) {
        die( 'Can\'t select database: ' . mysql_error() );
    }

    // Traverse all tables
    $tables_query = 'SHOW TABLES';
    $tables_result = mysql_query( $tables_query );
    while( $tables_rows = mysql_fetch_row( $tables_result ) ) {
        foreach( $tables_rows as $table ) {

            // Traverse all columns
            $columns_query = 'SHOW COLUMNS FROM ' . $table;
            $columns_result = mysql_query( $columns_query );
            while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {

                $column = $columns_row['Field'];
                $type = $columns_row['Type'];

                // Process only text-based columns
                if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {
                    // Process all replacements for the specific column
                    foreach( $replace_array as $old_string => $new_string ) {
                        $replace_query = 'UPDATE ' . $table .
                            ' SET ' .  $column . ' = REPLACE(' . $column .
                            ', \'' . $old_string . '\', \'' . $new_string . '\')';
                        mysql_query( $replace_query );
                    }
                }
            }
        }
    }

    mysql_free_result( $columns_result );
    mysql_free_result( $tables_result );
    mysql_close( $mysql_link );

    echo 'Done!
';

?>

<!--?php // Setup the associative array for replacing the old string with new string $replace_array = array( '/var/www/vhosts/' => '/var/www/wwwroot', '/home/username/user1' => '/home/user1');

$mysql_link = mysql_connect( 'localhost', 'yourDbUser', 'dbP4sswd' );

if( ! $mysql_link) {

die( 'Could not connect: ' . mysql_error() );

}

$mysql_db = mysql_select_db( 'yourDbName', $mysql_link );

if(! $mysql_db ) {

die( 'Can\'t select database: ' . mysql_error() );

}

// Traverse all tables

$tables_query = 'SHOW TABLES';

$tables_result = mysql_query( $tables_query );

while( $tables_rows = mysql_fetch_row( $tables_result ) ) {

foreach( $tables_rows as $table ) {

// Traverse all columns

$columns_query = 'SHOW COLUMNS FROM ' . $table;

$columns_result = mysql_query( $columns_query );

while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {

$column = $columns_row['Field'];

$type = $columns_row['Type'];

// Process only text-based columns

if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {

// Process all replacements for the specific column

foreach( $replace_array as $old_string => $new_string ) {

$replace_query = 'UPDATE ' . $table .

' SET ' . $column . ' = REPLACE(' . $column .

', \'' . $old_string . '\', \'' . $new_string . '\')';

mysql_query( $replace_query );

}

mysql_free_result( $columns_result );

mysql_free_result( $tables_result );

mysql_close( $mysql_link );

echo 'Done!

–>

Calculate of view the MySQL Data table size

Wondering which tables are big in your MySQL database?
Plain and simple: run the following query on your database to calculate the table size in MegaBytes (MB) for your select MySQL database:

 select table_name, round(((data_length + index_length) / (1024*1024)),2) as 'size in megs' from information_schema.tables where table_schema = 'db_name';

1	select table_name, round(((data_length + index_length) / (1024*1024)),2) as 'size in megs' from information_schema.tables where table_schema = 'db_name';