cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Highlighted
Helper II
Helper II

Solution: MySQL Backups to Amazon S3 / cPanel Business Hosting

 

Hi Everyone,

 

Solution

Here is my solution for backing up multiple MySQL databases & files to Amazon S3 storage. Then tagging the files to allow easy application of a lifecycle retention policy, so Amazon S3 knows how long to keep the files, daily, weekly, monthly, yearly.

 

All credit for the essential backup script & logic goes to Yong Mook Kim.
https://www.mkyong.com/linux/linux-script-to-backup-mysql-to-amazon-s3/

 

 

#!/bin/bash

#############################################################################
##
##   MySQL Database To Amazon S3
##   Written By: YONG MOOK KIM
##   https://www.mkyong.com/linux/linux-script-to-backup-mysql-to-amazon-s3/
##
##   Modified By: Aly
##   Mods: Execute AWS in virutal environment on shared hosting
##         Use virtual directory: /home/hostingdirectory/virtualenv/bin/aws
##         Multi database backup
##         Cleanup local backups after 3 days
##         Tag files in Amazon S3 allowing Lifecylce Retention Policy 
##
#############################################################################

##  Set Date (for all backups)
NOW=$(date +"%Y-%m-%d")

##  Construct S3API --query parameter (for all backups)
##  Parameter limits Amazon S3 tagging of records to today's files
STRING_1="Contents[?LastModified>="
STRING_2="].{Key:Key}"
STRING_3="${STRING_1}\`${NOW}\`${STRING_2}"
##
##  STRING_3="Contents[?LastModified>=`2019-10-10`].{Key:Key}"
## 
##  Why pre-construct the parameter?
##
##     ${NOW} variable does not correctly insert date (as text string) 
##     into S3API command line, when directly placed into --query parameter
##     --query won't parse date (as text string + `back ticks`)
##     --query fails to limit tagging to today's files

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

##  Database 1 Backup & Amazon S3 Values
##  Replace these values with your own
BACKUP_DIR="/home/hostingdirectory/backups/folder1"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="username1"
MYSQL_PASSWORD="password1"
DATABASE_NAME="database1"

AMAZON_S3_ROOT_BUCKET="bucket"
AMAZON_S3_UPLOAD_BUCKET="s3://bucket/backups/folder1/"
AMAZON_S3_FOLDER="backups/folder1"
AMAZON_S3_BIN="/home/hostingdirectory/virtualenv/bin/aws"

FOLDERS_TO_BACKUP=("/home/hostingdirectory/public_html/yourwebsite.com/folder/" "/home/hostingdirectory/public_html/yourwebsite.com/folder/")

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

##  Create Backup Directory (if not exist)
mkdir -p ${BACKUP_DIR}

