tp5数据库备份代码

发布时间:2023-12-07作者:小灵龙点击:98

问题描述:

一直使用tp5,做个数据库备份试试。

解决办法:

第一步:html页面
<a href="{:url(index/data)}">
数据库备份</a>

第二步:controller


//1.获取数据库信息(数据库名称)
$info = Db::getConfig();
$dbname = $info['database'];
//2.获取数据库所有表
$tables = Db::query("show tables");
//3、组装头部信息
header("Content-type:text/html;charset=utf-8");
$path = ROOT_PATH.'public/static/backup/';

$database = $dbname;   //获取当前数据库
$info  = "-- ----------------------------\r\n";
$info .= "-- 日期:".date("Y-m-d H:i:s",time())."\r\n";
$info .= "-- MySQL - 5.5.52-MariaDB : Database - ".$database."\r\n";
$info .= "-- ----------------------------\r\n\r\n";
$info .= "SET NAMES utf8;\r\nSET FOREIGN_KEY_CHECKS = 0;\r\n\r\n";
//4、检查目录是否存在
if (is_dir($path)) {
    if (is_writable($path)) {

    } else {
        echo '目录不可写'; exit();
    }

} else {
    mkdir($path,0777,true);
}

//5、保存的文件名称
$file_name = $path.$database.'_'.date('Ymd_His').'.sql';
file_put_contents($file_name, $info, FILE_APPEND);
//6、循环表,写入数据
foreach ($tables as $k => $v) {
    $val = $v["Tables_in_$database"];
    $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$val' AND TABLE_SCHEMA='$dbname'";
    $res = Db::query($sql);
    $max_num = Db::table("$val")->value('id');
    //查询表结构
    $info_table = "-- ----------------------------\r\n";
    $info_table .= "-- Table structure for `$val`\r\n";
    $info_table .= "-- ----------------------------\r\n\r\n";
    $info_table .= "DROP TABLE IF EXISTS `$val`;\r\n";
    if (count($res) < 1) {
        continue;
    }

    $info_table .= "CREATE TABLE `$val` (\n\r\t";
    foreach ($res as $kk => $vv) {
       $info_table .= " `".$vv['COLUMN_NAME']."` ";
       $info_table .= $vv['COLUMN_TYPE'];
       //是否允许空值
       if ($vv['IS_NULLABLE'] == 'NO') {
           $info_table .= " NOT NULL ";
       }

       //判断主键
       if ($vv['EXTRA']) {
           $info_table .= " AUTO_INCREMENT ";
           $key = $vv['COLUMN_NAME'];
       }
       //编码
       if ($vv['CHARACTER_SET_NAME']) {
           $info_table .= " CHARACTER SET ".$vv['CHARACTER_SET_NAME'];
       }
       //字符集
       if ($vv['COLLATION_NAME']) {
           $info_table .= " COLLATE ".$vv['COLLATION_NAME'];
       }
       //默认数值
       if ($vv['COLUMN_DEFAULT']) {
           $info_table .= " DEFAULT ".$vv['COLUMN_DEFAULT'];
       }
       //注释
       if ($vv['COLUMN_COMMENT']) {
           $info_table .= " COMMENT '".$vv['COLUMN_COMMENT']."',\n\r\t";
       }
   }

   $info_table .= " PRIMARY KEY (`$key`) USING BTREE";
   $info_table .= "\n\r) ENGINE = MyISAM AUTO_INCREMENT $max_num CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\r\n\r\n";

    $info_table .= "-- ----------------------------\r\n";
    $info_table .= "-- Data for the table `$val`\r\n";
    $info_table .= "-- ----------------------------\r\n\r\n";
    file_put_contents($file_name,$info_table,FILE_APPEND);
    //查询表数据
    $sql_data = "select * from $val";
    $data = Db::query($sql_data);
    $count= count($data);
    if ($count < 1) {
        continue;
    }

    foreach ($data as $key => $value) {
        $sqlStr = "INSERT INTO `$val` VALUES (";
        foreach($value as $v_d){
            $v_d = str_replace("'","\'",$v_d);
            $sqlStr .= "'".$v_d."', ";
        }
        //需要特别注意对数据的单引号进行转义处理
        //去掉最后一个逗号和空格
        $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
        $sqlStr .= ");\r\n";
        file_put_contents($file_name,$sqlStr,FILE_APPEND);
    }

    $info = "\r\n";
    file_put_contents($file_name,$info,FILE_APPEND);
}

//7、下载数据到本地
ob_end_clean();
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Length: ' . filesize($file_name));
header('Content-Disposition: attachment; filename=' . basename($file_name));
readfile($file_name);
DownloadFile($path.$file_name);
$this->success("数据已备份");
标签:tp5备份,数据库备份,tp5,数据库