laravel 凌晨0点 导出数据库

一、创建导出模型

<?php
namespace  App\Models;use Illuminate\Support\Facades\DB;class  DbBackup
{private $table;public function __construct(){$this->table = env('DB_DATABASE');}public function run($file = ''){$file       = !$file ? public_path($this->table.'.mysql') : $file;$tables     = DB::select('SHOW TABLES FROM '.$this->table);$tableName  = 'Tables_in_'.$this->table;$info  = "-- ----------------------------\r\n";$info .= "-- 日期：".date("Y-m-d H:i:s",time())."\r\n";$info .= "-- 本程序不适合处理超大量数据\r\n";$info .= "-- ----------------------------\r\n\r\n";file_put_contents($file,$info,FILE_APPEND);//将每个表的表结构导出到文件foreach($tables as $val){$sql    = "show create table ".$val->$tableName;$row    = DB::select($sql);$info   = "-- ----------------------------\r\n";$info   .= "-- Table structure for `".$val->$tableName."`\r\n";$info   .= "-- ----------------------------\r\n";$info   .= "DROP TABLE IF EXISTS `".$val->$tableName."`;\r\n";$temp   = 'Create Table';$sqlStr = $info.$row[0]->$temp.";\r\n\r\n";//追加到文件file_put_contents($file,$sqlStr,FILE_APPEND);}//将每个表的数据导出到文件foreach($tables as $val){$sql = "select * from ".$val->$tableName;$res = \DB::select($sql);//如果表中没有数据，则继续下一张表if(count($res)<1) continue;//$info = "-- ----------------------------\r\n";$info .= "-- Records for `".$val->$tableName."`\r\n";$info .= "-- ----------------------------\r\n";file_put_contents($file,$info,FILE_APPEND);//读取数据foreach ($res as $key => $value){$sqlStr     = "INSERT INTO `".$val->$tableName."` VALUES (";$sqlTemp    = '';foreach(get_object_vars($value) as $v){$sqlTemp = !$sqlTemp ? "'".$v ."'" : $sqlTemp . ',\''.$v."'";}$sqlStr =  $sqlStr . $sqlTemp .");\r\n";file_put_contents($file,$sqlStr,FILE_APPEND);}file_put_contents($file,"\r\n",FILE_APPEND);}return $file;}
}

二、创建任务文件

php artisan make:command Dbsql

三、任务业务逻辑

<?phpnamespace App\Console\Commands;use App\Models\DbBackup;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;class DbSql extends Command
{/*** The name and signature of the console command.** @var string*/protected $signature = 'command:dbsql';/*** The console command description.** @var string*/protected $description = 'Command description';/*** Create a new command instance.** @return void*/public function __construct(){parent::__construct();}/*** Execute the console command.** @return mixed*/public function handle(){$dir = public_path('upload/backup/');if(!is_dir($dir)) mkdir($dir);//因为要每天备份数据库，所以生成前清楚文件夹下的前一天sqlFile::cleanDirectory(public_path('upload/backup'));$file_name = date('Y-m-d').'.sql';$file_path = $dir.$file_name;$backup = new DbBackup();$backup->run($file_path);//下面为下载sql文件header('Content-type: application/sql');header("Content-Disposition: attachment; filename=\"{$file_name}\"");readfile($file_path);return "数据库导出完成";}
}

此时就可以对任务进行测试php artisan command:dbsql

四、高级配置任务的调度（可以对多个任务进行封装执行一次命令）

<?phpnamespace App\Console;use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;class Kernel extends ConsoleKernel
{/*** The Artisan commands provided by your application.** @var array* @info 配置command调度类*/protected $commands = [\App\Console\Commands\DbSql::class,\App\Console\Commands\gradeData::class,\App\Console\Commands\schoolCountData::class,//];/*** Define the application's command schedule.** @param  \Illuminate\Console\Scheduling\Schedule  $schedule* @return void* @info 执行任务调度*/protected function schedule(Schedule $schedule){$schedule->command('command:dbsql')//每五分钟
//                    ->everyFiveMinutes();//每天凌晨0点->daily();$schedule->command('command:gradeData')
//                    ->everyFiveMinutes();->daily();$schedule->command('command:schoolData')
//             ->everyFiveMinutes();->daily();//          ->hourly();}/*** Register the commands for the application.** @return void*/protected function commands(){$this->load(__DIR__.'/Commands');require base_path('routes/console.php');}
}

五、配置cron

执行crontab -e

1 * * * * php /var/www/sc/artisan DelRecycleAsset >> /dev/null 2>&1
0 0 * * * php /var/www/ty/artisan schedule:run >> /dev/null 2>&1```