Shell script for mysql database dump for everyday and keep for 7 days

Shell script that will create a folder for the MySQL dump backups and save the dump file in that folder for 7 days. The script will also schedule a cron job to run the script every day at 00:01 (1 minute past midnight):

#!/bin/bash

# Set the path to the backup folder
BACKUP_FOLDER="/path/to/backup/folder"

# Set the MySQL credentials
MYSQL_USERNAME="username"
MYSQL_PASSWORD="password"
MYSQL_HOSTNAME="localhost"

# Set the database name
DATABASE_NAME="database_name"

# Set the current date
CURRENT_DATE=$(date +%Y-%m-%d)

# Create the backup folder if it doesn't exist
if [ ! -d "$BACKUP_FOLDER" ]; then
  mkdir "$BACKUP_FOLDER"
fi

# Perform the MySQL dump
mysqldump --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD --host=$MYSQL_HOSTNAME $DATABASE_NAME > "$BACKUP_FOLDER/$DATABASE_NAME-$CURRENT_DATE.sql"

# Remove backups older than 7 days
find "$BACKUP_FOLDER" -type f -mtime +7 -exec rm {} \;

# Schedule a cron job to run the script everyday at 00:01
echo "1 0 * * * $0" | crontab -

Save the script with a suitable file name, for example mysql_backup.sh, and make it executable using the following command:

chmod +x mysql_backup.sh

You can then run the script manually by typing:

./mysql_backup.sh

The script will create a MySQL dump of the specified database and save it in the BACKUP_FOLDER with the filename in the format database_name-YYYY-MM-DD.sql, where YYYY-MM-DD is the current date. It will also remove any dump files in the BACKUP_FOLDER that are older than 7 days.

Finally, the script will schedule a cron job to run the script everyday at 00:01. This will ensure that the MySQL dump is performed on a daily basis and the backups are kept for 7 days.

Note: Make sure to replace /path/to/backup/folder, username, password, and database_name with the actual values for your setup.

To compress the MySQL dump file with gzip, you can add the following lines to the script after the MySQL dump is performed:

# Compress the MySQL dump file with gzip
gzip "$BACKUP_FOLDER/$DATABASE_NAME-$CURRENT_DATE.sql"

This will create a gzip-compressed version of the MySQL dump file with the same filename, but with a .gz extension.

Alternatively, you can use the gzip command directly as part of the mysqldump command to compress the dump file on the fly:

mysqldump --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD --host=$MYSQL_HOSTNAME $DATABASE_NAME | gzip > "$BACKUP_FOLDER/$DATABASE_NAME-$CURRENT_DATE.sql.gz"

This will create a gzip-compressed MySQL dump file with the filename in the format database_name-YYYY-MM-DD.sql.gz.

Note: Make sure to replace /path/to/backup/folder, username, password, and database_name with the actual values for your setup.