Super Quick Tutorial: Backing Up Database with Laravel Artisan on the Scheduler

Before we start; there are going to be many many ways to approach this, but this is a super quick tutorial. So expect action rather than perfection. I’m going to assume we’re running an Ubuntu box, and that everything is running on a single LAMP server. You’re free to modify any of these to suit your needs. This will work for most small applications, and can be modified for larger ones.

We’re also going to run on the assumption your server can gzip and connect to S3 which there are loads of tutorials about. Finally we’ll assume you’ve enough space on your server to store the backup in addition to the production database prior to us sending it across and removing it.

The Problem / Situation

You want Laravel to just handle its own backups, like it would if you were using a plugin on most common CMS etc. We’ll assume we’re going to push these backups across to S3 (or a compatible provider, like Digital Ocean Spaces, as an example).

The Super Quick Solution

Create an Artisan Command, for the purpose of this I’m going to call mine system:backup. You then need to call it in the Console Kernel to make it call automatically.

The handle method in your Artisan Command

$started = microtime(true);
$this->info(date('H:i:s') . ": Started Backup");

// You may need to pull this from somewhere else
$database = config('database.connections.mysql.database');
$this->line(date('H:i:s') . ": Database - $database");

// This is where we're going to store the local dump
$shortName = 'db_backup_' . $database . '_' . date('Ymd_His') . '.sql.gz';
$this->line(date('H:i:s') . ": Shortname - $shortName");

$filename = storage_path('app/' . $shortName);
$this->line(date('H:i:s') . ": Path - $filename");
$this->comment(date('H:i:s') . ": Dumping the database");

// You may need to modify this command!! To authenticate correctly
$command = "mysqldump -u root $database | gzip > $filename";
$this->line($command);

// Dump the file to the $filename variable
shell_exec($command);

$this->comment(date('H:i:s') . ": Moving the database to spaces");

// Take the file, and move it to your s3 drive
Storage::disk('s3')->put('backups/' . $shortName, fopen($filename, 'r+'));
$this->comment(date('H:i:s') . ": Deleting the local backups");

// Delete the file from local
unlink($filename);

$finished = microtime(true);
$this->info(date('H:i:s') . ": Backup completed");
$this->line("Backup took " . (round($finished - $started, 3)) . " seconds");
return 1;

Also Consider

1. How you’re authenticating, e.g. if you’re using root is that the best idea, how is the password being stored and presented, and is it being output into your console logs?
2. If you need to deliberately not backup some tables, to save file size etc. if so you can include this in your command --ignore-table=$database.dont_back_me_up
3. If you need to backup multiple databases, you can encapsulate most of the above into a foreach