如何使用ThinkPHP6實現數據庫備份與恢復

<?php return [     // 數據庫類型     'type'        => 'mysql',     // 數據庫連接DSN配置     'dsn'         =&gt; '',     // 服務器地址     'hostname'    =&gt; 'localhost',     // 數據庫名     'database'    =&gt; 'test',     // 數據庫用戶名     'username'    =&gt; 'root',     // 數據庫密碼     'password'    =&gt; 'root',     // 數據庫連接端口     'hostport'    =&gt; '3306',     // 數據庫連接參數     'params'      =&gt; [],     // 數據庫編碼默認采用utf8     'charset'     =&gt; 'utf8',     // 數據庫表前綴     'prefix'      =&gt; 'think_',     // 數據庫調試模式     'debug'       =&gt; false,     // 數據庫備份路徑，沒有則自動創建     'path'        =&gt; '',     // 數據庫備份卷大小，單位為字節，設為0表示不限制備份大小     'part'        =&gt; 20971520,     // 數據庫備份文件壓縮格式，這里是gzip     'compress'    =&gt; 'gzip',     // 數據庫備份文件名     'filename'    =&gt; '',     // 數據庫備份文件是否需要壓縮     'zip'         =&gt; true,     // 數據庫備份文件是否需要分卷備份     'split'       =&gt; true,     // 數據庫備份時是否將存儲過程和觸發器一起備份     'level'       =&gt; 9,     // 數據庫備份文件的存儲路徑，最好為絕對路徑，這也是最關鍵的路徑     'path'        =&gt; '/data/mysql/', ];

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;     } }

http://localhost/backup/backup

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;     } }

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