If you’ve ever had to manually create backups of your MySQL database for your Laravel project, you know how tedious and error-prone it can be. With a growing application, database sizes can balloon quickly, making manual backups a chore that’s easy to neglect. Before long, you’re facing the nightmare scenario of corrupted data or lost functionality.
You’ll build an automated system that takes care of MySQL backups for you, effortlessly running daily backups without requiring your intervention. Specifically, by the end of this tutorial, you’ll have set up GitHub Actions to run a backup script in Laravel and configured cron jobs to automate the process, ensuring your database is always safe and up-to-date.
Setting up a MySQL Database on a Local Environment
To get started with automating MySQL backups using GitHub Actions, we first need to set up a local environment for our database. I’ll be using XAMPP as my local development stack, but you can use any other setup like MAMP or Laravel Homestead.
First, download and install XAMPP from the official website: https://www.apachefriends.org/en/xampp.html. Follow the installation instructions for your operating system.
Once installed, create a new MySQL database by running the following command in the XAMPP control panel:
mysql -u root -pThis will open the MySQL command-line interface. Create a new database with the following SQL query:
CREATE DATABASE laravel_backup;Create a user for our Laravel project and grant privileges to this database:
CREATE USER 'laravel_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON laravel_backup.* TO 'laravel_user'@'%';
FLUSH PRIVILEGES;Note down the username, password, and database name as we’ll need these later for our Laravel project.
Creating a GitHub Repository for Your Laravel Project
Now that you have your local environment set up with a MySQL database and your Laravel project ready to go, it’s time to create a GitHub repository for your application.
First, log in to your GitHub account and click on the “+” button in the top-right corner of the dashboard. Select “New repository” from the dropdown menu. Fill in the required information:
- Repository name: Give your repository a unique name that reflects your project’s identity.
- Description: Provide a brief description of what your project does.
- Public/Private: Choose whether you want to make your repository public or private.
Once you’ve filled out this information, click “Create repository.” GitHub will then ask if you want to initialize the repository with a README file. Select “README” from the dropdown menu and choose whether you want to use the default template or create one from scratch.
Next, navigate to your terminal and run git add . followed by git commit -m "Initial commit" to stage all changes and commit them to your local repository. Then, run git branch -M main to rename your default branch from “master” to “main.” Finally, run git remote add origin https://github.com/your-username/repository-name.git followed by git push -u origin main to link your local repository with the one you just created on GitHub.
# Replace 'your-username' and 'repository-name' with your actual GitHub credentials.
$ git branch -M main
$ git remote add origin https://github.com/your-username/repository-name.git
$ git push -u origin mainWith these steps complete, you now have a GitHub repository set up for your Laravel project.
Defining a Backup Script in Laravel
To automate our MySQL backups, we need to create a script that will dump our database and store it safely somewhere. We’ll do this by creating a new artisan command in our Laravel project.
Create a new file called BackupDatabase.php in the app/Console/Commands directory of your project:
// app/Console/Commands/BackupDatabase.php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Storage;
class BackupDatabase extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'backup:database';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Backup database to storage';
public function handle()
{
// Get the dump of our database
$dump = \DB::connection()->getPdo()->exec('mysqldump ' . config('database.connections.mysql.database'));
// Store the dump in our storage directory
Storage::disk(config('backup.storage.disk'))->put(config('backup.storage.filename'), $dump);
}
}This script uses Laravel’s built-in DB facade to get a PDO instance for our MySQL connection. We then use this instance to run the mysqldump command, which dumps our database into a string. Finally, we store this dump in our storage directory.
Make sure to configure your storage driver in your .env file, and add the following code to your config/backup.php file:
// config/backup.php
return [
'storage' => [
'disk' => 'local',
'filename' => 'database.sql.gz',
],
];We’ll use this script in the next section to set up a GitHub Action that runs our backup command automatically.
Configuring GitHub Actions to Run the Backup Script
Now that our backup script is defined in Laravel, it’s time to configure GitHub Actions to run this script on each push to the repository. This will ensure our database backups are always up-to-date.
First, navigate to your project’s GitHub repository and create a new file in the .github/workflows directory named backup.yml. The contents of this file should be as follows:
name: Backup
on:
push:
branches:
- main
jobs:
backup:
runs-on: ubuntu-latest
steps:
- name: Checkout code
uses: actions/checkout@v3
- name: Run backup script
env:
DB_HOST: ${{ secrets.DB_HOST }}
DB_USER: ${{ secrets.DB_USER }}
DB_PASSWORD: ${{ secrets.DB_PASSWORD }}
DB_NAME: ${{ secrets.DB_NAME }}
run: |
php artisan db:backup --database=$DB_NAME --output=backup/$(date +"%Y-%m-%d_%H-%M-%S").sqlIn this YAML file, we’ve defined a job named backup that will be triggered on each push to the main branch. This job checks out our code using the actions/checkout action and then runs our backup script using the run command.
Note that we’re also using environment variables here to store sensitive database credentials as secrets in GitHub. Make sure you’ve already set these up in your repository settings.
With this workflow file in place, GitHub Actions will now automatically run our backup script on each push to the repository, ensuring our database backups are always up-to-date and easily recoverable if needed.
Automating Daily Backups with Cron Jobs and GitHub Actions
Now that we have a working backup script in place, let’s automate it to run daily using cron jobs and GitHub Actions.
Step 1: Add a Cron Job
In your Laravel project, open the config/scheduler.php file. We’ll add a new job to this configuration array:
// config/scheduler.php
$schedule->daily()->at('02:00')->exec(function () {
exec('bash ./backup.sh');
});This will run our backup script at 2 AM every day.
Step 2: Configure GitHub Actions
In your project’s .github/workflows directory, create a new file named daily-backup.yml. We’ll define a workflow that runs the cron job:
# .github/workflows/daily-backup.yml
name: Daily Backup
on:
schedule:
- cron: 0 2 * * *
jobs:
backup:
runs-on: ubuntu-latest
steps:
- name: Run Cron Job
run: |
php artisan scheduler:run --verboseThis workflow uses the schedule trigger to run at 2 AM every day.
Step 3: Test Your Daily Backup
Save and commit your changes. GitHub Actions will automatically pick up the new workflow configuration. You can verify the backup job runs successfully by checking the app/storage/logs/laravel.log file for any errors.
Testing and Validating Your Automated MySQL Backups
To ensure that your automated MySQL backups are working as expected, it’s essential to test and validate them regularly. Create a new file in the tests/Unit directory of your Laravel project, e.g., BackupTest.php.
// app/Tests/Unit/BackupTest.php
namespace App\Tests\Unit;
use Tests\TestCase;
use Illuminate\Support\Facades\DB;
use Illuminate\Foundation\Testing\RefreshDatabase;
class BackupTest extends TestCase
{
use RefreshDatabase;
public function test_backup_successfully_created()
{
// Run the backup script manually to create a backup file
$this->artisan('backup:mysql')->assertSuccessful();
// Verify that the backup file exists and is not empty
$backupFile = storage_path('app/backup/mysql_' . date('Ymd_His') . '.sql.gz');
$this->assertFileExists($backupFile);
$this->assertGreaterThan(0, filesize($backupFile));
}
}Run the test using phpunit in your terminal. The test should pass if the backup script creates a non-empty backup file successfully.
Additionally, you can also use tools like mysqldump to manually dump your database and compare it with the automated backup. You can also verify the integrity of the backups by checking the MD5 checksum or verifying that the backup can be restored correctly using the mysql command-line tool.
This will give you confidence in your automated MySQL backup system, ensuring that it’s working as expected even when it’s not visible to you. By testing and validating your backups regularly, you’ll catch any issues before they become critical problems.
Deploying Your Automated Backup System to Production
Once you have your automated backup system working on your local environment and GitHub repository, it’s time to deploy it to production.
Assuming you’re using a cloud platform like AWS or DigitalOcean for your Laravel application, the deployment process is relatively straightforward. In this example, we’ll use an AWS EC2 instance with a MySQL database. Update your hosts file to point to the IP address of your EC2 instance and configure your Laravel project’s environment settings accordingly.
To automate the backup deployment on production, you can create another GitHub Actions workflow using the aws-deploy action. This will deploy the backup script and database credentials to your EC2 instance, ensuring seamless integration with your automated backup system.
# .github/workflows/deploy-production.yml
name: Deploy Production
on:
push:
branches:
- main
jobs:
deploy:
runs-on: ubuntu-latest
steps:
- name: Checkout code
uses: actions/checkout@v3
- name: Configure AWS credentials
uses: aws-actions/configure-aws-credentials@v1
with:
aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
- name: Deploy backup script and database credentials
uses: aws-actions/deploy@v1
with:
bucket-name: your-bucket-name
object-path: path/to/backup/script.sqlRemember to update the bucket-name and object-path variables according to your AWS S3 configuration. This will deploy the backup script and database credentials to your production environment, ensuring your automated backup system is up and running.
With this final step, you now have a fully automated MySQL backup system that runs daily on both your local environment and production server.
Frequently Asked Questions
How do I set up a local environment for my MySQL database?
To get started, download and install XAMPP from the official website. Follow the installation instructions for your operating system, then create a new MySQL database by running the command mysql -u root -p in the XAMPP control panel.
What is the difference between using GitHub Actions to automate MySQL backups and setting up cron jobs manually?
Both methods can be used to automate MySQL backups, but GitHub Actions provides a more streamlined and automated process. With GitHub Actions, you can integrate your backup script directly into your repository’s workflow, making it easier to manage and maintain.
I’m getting an error saying ‘mysql’ is not recognized as an internal or external command. What should I do?
This error typically occurs when the MySQL executable is not in your system’s PATH environment variable. Try running the mysql command with its full path, such as C:\xampp\mysql\bin\mysql.exe (for Windows) or /usr/local/mysql/bin/mysql (for Linux/Mac).
Can I use a different local development stack instead of XAMPP?
Yes, you can use any other setup like MAMP or Laravel Homestead. Just follow the installation instructions for your chosen environment and create a new MySQL database accordingly.
How do I configure cron jobs to automate the backup process?
Once you’ve set up GitHub Actions, you can use cron jobs to schedule the backup process. Create a new file in your repository’s .github/workflows directory with a YAML configuration that specifies the schedule and command to run.