Laravel weekly mysql backups

How to back up your database with Laravel?

Backing up a database is very important for developers often time we make mistakes. If you by mistake delete your production database and you do not have a backup imagine how your boss would react?

Laravel is an awesome framework that I have ever worked with compared to other php frameworks. Let's learn how to create a new bash command that will take weekly backup.

Configure s3 Bucket and get credentials

In this tutorial we will use s3 bucket to put our database backups. First of all create a new s3 bucket by following steps and get the s3 credentials that we need for our laravel app.

Setup S3 Bucket

Let's add these credentials to our .env file so that we can use them when we push sql file to s3:

S3_REGION=...
S3_BUCKET=...
S3_ACCESS_KEY=...
S3_ACCESS_SECRET=...​

We have to modify existing s3 credentials located on config/filesystem.php file:

's3' => [
    'driver' => 's3',
    'region' => env('S3_REGION'),
    'bucket' => env('S3_BUCKET'),
    'key'    => env('S3_ACCESS_KEY'),
    'secret' => env('S3_ACCESS_SECRET'),
]

We are all set here with basic configurations. Let's create a new command that takes weekly backup.

How to create laravel command?

Using laravel command line tool we will generate console command. Open your terminal window while you are in laravel root directory and run following command:

php artisan make:console MySQLCommand​

Above command will generate a new console command in app/Console/Commands/MySQLCommand.php. We have to modify this file to meet following requirements:

  • you can generate backup
  • you can restore backup
  • you can delete backups older then 3 days

Basically, our aim is to create a regular backup of our database meanwhile we will not create lots of database backup. We will keep backups for last 3 days and will delete the reset as we won't need them anyways.

Let's modify our script, open app/Console/Commands/MySQLCommand.php file:

namespace App\Console\Commands;

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

class MySQLCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'backup:mysql {--command= : <create|restore> command to execute} {--snapshot= : provide name of snapshot}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Weekly MySQL Backup';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        switch ($this->option('command'))
        {
            case 'create':
                $this->takeSnapShot();
                break;

            case 'restore':
                $this->restoreSnapShot();
                break;

            default:
                $this->error("Invalid Option !!");
                break;
        }
    }

    /**
     * Function takes regular backup
     * for mysql database..
     *
     */
    private function takeSnapShot()
    {
        set_time_limit(0);

        // define target file
        $tempLocation     = '/tmp/' .env('DB_DATABASE') . '_' . date("Y-m-d_Hi") . '.sql';
        $targetFilePath   = '/mysql/' .env('DB_DATABASE') . '_' . date("Y-m-d_Hi") . '.sql';

        // run the cli job
        $process = new Process('mysqldump -u' .env('DB_USERNAME'). ' -p' .env('DB_PASSWORD'). ' ' .env('DB_DATABASE'). ' > ' .$tempLocation);
        $process->run();

        try {

            if ($process->isSuccessful())
            {
                $s3 = \Storage::disk('s3');
                $s3->put($targetFilePath, file_get_contents($tempLocation), 'private');

                $current_timestamp = time() - (72 * 3600);
                $allFiles = $s3->allFiles(env('APP_ENV'));

                foreach ($allFiles as $file)
                {
                    // delete the files older then 3 days..
                    if ( $s3->lastModified($file) <= $current_timestamp )
                    {
                         $s3->delete($file);
                         $this->info("File: {$file} deleted.");
                    }
                }
            }
            else {
                throw new ProcessFailedException($process);
            }

            @unlink($tempLocation);
        }
        catch (\Exception $e)
        {
            $this->info($e->getMessage());
        }
    }

    /**
     * Function restore given snapshot
     * for mysql database
     */
    private function restoreSnapShot()
    {
        $snapshot = $this->option('snapshot');
        if(!$snapshot) {
            $this->error("snapshot option is required.");
        }

        try {

            // get file from s3
            $s3 = \Storage::disk('s3');
            $found = $s3->get('/mysql/' .$snapshot. '.sql');
            $tempLocation = '/tmp/' .env('DB_DATABASE') . '_' . date("Y-m-d_Hi") . '.sql';

            // create a temp file
            $bytes_written = File::put($tempLocation, $found);
            if ($bytes_written === false) {
                $this->info("Error writing to file: " .$tempLocation);
            }

            // run the cli job
            $process = new Process("mysql -h " .env('DB_HOST'). " -u " .env('DB_USERNAME'). " -p" .env('DB_PASSWORD'). " ".env('DB_DATABASE'). " < {$tempLocation}");
            $process->run();

            //@unlink($tempLocation);
            if ($process->isSuccessful()) {
                $this->info("Restored snapshot: " .$snapshot);
            }
            else {
                throw new ProcessFailedException($process);
            }
        }
        catch (\Exception $e) {
            $this->info('File Not Found: '. $e->getMessage());
        }
    }
}

Now, we have created a console command however we have to use laravel scheduler feature to tell laravel that please use our new command to run at specified time.

Basically, we will use schedule our console command to run as a cron job. Let's schedule our console command to run daily or weekly whatever your criteria is for your backup.

Open app/Console/Kernal.php file and add our console command as shown below:

namespace App\Console;

use App\Console\Commands\MySQLCommand;
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
     */
    protected $commands = [
        MySQLCommand::class
    ];

    /**
     * Define the application's command schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
     * @return void
     */
    protected function schedule(Schedule $schedule)
    {
        $schedule->command('backup:mysql --command=create')
                 ->withoutOverlapping()
                 ->dailyAt('23:59');
    }
}

So we have scheduled our job to run daily @23:59 without overlapping.

How to run cron job using Laravel?

Finally, we have to run our cron job to create a new cron job for laravel app. Open your linux terminal window and run following commands:

# open cronjob file
crontab -e

# paste the following line
* * * * * php /path/to/artisan schedule:run >> /dev/null 2>&1​

Once the cronjob is set your linux server will call laravel app every minute and will execute all the scheduled jobs mentioned in app/Console/Kernel.php file.