How to use Larave to create a MySQL database backup schedule task

You can export the entire database by running a one-line command in the terminal. This solution is not only simple and direct but also effective. But there are more automated solutions. Let’s find out what it is!

Background

A few days ago I logged into the wrong database and killed 18 000 Online data recording. What's worse is that we don't have a backup of this database. I then decided to write a script that would automate the database export and save to a SQL file.

In addition, if you need a powerful data backup system, you can take a look at this extension. In this way, we do not need to pay attention to more database backup details and only need to focus on the database export and export plan.

Export command

Using this one-line snippet, you can quickly export the database to a SQL file. Many applications use the following command to export data from the database.

mysqldump -u[user] -p[pass] [db] > [file_path]

As you can see, we need to pass in the username, password and DB that needs to be exported, and then redirect the output to the specified file. It is simple and convenient to consume and has remarkable effects.

Now let us encapsulate this command by using the artisan command to make it easier to run and add to scheduled tasks.

Artiasn console interface warm-up

An important starting point for integrating shell commands by using the artisan console (console) is to be able to write once and run everywhere. What we need to do is configure and use these configurations. This means that once a parameter is modified, we do not need to adjust it through the command itself. Next, we can create this console command.

Create a custom command by running the php artisan make:comman command. Here our command is named BackupDatabase. After creating your command, Laravel will automatically register the command with the system. All you need to do is define the signature of the command.

Let’s preview this command file; we’ll explain how it works later:

<?php namespace App\Console\Commands;

use Illuminate\Console\Command;
use Symfony\Component\Process\Process;
use Symfony\Component\Process\Exception\ProcessFailedException;

class BackupDatabase extends Command
{
    protected $signature = &#39;db:backup&#39;;

    protected $description = &#39;Backup the database&#39;;

    protected $process;

    public function __construct()
    {
        parent::__construct();

        $this->process = new Process(sprintf(
            'mysqldump -u%s -p%s %s > %s',
            config('database.connections.mysql.username'),
            config('database.connections.mysql.password'),
            config('database.connections.mysql.database'),
            storage_path('backups/backup.sql')
        ));
    }

    public function handle()
    {
        try {
            $this->process->mustRun();

            $this->info('The backup has been proceed successfully.');
        } catch (ProcessFailedException $exception) {
            $this->error('The backup process has been failed.');
        }
    }
}

As you can see, our command signature is db:backup. Since Laravel already has a db command space, this command is clearer.

In the constructor, we instantiate a new Symfony\Component\Process\Process instance. The reason is that here we need to use Symfony's Process component - rather than simply calling the shell_exec function. This component provides many great features. For example, if a process fails, we can throw an exception and then handle the exception efficiently.

If you are using the run() method of process, you need to manually detect running errors and throw exceptions. And through the mustRun() method, it will automatically throw an exception for us. You can get more information from the documentation.

We pass the shell command and the required parameters into the sprintf() function, which will replace the placeholders with the actual parameters. After processing the process instance, we can proceed to the next step of handle)( method.

In the handle method, we have a try-catch code block. First, we call the mustRun() method. If there is no error, we output green information to the console; otherwise, a ProcessFailedException exception is thrown and captured in the catch code block , and output error information to the console.

What next? If we execute the php artisan db:backup command on the console, we will go to the database here and save it to storage/backups/backup.sql file. It is running well, but we still have some work to do, which is to write scheduled tasks.

Write scheduled tasks for backup tasks

First of all, you can easily create scheduled tasks in Laravel. It provides a built-in API interface for defining tasks that is both simple and supports chain operations. Before continuing to read this article, it is strongly recommended to read the Chinese translation of its documentation.

Then, go to the Console/Kernel.php file and look at the schedule() function. We can define tasks and task execution cycles. For example, we want to do this on every Monday 23:00 Run the plan, which is coded as follows:

protected function schedule(Schedule $schedule)
{
    $schedule->command('db:backup')->mondays()->at('23:00');
}

Isn’t it very simple? What’s even better is that you can define as many commands as you want here. The scheduler will run the specified Time to process these tasks separately.

To run this scheduler, we need to execute the php artisan schedule:run command, and then it will trigger all the commands that need to be run. This is great, we Just one line of command can trigger any corresponding command at a specified time.

But the question now is how to manage the scheduler itself. This is a bit like a chicken-and-egg problem, but believe me, It's not that complicated.

Use Forge to set up the scheduler

If you still need basic support related to the CORN execution principle, Mohamed Said has a series of articles that explain CRON-related knowledge in depth. The key point is that we don't need to create a CRON timer for each scheduled task. We only need to define the task execution luck as introduced before, and then run the task caller.

However, we need to set the time to run the php artisan schedule:run command. If you use Laravel Forge, you can easily create scheduled tasks. Just go to the Scheduler tab and you can create any scheduled task you want.

As you can see, the schedule:run command has been added by default. All you need to do is define Task frequency and replace the default commands with commands for your server.

If ready, the scheduler will run every time at the appropriate time and trigger all commands to be executed.

Summary

It’s great that we can provide a lightweight solution without relying on a larger package. Here, we can also take advantage of Laravel to meet our needs.

We can easily export the database using the Process component and encapsulate it in the artisan command. We can then quickly set up an execution cycle for our command and Laravel's scheduler will take care of the rest. We could just lie down and do the work.

Related recommendations:

The design process of the configuration management system under the Laravel framework (with code)

The above is the detailed content of How to use Larave to create a MySQL database backup schedule task. For more information, please follow other related articles on the PHP Chinese website!