基于 ThinkPHP6.0 的命令行备份恢复数据库，可用于定时任务等！

很久没有写博客了，最近弄个小项目，为了方便在不同电脑上做项目，把数据库放在项目里面一起带走，参考了 海豚PHP内的数据库备份，拿出来改了一下，做成命令行的方式，方便通过命令行或者定时任务来备份数据库。此命令行基于 ThinkPHP 6.0 

添加方式：1、把文件放在项目的 command内;2、在 console.php 内注册命令行

使用方式：php think databases ['export', 'import', 'optimize', 'repair', 'delete', 'list]  

import 和 delete 需要提供 保存时间戳 ，时间戳通过 list 里面的 time 获取

示例：

下面是完整代码：databases.php 

<?php

declare(strict_types=1);

namespace app\command;

use Console_Table;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
use think\facade\Db;

/**
 * 数据库管理
 */
class databases extends Command
{
    //输出对象
    protected $output;
    //数据库备份根路径 路径必须以 / 结尾
    protected $data_backup_path = '../database/back/';
    //数据库备份卷大小 该值用于限制压缩后的分卷最大长度。单位：B；建议设置20M
    protected $data_backup_part_size = 20971520;
    //数据库备份文件是否启用压缩   '0:否1:是', '压缩备份文件需要PHP环境支持 <code>gzopen</code>, <code>gzwrite</code>函数'
    protected $data_backup_compress = 1;
    //数据库备份文件压缩级别 1:最低4:一般9:最高', '数据库备份文件的压缩级别，该配置在开启压缩时生效
    protected $data_backup_compress_level = 9;

    protected function configure()
    {
        // 指令配置
        $this->setName('databases manager')
            ->addArgument('options', Argument::OPTIONAL, "options:'export', 'import', 'optimize', 'repair', 'delete', 'list'")
            ->addArgument('time', Argument::OPTIONAL, "import back_data_file time, run: php think databases list|delete ")
            ->setDescription("the databases manager command options :'export', 'import', 'optimize', 'repair', 'delete', 'list'");
    }

    protected function execute(Input $input, Output $output)
    {
        //检查是否安装 Console_Table 组件 ，此组件用于在命令行中输出表格
        $composer = \json_decode(file_get_contents($this->app->getRootPath() . 'composer.json'), true);
        if (!isset($composer['require']['pear/console_table'])) {

            fwrite(STDOUT, 'it found that you have not install [pear/console_table]?Y/N' . PHP_EOL);

            $answer = strtolower(trim(fread(STDIN, 1024), PHP_EOL));

            if ($answer == 'y' || $answer == 'yes') {
                exec('composer require pear/console_table');
            }
        }

        //设置输出对象
        $this->output = $output;
        //设置备份目录
        $this->data_backup_path = root_path() . 'database/back/';
        //获取参数数组
        $Argument =  $input->getArguments();

        //获取第一个参数
        $Argument_options = $Argument['options'];
        // 指令输出
        switch ($Argument_options) {
                //备份数据库
            case 'export':
                $this->export();
                break;
            case 'import': //恢复数据库
                $this->import($Argument);
                break;
            case 'optimize': //优化所有表
                $this->optimize();
                break;
            case 'repair': //修复所有表
                $this->repair();
                break;
            case 'delete': //删除指定时间备份文件
                $this->delete($Argument);
                break;
            case 'list':
            default:
                $this->list();
        }
    }

    /**
     * 获取数据库所有表名称
     */
    protected function getTableNames()
    {
        $tables = Db::query("SHOW TABLE STATUS");
        $tables = array_map('array_change_key_case', $tables);
        foreach ($tables as $key => &$table) {
            $table = $table['name'];
        }
        unset($table);
        return $tables;
    }

    /**
     * 输出备份文件列表
     */
    public function list()
    {
        // 列出备份文件列表
        $path = $this->data_backup_path;
        if (!is_dir($path)) {
            mkdir($path, 0755, true);
        }
        $path = realpath($path);
        $flag = \FilesystemIterator::KEY_AS_FILENAME;
        $glob = new \FilesystemIterator($path, $flag);

        $data_list = [];
        foreach ($glob as $name => $file) {
            if (preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql(?:\.gz)?$/', $name)) {
                $name = sscanf($name, '%4s%2s%2s-%2s%2s%2s-%d');

                $date = "{$name[0]}-{$name[1]}-{$name[2]}";
                $time = "{$name[3]}:{$name[4]}:{$name[5]}";
                $part = $name[6];



                if (isset($data_list["{$date} {$time}"])) {
                    $info = $data_list["{$date} {$time}"];
                    $part = max($info['part'], $part);
                    $size = $info['size'] + $file->getSize();
                } else {
                    $part = $part;
                    $size = $file->getSize();
                }
                $extension        = strtoupper(pathinfo($file->getFilename(), PATHINFO_EXTENSION));
                $compress = ($extension === 'SQL') ? '-' : $extension;

                $info['time']     = strtotime("{$date} {$time}");
                $info['part'] = $part;
                $info['size'] = $size;
                $info['compress'] = $compress;
                $info['back_time']    = $date . $time;
                $data_list[] = $info;
            }
        }
        $table = new Console_Table();
        $table->setHeaders(array_keys($data_list[0]));
        $table->addData($data_list);

        $this->output->writeln($table->getTable());
    }
    /**
     * 备份数据库
     */
    public function export()
    {

        // 初始化
        $path = $this->data_backup_path;
        if (!is_dir($path)) {
            mkdir($path, 0755, true);
        }

        // 读取备份配置
        $config = array(
            'path'     => realpath($path) . DIRECTORY_SEPARATOR,
            'part'     => $this->data_backup_part_size,
            'compress' => $this->data_backup_compress,
            'level'    => $this->data_backup_compress_level,
        );

        // 检查是否有正在执行的任务
        $lock = "{$config['path']}backup.lock";
        if (is_file($lock)) {
            $this->output->writeln('检测到有一个备份任务正在执行，请稍后再试！');
        } else {
            // 创建锁文件
            file_put_contents($lock, time());
        }

        // 生成备份文件信息
        $file = array(
            'name' => date('Ymd-His', time()),
            'part' => 1,
        );

        // 创建备份文件
        $Database = new DatabaseModel($file, $config);
        if (false !== $Database->create()) {
            // 备份所有表
            $tables = $this->getTableNames();

            foreach ($tables as $table) {
                $start = $Database->backup($table, 0);
                while (0 !== $start) {
                    if (false === $start) { // 出错
                        $this->output->writeln('备份出错！');
                        unlink($lock);
                        die;
                    }
                    $start = $Database->backup($table, $start[0]);
                }
            }

            // 备份完成，删除锁定文件
            unlink($lock);

            $this->output->writeln('备份完成！');
        } else {
            $this->output->writeln('初始化失败，备份文件创建失败！');
        }
    }

    /**
     * 还原数据库
     * @param array $Argument 文件时间戳
     */
    public function import($Argument)
    {
        $time =  (int)$Argument['time'];
        if ($time === 0) {
            $this->output->writeln('参数错误！');
            die;
        }

        // 初始化
        $name  = date('Ymd-His', $time) . '-*.sql*';
        $path  = realpath($this->data_backup_path) . DIRECTORY_SEPARATOR . $name;
        $files = glob($path);
        $list  = array();
        foreach ($files as $name) {
            $basename = basename($name);
            $match    = sscanf($basename, '%4s%2s%2s-%2s%2s%2s-%d');
            $gz       = preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql.gz$/', $basename);
            $list[$match[6]] = array($match[6], $name, $gz);
        }
        ksort($list);

        // 检测文件正确性
        $last = end($list);
        if (count($list) === $last[0]) {
            foreach ($list as $item) {
                $config = [
                    'path'     => realpath($this->data_backup_path) . DIRECTORY_SEPARATOR,
                    'compress' => $item[2]
                ];
                $Database = new DatabaseModel($item, $config);
                $start = $Database->import(0);

                // 循环导入数据
                while (0 !== $start) {
                    if (false === $start) { // 出错
                        $this->output->writeln('还原数据出错！');
                    }
                    $start = $Database->import($start[0]);
                }
            }

            $this->output->writeln('还原完成！');
        } else {
            $this->output->writeln('备份文件可能已经损坏，请检查！');
        }
    }

    /**
     * 优化表
     */
    public function optimize()
    {
        $tables = $this->getTableNames();
        foreach ($tables as $table) {

            $list = Db::query("OPTIMIZE TABLE `{$table}`");
            if ($list) {
                $this->output->writeln("数据表'{$table}'优化完成！");
            } else {
                $this->output->writeln("数据表'{$table}'优化出错请重试！");
            }
        }
    }

    /**
     * 修复表
     */
    public function repair()
    {
        $tables = $this->getTableNames();
        foreach ($tables as $table) {
            $list = Db::query("REPAIR TABLE `{$table}`");
            if ($list) {

                $this->output->writeln("数据表'{$table}'修复完成！");
            } else {
                $this->output->writeln("数据表'{$table}'修复出错请重试！");
            }
        }
    }

    /**
     * 删除备份文件
     * @param array $Argument 备份时间
     */
    public function delete($Argument)
    {
        $time =  (int)$Argument['time'];
        if ($time === 0) {
            $this->output->writeln('参数错误！');
            die;
        }

        $name  = date('Ymd-His', $time) . '-*.sql*';
        $path  = realpath($this->data_backup_path) . DIRECTORY_SEPARATOR . $name;
        array_map("unlink", glob($path));
        if (count(glob($path))) {
            $this->output->writeln('备份文件删除失败，请检查权限！');
        } else {

            $this->output->writeln('备份文件删除成功！');
        }
    }
}
//数据操作类
class DatabaseModel
{
    /**
     * 文件指针
     * @var resource
     */
    private $fp;

    /**
     * 备份文件信息 part - 卷号，name - 文件名
     * @var array
     */
    private $file;

    /**
     * 当前打开文件大小
     * @var integer
     */
    private $size = 0;

    /**
     * 备份配置
     * @var integer
     */
    private $config;

    /**
     * 数据库备份构造方法
     * @param array  $file   备份或还原的文件信息
     * @param array  $config 备份配置信息
     * @param string $type   执行类型，export - 备份数据， import - 还原数据
     */
    public function __construct($file, $config, $type = 'export')
    {
        $this->file   = $file;
        $this->config = $config;
    }

    /**
     * 打开一个卷，用于写入数据
     * @param integer $size 写入数据的大小
     */
    private function open($size = 0)
    {
        if ($this->fp) {
            $this->size += $size;
            if ($this->size > $this->config['part']) {
                $this->config['compress'] ? @gzclose($this->fp) : @fclose($this->fp);
                $this->fp = null;
                $this->file['part']++;
                session('backup_file', $this->file);
                $this->create();
            }
        } else {
            $backup_path = $this->config['path'];
            $filename    = "{$backup_path}{$this->file['name']}-{$this->file['part']}.sql";
            if ($this->config['compress']) {
                $filename = "{$filename}.gz";
                $this->fp = @gzopen($filename, "a{$this->config['level']}");
            } else {
                $this->fp = @fopen($filename, 'a');
            }
            $this->size = filesize($filename) + $size;
        }
    }

    /**
     * 写入初始数据
     * @return mixed
     */
    public function create()
    {
        $sql  = "-- -----------------------------\n";
        $sql .= "-- MySQL Data Transfer\n";
        $sql .= "--\n";
        $sql .= "-- Host     : " . config('database.hostname') . "\n";
        $sql .= "-- Port     : " . config('database.hostport') . "\n";
        $sql .= "-- Database : " . config('database.database') . "\n";
        $sql .= "--\n";
        $sql .= "-- Part : #{$this->file['part']}\n";
        $sql .= "-- Date : " . date("Y-m-d H:i:s") . "\n";
        $sql .= "-- -----------------------------\n\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";
        return $this->write($sql);
    }

    /**
     * 写入SQL语句
     * @param string $sql 要写入的SQL语句
     * @return int
     */
    private function write($sql = '')
    {
        $size = strlen($sql);

        // 由于压缩原因，无法计算出压缩后的长度，这里假设压缩率为50%，
        // 一般情况压缩率都会高于50%；
        $size = $this->config['compress'] ? $size / 2 : $size;

        $this->open($size);
        return $this->config['compress'] ? @gzwrite($this->fp, $sql) : @fwrite($this->fp, $sql);
    }

    /**
     * 备份表结构
     * @param string  $table 表名
     * @param integer $start 起始行数
     * @return array|bool|int  false - 备份失败
     */
    public function backup($table = '', $start = 0)
    {
        // 备份表结构
        if (0 == $start) {
            $result = Db::query("SHOW CREATE TABLE `{$table}`");
            $result = array_map('array_change_key_case', $result);

            $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";
            if (false === $this->write($sql)) {
                return false;
            }
        }

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

        //备份表数据
        if ($count) {
            // 写入数据注释
            if (0 == $start) {
                $sql  = "-- -----------------------------\n";
                $sql .= "-- Records of `{$table}`\n";
                $sql .= "-- -----------------------------\n";
                $this->write($sql);
            }

            // 备份数据记录
            $result = Db::query("SELECT * FROM `{$table}` LIMIT {$start}, 1000");
            foreach ($result as $row) {
                $row = array_map('addslashes', $row);
                $sql = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\r', '\n'), implode("', '", $row)) . "');\n";
                if (false === $this->write($sql)) {
                    return false;
                }
            }

            //还有更多数据
            if ($count > $start + 1000) {
                return array($start + 1000, $count);
            }
        }

        // 备份下一表
        return 0;
    }

    /**
     * 导入数据
     * @param integer $start 起始位置
     * @return array|bool|int
     */
    public function import($start = 0)
    {
        if ($this->config['compress']) {
            $gz   = gzopen($this->file[1], 'r');
            $size = 0;
        } else {
            $size = filesize($this->file[1]);
            $gz   = fopen($this->file[1], 'r');
        }

        $sql  = '';
        if ($start) {
            $this->config['compress'] ? gzseek($gz, $start) : fseek($gz, $start);
        }

        for ($i = 0; $i < 1000; $i++) {
            $sql .= $this->config['compress'] ? gzgets($gz) : fgets($gz);
            if (preg_match('/.*;$/', trim($sql))) {
                if (false !== Db::execute($sql)) {
                    $start += strlen($sql);
                } else {
                    return false;
                }
                $sql = '';
            } elseif ($this->config['compress'] ? gzeof($gz) : feof($gz)) {
                return 0;
            }
        }

        return array($start, $size);
    }

    /**
     * 导出
     * @param array $tables 表名
     * @param string $path 导出路径
     * @param string $prefix 表前缀
     * @param integer $export_data 是否导出数据
     * @author 蔡伟明 <314013107@qq.com>
     * @return bool
     */
    public static function export($tables = [], $path = '', $prefix = '', $export_data = 1)
    {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
        $datetime = date('Y-m-d H:i:s', time());
        $sql  = "-- -----------------------------\n";
        $sql .= "-- 导出时间 `{$datetime}`\n";
        $sql .= "-- -----------------------------\n";

        if (!empty($tables)) {
            foreach ($tables as $table) {
                $sql .= self::getSql($prefix . $table, $export_data);
            }

            // 写入文件
            if (file_put_contents($path, $sql)) {
                return true;
            };
        }
        return false;
    }

    /**
     * 导出卸载文件
     * @param  array $tables 表名
     * @param  string $path 导出路径
     * @param string $prefix 表前缀
     * @author 蔡伟明 <314013107@qq.com>
     * @return bool
     */
    public static function exportUninstall($tables = [], $path = '', $prefix = '')
    {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
        $datetime = date('Y-m-d H:i:s', time());
        $sql  = "-- -----------------------------\n";
        $sql .= "-- 导出时间 `{$datetime}`\n";
        $sql .= "-- -----------------------------\n";

        if (!empty($tables)) {
            foreach ($tables as $table) {
                $sql .= "DROP TABLE IF EXISTS `{$prefix}{$table}`;\n";
            }

            // 写入文件
            if (file_put_contents($path, $sql)) {
                return true;
            };
        }
        return false;
    }

    /**
     * 获取表结构和数据
     * @param string  $table 表名
     * @param integer $export_data 是否导出数据
     * @param integer $start 起始行数
     * @author 蔡伟明 <314013107@qq.com>
     * @return string
     */
    private static function getSql($table = '', $export_data = 0, $start = 0)
    {
        $sql  = "";
        if (Db::query("SHOW TABLES LIKE '%{$table}%'")) {
            // 表结构
            if ($start == 0) {
                $result = Db::query("SHOW CREATE TABLE `{$table}`");
                $sql .= "\n-- -----------------------------\n";
                $sql .= "-- 表结构 `{$table}`\n";
                $sql .= "-- -----------------------------\n";
                $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
                $sql .= trim($result[0]['Create Table']) . ";\n\n";
            }

            // 表数据
            if ($export_data) {
                $sql .= "-- -----------------------------\n";
                $sql .= "-- 表数据 `{$table}`\n";
                $sql .= "-- -----------------------------\n";

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

                // 备份数据记录
                $result = Db::query("SELECT * FROM `{$table}` LIMIT {$start}, 1000");
                foreach ($result as $row) {
                    $row = array_map('addslashes', $row);
                    $sql .= "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\r', '\n'), implode("', '", $row)) . "');\n";
                }

                // 还有更多数据
                if ($count > $start + 1000) {
                    $sql .= self::getSql($table, $export_data, $start + 1000);
                }
            }
        }

        return $sql;
    }

    /**
     * 析构方法，用于关闭文件资源
     */
    public function __destruct()
    {
        $this->config['compress'] ? @gzclose($this->fp) : @fclose($this->fp);
    }
}