♻️Automatic database backup (Updated)

// Complete db backup script for S3 bucket of aws
// make sure to create seperate credential file 
// DIR path /var/backups/ 

#######################################################################################


#!/bin/bash

# Load credentials from an external file
# Load all variables from an external file
source /var/backups/"your_credentials.sh"

timestamp=$(date +"%F-%H%M")
filename="$backup_path/${db_name}_backup_$timestamp.sql.gz"

# Ensure backup directory exists
mkdir -p $backup_path

# Take a database backup and compress it
mysqldump -u$db_user -p$db_password $db_name | gzip > $filename

# Check if the backup was created successfully
if [ $? -ne 0 ]; then
  # Log process and notify via email if backup fails
  {
    echo "Subject: Database Backup Failed"
    echo "To: $email"
    echo "From: [email protected]"
    echo ""
    echo "Database backup failed for $db_name at $timestamp."
  } | /usr/sbin/sendmail -t
  exit 1
fi

# Upload the backup to AWS S3
aws s3 cp $filename s3://$s3_bucket/database-backups/ --region eu-central-1

# Retain only the latest 2 backups in S3
backup_files=$(aws s3 ls s3://$s3_bucket/database-backups/ --region eu-central-1 | sort | awk '{print $4}')
backup_count=$(echo "$backup_files" | wc -l)

if [ $backup_count -gt 2 ]; then
  files_to_delete=$(echo "$backup_files" | head -n $(($backup_count - 2)))
  for file in $files_to_delete; do
    aws s3 rm s3://$s3_bucket/database-backups/$file --region eu-central-1
  done
fi

# Check if the upload was successful
if [ $? -eq 0 ]; then
  # Retain only the latest 2 local backups
local_backup_files=$(ls -1 $backup_path | sort)
local_backup_count=$(echo "$local_backup_files" | wc -l)

if [ $local_backup_count -gt 2 ]; then
  local_files_to_delete=$(echo "$local_backup_files" | head -n $(($local_backup_count - 2)))
  for file in $local_files_to_delete; do
    rm $backup_path/$file
  done
fi

# Remove local backup file after successful upload
  rm $filename

  # Log process and notify via email of success optional and not tested yet
  {
    echo "Subject: Database Backup Completed Successfully"
    echo "To: $email"
    echo "From: [email protected]"
    echo ""
    echo "Database backup completed successfully: $filename"
  } | /usr/sbin/sendmail -t
else
  # Log process and notify via email if upload fails
  {
    echo "Subject: Database Backup Upload Failed"
    echo "To: $email"
    echo "From: [email protected]"
    echo ""
    echo "Database backup upload failed for $filename at $timestamp."
  } | /usr/sbin/sendmail -t
  exit 1
fi

To schedule the script with cron, follow these steps:

Open the crontab file for editing:

the cron job is divided into two main parts:

1. The Scheduling Information (0 */4 * * *)

2. The Command to Execute /var/backups/backup.sh > /var/backups/db_cron.log 2>&1

crontab -e
// add the following line in crontab file 

0 */4 * * * /var/backups/backup.sh > /var/backups/db_cron.log 2>&1

1. Scheduling Information: The cron job is set to execute at specific times based on the format 0 */4 * * *. Here's what each field represents:

   • Minute (0): Runs at the start (0 minute) of the hour.

   • Hour (*/4): Every 4 hours. This means midnight (00:00), 4 AM, 8 AM, etc.

   • Day of the Month (*): Any day.

   • Month (*): Every month.

   • Day of the Week (*): Any day. This schedule results in the script executing every four hours throughout the day.

2. To monitor and log the scheduled tasks, you can redirect the cron output to a log file like db_cron.log to keep track of successful backups and errors:

• /var/backups/db_cron.log:

  • The output from running backup.sh will be written to this log file (db_cron.log). If the file doesn’t exist, it will be created. If it already exists, it will be overwritten each time the cron job runs.

• 2>&1:

  • 2 represents standard error (stderr).

  • >&1 means redirect standard error to standard output. This means that any error messages (stderr) generated by backup.sh will also be captured in the db_cron.log file.

Ensure that the server name, database name, and backup path are updated according to your environment. This script can be scheduled using Task Scheduler on Windows to run at desired intervals. Always test backup scripts in a development environment before deploying to production.

The below code block is not tested and may be outdated. It is recommended to review and test the script in a safe environment before using it in production:

#!/bin/bash

# Define the database name and backup file name
DB_NAME="mydatabase"
BACKUP_FILE="mydatabase_backup.sql"

# Dump the database to a SQL file
mysqldump -u root -p $DB_NAME > $BACKUP_FILE

# Compress the SQL file
gzip $BACKUP_FILE

Make sure to update any necessary paths or parameters as per your setup and requirements.

You can schedule this script to run automatically using a tool such as "cron" on Linux or "Task Scheduler" on Windows.

For SQL Server, Here is an example of a batch file that can be used to automate the backup of a SQL Server database

@echo off

rem Define the database name and backup file name
set DB_NAME="mydatabase"
set BACKUP_FILE="mydatabase_backup.bak"

rem Backup the database
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -Q "BACKUP DATABASE %DB_NAME% TO DISK = '%BACKUP_FILE%' WITH NOFORMAT, INIT, NAME = 'Full Backup of %DB_NAME%', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Create a database backup script which takes backup of the database, compresses it, and stores with name, time, date month year.

Sure, here's an example of a shell script that can be used to automate the backup of a MySQL database, compress it and store it with a name that includes the time, date, month, and year:

bashCopy code#!/bin/bash

# Define the database name
DB_NAME="mydatabase"

# Get the current date and time
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# Define the backup file name
BACKUP_FILE="mydatabase_backup_$DATE.sql"

# Dump the database to a SQL file
mysqldump -u root -p $DB_NAME > $BACKUP_FILE

# Compress the SQL file
tar -czvf $BACKUP_FILE.tar.gz $BACKUP_FILE

# Remove the SQL file
rm $BACKUP_FILE

This script will create a MySQL dump of the specified database and save it to a file named "mydatabase_backup_$DATE.sql", where $DATE is the current date and time. It will then compress the SQL file and rename it to "mydatabase_backup_$DATE.sql.tar.gz" and remove the original SQL file.

You can schedule this script to run automatically using a tool such as "cron" on Linux or "Task Scheduler" on Windows.

For SQL Server, Here is an example of a batch file that can be used to automate the backup of a SQL Server database, compress it and store it with a name that includes the time, date, month, and year:

@echo off

rem Define the database name
set DB_NAME="mydatabase"

rem Get the current date and time
set DATE=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%

rem Define the backup file name
set BACKUP_FILE="mydatabase_backup_%DATE%.bak"

rem Backup the database
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -Q "BACKUP DATABASE %DB_NAME% TO DISK = '%BACKUP_FILE%' WITH NOFORMAT, INIT, NAME = 'Full Backup of %DB_NAME%', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

rem Compress the backup file
"C:\Program Files\7-Zip\7z.exe" a -t7z %BACKUP_FILE%.7z %BACKUP_FILE%

rem Remove the backup file
del %BACKUP_FILE%

How can I store this database to amazon s3

There are several ways to store a database backup on Amazon S3, here are a few options:

1. You can use the AWS Command Line Interface (CLI) to upload the backup file to S3. The following command uploads a file named "mydatabase_backup_2021-01-01_12-00-00.tar.gz" to a S3 bucket named "my-backup-bucket":

bashCopy codeaws s3 cp mydatabase_backup_2021-01-01_12-00-00.tar.gz s3://my-backup-bucket/

1. You can also use the AWS SDK for your language of choice (e.g. boto3 for Python) to interact with S3 and upload the backup file. Here's an example of how to upload a file using the boto3 library in Python:

javaCopy codeimport boto3

s3 = boto3.client('s3')

file_name = "mydatabase_backup_2021-01-01_12-00-00.tar.gz"
bucket_name = "my-backup-bucket"

s3.upload_file(file_name, bucket_name, file_name)

1. Another option is to use the S3 Transfer Utility, which is a command-line utility that can be used to upload and download files from S3. Here's an example of how to upload a file using the S3 Transfer Utility:

bashCopy codeaws s3 cp mydatabase_backup_2021-01-01_12-00-00.tar.gz s3://my-backup-bucket/ --sse

You can also schedule these commands to run automatically using a tool such as "cron" on Linux or "Task Scheduler" on Windows.

It's also important to note that you should secure the data in S3 by using encryption and access controls, also you can use versioning and lifecycle policies to manage the data in S3.