1. <?php
    2. /**
    3.  * Created by PhpStorm.
    4.  * User: Administrator
    5.  * Date: 2018-9-12
    6.  * Time: 8:48
    7.  */
    9. namespace Backsql;
    10. use think\Db;
    11. use think\Config;
    12. class Backdb
    13. {
    14.     /**
    15.      * 文件指针
    16.      * @var resource
    17.      */
    18.     private $fp;
    19.     /**
    20.      * 备份文件信息 part - 卷号，name - 文件名
    21.      * @var array
    22.      */
    23.     private $file;
    24.     /**
    25.      * 当前打开文件大小
    26.      * @var integer
    27.      */
    28.     private $size = 0;
    30.     /**
    31.      * 数据库配置
    32.      * @var integer
    33.      */
    34.     private $dbconfig = array();
    35.     /**
    36.      * 备份配置
    37.      * @var integer
    38.      */
    39.     private $config = array(
    40.         'path' => './Data/',
    41.         //数据库备份路径
    42.         'part' => 20971520,
    43.         //数据库备份卷大小
    44.         'compress' => 0,
    45.         //数据库备份文件是否启用压缩 0不压缩 1 压缩
    46.         'level' => 9,
    47.     );
    48.     /**
    49.      * 数据库备份构造方法
    50.      * @param array  $file   备份或还原的文件信息
    51.      * @param array  $config 备份配置信息
    52.      */
    53.     public function __construct($config = [])
    54.     {
    55.         $this->config = array_merge($this->config, $config);
    56.         //初始化文件名
    57.         $this->setFile();
    58.         //初始化数据库连接参数
    59.         $this->setDbConn();
    60.         //检查文件是否可写
    61.         if (!$this->checkPath($this->config['path'])) {
    62.             throw new \Exception("The current directory is not writable");
    63.         }
    64.     }
    65.     /**
    66.      * 设置脚本运行超时时间
    67.      * 0表示不限制，支持连贯操作
    68.      */
    69.     public function setTimeout($time=null)
    70.     {
    71.         if (!is_null($time)) {
    72.             set_time_limit($time)||ini_set("max_execution_time", $time);
    73.         }
    74.         return $this;
    75.     }
    76.     /**
    77.      * 设置数据库连接必备参数
    78.      * @param array  $dbconfig   数据库连接配置信息
    79.      * @return object
    80.      */
    81.     public function setDbConn($dbconfig = [])
    82.     {
    83.         if (empty($dbconfig)) {
    84.             $this->dbconfig = config('database');
    85.             //$this->dbconfig = Config::get('database');
    86.         } else {
    87.             $this->dbconfig = $dbconfig;
    88.         }
    89.         return $this;
    90.     }
    91.     /**
    92.      * 设置备份文件名
    93.      * @param Array  $file  文件名字
    94.      * @return object
    95.      */
    96.     public function setFile($file = null)
    97.     {
    98.         if (is_null($file)) {
    99.             $this->file = ['name' => date('Ymd-His'), 'part' => 1];
    100.         } else {
    101.             if (!array_key_exists("name", $file) && !array_key_exists("part", $file)) {
    102.                 $this->file = $file['1'];
    103.             } else {
    104.                 $this->file = $file;
    105.             }
    106.         }
    107.         return $this;
    108.     }
    109.     //数据类连接
    110.     public static function connect()
    111.     {
    112.         return Db::connect();
    113.     }
    114.     //数据库表列表
    115.     public function dataList($table = null,$type=1)
    116.     {
    117.         $db = self::connect();
    118.         if (is_null($table)) {
    119.             $list = $db->query("SHOW TABLE STATUS");
    120.         } else {
    121.             if ($type) {
    122.                 $list = $db->query("SHOW FULL COLUMNS FROM {$table}");
    123.             }else{
    124.                 $list = $db->query("show columns from {$table}");
    125.             }
    126.         }
    127.         return array_map('array_change_key_case', $list);
    128.         //$list;
    129.     }
    130.     //数据库备份文件列表
    131.     public function fileList()
    132.     {
    133.         if (!is_dir($this->config['path'])) {
    134.             mkdir($this->config['path'], 0755, true);
    135.         }
    136.         $path = realpath($this->config['path']);
    137.         $flag = \FilesystemIterator::KEY_AS_FILENAME;
    138.         $glob = new \FilesystemIterator($path, $flag);
    139.         $list = array();
    140.         foreach ($glob as $name => $file) {
    141.             if (preg_match('/^\\d{8,8}-\\d{6,6}-\\d+\\.sql(?:\\.gz)?$/', $name)) {
    142.                 $name = sscanf($name, '%4s%2s%2s-%2s%2s%2s-%d');
    143.                 $date = "{$name[0]}-{$name[1]}-{$name[2]}";
    144.                 $time = "{$name[3]}:{$name[4]}:{$name[5]}";
    145.                 $part = $name[6];
    146.                 if (isset($list["{$date} {$time}"])) {
    147.                     $info = $list["{$date} {$time}"];
    148.                     $info['part'] = max($info['part'], $part);
    149.                     $info['size'] = $info['size'] + $file->getSize();
    150.                 } else {
    151.                     $info['part'] = $part;
    152.                     $info['size'] = $file->getSize();
    153.                 }
    154.                 $extension = strtoupper(pathinfo($file->getFilename(), PATHINFO_EXTENSION));
    155.                 $info['compress'] = $extension === 'SQL' ? '-' : $extension;
    156.                 $info['time'] = strtotime("{$date} {$time}");
    157.                 $list["{$date} {$time}"] = $info;
    158.             }
    159.         }
    160.         return $list;
    161.     }
    162.     public function getFile($type = '', $time = 0)
    163.     {
    164.         //
    165.         if (!is_numeric($time)) {
    166.             throw new \Exception("{$time} Illegal data type");
    167.         }
    168.         switch ($type) {
    169.             case 'time':
    170.                 $name = date('Ymd-His', $time) . '-*.sql*';
    171.                 $path = realpath($this->config['path']) . DIRECTORY_SEPARATOR . $name;
    172.                 return glob($path);
    173.                 break;
    174.             case 'timeverif':
    175.                 $name = date('Ymd-His', $time) . '-*.sql*';
    176.                 $path = realpath($this->config['path']) . DIRECTORY_SEPARATOR . $name;
    177.                 $files = glob($path);
    178.                 $list = array();
    179.                 foreach ($files as $name) {
    180.                     $basename = basename($name);
    181.                     $match = sscanf($basename, '%4s%2s%2s-%2s%2s%2s-%d');
    182.                     $gz = preg_match('/^\\d{8,8}-\\d{6,6}-\\d+\\.sql.gz$/', $basename);
    183.                     $list[$match[6]] = array($match[6], $name, $gz);
    184.                 }
    185.                 $last = end($list);
    186.                 if (count($list) === $last[0]) {
    187.                     return $list;
    188.                 } else {
    189.                     throw new \Exception("File {$files['0']} may be damaged, please check again");
    190.                 }
    191.                 break;
    192.             case 'pathname':
    193.                 return "{$this->config['path']}{$this->file['name']}-{$this->file['part']}.sql";
    194.                 break;
    195.             case 'filename':
    196.                 return "{$this->file['name']}-{$this->file['part']}.sql";
    197.                 break;
    198.             case 'filepath':
    199.                 return $this->config['path'];
    200.                 break;
    201.             default:
    202.                 $arr = array('pathname' => "{$this->config['path']}{$this->file['name']}-{$this->file['part']}.sql", 'filename' => "{$this->file['name']}-{$this->file['part']}.sql", 'filepath' => $this->config['path'], 'file' => $this->file);
    203.                 return $arr;
    204.         }
    205.     }
    206.     //删除备份文件
    207.     public function delFile($time)
    208.     {
    209.         if ($time) {
    210.             $file = $this->getFile('time', $time);
    211.             array_map("unlink", $this->getFile('time', $time));
    212.             if (count($this->getFile('time', $time))) {
    213.                 throw new \Exception("File {$path} deleted failed");
    214.             } else {
    215.                 return $time;
    216.             }
    217.         } else {
    218.             throw new \Exception("{$time} Time parameter is incorrect");
    219.         }
    220.     }
    221.     /**
    222.      * 下载备份
    223.      * @Author: 浪哥 <939881475@qq.com>
    224.      * @param string $time
    225.      * @param integer $part
    226.      * @return array|mixed|string
    227.      */
    228.     public function downloadFile($time, $part = 0)
    229.     {
    230.         $file = $this->getFile('time', $time);
    231.         $fileName = $file[$part];
    232.         if (file_exists($fileName)) {
    233.             ob_end_clean();
    234.             header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    235.             header('Content-Description: File Transfer');
    236.             header('Content-Type: application/octet-stream');
    237.             header('Content-Length: ' . filesize($fileName));
    238.             header('Content-Disposition: attachment; filename=' . basename($fileName));
    239.             readfile($fileName);
    240.         } else {
    241.             throw new \Exception("{$time} File is abnormal");
    242.         }
    243.     }
    244.     public function import($start)
    245.     {
    246.         //还原数据
    247.         $db = self::connect();
    248.         if ($this->config['compress']) {
    249.             $gz = gzopen($this->file[1], 'r');
    250.             $size = 0;
    251.         } else {
    252.             $size = filesize($this->file[1]);
    253.             $gz = fopen($this->file[1], 'r');
    254.         }
    255.         $sql = '';
    256.         if ($start) {
    257.             $this->config['compress'] ? gzseek($gz, $start) : fseek($gz, $start);
    258.         }
    259.         for ($i = 0; $i < 1000; $i++) {
    260.             $sql .= $this->config['compress'] ? gzgets($gz) : fgets($gz);
    261.             if (preg_match('/.*;$/', trim($sql))) {
    262.                 if (false !== $db->execute($sql)) {
    263.                     $start += strlen($sql);
    264.                 } else {
    265.                     return false;
    266.                 }
    267.                 $sql = '';
    268.             } elseif ($this->config['compress'] ? gzeof($gz) : feof($gz)) {
    269.                 return 0;
    270.             }
    271.         }
    272.         return array($start, $size);
    273.     }
    274.     /**
    275.      * 写入初始数据
    276.      * @return boolean true - 写入成功，false - 写入失败
    277.      */
    278.     public function Backup_Init()
    279.     {
    280.         $sql = "-- -----------------------------\n";
    281.         $sql .= "-- Think MySQL Data Transfer \n";
    282.         $sql .= "-- \n";
    283.         $sql .= "-- Host     : " . $this->dbconfig['hostname'] . "\n";
    284.         $sql .= "-- Port     : " . $this->dbconfig['hostport'] . "\n";
    285.         $sql .= "-- Database : " . $this->dbconfig['database'] . "\n";
    286.         $sql .= "-- \n";
    287.         $sql .= "-- Part : #{$this->file['part']}\n";
    288.         $sql .= "-- Date : " . date("Y-m-d H:i:s") . "\n";
    289.         $sql .= "-- -----------------------------\n\n";
    290.         $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
    291.         return $this->write($sql);
    292.     }
    293.     /**
    294.      * 备份表结构
    295.      * @param  string  $table 表名
    296.      * @param  integer $start 起始行数
    297.      * @return boolean        false - 备份失败
    298.      */
    299.     public function backup($table, $start)
    300.     {
    301.         $db = self::connect();
    302.         // 备份表结构
    303.         if (0 == $start) {
    304.             $result = $db->query("SHOW CREATE TABLE `{$table}`");
    305.             $sql = "\n";
    306.             $sql .= "-- -----------------------------\n";
    307.             $sql .= "-- Table structure for `{$table}`\n";
    308.             $sql .= "-- -----------------------------\n";
    309.             $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
    310.             $sql .= trim($result[0]['Create Table']) . ";\n\n";
    311.             if (false === $this->write($sql)) {
    312.                 return false;
    313.             }
    314.         }
    315.         //数据总数
    316.         $result = $db->query("SELECT COUNT(*) AS count FROM `{$table}`");
    317.         $count = $result['0']['count'];
    318.         //备份表数据
    319.         if ($count) {
    320.             //写入数据注释
    321.             if (0 == $start) {
    322.                 $sql = "-- -----------------------------\n";
    323.                 $sql .= "-- Records of `{$table}`\n";
    324.                 $sql .= "-- -----------------------------\n";
    325.                 $this->write($sql);
    326.             }
    327.             //备份数据记录
    328.             $result = $db->query("SELECT * FROM `{$table}` LIMIT {$start}, 1000");
    329.             foreach ($result as $row) {
    330.                 $row = array_map('addslashes', $row);
    331.                 $sql = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\\r', '\\n'), implode("', '", $row)) . "');\n";
    332.                 if (false === $this->write($sql)) {
    333.                     return false;
    334.                 }
    335.             }
    336.             //还有更多数据
    337.             if ($count > $start + 1000) {
    338.                 //return array($start + 1000, $count);
    339.                 return $this->backup($table, $start + 1000);
    340.             }
    341.         }
    342.         //备份下一表
    343.         return 0;
    344.     }
    346.     public function backall(){
    347.         $db = self::connect();
    348.         $datalist=$this->dataList();
    350.         $sql = "\n";
    351.         $sql.="SET FOREIGN_KEY_CHECKS=0;\n";
    352.         $start=0;
    353.         foreach($datalist as $t){
    354.             $table=$t['name'];
    355.             $result = $db->query("SHOW CREATE TABLE `{$table}`");
    356.             $sql .= "-- -----------------------------\n";
    357.             $sql .= "-- Table structure for `{$table}`\n";
    358.             $sql .= "-- -----------------------------\n";
    359.             $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
    360.             $sql .= trim($result[0]['Create Table']) . ";\n\n";
    361.             if (false === $this->write($sql)) {
    362.                 return false;
    363.             }
    365.             //数据总数
    366.             $result = $db->query("SELECT COUNT(*) AS count FROM `{$table}`");
    367.             $count = $result['0']['count'];
    368.             //备份表数据
    369.             if ($count) {
    370.                 //写入数据注释
    371.                 $sql = "-- -----------------------------\n";
    372.                 $sql .= "-- Records of `{$table}`\n";
    373.                 $sql .= "-- -----------------------------\n";
    374.                 $this->write($sql);
    375.                 //备份数据记录
    376.                 $result = $db->query("SELECT * FROM `{$table}` LIMIT {$start}, 1000");
    377.                 foreach ($result as $row) {
    378.                     $row = array_map('addslashes', $row);
    379.                     $sql = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\\r', '\\n'), implode("', '", $row)) . "');\n";
    380.                     if (false === $this->write($sql)) {
    381.                         return false;
    382.                     }
    383.                 }
    384.                 //还有更多数据
    385.                 if ($count > $start + 1000) {
    386.                     //return array($start + 1000, $count);
    387.                     return $this->backup($table, $start + 1000);
    388.                 }
    389.             }
    390.         }
    391.         return true;
    392.     }
    395.     /**
    396.      * 优化表
    397.      * @param  String $tables 表名
    398.      * @return String $tables
    399.      */
    400.     public function optimize($tables = null)
    401.     {
    402.         if ($tables) {
    403.             $db = self::connect();
    404.             if (is_array($tables)) {
    405.                 $tables = implode('`,`', $tables);
    406.                 $list = $db->query("OPTIMIZE TABLE `{$tables}`");
    407.             } else {
    408.                 $list = $db->query("OPTIMIZE TABLE `{$tables}`");
    409.             }
    410.             if ($list) {
    411.                 return $tables;
    412.             } else {
    413.                 throw new \Exception("data sheet'{$tables}'Repair mistakes please try again!");
    414.             }
    415.         } else {
    416.             throw new \Exception("Please specify the table to be repaired!");
    417.         }
    418.     }
    419.     /**
    420.      * 修复表
    421.      * @param  String $tables 表名
    422.      * @return String $tables
    423.      */
    424.     public function repair($tables = null)
    425.     {
    426.         if ($tables) {
    427.             $db = self::connect();
    428.             if (is_array($tables)) {
    429.                 $tables = implode('`,`', $tables);
    430.                 $list = $db->query("REPAIR TABLE `{$tables}`");
    431.             } else {
    432.                 $list = $db->query("REPAIR TABLE `{$tables}`");
    433.             }
    434.             if ($list) {
    435.                 return $list;
    436.             } else {
    437.                 throw new \Exception("data sheet'{$tables}'Repair mistakes please try again!");
    438.             }
    439.         } else {
    440.             throw new \Exception("Please specify the table to be repaired!");
    441.         }
    442.     }
    443.     /**
    444.      * 写入SQL语句
    445.      * @param  string $sql 要写入的SQL语句
    446.      * @return boolean     true - 写入成功，false - 写入失败！
    447.      */
    448.     private function write($sql)
    449.     {
    450.         $size = strlen($sql);
    451.         //由于压缩原因，无法计算出压缩后的长度，这里假设压缩率为50%，
    452.         //一般情况压缩率都会高于50%；
    453.         $size = $this->config['compress'] ? $size / 2 : $size;
    454.         $this->open($size);
    455.         return $this->config['compress'] ? @gzwrite($this->fp, $sql) : @fwrite($this->fp, $sql);
    456.     }
    457.     /**
    458.      * 打开一个卷，用于写入数据
    459.      * @param  integer $size 写入数据的大小
    460.      */
    461.     private function open($size)
    462.     {
    463.         if ($this->fp) {
    464.             $this->size += $size;
    465.             if ($this->size > $this->config['part']) {
    466.                 $this->config['compress'] ? @gzclose($this->fp) : @fclose($this->fp);
    467.                 $this->fp = null;
    468.                 $this->file['part']++;
    469.                 session('backup_file', $this->file);
    470.                 $this->Backup_Init();
    471.             }
    472.         } else {
    473.             $backuppath = $this->config['path'];
    474.             $filename = "{$backuppath}{$this->file['name']}-{$this->file['part']}.sql";
    475.             if ($this->config['compress']) {
    476.                 $filename = "{$filename}.gz";
    477.                 $this->fp = @gzopen($filename, "a{$this->config['level']}");
    478.             } else {
    479.                 $this->fp = @fopen($filename, 'a');
    480.             }
    481.             $this->size = filesize($filename) + $size;
    482.         }
    483.     }
    484.     /**
    485.      * 检查目录是否可写
    486.      * @param  string   $path    目录
    487.      * @return boolean
    488.      */
    489.     protected function checkPath($path)
    490.     {
    491.         if (is_dir($path)) {
    492.             return true;
    493.         }
    494.         if (mkdir($path, 0755, true)) {
    495.             return true;
    496.         } else {
    497.             return false;
    498.         }
    499.     }
    500.     /**
    501.      * 析构方法，用于关闭文件资源
    502.      */
    503.     public function __destruct()
    504.     {
    505.         $this->config['compress'] ? @gzclose($this->fp) : @fclose($this->fp);
    506.     }
    507. }