How to Backup Database Laravel 10 While Excluding Specific Tables?

Hello Dev,

Today, I will let you know an example of how to backup database laravel 10 while excluding specific tables. let’s discuss database backup without a specific table. I explained simply step by step how to back up the database by ignoring some tables. you'll learn to back particular databases in laravel 10.

This tutorial is focused on how to backup database laravel 10 while excluding specific tables. I would like to show you a database backup without a specific table. if you want to see an example of how to backup a database with ignore some tables then you are in the right place. This article will give you a simple example of the backup particular database in laravel 10. follow the below steps for how to exclude a backup database from a specific table.

Step 1: Laravel 10 Installation

We are going from scratch so, If you haven't installed laravel in your system then you can run the below command and get a fresh Laravel project.

composer create-project laravel/laravel example-app

Step 2: Database Configuration

In the second step, we will make database configuration for example database name, username, password, etc for our crud application of laravel 10. So let's open the .env file and fill in all the details like as below:

.env

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

Step 3: Create Migration

We are going to create some random data tables. To start, let's create a migration for the 'products' table using the Laravel 10 PHP artisan command. Please execute the following command:

1. Create Products Table

php artisan make:migration create_products_table --create=products

After this command you will find one file in the following path "database/migrations" and you have to put the below code in your migration file to create a products table.

<?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');
    }
};

2. Create Orders Table

php artisan make:migration create_orders_table --create=orders

After this command you will find one file in the following path "database/migrations" and you have to put the below code in your migration file to create an orders table.

<?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');
    }
};

3. Create Employees Table

php artisan make:migration create_employees_table --create=employees

After this command you will find one file in the following path "database/migrations" and you have to put the below code in your migration file to create employees table.

<?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('employees', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email');
            $table->string('contact_no');
            $table->string('address');
            $table->timestamps();
        });
    }

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

Now you have to run this migration by following the command:

php artisan migrate

Note: After migrate table create some dummy records in tables.

Step 4: Create Scheduler Command

In this step, we need to create a scheduler command to back up the database while ignoring specific tables. To create the scheduler command, run the following command:

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

After run bellow command you will find new file path "app/Console/Commands/DatabaseBackup.php".

Set bellow 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');
    }
}

To execute the scheduler code, run the command below.

php artisan databasebackup:cron

All the required steps have been done, now you have to type the given below command and hit enter to run the Laravel app:

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

I hope it can help you...