##  Backup MySQL Database
backup_mysql_database1(){
        mysqldump -h ${MYSQL_HOST} \
           -P ${MYSQL_PORT} \
           -u ${MYSQL_USER} \
           -p${MYSQL_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-database.sql.gz
}

##  Backup Folders
backup_files_database1(){
        tar -cvzf ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-files.tar.gz ${FOLDERS_TO_BACKUP[@]}
}

##  Upload Database & Folders to Amazon S3
upload_s3_database1(){
        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-database.sql.gz ${AMAZON_S3_UPLOAD_BUCKET}
        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-files.tar.gz ${AMAZON_S3_UPLOAD_BUCKET}
}

##  Tag Uploaded Files at Amazon S3
##  (Modify TagSet Values as Needed. Example Value="Daily", "Weekly", "Monthly", "Yearly")
tag_s3_database1(){
        ${AMAZON_S3_BIN} s3api list-objects-v2 --bucket ${AMAZON_S3_ROOT_BUCKET} --prefix ${AMAZON_S3_FOLDER} --query ${STRING_3} --output text | while read line ; do ${AMAZON_S3_BIN} s3api put-object-tagging --bucket ${AMAZON_S3_ROOT_BUCKET} --tagging "TagSet=[{Key=Backup,Value=Yearly}]" --key $line ; done
}

##  Execute Database1 Functions
backup_mysql_database1
backup_files_database1
upload_s3_database1
tag_s3_database1

##  Cleanup ".gz" files older than 3 days, from local backup directory
##  Modify number of days to keep files '+3' as needed
find ${BACKUP_DIR} -type f -name "*.gz" -mtime +3 -exec rm -f {} \;

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

##  Database 2 Backup & Amazon S3 Values
##  Replace these values with your own
BACKUP_DIR="/home/hostingdirectory/backups/folder2"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="username2"
MYSQL_PASSWORD="password2"
DATABASE_NAME="database2"

AMAZON_S3_ROOT_BUCKET="bucket"
AMAZON_S3_UPLOAD_BUCKET="s3://bucket/backups/folder2/"
AMAZON_S3_FOLDER="backups/folder2"
AMAZON_S3_BIN="/home/hostingdirectory/virtualenv/bin/aws"

FOLDERS_TO_BACKUP=("/home/hostingdirectory/public_html/yourwebsite.com/folder/" "/home/hostingdirectory/public_html/yourwebsite.com/folder/")

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

##  Create Backup Directory (if not exist)
mkdir -p ${BACKUP_DIR}

##  Backup MySQL Database
backup_mysql_database2(){
        mysqldump -h ${MYSQL_HOST} \
           -P ${MYSQL_PORT} \
           -u ${MYSQL_USER} \
           -p${MYSQL_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-database.sql.gz
}

##  Backup Folders
backup_files_database2(){
        tar -cvzf ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-files.tar.gz ${FOLDERS_TO_BACKUP[@]}
}

##  Upload Database & Folders to Amazon S3
upload_s3_database2(){
        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-database.sql.gz ${AMAZON_S3_UPLOAD_BUCKET}
        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}/${DATABASE_NAME}-${NOW}-files.tar.gz ${AMAZON_S3_UPLOAD_BUCKET}
}

##  Tag Uploaded Files at Amazon S3
##  (Modify TagSet Values as Needed. Example Value="Daily", "Weekly", "Monthly", "Yearly")
tag_s3_database2(){
        ${AMAZON_S3_BIN} s3api list-objects-v2 --bucket ${AMAZON_S3_ROOT_BUCKET} --prefix ${AMAZON_S3_FOLDER} --query ${STRING_3} --output text | while read line ; do ${AMAZON_S3_BIN} s3api put-object-tagging --bucket ${AMAZON_S3_ROOT_BUCKET} --tagging "TagSet=[{Key=Backup,Value=Yearly}]" --key $line ; done
}

##  Execute Database2 Functions
backup_mysql_database2
backup_files_database2
upload_s3_database2
tag_s3_database2

##  Cleanup ".gz" files older than 3 days, from local backup directory
##  Modify number of days to keep files '+3' as needed
find ${BACKUP_DIR} -type f -name "*.gz" -mtime +3 -exec rm -f {} \;

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

 

 

 

Script Modifications

The modifications I made to this script are:

  1. Execute AWS in virtual environment on shared hosting
  2. Multi database backup
  3. Cleanup local backups after 3 days
  4. Tag files in Amazon S3

 

Here was the biggest challenge:

Challenge - AWS Install on Shared Hosting

  • Amazon AWS CLI (Command Line Interface) tool would not install in a shared hosting environment via SSH. You don’t have write privileges to the system installed Python directory preventing the install.

Solution

  • AWS will then run from a bash shell script without the need to “source activate” the virtual environment if you specify the full virtual environment path when calling AWS from the script.
    /home/hostingdirectory/virtualenv/bin/aws
  • Add as many databases to the script as you want but make sure to change the function names to be unique for each database.
backup_mysql_database1
backup_files_database1
upload_s3_database1
tag_s3_database1
  • Change the S3 backup bucket & folder names to match your setup

SSH Access

Script

  • Logged in under SSH shell or via the cPanel File Manager
  • Create a /scripts/ directory or choose a different location
  • Create a /backups/ directory or choose a different location
  • Upload the bash shell script to /scripts/backup_script.sh
  • Set directory & script file permissions via chmod (or cPanel file manager) to 700 to restrict access to just the user account, to improve securitypermissions.PNG

     

Cron Job

  • Setup a cron job in cPanel to run the script, in this case once a day at 2amcron-job.PNG

     

    0 2 * * * /home/hostingdirectory/scripts/backup-script.sh > /dev/null 2>&1

Data Transfer Limits

  • Keep in mind the GoDaddy data transfer limits for your account
  • If you overuse the limits GoDaddy will suspend your account or ask you to upgrade

Amazon S3 Lifecyle Policy

Challenge: Tagging Using S3API --query parameter

  • Getting S3API --query parameter to accept a variable date proved maddeningly challenging.

    Contents[?LastModified>=`2019-10-10`].{Key:Key}" 
  • The above parameter HAS to use a text string value of the date surrounded with `back ticks` otherwise the query won't limit the files updated just to today's date but will update ALL the files in the direcory.
  • If you insert the ${NOW} date variable, even escaping the value or converting to a text string first, the --query won't parse it as a date.
  • The solution was to pre-construct the parameter then insert it as a complete item.

 

STRING_1="Contents[?LastModified>="
STRING_2="].{Key:Key}"
STRING_3="${STRING_1}\`${NOW}\`${STRING_2}"

Limited Script Security

 

This script as it passes database credentials in the clear via a cron job, so does cause SSH to throw the warning of exposed username & passwords when run. 

 

With cPanel Business Hosting the PID (Process ID) list is limited to just one user, you cannot see the other user's PID's when using the TOP command.  In other shared hosting environments your cron job PID could be more visible to other users, so is considered a security vulnerability, exposing usernames & passwords.

 

This is a limitation of this script.

 

Hope this solution helps someone.

 

Aly 🙂

2 REPLIES 2
Community Manager
Community Manager

Re: Solution: MySQL Backups to Amazon S3 / cPanel Business Hosting

Thanks so much @Aly for spending the time to write this great post and provide such good information. Take care!

Rachel

Helper II
Helper II

Re: Solution: MySQL Backups to Amazon S3 / cPanel Business Hosting

My pleasure @RachelM when you work out a solution for yourself and its a joy to share the knowledge.