php MySQL数据备份方法

admin 2024-06-24 206 阅读 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;
}


发表评论

快捷回复: 表情:
Addoil Applause Badlaugh Bomb Coffee Fabulous Facepalm Feces Frown Heyha Insidious KeepFighting NoProb PigHead Shocked Sinistersmile Slap Social Sweat Tolaugh Watermelon Witty Wow Yeah Yellowdog
提交
评论列表 (有 0 条评论, 206人围观)