Automate Your MySQL Databases Backups with this BASH Script

MySQL is a popular open-source relational database management system used by many web applications. Regularly backing up your MySQL databases is essential to ensure that your data is safe and secure. Although there are many tools available to automate MySQL database backups, using a BASH script can provide a simple and customizable solution. In this article, we will walk through how to create a BASH script to backup multiple MySQL databases from remote locations and store them for a week.

Step 1: Setting up the Environment

Before we dive into the code, we need to set up our environment. First, create a new directory to store your backups. In our example, we will create a directory named “mysql_backups” in the home directory:

mkdir ~/mysql_backups

Next, we need to install the sshpass package, which allows us to connect to remote hosts using a password. We can install sshpass on Ubuntu-based systems using the following command:

sudo apt-get install sshpass

Once we have set up our environment, we can move on to writing our BASH script.

Step 2: Writing the BASH Script

Our BASH script will backup multiple MySQL databases located on remote hosts. Each database will have a unique name, username, and password. We will name each backup file using the format "databasename-dd-mm-yyyy.sql".

First, we need to define the backup directory and the list of databases we want to backup. We will create an array called DATABASES, where each element is a string that contains the database’s remote host, name, username, and password separated by the | character:

#!/bin/bash

# Define backup directory
BACKUP_DIR="$HOME/mysql_backups"

# Define list of databases to backup
DATABASES=(
"remote_host1|database1|username1|password1"
"remote_host2|database2|username2|password2"
"remote_host3|database3|username3|password3"
)

Next, we will loop through each element in the DATABASES array and create a backup file for each database. Inside the loop, we will split the database string into its components using the IFS variable and the read command. We will then generate a filename for the backup file using the current date and time in the "dd-mm-yyyy" format. Finally, we will use sshpass to connect to the remote host and use the mysqldump command to backup the database to a file:

# Loop through each database
for db in "${DATABASES[@]}"; do
# Split database string into components
IFS='|' read -ra DB_CONFIG <<< "$db"
REMOTE_HOST="${DB_CONFIG[0]}"
DB_NAME="${DB_CONFIG[1]}"
DB_USER="${DB_CONFIG[2]}"
DB_PASSWORD="${DB_CONFIG[3]}"

# Generate filename with current date
DATE=$(date +%d-%m-%Y)
FILENAME="$BACKUP_DIR/$DB_NAME-$DATE.sql"

# Dump database to file
sshpass -p "$DB_PASSWORD" ssh "$DB_USER@$REMOTE_HOST" "mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME" > "$FILENAME"
done

After creating the backups, we need to remove any backups that are older than a week. We will use the find command to search for files in the backup directory that are older than seven days, and then delete them using the rm command:

# Remove backups older than 7 days
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +7 -exec rm {} ;

Finally, we can run the script using the following command:

bash mysql_backup.sh

Step 3: Making the Script More Robust

Our BASH script is now capable of backing up multiple MySQL databases from remote hosts and storing them for a week. However, there are a few additional steps we can take to make the script more robust.

First, we can add error handling to the script to ensure that it doesn’t fail silently. We can use the set -e command to exit the script immediately if any command fails:

#!/bin/bash
set -e

Step 4. Define backup directory

BACKUP_DIR="$HOME/mysql_backups"

Step 4. Define list of databases to backup

DATABASES=(
"remote_host1|database1|username1|password1"
"remote_host2|database2|username2|password2"
"remote_host3|database3|username3|password3"
)

Step 5. Loop through each database

for db in "${DATABASES[@]}"; do

Step 6. Split database string into components

IFS='|' read -ra DB_CONFIG <<< "$db"
REMOTE_HOST="${DB_CONFIG[0]}"
DB_NAME="${DB_CONFIG[1]}"
DB_USER="${DB_CONFIG[2]}"
DB_PASSWORD="${DB_CONFIG[3]}"

Step 7. Generate filename with current date

DATE=$(date +%d-%m-%Y)
FILENAME="$BACKUP_DIR/$DB_NAME-$DATE.sql"

Step 8. Dump database to file

sshpass -p "$DB_PASSWORD" ssh "$DB_USER@$REMOTE_HOST" "mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME" > "$FILENAME"
done

Step 9. Remove backups older than 7 days

find "$BACKUP_DIR" -type f -name "*.sql" -mtime +7 -exec rm {} ;

We can also add logging to the script to keep track of when backups are created and any errors that occur. We can redirect the output of the script to a log file using the >> operator:

bash mysql_backup.sh >> "$HOME/mysql_backups/mysql_backup.log" 2>&1

Step 10. Setting Corn Job

0 2 * * * /path/to/backup-script.sh

Conclusion: Complete BASH Script

#!/bin/bash

# Configuration variables
BACKUP_DIR="/path/to/backups"

# List of databases to backup
DATABASES=(
  "remote_host1|database1|username1|password1"
  "remote_host2|database2|username2|password2"
  "remote_host3|database3|username3|password3"
)

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Loop through each database
for db in "${DATABASES[@]}"; do
  # Split database config string into components
  IFS='|' read -ra DB_CONFIG <<< "$db" REMOTE_HOST="${DB_CONFIG[0]}" DB_NAME="${DB_CONFIG[1]}" DB_USER="${DB_CONFIG[2]}" DB_PASSWORD="${DB_CONFIG[3]}" # Generate filename with current date DATE=$(date +%d-%m-%Y) FILENAME="$BACKUP_DIR/$DB_NAME-$DATE.sql" # Dump database to file ssh "$REMOTE_HOST" "mysqldump -u $DB_USER -p$DB_PASSWORD $DB_NAME" > "$FILENAME"
done

# Remove oldest backup if there are more than 7 backups
BACKUP_COUNT=$(ls -1 "$BACKUP_DIR"/*.sql | wc -l)
if [ "$BACKUP_COUNT" -gt 7 ]; then
  OLDEST_BACKUP=$(ls -1 "$BACKUP_DIR"/*.sql | head -n 1)
  rm "$OLDEST_BACKUP"
fi

In this article, we have shown how to create a BASH script to backup multiple MySQL databases from remote hosts and store them for a week. By automating this process, you can ensure that your data is safe and secure. We have also demonstrated how to make the script more robust by adding error handling and logging. With a little bit of scripting knowledge, you can easily customize this script to meet your specific backup needs.