Restore MySql incremental backup from S3 with the shell script

I used binary log files for incremental backup. You can check out this blog. I saved my binary logs data in this s3 path, s3://test/incremental-backup/2023/08/13/2023–08–13.zip . Now let’s restore it.

Basically what we gonna do is that we’ll use a bash script that takes a starting date as a parameter. In my S3 storage, files are saved daily and organized like this:

/2023/08/13/2023–08–10.zip
/2023/08/13/2023–08–11.zip
/2023/08/13/2023–08–12.zip
/2023/08/13/2023–08–13.zip
/2023/08/13/2023–08–14.zip

When we provide the start date, the script will go through each day til the current day, unzip the files, and restore them.

Here is the final sh file.

#!/bin/bash

# Check if the date parameter is provided
if [ $# -eq 0 ]; then
    echo "Error: Date parameter is missing"
    echo "Usage: $0 <start date in YYYY-MM-DD format>"
    exit 1
fi
# Set the start date parameter
start_date=$1

# Get the current date
current_date=$(date +%Y-%m-%d)
# Convert dates to Unix timestamps for comparison
start_timestamp=$(date -d "$start_date" +%s)
current_timestamp=$(date -d "$current_date" +%s)

# Loop through the dates from start_date to current_date (inclusive)
while [ "$start_timestamp" -le "$current_timestamp" ]; do
cd /root

# Set the S3 path
s3_path="s3://denied/incremental-backup/$(echo $start_date | awk -F "-" '{print $1}')/$(echo $start_date | awk -F "-" '{print $2}')/$(echo $start_date | awk -F "-" '{print $3}')"

binary_log_file="$start_date.zip"

s3cmd get "$s3_path/$binary_log_file"

unzip -P hello $binary_log_file

cd /root/var/log/mysql

# Restore the binary log file into MySQL
mysqlbinlog  $(ls) | sudo mysql --user=root --password=123456 
# echo "restored"
# Clean up
rm -rf /root/var 
rm /root/$start_date.zip
# echo "deleted"
   # Increment the start_date
    start_timestamp=$((start_timestamp + 86400))  # Adding 86400 seconds (1 day)
    start_date=$(date -d "$start_date + 1 day" +%Y-%m-%d)
done

Now let’s break down the code.

#!/bin/bash

This line is called a "shebang." It tells the system that the script should be executed using the Bash shell.

# Check if the date parameter is provided
if [ $# -eq 0 ]; then
    echo "Error: Date parameter is missing"
    echo "Usage: $0 <start date in YYYY-MM-DD format>"
    exit 1
fi

Here, the script checks whether you've provided a date as an argument when running the script. The $# represents the number of arguments passed to the script. If no arguments are provided (the count is equal to 0), the script prints an error message and the correct usage of the script (expecting a start date in the format YYYY-MM-DD) and exits with an error code of 1.

start_date=$1 

This line takes the first argument you provided when running the script (the start date) and stores it in a variable called start_date.

current_date=$(date +%Y-%m-%d)

Here, the current date is obtained using the date command with the format %Y-%m-%d (year-month-day), and it's stored in a variable called current_date.

start_timestamp=$(date -d "$start_date" +%s)
current_timestamp=$(date -d "$current_date" +%s)

These lines convert the start date and current date into Unix timestamps (number of seconds since January 1, 1970). This conversion helps in comparing dates.

while [ "$start_timestamp" -le "$current_timestamp" ]; do

A loop begins here. It runs as long as the start date's timestamp is less than or equal to the current date's timestamp.

cd /root

This changes the current directory to /root.

s3_path="s3://test/incremental-backup/$(echo $start_date | awk -F "-" '{print $1}')/$(echo $start_date | awk -F "-" '{print $2}')/$(echo $start_date | awk -F "-" '{print $3}')"

This line constructs an Amazon S3 path based on the provided start date. It uses the components of the start date (year, month, day) to create a path.

binary_log_file="$start_date.zip"

This creates a variable called binary_log_file with the value of the start date and the ".zip" extension.

s3cmd get "$s3_path/$binary_log_file"

This line uses the s3cmd tool to download a file from the specified S3 path.

unzip -P hello $binary_log_file

Here, the script extracts the contents of the downloaded zip file using the password "hello."

cd /root/var/log/mysql

This changes the current directory to /root/var/log/mysql.

mysqlbinlog  $(ls) | sudo mysql --user=root --password=123456

The binary log files in the current directory are processed using mysqlbinlog and then piped to the mysql command with the specified user and password for restoration.

rm -rf /root/var
rm /root/$start_date.zip

These lines delete the /root/var directory and the downloaded zip file to clean up after the restoration process.

start_timestamp=$((start_timestamp + 86400))
start_date=$(date -d "$start_date + 1 day" +%Y-%m-%d)

Here, the script increments the start date by one day by adding 86,400 seconds (24 hours) to the timestamp. It then updates the start date variable accordingly

done

The done keyword signifies the end of the loop.

That’s all!!!