Backup MySQL Database to a date-time suffixed file, gzipped, with excluding specific set of tables

backup.sh

#!/bin/bash

# Configuration
DB_USER="root"
DB_PASS="12345678XYZ"
DB_HOST="localhost"
DB_NAME="my_database_name"
BACKUP_DIR="database/backup"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILEPATH="${BACKUP_DIR}/${DB_NAME}-${TIMESTAMP}.sql.gz"

# 1. Create the backup directory if it doesn't exist
# -p ensures no error if it already exists and creates parent directories if needed
mkdir -p "$BACKUP_DIR"

# 2. List of tables to exclude
# Update the list according to your need
EXCLUDED_TABLES=(
    "password_reset_tokens"
    "failed_jobs"
    "jobs"
    "sessions"
    "personal_access_tokens"
    "activity_log"
    "audit_logs"
    "cache"
    "compare_items"
    "notifications"
    "telescope_entries"
    "telescope_entries_tags"
    "telescope_monitoring"
)

# 3. Build the ignore arguments
IGNORE_ARGS=""
for table in "${EXCLUDED_TABLES[@]}"; do
    IGNORE_ARGS="$IGNORE_ARGS --ignore-table=${DB_NAME}.${table}"
done

# 4. Run mysqldump
# export MYSQL_PWD is safer than -p on CLI for process list visibility
export MYSQL_PWD=$DB_PASS

mysqldump -u "$DB_USER" -h "$DB_HOST" "$DB_NAME" $IGNORE_ARGS | gzip > "$FILEPATH"

# Unset password for security
unset MYSQL_PWD

echo "✅ Backup saved at $FILEPATH"

Laravel_MySQL_backup.md

Laravel MySQL Backup Script with Table Exclusions

A robust Bash script to create compressed backups of a MySQL database while excluding high-churn, non-essential tables. This is specifically optimized for Laravel applications to avoid backing up massive log tables (Telescope, Activity Log, Sessions, etc.).

Features

• Smart Exclusions: Automatically ignores data from tables that bloat backups but aren't critical for restoration (e.g., telescope_entries, sessions, failed_jobs, audit_logs).
• Compression: Pipes output directly to gzip to save disk space.
• Safety:
  • Automatically creates the backup directory if it doesn't exist.
  • Uses MYSQL_PWD environment variable to avoid "using password on command line" security warnings.
• Maintainable: Uses a Bash array for the exclusion list, making it easy to add or remove tables without fighting complex string concatenation.

Usage

1. Installation

Download the backup.sh file to your server.

2. Configuration

Open the file and update the top configuration section with your database credentials:

DB_USER="forge"
DB_PASS="your_secure_password"
DB_HOST="localhost"
DB_NAME="your_database_name"
BACKUP_DIR="database/backup"

You can also add or remove tables from the EXCLUDED_TABLES array to suit your needs.

3. Execution

Make the script executable and run it using Bash:

chmod +x backup.sh
./backup.sh

Note: Do not run with sh backup.sh. This script uses Bash arrays, which are not supported by the default sh shell on Ubuntu/Debian.

Automation (Cron Job)

To run this backup daily at 2:00 AM, add the following to your crontab (crontab -e):

0 2 * * * /path/to/your/backup.sh > /dev/null 2>&1

Default Excluded Tables

By default, the script ignores the table that generally don't want to backup (in context of a Laravel Application):

• Session/Auth: sessions, password_reset_tokens, personal_access_tokens
• Queue: jobs, failed_jobs
• Logs & Monitoring: telescope_entries, telescope_entries_tags, telescope_monitoring, activity_log, audit_logs
• Cache: cache