Scheduling MySQL Backups with Laravel – Pine

You can export your whole database by running one line in your command line. It’s accessible and useful. But it’s a bit wiser to you automatize the entire process. Let’s see how!

The Background

A few days ago, I signed in to the wrong database and deleted around 18 000 records that were in use. Not to mention, we had no backup for that data. So after all, I decided to make a little script to dump the database and save it to an SQL file automatically.

But, if you are here for a fully working backup system, you should take a look at this package. We won’t cover more just scheduling database exports and its scheduling.

The Dump Command

With this one-liner snippet, you can quickly dump and save your database to an SQL file. Many apps use this to export data from a database.

As you see, we give the user, the password, and the DB, then put the output to the given file. Quick, easy and helpful.

Now let’s wrap this in an artisan command, to make it easily runnable and schedulable.

Warming Up the Artisan Console

One of the main point to integrate the shell command in the artisan console is that we can make it work in any environment. All we have to do, to use the config we have and use them. It means, if any of the parameters change, we don’t have to bother with the command itself. Move on, to create the console command.

You can create commands by running the php artisan make:command order.  We named our command to BackupDatabase. After you created your console command, Laravel automatically registers it for you in the artisan console. All you need to do, to specify the signature of the command.

Let’s take a look at the full command; then we explain how it does work:

In the constructor, we initialize a new SymfonyComponentProcessProcess instance. The reason we use Symfony’s Process component here – and not the simple shell_exec function – is simple. It offers many nice features. For example, if the process fails, we want to throw an exception that we can handle how we want.

We pass the shell command and the parameters to the sprintf() function, that will replace the placeholders with the credentials we passed. After we built and passed the command to the process, we can move on to the handle() method.

In the handle method, we have an elementary try-catch block. First, we call the mustRun() method, and if everything is fine, we print a green message on the screen; otherwise, it will throw a ProcessFailedException that we can catch in the other block and we write an error message on the console screen.

So what’s now? If we run the php artisan db:run command, we export the database and save it to the storage/backups/backup.sql file. Works well, but still, we have one job left, to schedule this task.

Scheduling the Backup Task

First of all, Laravel makes task scheduling very easy. It provides a very simple and fluent API to define your tasks’ run cycle. So before you continue, it’s strongly suggested to check out the documentation.

Now, we can navigate to the Console/Kernel.php and take a look at the schedule() method. We can define our tasks and the period for them. Let’s say we want to run the backups on every Monday at 23:00. With code, it looks like this:

Simple, right? Now the good thing is, you can define as much commands you want here. The scheduler will handle them separately and trigger them when the exact time comes.

To run the scheduler, we need to call the php artisan schedule:run command, and it runs all the commands that should be triggered. It’s good because with only one command we can take a look if we have any tasks to perform and if yes trigger them.

Now the question is, how can schedule the scheduler itself. It’s a bit like a dream in a dream, but believe me, it’s not that complex.

Setting Up the Scheduler in Forge

If you need a basic introduction how CRON works, Mohamed Said wrote an excellent series about it. The point is, we don’t need to set a CRON tab for every job we schedule. We can define the periods like above and run the scheduler that takes care of the rest.

But, we need to set the period when do we want to run the php artisan schedule:run command. If you are using Laravel Forge, you have an easy job. So, navigate to your server’s Scheduler tab, and you can schedule any command you wish here.

As you see, by default the schedule:run command is ready to add, all you need to do, to define the frequency and to replace the “default” to your site’s domain.

If you are ready, the scheduler will run every time, when it’s appropriate and it will trigger all the commands are due to perform.

It’s nice; we can roll out our little solutions every time without depending on a bigger package. Here also, we can use Laravel’s advantages and cover the needs.

We can easily export our database with the Process component and wrap it up in an artisan command. Then we can quickly set a frequency for our command and Laravel’s scheduler will take care of the rest. We can lay back and feels a bit safer than before.