备份数据库最新的50W条数据

秒速五厘米 2022-12-21 14:51 112阅读 0赞

**开发中的测试环境，经常需要更新开发数据库，这个脚本用于获取数据库最新的50W条数据**

$sourceDb是需要备份的数据库

$insertDb是备份的数据恢复到哪个数据库

<?php
    
    $limit      = 500000;
    $backupPath = 'E:\database\order\\';//备份表到这个路径
    $mysqlbin   = 'E:\phpstudy\Extensions\MySQL5.7.26\bin\\';//mysql bin路径
    //需要备份的数据库
    $sourceDb = array(
            'hostname'=>"192.168.0.2",
            'database'=>"order",
            'username'=>"22222",
            'password'=>"1111111",
            'hostport'=>3306,
    );
    
    $sdb        = $sourceDb['database'];
    
    //恢复表到那个数据库
    $insertDb = array(
        'hostname'=>"127.0.0.1",
        'database'=>"order",
        'username'=>"root",
        'password'=>"root",
        'hostport'=>3306,
    );
    
    $mysqli = new MySQLi($sourceDb['hostname'],$sourceDb['username'],$sourceDb['password'],$sourceDb['database'],$sourceDb['hostport']);
    //判断数据库是否连接
    if($mysqli->connect_errno){
        die('connect error' . $mysqli -> connect_error);
    }
    
    $nowtime = date("Y-m-d H:i:s");
    echo  "------------{$nowtime}原数据库连接成功----------------------".PHP_EOL;
    
    //设置字符集
    $mysqli-> set_charset('utf8');
    $object = $mysqli->query("show tables");
    
    //清空命令文件内容
    file_put_contents($backupPath."backupcmd.txt","");
    
    $allTables  = [];
    $tablesKeys = 0;
    while($row = mysqli_fetch_array($object)){
    //I:\phpStudy\PHPTutorial\MySQL\bin\mysqldump -h192.168.0.3 -uroot  -p12qw!@QW mmoga-static cdkey_2007>E:\database\cdkey_2007.sql
        //生成备份语句
    //mysqldump -h192.168.0.3 -uroot  -p12qw!@QW  mmoga-static customers_ip_2020 --where="1=1 order by customers_ip_id desc limit 10">t111.sql
        $thisTable = isset($row[0])&&$row[0]?$row[0]:'';
        if($thisTable){
            $doingTime = date("Y-m-d H:i:s");
            //echo  "------------{$doingTime}正在备份数据库{$sourceDb['database']}的{$thisTable}表----------------------".PHP_EOL;
    
            //这里要用反引号,不然有些表名奇怪的会有问题
            $desc = $mysqli->query("desc `{$row[0]}`");
    
            //为什么下面的表会备份出错,为什么???,可以了,desc `表名称` 时候要反引号,不然下面的表是会出错的
            //'log_order_auction_ff19-1-archive','log_order_auction_ff19-2-archive',
    
            $first = $desc->fetch_array();
    
            $firstField = $first['Field'];
    
            $cmd  = $mysqlbin."mysqldump -h{$sourceDb['hostname']}  -u{$sourceDb['username']}   -p{$sourceDb['password']} ";
            $cmd .= "{$sourceDb['database']} {$thisTable} --where=\"1=1 order by $firstField desc limit $limit \"> $backupPath{$thisTable}.sql";
    
            $allTables[$tablesKeys] = array(
                'Table' => $row[0],//表名称
                'Field' => $firstField,//表第一个字段
                'cmd' => $cmd,//备份语句
            );
    
            $tablesKeys++;
        }else{
            dd("生成语句出错".json_encode($row));
        }
    }
    //dd($allTables);
    file_put_contents($backupPath."tablesCmd.txt",json_encode($allTables));
    $sourceData = file_get_contents($backupPath."tablesCmd.txt");
    $sourceData = json_decode($sourceData,true);
    
    $backUpFile = [];
    file_put_contents($backupPath."dumpError.txt","");//清空错误日志
    foreach($sourceData as $k=>$v){
        $thisTable = $v['Table'];
        $doingTime = date("Y-m-d H:i:s");
        echo  "------------{$doingTime}正在备份数据库{$sourceDb['database']}的{$thisTable}表----------------------".PHP_EOL;
        exec($v['cmd'],$output,$return_var);
    
        if($return_var==1){//命令执行失败
            file_put_contents($backupPath."dumpError.txt",$thisTable.PHP_EOL,FILE_APPEND);
        }else{
            $backUpFile[] = "$backupPath{$thisTable}.sql";//命令执行成功
        }
    }
    //p($sourceData);
    //dd(count($allTables));
    //exit();
    $backUptime = date("Y-m-d H:i:s");
    echo  "------------{$backUptime}数据备份完成----------------------".PHP_EOL;
    echo "---------------正在恢复数据到{$insertDb['database']}----------------------".PHP_EOL;
    //还原数据库,清空还原命令文件内容
    file_put_contents($backupPath."insertError.txt","");
    file_put_contents($backupPath."insertCmd.txt","");
    foreach($backUpFile as $k=>$v){
        //I:\phpStudy\PHPTutorial\MySQL\bin\mysql -uroot  -proot mmoga-static <E:\database\cdkey_2008.sql
        $insertTime = date("Y-m-d H:i:s");
        echo  "------------{$insertTime}正在恢复表{$v}到{$insertDb['database']}----------------------".PHP_EOL;
    
        $cmd = $mysqlbin."mysql -h{$insertDb['hostname']} -u{$insertDb['username']} -p{$insertDb['password']} {$insertDb['database']} < $v";
    
        file_put_contents($backupPath."insertCmd.txt",$cmd,FILE_APPEND);
        exec($cmd,$output,$return_var);
        if($return_var==1){//还原失败
            file_put_contents($backupPath."insertError.txt",$v.PHP_EOL,FILE_APPEND);
        }
    }
    echo "---------------成功恢复数据到{$insertDb['database']}----------------------";
    
    
    function p($data=""){
        echo "<pre>";var_dump($data);
    }
    function dd($data=""){
        echo "<pre>";var_dump($data);exit();
    }