How to Backup Your Laravel MySQL Database Daily ( without any package)

How to Backup Your Laravel MySQL Database Daily ( without any package)

Posted By

kamlesh paul

on

Dec 8, 2024
TipsCopy Paste

Hey there, fellow Laravel developers! 👋 I recently had to set up daily database backups for one of my projects, and I thought I’d share my experience with you. Trust me, it’s not as daunting as it might seem at first. Let’s dive in!

Table of Contents

Why Bother with Daily Backups?

Before we get into the nitty-gritty, let’s talk about why this is important. Imagine this: you’re working on a client project, and suddenly, your database goes kaput. Nightmare fuel, right? That’s why regular backups are your best friend. They’re like a safety net for your data – you hope you never need them, but you’ll be incredibly grateful when you do.

Step 1: Creating a Database Backup Command

First things first, we need to create a command that will do the heavy lifting for us. Don’t worry; I’ll walk you through it step by step.

Create a new file called DatabaseBackup.php in the app/Console/Commands directory. Here’s what you need to put in it:

app/Console/Commands/DatabaseBackup.php
<?php
 
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Symfony\Component\Process\Exception\ProcessFailedException;
use Symfony\Component\Process\Process;
 
class DatabaseBackup extends Command
{
    protected $signature = 'db:backup';
    protected $description = 'Backup the database';
 
    protected $connection;
    protected $database;
    protected $username;
    protected $password;
    protected $host;
 
    public function handle()
    {
        $this->init();
        $tables = $this->getAllTables();
        $this->backupTables($tables);
    }
 
    private function init(): void
    {
        $this->connection = config('database.default');
        $this->database = config('database.connections.' . $this->connection . '.database');
        $this->username = config('database.connections.' . $this->connection . '.username');
        $this->password = config('database.connections.' . $this->connection . '.password');
        $this->host = config('database.connections.' . $this->connection . '.host');
 
        if ($this->connection !== 'mysql') {
            $this->error("Oops! We only support MySQL for now. Your connection '{$this->connection}' isn't compatible.");
            exit(1);
        }
    }
 
    private function getAllTables(): array
    {
        $tables = DB::select('SHOW TABLES');
        return array_map(fn($table) => $this->getTableName($table), $tables);
    }
 
    private function getTableName($table): string
    {
        $property = 'Tables_in_' . $this->database;
        return $table->$property;
    }
 
    private function backupTables(array $tables): void
    {
        $tableNames = implode(' ', $tables);
        $date = now()->format('Y-m-d_H-i-s');
        $filePath = storage_path("app/{$date}-backup.sql");
 
        $command = sprintf(
            'mysqldump --user=%s --password=%s --host=%s --skip-lock-tables --no-tablespaces %s %s > %s',
            escapeshellarg($this->username),
            escapeshellarg($this->password),
            escapeshellarg($this->host),
            escapeshellarg($this->database),
            $tableNames,
            escapeshellarg($filePath)
        );
 
        $process = Process::fromShellCommandline($command);
        $process->run();
 
        if (!$process->isSuccessful()) {
            throw new ProcessFailedException($process);
        }
 
        $this->info("Woohoo! Database backup successful. You can find it here: {$filePath}");
    }
}

I know it looks like a lot, but don’t sweat it. This command does a few key things:

  • It grabs your database connection details from your config.
  • It figures out all the tables in your database.
  • It uses mysqldump to create a backup of your entire database.

Step 2: Scheduling Command

Now that we’ve got our shiny new command, let’s make sure it runs every day. We’ll use Laravel’s built-in scheduler for this.

Open up app/Console/Kernel.php and add this line to the schedule method:

app/Console/Kernel.php
protected function schedule(Schedule $schedule)
{
    $schedule->command('db:backup')->daily()->at('01:30');
}

This tells Laravel, “Hey, run our db:backup command every day at 1:30 AM.”. I chose 1:30 AM because it’s usually a quiet time for most websites, but feel free to adjust this to whatever time works best for you.

Wrapping Up

And there you have it! You’ve just set up daily backups for your Laravel project. Here’s a quick recap of what we did:

  • We created a custom command to handle the backup process.
  • We scheduled this command to run daily using Laravel’s scheduler.

Remember, for the scheduler to work, you’ll need to set up a cron job on your server to run php artisan schedule:run every minute. If you’re not sure how to do this, most hosting providers have guides on setting up cron jobs.

I hope this guide helps you sleep a little easier at night, knowing your data is being backed up regularly. If you have any questions or run into any issues, feel free to drop a comment below. Happy coding! 🚀

Share this article

33 views