php MySQL数据备份方法

admin 2024-06-24 2017 阅读 0评论

注：该示例为tp8中使用，可根据自己的实际需求进行修改


/**
 * @param $table
 * @param $name
 * @return array|bool|int
 * 数据表备份导出
 */
function backup($table,$name){
    //打开缓冲
    open_buffer();
    $sql  = "-- -----------------------------\n";
    $sql .= "-- MiniCms MySQL Data Transfer \n";
    $sql .= "-- \n";
    $sql .= "-- Host     : " . env('database.hostname') . "\n";
    $sql .= "-- Port     : " . env('database.hostport') . "\n";
    $sql .= "-- Database : " . env('database.database') . "\n";
    $sql .= "-- \n";
    $sql .= "-- Target Server Type : MYSQL\n";
    $sql .= "-- Date : " . date("Y-m-d H:i:s") . "\n";
    $sql .= "-- -----------------------------\n\n";
    $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
    $filename = write($sql,$name);

    //备份表结构
    $result = Db::query("SHOW CREATE TABLE `{$table}`");
    $sql  = "\n";
    $sql .= "-- -----------------------------\n";
    $sql .= "-- Table structure for `{$table}`\n";
    $sql .= "-- -----------------------------\n";
    $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
    $sql .= trim($result[0]['Create Table']) . ";\n\n";
    $filename = write($sql,$name);

    //数据总数
    $result = Db::query("SELECT COUNT(*) AS count FROM `{$table}`");
    $count  = $result['0']['count'];

    //备份表数据
    if($count){
        //写入数据注释
        $sql  = "-- -----------------------------\n";
        $sql .= "-- Records of `{$table}`\n";
        $sql .= "-- -----------------------------\n";
        $filename = write($sql,$name);
        //备份数据记录
        //计算页面码
        $ce = ceil($count/1000)-1;
        $size = 1000;
        for($i=0;$i<=$ce;$i++){
            $limit = ($i+1-1)*$size;
            $result = Db::query("SELECT * FROM `{$table}` LIMIT {$limit},$size");
            $rows = count($result)-1;
            $sql = "INSERT INTO `{$table}` VALUES ";
            foreach ($result as $k=>$row) {
                $one = '';
                foreach ($row as $v){
                    $one .= (gettype($v) == 'string') ? "'".str_replace("'","\'",$v)."'," : $v.",";
                }
                $one = rtrim($one,',');
                $one = str_replace(["\n","\r"],'',$one);
                $sql .= $rows == $k ? "(" . $one . ");\n" : "(" . $one . "),\n";
            }
            $filename = write($sql,$name);
            //输出缓冲
            output_buffer();
        }
    }
    return ['code'=>200,'msg'=>'完成操作'];
}

使用gz进行压缩


/**
 * @param $sql
 * @param $name
 * @return false|resource
 * 压缩写入文件
 */
function write($sql,$name){
    $size = strlen($sql);
    $path = backupDatabasePath();
    if(!file_exists($path)){
        mkdir($path,0777,true);
    }
    $filename = $path.$name.'.sql';
    $filename = gzopen($filename.'.gz','a4');
    gzwrite($filename,$sql);
    gzclose($filename);
    return $filename;
}