数据从线上dump到线下库［工具脚本］

场景：需要将线上的数据同步到测试环境库里面。可以用这个脚本跑一下

<?php
define('BASE_DIR',dirname(__FILE__)."/");
$db_conf = array(
    "armory"=>array(
            "dbhost"=>'',
            "dbport"=>'3306',
            "dbuser"=>'',
            "dbpass"=>'',
            "dbname"=>'',
            ),
    "armory_dev"=>array(
            "dbhost"=>'',
            "dbport"=>'3306',
            "dbuser"=>'',
            "dbpass"=>'',
            "dbname"=>'',
            ),
    "armory_test"=>array(
            "dbhost"=>'',
            "dbport"=>'3306',
            "dbuser"=>'',
            "dbpass"=>'56',
            "dbname"=>'',
            ),
);
$armory_link = db_connect('armory');
if(isset($_SERVER['argv'][1]) && ($_SERVER['argv'][1] == 'dev')){
    $armory_dev_link  = db_connect('armory_dev');
} else {
    $armory_dev_link  = db_connect('armory_test');
}
function db_connect($db_token = "armory_test"){
    global $db_conf;
    $dblink = null;
    $conf = $db_conf[$db_token];
    $dblink = mysql_connect($conf['dbhost'].':'.$conf['dbport'],$conf['dbuser'],$conf['dbpass'],true);
    if(!$dblink){
        exit("DB connect error\n");
    }
    mysql_select_db($conf['dbname'],$dblink);
    mysql_query("SET NAMES utf8",$dblink);
    return $dblink;
}
// php synsql dev cabinet
if(isset($_SERVER['argv'][2])){
    $tableName = $_SERVER['argv'][2];
    truncateTable($tableName);
    if ($tableName == "device_base") {
        $start = 0;
        for($i = 0;$i < 9;$i++){
            insertTable($value,$start);
               $start = $start + 10000;
        }
    } else {
        insertTable($value,0);
    }
    //数据准确性校验
    countTables($value);
    exit(0);
}

/**
 * 将线上的库导到测试环境
 * */
$syntable = array("machine_room","machine_idc");

foreach($syntable as $value){
    truncateTable($value);
    if ($value == "app_server"){
        $start = 0;
        for($i = 0;$i <= 9;$i++){
            insertTable($value,$start);
            $start = $start + 10000;
        }
    } else if ($value == "device_base" || $value == "server" || $value == "os" || $value == "cpu") {
        $start = 0;
        truncateTable($value);
        for($i = 0;$i < 9;$i++){
            insertTable($value,$start);
               $start = $start + 10000;
        }
    } else {
        truncateTable($value);
        insertTable($value,0);
    }
    //数据准确性校验
    countTables($value);
}


/**
 * 输入一个表名查看两边的数据记录个数
 * */
function countTables($value) {
    global $armory_link,$armory_dev_link;
    $armory_query = mysql_query("select count(*) as num from $value limit 1",$armory_link);
    $armory_result = mysql_fetch_array($armory_query);
    $dev_query = mysql_query("select count(*) as num from $value limit 1",$armory_dev_link);
    $dev_result = mysql_fetch_array($dev_query);
    print "Table $value count is : " .$armory_result[0] . "\t" .  $dev_result[0] . "\n";
}



/**
 * 输入一个表名返回一个字段列表
 * */
function fieldByTable($tableName) {
    global $armory_link;
    $sql = "select * from $tableName limit 1";
    $result = mysql_query($sql,$armory_link);
    $field = array();
    while($propery = mysql_fetch_field($result)) {
        $field[] = $propery->name;
    }
    return $field;
}
/**
 * 输入一个表名将原opsdb的表给truncate掉
 * */
function truncateTable($tableName) {
    global $armory_dev_link;
    // 建议使用truncate 的方式delete的好像auto_increment的ID会有问题[fix bug]
    $sql = "truncate table $tableName";
    mysql_query($sql,$armory_dev_link);
}

/**
 * 查询指定的表从线上取记录插入到测试库
 * */
function insertTable($tableName,$start) {
    global $armory_dev_link,$armory_link;
    $query = mysql_query("select * from $tableName limit $start,10000",$armory_link);
    $field_array = fieldByTable($tableName);
    while($arr = mysql_fetch_array($query)){
        $armory_sql = "insert into $tableName(";
        $filed_str = '';
        $value_str = '';
        foreach($field_array as $value) {
    /**
 		排除掉null与empty string类型的字段，但0是需要同步的.[fix bug]
              */
            if(!is_null($arr[$value]) && ($arr[$value] ==0 || !empty($arr[$value])){
                $filed_str.="`$value`,";
                $value_str.="'".addslashes($arr[$value])."',";
            }
        }
        $filed_str = substr($filed_str,0,strlen($filed_str)-1);
        $value_str = substr($value_str,0,strlen($value_str)-1);
        $armory_sql = $armory_sql.$filed_str.") VALUES (".$value_str.")";
        $result = mysql_query($armory_sql,$armory_dev_link);
        if (!$result){
            echo $armory_sql . "\n";
            exit(0);
        }
    }
}