Proof of concept of script that does mysql dump of subset of Magento orders + related sales tables and customer's data of customer that did the orders. The script also attempts to import the data in local database. Currently the script gets last 1000 orders and customers data of customers that created the orders. Data anonymization is not implem…

dump-subset-of-magento-orders-and-customer-data.php

<?php
$mysqlCommandPath      = 'to be filled';
$mysqldumptCommandPath = 'to be filled';

$remoteDbUnsername = 'to be filled';
$remoteDbPassword  = 'to be filled';
$remotePort        = 'to be filled';
$remotHost         = 'to be filled';
$remoteDb          = 'to be filled';


$localDbUnsername = 'to be filled';
$localDbPassword  = 'to be filled';
$localDb          = 'to be filled';

$con = mysqli_connect($remotHost, $remoteDbUnsername, $remoteDbPassword, $remoteDb, $remotePort);

if (!$con)
{
    die('Could not connect: ' . mysqli_error($con));
}

// Fetch Order IDs
$query = "SELECT entity_id FROM `sales_flat_order` ORDER BY entity_id DESC LIMIT 0 , 1000";

$ordersData = mysqli_query($con, $query, 1);
$orderIds = array();

while($row = mysqli_fetch_array($ordersData, 1))
{
    $orderIds[] = $row['entity_id'];
}

$orderIdsString = implode(',', $orderIds);


// Fetch CreditMemo IDs
$query = "SELECT entity_id FROM `sales_flat_creditmemo` WHERE order_id IN( " . $orderIdsString . ")";

$creditMemoData = mysqli_query($con, $query, 1);
$creditMemoIds = array();

while($row = mysqli_fetch_array($creditMemoData, 1))
{
    $creditMemoIds[] = $row['entity_id'];
}

$creditMemoIdsString = implode(',', $creditMemoIds);


// Fetch Invoice IDs
$query = "SELECT entity_id FROM `sales_flat_invoice` WHERE order_id IN( " . $orderIdsString . ")";

$invoiceData = mysqli_query($con, $query, 1);
$invoiceDataIds = array();

while($row = mysqli_fetch_array($invoiceData, 1))
{
    $invoiceDataIds[] = $row['entity_id'];
}

$invoiceDataIdsString = implode(',', $invoiceDataIds);


// Fetch Shipment IDs
$query = "SELECT entity_id FROM `sales_flat_shipment` WHERE order_id IN( " . $orderIdsString . ")";

$shipmentData = mysqli_query($con, $query, 1);
$shipmentDataIds = array();

while($row = mysqli_fetch_array($shipmentData, 1))
{
    $shipmentDataIds[] = $row['entity_id'];
}

$shipmentDataIdsString = implode(',', $shipmentDataIds);

// Fetch Customer IDs
$query = "select customer_id from sales_flat_order where `entity_id` in (" . $orderIdsString  .") and customer_id is not null group by customer_id;";
$customerIdData = mysqli_query($con, $query, 1);

$customerIds = array();

while($row = mysqli_fetch_array($customerIdData, 1))
{
    $customerIds[] = $row['customer_id'];
}

$customerIdsString = implode(',', $customerIds);

// Fetch Customer Address IDs
$query = "select entity_id from customer_address_entity where `parent_id` in (" . $customerIdsString .  ");";
$customerAddressIdData = mysqli_query($con, $query, 1);

$customerAddressIds = array();

while($row = mysqli_fetch_array($customerAddressIdData, 1))
{
    $customerAddressIds[] = $row['entity_id'];
}

$customerAddressIdsString = implode(',', $customerAddressIds);

//print_r($orderIds);
//print_r($creditMemoIds);
//print_r($invoiceDataIds);
//print_r($invoiceDataIds);

mysqli_close($con);


