Simple Laravel DB backups for MySQL/MariaDB

Databases(DB) persists all critical information related to a web application. Hence DB backups are a necessity. I will post a simple code snippet suitable for Laravel applications to backup your DB to AWS S3,. I assume that you are using EC2 machine for hosting the Laravel application.

Since configuring and setting up a S3 bucket is out of scope for the article, I will outline the steps for reference

Start by creating a S3 bucket(eg: db-backups) which is private.
Enable versioning for the bucket, so that overwrite will still preserve the backups.
Complete creating the bucket and note down ARN(eg: arn:aws:s3:::db-backups/*).
Create an IAM user(eg: s3user) having programatic write access without selecting any existing policies.
Note down the API access/secret keys and the ARN of the user(eg: arn:aws:iam::8XX45XXXX57:user/s3user).

Under the Permissions tab of the IAM user, add the following as an Inline policy.

{
    "Version":"2012-10-17",
    "Statement":[
       {
          "Effect":"Allow",
          "Action":[
             "s3:PutObject"
          ],
          "Resource":[
             "arn:aws:s3:::db-backups/*"
          ]
       }
    ]
}

In the S3 bucket policy, add the following:

    {
       "Version": "2012-10-17",
       "Statement": [
          {
             "Sid": "statement1",
             "Effect": "Allow",
             "Principal": {
                "AWS": "arn:aws:iam::8XX45XXXX57:user/s3user"
             },
             "Action": [
                 "s3:PutObject"
             ],
             "Resource": [
                "arn:aws:s3:::db-backups/*"
             ]
          }
       ]
    }

Make sure to update the user, bucket ARN's. This IAM user will only be able to write to the bucket. No other permissions are granted for security reasons.

Coming back to the main topic, DB backups in Laravel. If you are using RDS as the DB store, EC2 machine won't have the mysqldump command. Hence, please install them using (Ubuntu distros)

sudo apt-get install mysql-client

Once installed, execute the following commands in shell and note the path
which mysqldump (eg: /usr/bin/mysqldump) &
which gzip (eg: /bin/gzip)

Update the .env file variables of the Laravel application

AWS_ACCESS_KEY_ID=XXXXXXX
AWS_SECRET_ACCESS_KEY=XXXXXXX
AWS_DEFAULT_REGION=eu-central-1
DB_AWS_BUCKET=db-backups

MYSQLDUMP_EXE=/usr/bin/mysqldump
GZIP_EXE=/bin/gzip

Read through the Laravel documentation and install packages for S3 access. Create a new disk in config/filesystems.php file, eg:

        's3db' => [
            'driver' => 's3',
            'key' => env('AWS_ACCESS_KEY_ID'),
            'secret' => env('AWS_SECRET_ACCESS_KEY'),
            'region' => env('AWS_DEFAULT_REGION'),
            'bucket' => env('DB_AWS_BUCKET'),
        ],

To utilise Laravel configuration caching, I have created a file called config/env.php, where custom ENV variables are configured. Its similar to config/app.php, except it holds variables which are newly introduced in the application. A sample follows:

<?php 

return [
    'mysqldump_exe' => env('MYSQLDUMP_EXE'),
    'gzip_exe' => env('GZIP_EXE'),
];

I prefer to create custom classes, under the app/Classes directory. Feel free to use yours, but change the namespace of the following file accordingly. Create a file app/Classes/DbBackup.php. The code is self explanatory.

<?php

namespace App\Classes;

use Illuminate\Support\Facades\Storage;

class DbBackup
{
    private $fileName, $tmpFile, $gzipFile;

    /**
     * Initialise the variables
     *
     * @return void
     */
    public function __construct()
    {
        $this->fileName = config('database.connections.mysql.database').'-'.date('Y-m-d').'.sql';
        $this->tmpFile = '/tmp/'.$this->fileName;
        $this->gzipFile = $this->tmpFile.'.gz';
    }

    /**
     * Create the backup
     *
     * @return object this
     */
    protected function createBackup()
    {
        exec(config('env.mysqldump_exe').
            ' --user='.config('database.connections.mysql.username').
            ' --host='.config('database.connections.mysql.host').
            ' --password='.config('database.connections.mysql.password').
            ' --databases '.config('database.connections.mysql.database').
            ' > '.$this->tmpFile);

        exec(config('env.gzip_exe').' '.$this->tmpFile);

        return $this;
    }

    /**
     * Upload to S3
     *
     * @return object this
     */
    protected function uploadToS3()
    {
        Storage::disk('s3db')
            ->put($this->fileName.'.gz', fopen($this->gzipFile, 'r'));

        return $this;
    }

    /**
     * Delete the temp file
     *
     * @return object this
     */
    protected function removeBackup()
    {
        sleep(2);
        unlink($this->gzipFile);
    }

    /**
     * Method which calls all sub functions
     *
     * @return void
     */
    public function init()
    {
        $this->createBackup()
            ->uploadToS3()
            ->removeBackup();
    }

}

Now, we require the DB backups to be run daily, only in Production systems. Hence update the method in app/Console/Kernel.php.

    protected function schedule(Schedule $schedule)
    {
        // this code will create the automatic DB backups.
        $schedule->call('App\Classes\DbBackup@init')
            ->daily()
            ->environments(['production']);
    }

The above code will only run if the Laravel scheduler is configured.

That's it, folks! DB backups will be created & saved to S3 daily at midnight.

Blog

Simple Laravel DB backups for MySQL/MariaDB

KM

Join Our Newsletter. No Spam, Only the good stuff.

Related