Backup utilities for Magento

backup-orders.php

<?php
if(count($argv) < 4){
    echo "No database credentials supplied\n";
    exit(1);
}

$username = $argv[1];
$password = $argv[2];
$db = $argv[3];

$dbo = new PDO("mysql:host=localhost;dbname=$db", $username, $password);
$table_prefixes = array('customer_entity%', 'customer_address%', 'sales_flat%', 'm2epro%', 'paypal%', 'sagepay%');

$backupCommand = "/usr/bin/mysqldump -u '$username' -p'$password' --opt --compact --add-drop-table --skip-lock-tables --single-transaction --tz-utc"
    . " $db ";

$table_names = array();
foreach($table_prefixes as $prefix){
    $tables_query = $dbo->prepare("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database() AND TABLE_NAME LIKE :prefix");
    $res = $tables_query->execute(array(':prefix' => $prefix));
    if($res === false){
        echo "Warning: Query for tables matching prefix $prefix failed.\n";
        echo "Error code: " . errorMsg($res) . "\n";
        continue;
    }
    $table_names = array_merge($table_names, $tables_query->fetchAll(PDO::FETCH_COLUMN, 0));
}
$backupCommand .= implode(" ", array_unique($table_names));
$backupCommand .= " > /root/cutomersandorders.sql";
echo "Command is: " . $backupCommand . "\n";
echo shell_exec($backupCommand);
exit(0);

function errorMsg($pdoStatement){
    $a = $pdoStatement->errorInfo();
    return $a[2];
}

fix-table-collation.php

<?php
if(count($argv) != 4){
    echo "No database credentials supplied\n";
    exit(1);
}

$username = $argv[1];
$password = $argv[2];
$db = $argv[3];
$dbo = new PDO("mysql:host=localhost;dbname=$db", $username, $password);

$alterDbRes = $dbo->exec("ALTER DATABASE $db CHARACTER SET utf8 COLLATE utf8_general_ci");
if($alterDbRes === false){
    echo "Failed to change Database collation\n";
    exit(1);
}

$tables_query = $dbo->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_COLLATION = 'utf8_unicode_ci'");
if($tables_query === false){
    echo "Failed to query Information Schema for tables matching utf8_unicode_ci\n";
    exit(1);
}
foreach($tables_query->fetchAll(PDO::FETCH_COLUMN, 0) as $table){
    $alterRes = $dbo->exec("ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    if($alterRes === false){
        echo "Warning: Failed to change collation of table: $table\n";
    } else {
        echo "Altered table $table successfully\n";
    }
}
exit(0);

partial-backup-tk1.php

<?php
if(count($argv) < 4){
    echo "No database credentials supplied\n";
    exit(1);
}

$username = $argv[1];
$password = $argv[2];
$db = $argv[3];

$dbo = new PDO("mysql:host=localhost;dbname=$db", $username, $password);
$common_table_prefixes = array('customer_entity%', 'customer_address%', 'sales_flat%', 'eav_entity%', 'eav_form%', 'm2epro%', 'paypal%', 'tax_%', 'sagepay%');

$backupCommand = "/usr/bin/mysqldump -u '$username' -p'$password' --opt --compact --add-drop-table --skip-lock-tables --single-transaction --tz-utc"
    . " $db core_config_data cms_page cms_block cms_page_store cms_block_store ";

$table_names = array();
foreach($common_table_prefixes as $prefix){
    $tables_query = $dbo->prepare("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database() AND TABLE_NAME LIKE :prefix");
    $res = $tables_query->execute(array(':prefix' => $prefix));
    if($res === false){
        echo "Warning: Query for tables matching prefix $prefix failed.\n";
        echo "Error code: " . errorMsg($res) . "\n";
        continue;
    }
    $table_names = array_merge($table_names, $tables_query->fetchAll(PDO::FETCH_COLUMN, 0));
}

if(count($argv) > 4){
    for($i = 4; $i < count($argv); $i++){
        $prefix = $argv[$i];
        $tables_query = $dbo->prepare("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database() AND TABLE_NAME LIKE :prefix");
        $res = $tables_query->execute(array(':prefix' => $prefix));
        if($res === false){
            echo "Warning: Query for tables matching prefix $prefix failed.\n";
            echo "Error code: " . errorMsg($res) . "\n";
            continue;
        }
        $table_names = array_merge($table_names, $tables_query->fetchAll(PDO::FETCH_COLUMN, 0));
    }
}
$backupCommand .= implode(" ", array_unique($table_names));
$backupCommand .= " > /root/partialbackup.sql";
echo "Command is: " . $backupCommand . "\n";
echo shell_exec($backupCommand);
exit(0);

function errorMsg($pdoStatement){
    $a = $pdoStatement->errorInfo();
    return $a[2];
}