1. <?php
  3. /**
  4.  * 备份数据库的扩展类
  5.  */
  7. namespace toolkit;
  9. class BackSql
  10. {
  11.     private $config = [];
  12.     private $handler;
  13.     private $tables = array(); //需要备份的表
  14.     private $begin; //开始时间
  15.     private $error; //错误信息
  17.     public function __construct($config)
  18.     {
  19.         $config['path'] = env('ROOT_PATH') . 'public/static/data/'; //默认目录
  20.         $config["sqlbakname"] = date("YmdHis", time()) . ".sql"; //默认保存文件
  21.         $this->config = $config;
  23.         $this->begin = microtime(true);
  24.         header("Content-type: text/html;charset=utf-8");
  25.         $this->connect();
  26.     }
  27.     //首次进行pdo连接
  28.     private function connect()
  29.     {
  31.         try {
  32.             $this->handler = new \PDO(
  33.                 "{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']};",
  34.                 $this->config['username'],
  35.                 $this->config['password'],
  36.                 array(
  37.                     \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']};",
  38.                     \PDO::ATTR_ERRMODE =>  \PDO::ERRMODE_EXCEPTION,
  39.                     \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
  40.                 )
  41.             );
  42.         } catch (PDOException $e) {
  43.             die("Error!: " . $e->getMessage() . "<br/>");
  44.         }
  45.     }
  46.     /**
  47.      * 查询
  48.      * @param string $sql
  49.      * @return mixed
  50.      */
  51.     private function query($sql = '')
  52.     {
  53.         $stmt = $this->handler->query($sql);
  54.         $stmt->setFetchMode(\PDO::FETCH_NUM);
  55.         $list = $stmt->fetchAll();
  56.         return $list;
  57.     }
  58.     /**
  59.      * 获取全部表
  60.      * @param string $dbName
  61.      * @return array
  62.      */
  63.     private function get_dbname($dbName = '*')
  64.     {
  65.         $sql = 'SHOW TABLES';
  66.         $list = $this->query($sql);
  67.         $tables = array();
  68.         foreach ($list as $value) {
  69.             $tables[] = $value[0];
  70.         }
  71.         return $tables;
  72.     }
  73.     /**
  74.      * 获取表定义语句
  75.      * @param string $table
  76.      * @return mixed
  77.      */
  78.     private function get_dbhead($table = '')
  79.     {
  80.         $sql = "SHOW CREATE TABLE `{$table}`";
  81.         $ddl = $this->query($sql)[0][1] . ';';
  82.         return $ddl;
  83.     }
  84.     /**
  85.      * 获取表数据
  86.      * @param string $table
  87.      * @return mixed
  88.      */
  89.     private function get_dbdata($table = '')
  90.     {
  91.         $sql = "SHOW COLUMNS FROM `{$table}`";
  92.         $list = $this->query($sql);
  93.         //字段
  94.         $columns = '';
  95.         //需要返回的SQL
  96.         $query = '';
  97.         foreach ($list as $value) {
  98.             $columns .= "`{$value[0]}`,";
  99.         }
  100.         $columns = substr($columns, 0, -1);
  101.         $data = $this->query("SELECT * FROM `{$table}`");
  102.         foreach ($data as $value) {
  103.             $dataSql = '';
  104.             foreach ($value as $v) {
  105.                 $dataSql .= "'{$v}',";
  106.             }
  107.             $dataSql = substr($dataSql, 0, -1);
  108.             $query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});\r\n";
  109.         }
  110.         return $query;
  111.     }
  112.     /**
  113.      * 写入文件
  114.      * @param array $tables
  115.      * @param array $ddl
  116.      * @param array $data
  117.      */
  118.     private function writeToFile($tables = array(), $ddl = array(), $data = array())
  119.     {
  120.         $str = "/*\r\nMySQL Database Backup Tools\r\n";
  121.         $str .= "Server:{$this->config['hostname']}:{$this->config['hostport']}\r\n";
  122.         $str .= "Database:{$this->config['database']}\r\n";
  123.         $str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n";
  124.         $str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
  125.         $i = 0;
  126.         foreach ($tables as $table) {
  127.             $str .= "-- ----------------------------\r\n";
  128.             $str .= "-- Table structure for {$table}\r\n";
  129.             $str .= "-- ----------------------------\r\n";
  130.             $str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
  131.             $str .= $ddl[$i] . "\r\n";
  132.             $str .= "-- ----------------------------\r\n";
  133.             $str .= "-- Records of {$table}\r\n";
  134.             $str .= "-- ----------------------------\r\n";
  135.             $str .= $data[$i] . "\r\n";
  136.             $i++;
  137.         }
  139.         if (!file_exists($this->config['path'])) {
  140.             mkdir($this->config['path']);
  141.         }
  142.         return file_put_contents($this->config['path'] . $this->config['sqlbakname'], $str) ? '备份成功!花费时间' . round(microtime(true) - $this->begin, 2) . 'ms' : '备份失败!';
  143.     }
  144.     /**
  145.      * 设置要备份的表
  146.      * @param array $tables
  147.      */
  148.     private function setTables($tables = array())
  149.     {
  150.         if (!empty($tables) && is_array($tables)) {
  151.             //备份指定表
  152.             $this->tables = $tables;
  153.         } else {
  154.             //备份全部表
  155.             $this->tables = $this->get_dbname();
  156.         }
  157.     }
  158.     /**
  159.      * 备份
  160.      * @param array $tables
  161.      * @return bool
  162.      */
  163.     public function backup($tables = array())
  164.     {
  165.         //存储表定义语句的数组
  166.         $ddl = array();
  167.         //存储数据的数组
  168.         $data = array();
  169.         $this->setTables($tables);
  170.         if (!empty($this->tables)) {
  171.             foreach ($this->tables as $table) {
  172.                 $ddl[] = $this->get_dbhead($table);
  173.                 $data[] = $this->get_dbdata($table);
  174.             }
  176.             //开始写入
  177.             return $this->writeToFile($this->tables, $ddl, $data);
  178.         } else {
  179.             $this->error = '数据库中没有表!';
  180.             return false;
  181.         }
  182.     }
  183.     /**
  184.      * 错误信息
  185.      * @return mixed
  186.      */
  187.     public function getError()
  188.     {
  189.         return $this->error;
  190.     }
  191.     public function restore($filename = '')
  192.     {
  193.         $path = $this->config['path'] . $filename;
  194.         if (!file_exists($path)) {
  195.             $this->error('SQL文件不存在!');
  196.             return false;
  197.         } else {
  198.             $sql = $this->parseSQL($path);
  199.             //dump($sql);die;
  200.             try {
  201.                 $this->handler->exec($sql);
  202.                 echo '还原成功!花费时间', round(microtime(true) - $this->begin, 2) . 'ms';
  203.             } catch (PDOException $e) {
  204.                 $this->error = $e->getMessage();
  205.                 return false;
  206.             }
  207.         }
  208.     }
  210.     /**
  211.      * 解析SQL文件为SQL语句数组
  212.      * @param string $path
  213.      * @return array|mixed|string
  214.      */
  215.     private function parseSQL($path = '')
  216.     {
  217.         $sql = file_get_contents($path);
  218.         $sql = explode("\r\n", $sql);
  219.         //先消除--注释
  220.         $sql = array_filter($sql, function ($data) {
  221.             if (empty($data) || preg_match('/^--.*/', $data)) {
  222.                 return false;
  223.             } else {
  224.                 return true;
  225.             }
  226.         });
  227.         $sql = implode('', $sql);
  228.         //删除/**/注释
  229.         $sql = preg_replace('/\/\*.*\*\//', '', $sql);
  230.         return $sql;
  231.     }
  232.     /**
  233.      * 下载备份
  234.      * @param string $fileName
  235.      * @return array|mixed|string
  236.      */
  237.     public function downloadFile($fileName)
  238.     {
  239.         $fileName = $this->config['path'] . $fileName;
  240.         if (file_exists($fileName)) {
  241.             ob_end_clean();
  242.             header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  243.             header('Content-Description: File Transfer');
  244.             header('Content-Type: application/octet-stream');
  245.             header('Content-Length: ' . filesize($fileName));
  246.             header('Content-Disposition: attachment; filename=' . basename($fileName));
  247.             readfile($fileName);
  248.         } else {
  249.             $this->error = "文件有错误！";
  250.         }
  251.     }
  252.     /**
  253.      * 获取文件是时间
  254.      * @param string $file
  255.      * @return string
  256.      */
  257.     private function getfiletime($file)
  258.     {
  259.         $path = $this->config['path'] . $file;
  260.         $a = filemtime($path);
  261.         $time = date("Y-m-d H:i:s", $a);
  262.         return $time;
  263.     }
  264.     /**
  265.      * 获取文件是大小
  266.      * @param string $file
  267.      * @return string
  268.      */
  269.     private function getfilesize($file)
  270.     {
  271.         $perms = stat($this->config['path'] . $file);
  272.         $size = $perms['size'];
  273.         $a    = ['B', 'KB', 'MB', 'GB', 'TB'];
  274.         $pos  = 0;
  275.         while ($size >= 1024) {
  276.             $size /= 1024;
  277.             $pos++;
  278.         }
  279.         return round($size, 2) . $a[$pos];
  280.     }
  282.     /**
  283.      * 获取文件列表
  284.      * @param string $Order 级别
  285.      * @return array
  286.      */
  287.     public function get_filelist($Order = 0)
  288.     {
  289.         $FilePath = $this->config['path'];
  290.         //        print_r($FilePath);die;
  291.         $FilePath = opendir($FilePath);
  292.         //        $FilePath = scandir($FilePath);
  293.         $FileAndFolderAyy = array();
  294.         $i = 1;
  295.         while (false !== ($filename = readdir($FilePath))) {
  296.             if ($filename != "." && $filename != "..") {
  297.                 $i++;
  298.                 $FileAndFolderAyy[$i]['name'] = $filename;
  299.                 $FileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
  300.                 $FileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
  301.             }
  302.         }
  303.         $Order == 0 ? sort($FileAndFolderAyy) : rsort($FileAndFolderAyy);
  304.         return $FileAndFolderAyy;
  305.     }
  306.     public function delfilename($filename)
  307.     {
  308.         $path = $this->config['path'] . $filename;
  309.         if (@unlink($path)) {
  310.             return '删除成功';
  311.         }
  312.     }
  313. }

