Tag: mariadb

Just digging deeper in automating thing, so I made two scripts:

backup_cron.sh

#!/bin/bash

# Define the path to the backup script
BACKUP_SCRIPT="/backup_mysql.sh"

# Define the cron job command to run the backup script once a week (every Sunday at midnight)
CRON_COMMAND="0 0 * * 0 $BACKUP_SCRIPT"

# Add the cron job to the crontab
(crontab -l 2>/dev/null; echo "$CRON_COMMAND") | crontab -

echo "Backup cron job set up successfully."

And the script that executes the backup every Sunday:

backup_mysql.sh

#!/bin/bash

# Define the directory where backup files will be stored
BACKUP_DIR="/var/backups/mysql"

# Ensure the backup directory exists
mkdir -p "$BACKUP_DIR"

# Define the filename for the backup file (include date in the filename)
BACKUP_FILE="$BACKUP_DIR/mysql_backup_$(date +%Y-%m-%d_%H-%M-%S).sql"

# Define the compressed filename
COMPRESSED_FILE="$BACKUP_FILE.gz"

# Define MySQL username and password (replace with your MySQL credentials)
DB_USER="USERNAME"
DB_PASS="PASSWORD"

# Dump all databases into a single SQL file
mysqldump -u "$DB_USER" -p"$DB_PASS" --all-databases > "$BACKUP_FILE"

# Add permissions to the backup file
chmod 600 "$BACKUP_FILE"

# Compress the backup file
gzip "$BACKUP_FILE"

echo "Database backup completed. Backup stored in: $COMPRESSED_FILE"

To make the script executable:

sudo chmod +x backup_cron.sh
sudo chmod +x backup_mysql.sh

and run it with

sudo ./backup_cron.sh