MWZ

MINDWAREZONE

Laravel Python Blog

Tutorials

How to Create a Database Backup in Laravel 13 Using Mysqldump

Verify mysqldump Installation  

Before creating backups, ensure that mysqldump is installed and accessible.  

mysqldump is a command-line utility provided by MySQL that allows you to export the structure and data of a database into an SQL file. It is commonly used to create database backups, migrate databases between servers, and restore data when needed. The generated SQL file contains the necessary statements to recreate the database tables and insert the stored records.  

Open Command Prompt and run: 

C:\Users\mindwarezone>mysqldump --version
mysqldump  Ver 8.0.36 for Win64 on x86_64 (MySQL Community Server - GPL)

If the command is not recognized, locate mysqldump.exe. For MySQL installed separately on Windows, it is commonly found at:   

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe

Create a database backup command using Artisan for cron job scheduling.

Generate a new command: 

php artisan make:command DatabaseBackup

Open the DatabaseBackup.php file located in the app/Console/Commands directory.

Full code for generate database backup 
<?php

namespace App\Console\Commands;

use Illuminate\Console\Attributes\Description;
use Illuminate\Console\Attributes\Signature;
use Illuminate\Console\Command;
use Symfony\Component\Process\Process;

#[Signature('db:backup')]
#[Description('Command description')]

class DatabaseBackup extends Command
{
    /**
     * Execute the console command.
     */
    public function handle()
    {
        ini_set('memory_limit', '500M');
        set_time_limit(10000);

        $host = env('DB_HOST', '127.0.0.1');
        $database = env('DB_DATABASE');
        $username = env('DB_USERNAME');
        $password = env('DB_PASSWORD');

        $backupDir = storage_path('app/backups');
        if (!is_dir($backupDir)) {
            mkdir($backupDir, 0777, true);
        }

        $sqlFile = "{$backupDir}/backup_" . date('d_m_y_h_i') . ".sql";
        $gzFile = $sqlFile . '.gz';

        // safe mysqldump command
        $process = new Process([
            'mysqldump',
            '--single-transaction', // no table locking (safe for InnoDB)
            '--quick',              // reduce memory use
            '-h', $host,
            '-u', $username,
            "--password={$password}",
            $database,
        ]);

        $this->info('Creating database dump...');
        $process->run();

        if (!$process->isSuccessful()) {
            return $this->error('Backup failed: ' . $process->getErrorOutput());
        }

        // Write the SQL file
        file_put_contents($sqlFile, $process->getOutput());

        // Compress with php (no external gzip needed)
        $this->info('Compressing backup...');
        $this->compressGzip($sqlFile, $gzFile);

        unlink($sqlFile);

        $this->info("Backup complete: $gzFile");
    }

    // Compress gzip file
    private function compressGzip($source, $dest){
        $fpIn = fopen($source, 'rb');
        $fpOut = gzopen($dest, 'wb9');

        while(!feof($fpIn)){
            gzwrite($fpOut, fread($fpIn, 1024 * 512));
        }

        fclose($fpIn);
        gzclose($fpOut);
    }
}

Database Configuration

Retrieve database credentials from the Laravel configuration. 

$host = env('DB_HOST', '127.0.0.1');
$database = env('DB_DATABASE');
$username = env('DB_USERNAME');
$password = env('DB_PASSWORD');

Define the Backup File 

$backupDir = storage_path('app/backups');
if (!is_dir($backupDir)) {
     mkdir($backupDir, 0777, true);
}

$sqlFile = "{$backupDir}/backup_" . date('d_m_y_h_i') . ".sql";       
$gzFile = $sqlFile . '.gz';

Mysqldump Process 

$process = new Process([
      'mysqldump',
      '--single-transaction',
      '--quick',
      '-h', $host,
      '-u', $username,
      "--password={$password}",
      $database,
]);

Why These Options?

  • --single-transaction Creates a consistent backup without locking InnoDB tables.
  • --quick Reduces memory usage by streaming rows directly.
  • --password={$password} Prevents MySQL from treating the password as the database name.

Run the Backup 

$this->info('Creating database dump...');
$process->run();

// Return if fail
if (!$process->isSuccessful()) {
     return $this->error('Backup failed: ' . $process->getErrorOutput());
}

  Compress the SQL backup file into a .gz archive.   

private function compressGzip($source, $dest){
     $fpIn = fopen($source, 'rb');
     $fpOut = gzopen($dest, 'wb9');

     while(!feof($fpIn)){
         gzwrite($fpOut, fread($fpIn, 1024 * 512));
     }

     fclose($fpIn);
     gzclose($fpOut);
}

Run the Command 

php artisan db:backup

Result: 

PS D:\Project\laravel\mindwarezone> php artisan db:backup
Creating database dump...
Compressing backup...
Backup complete: D:\Project\laravel\mindwarezone\storage\app/backups/backup_14_06_26_09_43.sql.gz
Next