How to use ThinkPHP6 to implement database backup and recovery

In the process of developing business systems, the database is a very important part. Therefore, backing up and restoring the database is a very necessary operation. This article will combine examples of the ThinkPHP6 framework to introduce how to use ThinkPHP6 to implement database backup and recovery.

1. Database backup

1.1 Environment preparation

Before performing database backup, you need to confirm the following points:

1. The mysql database needs to be set up bin directory address, and add its path to the system Path variable;

2. The mysqldump command line tool needs to be installed;

3. Confirm that the backup is performed on the machine where the database is located. The user has the authority to execute the mysqldump command on the database.

1.2 Database backup implementation

1.2.1 Configure backup parameters

Create the database.php file in the config folder and set the database connection information and parameters required for backup.

<?php
return [
    // 数据库类型
    'type'        => 'mysql',
    // 数据库连接DSN配置
    'dsn'         => '',
    // 服务器地址
    'hostname'    => 'localhost',
    // 数据库名
    'database'    => 'test',
    // 数据库用户名
    'username'    => 'root',
    // 数据库密码
    'password'    => 'root',
    // 数据库连接端口
    'hostport'    => '3306',
    // 数据库连接参数
    'params'      => [],
    // 数据库编码默认采用utf8
    'charset'     => 'utf8',
    // 数据库表前缀
    'prefix'      => 'think_',
    // 数据库调试模式
    'debug'       => false,
    // 数据库备份路径，没有则自动创建
    'path'        => '',
    // 数据库备份卷大小，单位为字节，设为0表示不限制备份大小
    'part'        => 20971520,
    // 数据库备份文件压缩格式，这里是gzip
    'compress'    => 'gzip',
    // 数据库备份文件名
    'filename'    => '',
    // 数据库备份文件是否需要压缩
    'zip'         => true,
    // 数据库备份文件是否需要分卷备份
    'split'       => true,
    // 数据库备份时是否将存储过程和触发器一起备份
    'level'       => 9,
    // 数据库备份文件的存储路径，最好为绝对路径，这也是最关键的路径
    'path'        => '/data/mysql/',
];

1.2.2 Write backup code

Create the BackupController.php file under app/controller and add the following code.

<?php
declare(strict_types=1);

namespace appcontroller;

use thinkacadeDb;

class BackupController
{
    protected $backupConfig;

    public function __construct()
    {
        $this->backupConfig = config('database');
    }

    public function backup()
    {
        // 防止备份数据过程超时
        set_time_limit(0);

        $database = $this->backupConfig['database'];
        $filename = date('Ymd-His', time()) . ".sql";
        $path = $this->backupConfig['path'].$filename;

        // 检查目录是否存在或者是否有权限写入
        if(!is_dir($this->backupConfig['path'])){
            mkdir($this->backupConfig['path'], 0755, true);
        }else{
            if(!is_writeable($this->backupConfig['path'])){
                chmod($this->backupConfig['path'], 0755);
            }
        }

        // 备份所有数据表
        $result = Db::query("SHOW TABLES");

        $tables = array();
        foreach($result as $index => $row){
            $tables[] = $row['Tables_in_'.$database];
        }

        // 备份所有表结构和表数据
        $content = '';
        foreach($tables as $table){
            $content = $content . "/*" . PHP_EOL;
            $content = $content . "表名：" . $table . PHP_EOL;
            $content = $content . "表结构：" . PHP_EOL;
            $content = $content . "*/" . PHP_EOL;
            $content = $content . $this->backupTableSchema($table);
            $content = $content . "/*" . PHP_EOL;
            $content = $content . "表数据：" . PHP_EOL;
            $content = $content . "*/" . PHP_EOL;
            $content = $content . $this->buildInsertSql($table);
        }

        // 是否需要压缩
        if ($this->backupConfig['zip']) {
            $zip = new ZipArchive();
            $zipfilename = $this->backupConfig['path'] . date('Ymd-His', time()) . ".zip";
            if ($zip->open($zipfilename, ZipArchive::OVERWRITE) === TRUE) {
                $zip->addFile($path,$filename);
                $zip->close();
                // 删除非压缩的文件
                unlink($path);
            } else {
                // 备份失败
            }
        }
    }

    // 备份表结构
    protected function backupTableSchema($table)
    {
        $database = $this->backupConfig['database'];
        $result = Db::query("SHOW CREATE TABLE `" . $table . "`");
        $create = $result[0]['Create Table'] . ";" . PHP_EOL.PHP_EOL;
        return $create;
    }

    // 备份表数据
    protected function buildInsertSql($table)
    {
        $database = $this->backupConfig['database'];
        $result = Db::query("SELECT * FROM `" . $table . "`");
        $insert = '';
        foreach ($result as $key => $value) {
            $keys = array_keys($value);
            $values = array_map(array(Db::class, 'quote'), array_values($value));
            $values = join(",", $values);
            $insert .= "INSERT INTO `" . $table . "` (`" . join("`,`", $keys) . "`) VALUES (" . $values . ");" . PHP_EOL;
        }
        $insert .= PHP_EOL;
        return $insert;
    }
}

1.2.3 Perform backup

Enter the following URL address in the browser to perform backup:

http://localhost/backup/backup

1.3 Database recovery

1.3.1 Write recovery code

Create the RecoveryController.php file under app/controller and add the following code.

<?php
declare(strict_types=1);

namespace appcontroller;

use thinkacadeDb;

class RecoveryController
{
    protected $backupConfig;

    public function __construct()
    {
        $this->backupConfig = config('database');
    }

    public function recovery()
    {
        // 防止还原数据过程超时
        set_time_limit(0);
        ini_set('memory_limit', '1024M');

        $filename = input('get.filename');

        // 读取备份文件
        if ($this->backupConfig['zip']) {
            $zip = new ZipArchive();
            if ($zip->open($this->backupConfig['path'].$filename) === true) {
                $filename = $zip->getNameIndex(0);
                $zip->extractTo($this->backupConfig['path']);
                $zip->close();
            }
        }

        $content = file_get_contents($this->backupConfig['path'] . $filename);

        // 使用";"分割内容
        $statements = explode(";", $content);

        // 开始事务
        Db::startTrans();

        foreach ($statements as $index => $stmt) {
            if (trim($stmt) === '') {
                continue;
            }
            $results = Db::query($stmt);
            if ($results === false) {
                Db::rollback();
                return false;
            }
        }

        // 提交事务
        Db::commit();

        // 删除非压缩的文件
        unlink($this->backupConfig['path'] . $filename);

        return true;
    }
}

1.3.2 Perform recovery

Enter the following url address in the browser to perform recovery:

http://localhost/recovery/recovery?filename=20200101-121212.sql.zip

The above is the implementation method for database backup and recovery in ThinkPHP6. Readers can apply the code to their own projects and flexibly use the techniques to make our business more robust and reliable.

The above is the detailed content of How to use ThinkPHP6 to implement database backup and recovery. For more information, please follow other related articles on the PHP Chinese website!