Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Backups»Backup MySQL Databases to Amazon S3 (Shell Script)

    Backup MySQL Databases to Amazon S3 (Shell Script)

    RahulBy RahulMay 28, 20222 Mins ReadUpdated:May 28, 2022

    A shell script is a collection of commands to perform a specific job. MySQL is a relational database management system widely used on Linux systems. Amazon S3 is a cloud storage device provided by Amazon Web Services. It’s a good practice for the system administrator to back up databases at regular intervals and store them in a remote location like Amazon S3.

    • A Simple Bash Script for MySQL Database Backup
    • An Advance Bash Script for MySQL Database Backup

    This tutorial contains a shell script that creates MySQL databases backup and uploads them to Amazon S3 buckets. You can also use this shell script to back up MariaDB or Amazon Aurora (MySQL compatible) databases.

    Backup MySQL Databases to S3

    Use the below step-by-step tutorial to back up the MySQL databases and upload them to the Amazon S3 bucket.

    1. Install AWS CLI

    In order to use this script, the system must have AWS CLI installed.

    https://tecadmin.net/installing-aws-cli-in-linux/

    2. Create S3 Bucket

    Login to AWS Management Console and create a new s3 bucket.

    Alternatively, you can also create s3 bucket via AWS CLI. The command will be like:

    aws s3api create-bucket --bucket s3-bucket-name --region us-east-1 
    

    Just replace the bucket name and region.

    3. Shell Script to Backup MySQL database to S3

    Copy the below shell script to a file like db-backup.sh. This script uses mysqldump command to create databases backups. Then use gzip command to archive backup files and finally use aws command to upload backup files to Amazon S3 bucket.

    Create a file like /backup/scripts/s3-backup-mysql.sh in edit your favorite text editor. Then add the below content:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    #!/usr/bin/env bash
     
    #########################################################################
    #########################################################################
    ###
    ####       Author: Rahul Kumar
    #####      Website: https://tecadmin.net
    ####
    #########################################################################
    #########################################################################
     
    # Set the folder name formate with date (2022-05-28)
    DATE_FORMAT=$(date +"%Y-%m-%d")
     
    # MySQL server credentials
    MYSQL_HOST="localhost"
    MYSQL_PORT="3306"
    MYSQL_USER="user"
    MYSQL_PASSWORD="password"
     
    # Path to local backup directory
    LOCAL_BACKUP_DIR="/backup/dbbackup"
     
    # Set s3 bucket name and directory path
    S3_BUCKET_NAME="s3-bucket-name"
    S3_BUCKET_PATH="backups/db-backup"
     
    # Number of days to store local backup files
    BACKUP_RETAIN_DAYS=30
     
    # Use a single database or space separated database's names
    DATABASES="DB1 DB2 DB3"
     
    ##### Do not change below this line
     
    mkdir -p ${LOCAL_BACKUP_DIR}/${DATE_FORMAT}
     
    LOCAL_DIR=${LOCAL_BACKUP_DIR}/${DATE_FORMAT}
    REMOTE_DIR=s3://${S3_BUCKET_NAME}/${S3_BUCKET_PATH}
     
    for db in $DATABASES; do
       mysqldump \
            -h ${MYSQL_HOST} \
            -P ${MYSQL_PORT} \
            -u ${MYSQL_USER} \
            -p${MYSQL_PASSWORD} \
            --single-transaction ${db} | gzip -9 > ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz
     
            aws s3 cp ${LOCAL_DIR}/${db}-${DATE_FORMAT}.sql.gz ${REMOTE_DIR}/${DATE_FORMAT}/
    done
     
    DBDELDATE=`date +"${DATE_FORMAT}" --date="${BACKUP_RETAIN_DAYS} days ago"`
     
    if [ ! -z ${LOCAL_BACKUP_DIR} ]; then
    cd ${LOCAL_BACKUP_DIR}
    if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
    rm -rf ${DBDELDATE}
     
    fi
    fi
     
    ## Script ends here

    Update all the necessary variables as per your system environment.

    4. How to run backup script

    Set the execute (x) permission on script:

    chmod +x s3-backup-mysql.sh 
    

    Then run the backup script.

    ./s3-backup-mysql.sh 
    

    5. Schedule backup script to run daily

    Schedule the shell script using crontab to run on a daily basis.

    crontab -e 
    

    Add the below settings to end of the file:

    # Run daily @ 2am
    0 2 * * * /backup/scripts/s3-backup-mysql.sh > /dev/null 2>&1
    

    Save the file and close it.

    Conclusion

    This tutorial provides you with a shell script to back up MySQL databases and upload them to the Amazon S3 bucket. That could be helpful for you to automate database backups and save a copy on cloud storage.

    aws awscli backup MySQL s3 shell script
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp
    Previous ArticleHow to Install Apache Maven on Ubuntu 22.04
    Next Article Setting Up Environment Variables on Ubuntu

    Related Posts

    How to Install MariaDB on Ubuntu 22.04

    Updated:May 28, 20222 Mins Read

    How To Install Linux, Nginx, MySQL, & PHP (LEMP Stack) on Ubuntu 22.04

    Updated:April 7, 20227 Mins Read

    How To Install MySQL Server on Ubuntu 22.04

    Updated:April 6, 20224 Mins Read

    How to Install Apache, MySQL, PHP (LAMP Stack) on Ubuntu 22.04

    Updated:June 28, 20225 Mins Read

    How to Backup Website to Amazon S3 using Shell Script

    Updated:March 24, 20222 Mins Read

    How To Install MariaDB on Debian 11

    4 Mins Read

    Leave A Reply Cancel Reply

    Recent Posts
    • What is the /etc/aliases file
    • What is the /etc/nsswitch.conf file in Linux
    • How to Install Ionic Framework on Ubuntu 22.04
    • What is the /etc/hosts file in Linux
    • How to Install Angular CLI on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.