laravel 凌晨0点 导出数据库

一、创建导出模型

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

二、创建任务文件

php artisan make:command Dbsql
1

三、任务业务逻辑

namespace 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);
        //因为要每天备份数据库，所以生成前清楚文件夹下的前一天sql
        File::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 "数据库导出完成";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

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

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

namespace 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');
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

五、配置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```

1
2
3