How to safely migrate data between databases in MySQL with PHP

2012-07-13 How to safely migrate data between databases in MySQL with PHP

I saw many people using lots of complex methods in attempt to move data between databases and file systems.

One of the most frequent methods is storing sequential data records in CSV, TSV, XML, or other formats. Obviously, each syntax requires specific escaping algorithms, a fact that most of times is omitted or forgotten by developer.

The last time I had to move data between two different instances of MySQL from two different sites I followed the approach:

Select the database records and put them in an object (array);
serialize the array;
encode the array in base64 to ensure the text needs no escaping algorithm;
copy the text on a support (file, ftp, mail, etc.);
decode the array from base64 to native format;
deserialize the array;
take each record and save it to the database.

Some of the advantages are:

you don't transfer database records, but business objects;
if you have complex relations or fields, they just get wrapped, irrelevant on the database representation.

A code example is in sample.php.

And the result will be similar to sample.txt.

sample.php

<?php $dbConnection=BO::getConnection();
$recordSet=$dbConnection->SELECT('select timeStamp, uuid, author from articleComment where oid<3');
echo("<p/>var_dump: ");
var_dump($recordSet);
$serialization=serialize($recordSet);
echo("<p/>Serialization: $serialization");
$base64=base64_encode($serialization);
echo("<p/>Base 64 final result: $base64");
echo("<p/>Deserialization stage - running on the target machine");
$decodedValue=base64_decode($base64);
echo("<p/>Decoded string: $decodedValue");
$deserializedObject=unserialize($decodedValue);
echo("<p/>Deserialized object: ");
var_dump($deserializedObject);

sample.txt

var_dump: array(2) { [0]=> array(3) { ["timeStamp"]=> string(12) "120704214131" ["uuid"]=> string(36) "bb9ba0c8-61e6-4eef-826c-a8f306eec4d1" ["author"]=> string(1) "1" } [1]=> array(3) { ["timeStamp"]=> string(12) "120704214635" ["uuid"]=> string(36) "d4fb5320-4b01-4c92-b58f-65555dc8387d" ["author"]=> string(1) "1" } }

Serialization: a:2:{i:0;a:3:{s:9:"timeStamp";s:12:"120704214131";s:4:"uuid";s:36:"bb9ba0c8-61e6-4eef-826c-a8f306eec4d1";s:6:"author";s:1:"1";}i:1;a:3:{s:9:"timeStamp";s:12:"120704214635";s:4:"uuid";s:36:"d4fb5320-4b01-4c92-b58f-65555dc8387d";s:6:"author";s:1:"1";}}

Base 64 final result: YToyOntpOjA7YTozOntzOjk6InRpbWVTdGFtcCI7czoxMjoiMTIwNzA0MjE0MTMxIjtzOjQ6InV1aWQiO3M6MzY6ImJiOWJhMGM4LTYxZTYtNGVlZi04MjZjLWE4ZjMwNmVlYzRkMSI7czo2OiJhdXRob3IiO3M6MToiMSI7fWk6MTthOjM6e3M6OToidGltZVN0YW1wIjtzOjEyOiIxMjA3MDQyMTQ2MzUiO3M6NDoidXVpZCI7czozNjoiZDRmYjUzMjAtNGIwMS00YzkyLWI1OGYtNjU1NTVkYzgzODdkIjtzOjY6ImF1dGhvciI7czoxOiIxIjt9fQ==

Deserialization stage - running on the target machine

Decoded string: a:2:{i:0;a:3:{s:9:"timeStamp";s:12:"120704214131";s:4:"uuid";s:36:"bb9ba0c8-61e6-4eef-826c-a8f306eec4d1";s:6:"author";s:1:"1";}i:1;a:3:{s:9:"timeStamp";s:12:"120704214635";s:4:"uuid";s:36:"d4fb5320-4b01-4c92-b58f-65555dc8387d";s:6:"author";s:1:"1";}}

Deserialized object: array(2) { [0]=> array(3) { ["timeStamp"]=> string(12) "120704214131" ["uuid"]=> string(36) "bb9ba0c8-61e6-4eef-826c-a8f306eec4d1" ["author"]=> string(1) "1" } [1]=> array(3) { ["timeStamp"]=> string(12) "120704214635" ["uuid"]=> string(36) "d4fb5320-4b01-4c92-b58f-65555dc8387d" ["author"]=> string(1) "1" } }