Hi Dev,

Today, we will show you laravel 10 backup database while excluding specific tables. This article will give you simple example of laravel 10 backup database while excluding specific tables. Let’s discuss laravel 10 backup database while excluding specific tables. In this article, we will implement a laravel 10 backup database while excluding specific tables.

So let’s follow few step to create example of laravel 10 backup database while excluding specific tables.

Install the Laravel 10

composer create-project laravel/laravel example-app

Database Configuration

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database name(laravel)
DB_USERNAME=username(root)
DB_PASSWORD=password(root)

Create Migration

Create Products Table

php artisan make:migration create_products_table --create=products

database\migrations\create_products_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('detail');
            $table->string('price');
            $table->string('image');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};

Create Orders Table
database\migrations\create_orders_table.php

php artisan make:migration create_orders_table --create=orders

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->string('detail');
            $table->string('quantity');
            $table->string('total');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('orders');
    }
};

run this migration by following the command

php artisan migrate

Create Scheduler Command

We need to create a scheduler command to backup the database ignoring specific tables. To create a scheduler command, run the following command:

php artisan make:command DatabaseBackup --command=databasebackup:cron

app/Console/Commands/DatabaseBackup.php
Set code to “DatabaseBackup.php” file.

DatabaseBackup.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use Redirect;
use Carbon\Carbon;
use Carbon\CarbonPeriod;

class DatabaseBackup extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'databasebackup:cron';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     */
    public function handle()
    {
        // Genrate backup files 
        $filename = \Carbon\Carbon::now()->format('m-d-Y')."-backup.sql.gz";

        $command = "mysqldump --user=" . env('DB_USERNAME') ." --password=" . env('DB_PASSWORD') . " --host=" . env('DB_HOST') . " \
            --ignore-table=".env('DB_DATABASE').".tablename_to_ignore \
            --ignore-table=".env('DB_DATABASE').".tablename_to_ignore " . env('DB_DATABASE') . " | gzip > " .storage_path("app/backup/". $filename);

        $returnVar = NULL;
        $output  = NULL;
  
        exec($command, $output, $returnVar);

        // Delete existing backup files 
        $startDate = Carbon::now()->subDays(30);
        $endDate = Carbon::now();
  
        $dateRange = CarbonPeriod::create($startDate, $endDate);
        $dates = ['.gitignore'];
        foreach ($dateRange->toArray() as $key => $value) {
            $dates[] = $value->format('m-d-Y')."-backup.sql.gz";
        }

        $mediaPath = storage_path('app/backup');
        $files = File::allFiles($mediaPath);

        foreach ($files as $key => $value) {
            $fileName = $value->getRelativePathname();
            if (!in_array($fileName, $dates)) {
                File::delete($value);
            }
        }
    }
}

Set up a daily cron job for automatic backups in file path “app/Console/Kernel.php”.
Kernel.php

<?php

namespace App\Console;

use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * Define the application's command schedule.
     */
    protected function schedule(Schedule $schedule): void
    {
        $schedule->command('databasebackup:cron')->daily();
    }

    /**
     * Register the commands for the application.
     */
    protected function commands(): void
    {
        $this->load(__DIR__.'/Commands');

        require base_path('routes/console.php');
    }
}

execute the scheduler code, run the command below.

php artisan databasebackup:cron

The backup file will be generated at the path ‘storage/app/backup’.

I hope it will assist you…