控制器内容：

<?php

namespace app\admin\controller;

use think\Controller;
use toolkit\BackSql;
use think\facade\Config;

class Backup extends Base
{
    //数据库备份
    public function index()
    {
        //获取操作内容：（备份/下载/还原/删除）数据库
        $type = input("type");
        //获取需要操作的数据库名字
        $name = input("name");
        $backup = new BackSql(Config::get("database."));
        switch ($type) {
                //备份
            case "backup":
                $info = $backup->backup();
                $this->success("$info", 'index/backup/bak');
                break;
                //下载
            case "dowonload":
                $info = $backup->downloadFile($name);
                $this->success("$info", 'index/backup/bak');
                break;
                //还原
            case "restore":
                $info = $backup->restore($name);
                $this->success("$info", 'index/backup/bak');
                break;
                //删除
            case "del":
                $info = $backup->delfilename($name);
                $this->success("$info", 'index/backup/bak');
                break;
                //如果没有操作，则查询已备份的所有数据库信息
            default:
                return $this->fetch("index", ["list" => array_reverse($backup->get_filelist())]); //将信息由新到老排序
        }
    }
}

视图中：

{extend name="layout" /}

{block name="title"}新闻列表{/block}

{block name="main"}

<nav class="breadcrumb"><i class="Hui-iconfont">&#xe67f;</i> 首页 <span class="c-gray en">&gt;</span> 新闻管理<span
        class="c-gray en">&gt;</span>
    新闻列表<a class="btn btn-success radius r" style="line-height:1.6em;margin-top:3px"
        href="javascript:location.replace(location.href);" title="刷新"><i class="Hui-iconfont">&#xe68f;</i></a></nav>


