Simple Laravel DB backups for MySQL/MariaDB
Tags: laravel, sql, php
Published: 2019-08-19 15:13:54

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 <a href="https://aws.amazon.com/s3/">AWS S3</a>,. 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
1. Start by creating a S3 bucket(eg: _db-backups_) which is private. 
1. **Enable versioning for the bucket**, so that overwrite will still preserve the backups.
1. Complete creating the bucket and note down ARN(eg: _arn:aws:s3:::db-backups/*_).
1. Create an IAM user(eg: _s3user_) having programatic write access without selecting any existing policies. 
1. Note down the API access/secret keys and the ARN of the user(eg: _arn:aws:iam::8XX45XXXX57:user/s3user_).
1. 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/*"
              ]
           }
        ]
    }
```
1. 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 <a href="https://laravel.com/docs/5.8/filesystem">Laravel documentation</a> 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 <a href="https://laravel.com/docs/5.8/configuration#configuration-caching">Laravel configuration caching</a>, 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 <a href="https://laravel.com/docs/5.8/scheduling#introduction">Laravel scheduler</a> is configured. 

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