本文目录导读：
我来提供一个完整的ThinkPHP项目定时备份数据库的解决方案：
创建数据库备份类
<?php
// app/common/library/DatabaseBackup.php
namespace app\common\library;
use think\facade\Db;
use think\facade\Config;
use think\Exception;
class DatabaseBackup
{
/**
* 备份文件存储路径
*/
protected $backupPath;
/**
* 备份文件名前缀
*/
protected $prefix = 'db_backup_';
/**
* 需要备份的表
*/
protected $tables = [];
/**
* 需要跳过的表
*/
protected $ignoreTables = [
'sessions',
'logs',
'cache'
];
public function __construct()
{
$this->backupPath = root_path() . 'backup' . DIRECTORY_SEPARATOR;
if (!is_dir($this->backupPath)) {
mkdir($this->backupPath, 0755, true);
}
}
/**
* 执行备份
*/
public function backup()
{
try {
// 获取所有表
$tables = $this->getTables();
$sql = "-- ThinkPHP Database Backup\n";
$sql .= "-- Date: " . date('Y-m-d H:i:s') . "\n";
$sql .= "-- Database: " . config('database.connections.mysql.database') . "\n\n";
$sql .= "SET FOREIGN_KEY_CHECKS=0;\n\n";
// 备份每个表
foreach ($tables as $table) {
if (in_array($table, $this->ignoreTables)) {
continue;
}
$sql .= $this->backupTable($table);
}
$sql .= "SET FOREIGN_KEY_CHECKS=1;\n";
// 写入备份文件
$filename = $this->backupPath . $this->prefix . date('Ymd_His') . '.sql';
file_put_contents($filename, $sql);
// 压缩文件
$this->compress($filename);
// 清理旧备份
$this->cleanOldBackups();
return [
'status' => true,
'message' => '数据库备份成功',
'filename' => basename($filename),
'size' => $this->formatSize(filesize($filename . '.gz'))
];
} catch (Exception $e) {
return [
'status' => false,
'message' => '备份失败: ' . $e->getMessage()
];
}
}
/**
* 获取所有数据表
*/
protected function getTables()
{
$tables = Db::query('SHOW TABLES');
$result = [];
foreach ($tables as $table) {
foreach ($table as $value) {
$result[] = $value;
}
}
return $result;
}
/**
* 备份单个表
*/
protected function backupTable($table)
{
$sql = "-- Table structure for table `{$table}`\n";
$sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
// 获取建表语句
$createTable = Db::query("SHOW CREATE TABLE `{$table}`");
$sql .= $createTable[0]['Create Table'] . ";\n\n";
// 获取表数据
$sql .= "-- Data for table `{$table}`\n";
$sql .= "INSERT INTO `{$table}` VALUES\n";
$rows = Db::table($table)->select();
$values = [];
foreach ($rows as $row) {
$values[] = "(" . $this->formatValues($row) . ")";
}
if (!empty($values)) {
$sql .= implode(",\n", $values) . ";\n\n";
} else {
$sql = str_replace("INSERT INTO `{$table}` VALUES\n", "INSERT INTO `{$table}` VALUES ();\n", $sql);
}
return $sql;
}
/**
* 格式化字段值
*/
protected function formatValues($row)
{
$values = [];
foreach ($row as $value) {
if ($value === null) {
$values[] = 'NULL';
} elseif (is_numeric($value)) {
$values[] = $value;
} else {
$values[] = "'" . mysqli_real_escape_string($this->getConnection(), $value) . "'";
}
}
return implode(',', $values);
}
/**
* 获取数据库连接
*/
protected function getConnection()
{
return Db::connect()->getConnection();
}
/**
* 压缩备份文件
*/
protected function compress($filename)
{
$data = file_get_contents($filename);
$gzdata = gzencode($data, 9);
file_put_contents($filename . '.gz', $gzdata);
unlink($filename); // 删除原始文件
}
/**
* 清理旧备份
*/
protected function cleanOldBackups($keepDays = 7)
{
$files = glob($this->backupPath . $this->prefix . '*.gz');
$keepTime = time() - ($keepDays * 24 * 3600);
foreach ($files as $file) {
if (filemtime($file) < $keepTime) {
unlink($file);
}
}
}
/**
* 恢复数据库
*/
public function restore($filename)
{
try {
$filepath = $this->backupPath . $filename;
if (!file_exists($filepath)) {
throw new Exception('备份文件不存在');
}
// 解压文件
$gzdata = file_get_contents($filepath);
$sql = gzdecode($gzdata);
// 执行SQL
Db::execute($sql);
return [
'status' => true,
'message' => '数据库恢复成功'
];
} catch (Exception $e) {
return [
'status' => false,
'message' => '恢复失败: ' . $e->getMessage()
];
}
}
/**
* 获取备份列表
*/
public function getBackupList()
{
$files = glob($this->backupPath . $this->prefix . '*.gz');
$list = [];
foreach ($files as $file) {
$list[] = [
'filename' => basename($file),
'size' => $this->formatSize(filesize($file)),
'date' => date('Y-m-d H:i:s', filemtime($file))
];
}
// 按日期倒序排序
usort($list, function($a, $b) {
return strtotime($b['date']) - strtotime($a['date']);
});
return $list;
}
/**
* 格式化文件大小
*/
protected function formatSize($bytes)
{
if ($bytes >= 1073741824) {
return number_format($bytes / 1073741824, 2) . ' GB';
} elseif ($bytes >= 1048576) {
return number_format($bytes / 1048576, 2) . ' MB';
} elseif ($bytes >= 1024) {
return number_format($bytes / 1024, 2) . ' KB';
} else {
return $bytes . ' B';
}
}
}
创建定时任务控制器
<?php
// app/command/BackupDatabase.php
namespace app\command;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use app\common\library\DatabaseBackup;
class BackupDatabase extends Command
{
protected function configure()
{
$this->setName('backup:database')
->setDescription('自动备份数据库');
}
protected function execute(Input $input, Output $output)
{
$backup = new DatabaseBackup();
$result = $backup->backup();
if ($result['status']) {
$output->writeln("备份成功: " . $result['filename'] . " (" . $result['size'] . ")");
} else {
$output->writeln("备份失败: " . $result['message'] . PHP_EOL . $result['message']);
}
return 0;
}
}
注册命令
在 config/console.php 中添加：
<?php
// config/console.php
return [
'commands' => [
'backup:database' => \app\command\BackupDatabase::class,
],
];
创建定时任务配置
Linux Cron 配置方式：
# 每天凌晨2点备份 0 2 * * * cd /path/to/your/project && php think backup:database >> /path/to/your/project/runtime/logs/backup.log 2>&1 # 每周一凌晨2点备份 0 2 * * 1 cd /path/to/your/project && php think backup:database >> /path/to/your/project/runtime/logs/backup.log 2>&1 # 每6小时备份一次 0 */6 * * * cd /path/to/your/project && php think backup:database >> /path/to/your/project/runtime/logs/backup.log 2>&1
Windows 计划任务配置：
创建 backup.bat 文件：
@echo off cd /d D:\your\project\path php think backup:database >> runtime\logs\backup.log 2>&1
使用 ThinkPHP 自带定时任务（可选）
如果你使用 ThinkPHP 6.x,可以配置计划任务：
<?php
// config/crontab.php
return [
'tasks' => [
[
'name' => '数据库备份',
'command' => 'backup:database',
'schedule' => '* * * * *',
'status' => 1,
],
],
];
创建备份管理控制器（后台管理）
<?php
// app/admin/controller/Backup.php
namespace app\admin\controller;
use think\Request;
use think\facade\View;
use app\common\library\DatabaseBackup;
class Backup
{
protected $backup;
public function __construct()
{
$this->backup = new DatabaseBackup();
}
/**
* 备份列表
*/
public function index()
{
$list = $this->backup->getBackupList();
return View::fetch('backup/index', ['list' => $list]);
}
/**
* 立即备份
*/
public function create()
{
$result = $this->backup->backup();
return json($result);
}
/**
* 恢复备份
*/
public function restore($filename)
{
$result = $this->backup->restore($filename);
return json($result);
}
/**
* 删除备份
*/
public function delete($filename)
{
$filepath = root_path() . 'backup/' . $filename;
// 安全检查
if (strpos($filepath, '..') !== false || !file_exists($filepath)) {
return json([
'status' => false,
'message' => '文件不存在'
]);
}
if (unlink($filepath)) {
return json([
'status' => true,
'message' => '删除成功'
]);
} else {
return json([
'status' => false,
'message' => '删除失败'
]);
}
}
/**
* 下载备份
*/
public function download($filename)
{
$filepath = root_path() . 'backup/' . $filename;
if (!file_exists($filepath)) {
return '文件不存在';
}
return download($filepath, $filename);
}
}
邮件通知（可选）
// 在backup方法中添加邮件通知
protected function sendNotification($result)
{
try {
// 使用ThinkPHP邮件发送
$content = "数据库备份" . ($result['status'] ? '成功' : '失败') . "\n";
$content .= "时间: " . date('Y-m-d H:i:s') . "\n";
if ($result['status']) {
$content .= "文件名: " . $result['filename'] . "\n";
$content .= "文件大小: " . $result['size'] . "\n";
} else {
$content .= "错误信息: " . $result['message'] . "\n";
}
// 发送邮件
// Mail::send(...);
} catch (\Exception $e) {
// 记录日志
\think\facade\Log::error('备份通知发送失败: ' . $e->getMessage());
}
}
注意事项：
- 备份路径权限：确保
backup目录有写权限 - 文件大小限制：数据库较大时请注意磁盘空间
- 安全防护：backup目录应设置访问限制
- 测试恢复：定期测试备份文件的恢复功能
- 日志记录：记录备份操作日志
这个方案提供了完整的数据库备份功能，包括自动备份、手动备份、恢复、下载等功能,可以直接集成到项目中。
