Automate daily MYSQL backups in Laravel

Introduction

Databases are essential for any application because they provide a structured way to store, manage, and access data. This allows applications to efficiently organise and retrieve information, which is critical for their functionality and performance. Databases also enable data consistency, reliability, and security, ensuring that applications operate smoothly and that data remains accurate and safe.

Also Read: How Database effects the Web Design

The frequency of database backups depends on factors such as the amount of data being processed, the importance of the data, and the frequency of changes to the data. In general, it is recommended to backup databases regularly, with some applications requiring daily backups, while others may only need weekly or monthly backups. The backup frequency should be determined by the amount of data loss that can be tolerated, as well as the potential impact of data loss on the business or organisation. It is also a good practice to test the backups regularly to ensure that they can be successfully restored in the event of a disaster.

Earn $200 Credits with Digital Ocean

Automate MySQL Backups with DigitalOcean Spaces

Prior to follow the steps below, please make sure you have configured any storage driver properly in filesystems.php. In this article, we'll be learning to automate our MySQL database to Digital Ocean Spaces with Laravel. 

First things first, lets install Laravel Backup package by Spatie by running the following command in your project root.

composer require spatie/laravel-backup

You can  now publish the config file by running the following command:

php artisan vendor:publish --provider="Spatie\Backup\BackupServiceProvider"

Edit the configuration file config/backup.php to specify the backup disk and database connection details.

'disks' => [
  's3',
],

Also ensure that the default database is mysql.

'databases' => [
   'mysql',
],

Also Read: Deploying Laravel app with Envoy

Test Run

Now, we can test if its backing up our mysql database all fine, just run the following command

php artisan backup:run --only-db --only-to-disk=s3 

If successfully, it should display the output:

Starting backup...
Dumping database twt…
Determining files to backup...
Zipping 1 files and directories...
Created zip containing 1 files and directories. Size is 15.04 MB
Copying zip to disk named s3...
Successfully copied zip to disk named s3.
Backup completed!

Add to Scheduler

Once this is done, you can setup to run it using the scheduler on a frequency that you prefer. Add the following code to Console\Kernel.php. In our case, we are taking daily backups.

Also Read: Laravel Livewire Comments

$schedule->command("backup:run --only-db --only-to-disk=s3")->daily();

Lastly, if you haven't already added the cron entry into your server, you can do by:

* * * * * php /path-to-your-project/artisan schedule:run >> /dev/null 2>&1

For Notifications

Also Read: API Design and Development

Optionally, you can add a notification email address to receive notifications about backup success or failure by adding the following to the config/backup.php file:

'notifications' => [
     'mail' => [
         'to' => '[email protected]',
     ],
],

Conclusion

there you have it, your MySQL database will be backed up daily to your DO spaces. Please note that same could be done with Amazon S3 or any other cloud storage, you just need to change the driver keys and configurations.

Related: 

- Automate Code Formatting with Laravel Pint 
- Implement Laravel Tagging 
- Laravel Responsables 
- Securing Laravel app before deployment