<div class="page-container">
    <div class="cl pd-5 bg-1 bk-gray mt-20">
        <span class="l">
            <a class="btn btn-primary " href="{:url('admin_backup',['type'=>'backup'])}"
                onclick="return confirm('备份数据的时间较长，确定要备份所有数据吗?')"><i class="glyphicon glyphicon-plus"></i> <span
                    class="bold">添加备份</span></a>
    </div>

    <div class="">
        <table class="table table-border table-bordered table-hover table-bg ">
            <thead>
                <tr class="text-c">
                    <th>序号</th>
                    <th>备份名称</th>
                    <th>备份时间</th>
                    <th>备份大小</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                {volist name='list' id='vo' }
                <tr class="gradeX">
                    <td>{$key+1}</td>
                    <td>{$vo.name}</td>
                    <td>{$vo.time}</td>
                    <td>{$vo.size}</td>
                    <td width="25%">
                        <a href="{:url('admin_backup',['type'=>'dowonload','name'=>$vo.name])}"
                            class="btn btn-success "><i class="glyphicon glyphicon-download-alt"></i> <span
                                class="bold">下载</span></a>
                        <a href="{:url('admin_backup',['type'=>'restore','name'=>$vo.name])}" class="btn btn-info "
                            onclick="return confirm('备份还原后仅会显示当前备份的数据库的信息，您确定还原备份吗 ?')"><i
                                class="glyphicon glyphicon-repeat"></i> <span class="bold">还原</span></a>
                        <a href="{:url('admin_backup',['type'=>'del','name'=>$vo.name])}" class="btn btn-warning"
                            onclick="return confirm('数据库备份一旦删除不可找回，您确定操作吗?')"><i class="fa fa-warning"></i> <span
                                class="admin_backup">删除</span></a>
                    </td>
                </tr>
                {/volist}
            </tbody>
        </table>

    </div>

</div>


</div>
{/block}

{block name="js"}

<script>


</script>
{/block}