//Tables list and where conditions for mysqldump
$dumpDefinitions = array(
    //Orders
    array(
        'table' => 'sales_flat_order',
        'where' => "entity_id IN({$orderIdsString})"
    ),
    array(
        'table' => 'sales_flat_order_address',
        'where' => "parent_id IN({$orderIdsString})"
    ),
    array(
        'table' => 'sales_flat_order_grid',
        'where' => "entity_id IN({$orderIdsString})"
    ),
    array(
        'table' => 'sales_flat_order_item',
        'where' => "order_id IN({$orderIdsString})"
    ),
    array(
        'table' => 'sales_flat_order_payment',
        'where' => "parent_id IN({$orderIdsString})"
    ),
    array(
        'table' => 'sales_flat_order_status_history',
        'where' => "parent_id IN({$orderIdsString})"
    ),
    //CreditMemo
    array(
        'table' => 'sales_flat_creditmemo',
        'where' => "entity_id IN({$creditMemoIdsString})"
    ),
    array(
        'table' => 'sales_flat_creditmemo_comment',
        'where' => "parent_id IN({$creditMemoIdsString})"
    ),
    array(
        'table' => 'sales_flat_creditmemo_grid',
        'where' => "entity_id IN({$creditMemoIdsString})"
    ),
    array(
        'table' => 'sales_flat_creditmemo_item',
        'where' => "parent_id IN({$creditMemoIdsString})"
    ),
    array(
        'table' => 'sales_flat_invoice',
        'where' => "entity_id IN({$invoiceDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_invoice_comment',
        'where' => "parent_id IN({$invoiceDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_invoice_grid',
        'where' => "entity_id IN({$invoiceDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_invoice_item',
        'where' => "parent_id IN({$invoiceDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_shipment',
        'where' => "entity_id IN({$shipmentDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_shipment_comment',
        'where' => "parent_id IN({$shipmentDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_shipment_grid',
        'where' => "entity_id IN({$shipmentDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_shipment_item',
        'where' => "parent_id IN({$shipmentDataIdsString})"
    ),
    array(
        'table' => 'sales_flat_shipment_track',
        'where' => "parent_id IN({$shipmentDataIdsString})"
    ),
    array(
        'table' => 'customer_entity',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_entity_datetime',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_entity_decimal',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_entity_int',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_entity_text',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_entity_varchar',
        'where' => "entity_id IN({$customerIdsString})"
    ),
    array(
        'table' => 'customer_address_entity',
        'where' => "entity_id IN({$customerAddressIdsString})"
    ),
    array(
        'table' => 'customer_address_entity_datetime',
        'where' => "entity_id IN({$customerAddressIdsString})"
    ),
    array(
        'table' => 'customer_address_entity_decimal',
        'where' => "entity_id IN({$customerAddressIdsString})"
    ),
    array(
        'table' => 'customer_address_entity_int',
        'where' => "entity_id IN({$customerAddressIdsString})"
    ),
    array(
        'table' => 'customer_address_entity_text',
        'where' => "entity_id IN({$customerAddressIdsString})"
    ),
    array(
        'table' => 'customer_address_entity_varchar',
        'where' => "entity_id IN({$customerAddressIdsString})"
    )
);

//Dumps the data
foreach ($dumpDefinitions as $definition) {
    echo 'Dumping table: ' .  $definition['table'] . PHP_EOL;
    shell_exec($mysqldumptCommandPath . ' -u ' . $remoteDbUnsername . ' -P ' . $remotePort . ' -h ' . $remotHost . ' -p' . $remoteDbPassword . ' --single-transaction --no-create-db --extended-insert --net_buffer_length=20000 --quick ' . $remoteDb . ' ' . $definition['table'] . ' --where "' . $definition['where'] . '" > test-dump/' . $definition['table'] . '.sql');
}

//Importing the data
foreach ($dumpDefinitions as $definition) {
    echo 'Importing table: ' .  $definition['table'] . PHP_EOL;
    shell_exec($mysqlCommandPath . ' -u ' . $localDbUnsername . ' -p' . $localDbPassword . ' ' . $localDb . ' < test-dump/' . $definition['table'] . '.sql');
}