Kotchasan PHP Framework

Backing Up MySQL Database Tables with Date-Named Directories Using Cron

Backing up MySQL database tables is crucial to prevent the loss of valuable data. This script allows you to back up each table as a separate SQL file and store it in a directory named after the current date automatically. Additionally, you can set up a cron job to run this script every day at midnight for continuous backups.
#!/bin/bash

# Database connection details
DB_USER="username"
DB_PASS="password"
DB_NAME="database_name"
BASE_BACKUP_DIR="/path/to/backup/directory"

# Define the date format as YYYY-MM-DD
DATE=$(date +"%Y-%m-%d")

# Create a new directory named after the current date
BACKUP_DIR="$BASE_BACKUP_DIR/$DATE"
mkdir -p "$BACKUP_DIR"

# Retrieve the list of tables from the database
TABLES=$(mysql -u $DB_USER -p$DB_PASS -e "SHOW TABLES IN $DB_NAME;" | tail -n +2)

# Back up each table as a separate SQL file
for TABLE in $TABLES; do
    FILE_NAME="$BACKUP_DIR/$TABLE.sql"
    echo "Backing up $TABLE to $FILE_NAME"
    mysqldump -u $DB_USER -p$DB_PASS $DB_NAME $TABLE > $FILE_NAME
done

echo "Backup completed."

 

Setting Up Database Connection Information

  • DB_USER Database username
  • DB_PASS Database user password
  • DB_NAME Name of the database to back up
  • BACKUP_DIR Directory to store backup files
 

Usage

  1. Create a script file (e.g., backup_tables.sh) and place the code above into that file.
  2. Modify the database connection information to match your setup.
  3. Make the script executable with the command chmod +x backup_tables.sh
  4. Run the script with the command ./backup_tables.sh
  5. Or use cron to automatically back up the data daily.
 

Set Up Cron to Run the Script at Midnight Daily

Open the cron table for editing
crontab -e

Add a new entry to the cron table
0 0 * * * /path/to/backup_tables.sh

This script will back up all tables in the specified database to a directory named after the current date, with each table backed up as a separate SQL file. Cron will automatically run the script at midnight daily to back up the entire database.