MySQLdump to CSV

There are situations when we need to migrate data from one database to another. For example, I’d some unit tests for which I need to use SQLite with minimal data set replicated from production DB. Loading fixtures was not that easy option due to some chained interdependent calls.

There are many open sourced available scripts which converts MySQL data to SQLite compatible data. But there are some common issues with most of them

    1. Script assumes that all the data from table needs to be migrated. That’s not practical enough when you have a table with million of rows.
    2. Script assumes primary key of the table is auto-incremented. It’s not the case always!

So I took the most convenient option- dump the minimal set of data into CSV and then load the same to SQLite. This approach is practical and suitable enough to work in any situations, irrespective of the databases e.g. MySQL, Oracle or SQLite.

<?php

$exportData = [
    'db0' => ['table0', 'table1'],
    'db1' => ['table0']
];

$user = 'test';
$password = 'test123';
$path = '/tmp';

foreach ($exportData as $db => $tables) {
    foreach ($tables as $table) {
        $csvFile = $path . '/' . $db . '.' . $table . '.csv';

        $command = 'mysql -u ' . $user . ' -p' . $password . ' -B -e "select * from ' . $db . '.' . $table . ' limit 10;"';
        /*Note: Change the separator as required; ',' is not suitable always*/
        $command .= '| sed "s/\'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > ' . $csvFile;  //this regex is taken from stackoverflow; can't take its credit
        shell_exec($command);

        if (file_exists($csvFile)) {    //remove column header
            $command = "sed -i 1d $csvFile";
            shell_exec($command);
        }

        echo "Created $csvFile\n";
    }
}

So CSV is there. Now it’s easy enough to load into any database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s