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 [
    // 數(shù)據(jù)庫類型
    'type'        => 'mysql',
    // 數(shù)據(jù)庫連接DSN配置
    'dsn'         => '',
    // 服務(wù)器地址
    'hostname'    => 'localhost',
    // 數(shù)據(jù)庫名
    'database'    => 'test',
    // 數(shù)據(jù)庫用戶名
    'username'    => 'root',
    // 數(shù)據(jù)庫密碼
    'password'    => 'root',
    // 數(shù)據(jù)庫連接端口
    'hostport'    => '3306',
    // 數(shù)據(jù)庫連接參數(shù)
    'params'      => [],
    // 數(shù)據(jù)庫編碼默認(rèn)采用utf8
    'charset'     => 'utf8',
    // 數(shù)據(jù)庫表前綴
    'prefix'      => 'think_',
    // 數(shù)據(jù)庫調(diào)試模式
    'debug'       => false,
    // 數(shù)據(jù)庫備份路徑，沒有則自動創(chuàng)建
    'path'        => '',
    // 數(shù)據(jù)庫備份卷大小，單位為字節(jié)，設(shè)為0表示不限制備份大小
    'part'        => 20971520,
    // 數(shù)據(jù)庫備份文件壓縮格式，這里是gzip
    'compress'    => 'gzip',
    // 數(shù)據(jù)庫備份文件名
    'filename'    => '',
    // 數(shù)據(jù)庫備份文件是否需要壓縮
    'zip'         => true,
    // 數(shù)據(jù)庫備份文件是否需要分卷備份
    'split'       => true,
    // 數(shù)據(jù)庫備份時是否將存儲過程和觸發(fā)器一起備份
    'level'       => 9,
    // 數(shù)據(jù)庫備份文件的存儲路徑，最好為絕對路徑，這也是最關(guān)鍵的路徑
    '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()
    {
        // 防止備份數(shù)據(jù)過程超時
        set_time_limit(0);

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

        // 檢查目錄是否存在或者是否有權(quán)限寫入
        if(!is_dir($this->backupConfig['path'])){
            mkdir($this->backupConfig['path'], 0755, true);
        }else{
            if(!is_writeable($this->backupConfig['path'])){
                chmod($this->backupConfig['path'], 0755);
            }
        }

        // 備份所有數(shù)據(jù)表
        $result = Db::query("SHOW TABLES");

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

        // 備份所有表結(jié)構(gòu)和表數(shù)據(jù)
        $content = '';
        foreach($tables as $table){
            $content = $content . "/*" . PHP_EOL;
            $content = $content . "表名：" . $table . PHP_EOL;
            $content = $content . "表結(jié)構(gòu)：" . PHP_EOL;
            $content = $content . "*/" . PHP_EOL;
            $content = $content . $this->backupTableSchema($table);
            $content = $content . "/*" . PHP_EOL;
            $content = $content . "表數(shù)據(jù)：" . 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 {
                // 備份失敗
            }
        }
    }

    // 備份表結(jié)構(gòu)
    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;
    }

    // 備份表數(shù)據(jù)
    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()
    {
        // 防止還原數(shù)據(jù)過程超時
        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);

        // 使用";"分割內(nèi)容
        $statements = explode(";", $content);

        // 開始事務(wù)
        Db::startTrans();

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

        // 提交事務(wù)
        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!