{"id":7847,"date":"2019-06-19T08:29:17","date_gmt":"2019-06-19T00:29:17","guid":{"rendered":"http:\/\/rmohan.com\/?p=7847"},"modified":"2019-06-19T08:29:19","modified_gmt":"2019-06-19T00:29:19","slug":"mysqldump-gzip-aws","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7847","title":{"rendered":"mysqldump + gzip + aws"},"content":{"rendered":"\n

A shell script to backup MYSQL database and upload it to Amazon S3.<\/p>\n\n\n\n

Note
\nMake sure the AWS CLI is installed properly<\/p>\n\n\n\n

  1. mysqldump + gzip + aws
    \nDump the database with mysqldump and gzip it into a folder, later uses the aws command to upload the file to Amazon S3<\/li><\/ol>\n\n\n\n

    backup-script.sh<\/p>\n\n\n\n

    !\/bin\/bash<\/h1>\n\n\n\n

    #<\/h6>\n\n\n\n

    #<\/h1>\n\n\n\n

    MySQL Database To Amazon S3<\/h2>\n\n\n\n

    #<\/h6>\n\n\n\n

    NOW=$(date +”%Y-%m-%d”)<\/p>\n\n\n\n

    BACKUP_DIR=”\/home\/mohan\/backup”
    \nMYSQL_HOST=”localhost”
    \nMYSQL_PORT=”3306″
    \nMYSQL_USER=”YOUR_DB_USER”
    \nMYSQL_PASSWORD=”YOUR_DB_PASSWORD”
    \nDATABASE_NAME=”YOUR_DB_NAME”<\/p>\n\n\n\n

    AMAZON_S3_BUCKET=”s3:\/\/mohan\/backup\/mysql\/”
    \nAMAZON_S3_BIN=”\/home\/mohan\/.local\/bin\/aws”<\/p>\n\n\n\n

    FOLDERS_TO_BACKUP=(“\/home\/mohan\/bk1” “\/home\/mohan\/bk2”)<\/p>\n\n\n\n

    #<\/h6>\n\n\n\n

    mkdir -p ${BACKUP_DIR}<\/p>\n\n\n\n

    backup_mysql(){
    \n mysqldump -h ${MYSQL_HOST} \\
    \n -P ${MYSQL_PORT} \\
    \n -u ${MYSQL_USER} \\
    \n -p${MYSQL_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DIR}\/${DATABASE_NAME}-${NOW}.sql.gz
    \n}<\/p>\n\n\n\n

    backup any folders?<\/h1>\n\n\n\n

    backup_files(){<\/p>\n\n\n\n

        tar -cvzf ${BACKUP_DIR}\/backup-files-${NOW}.tar.gz ${FOLDERS_TO_BACKUP[@]}<\/code><\/pre>\n\n\n\n
    }<\/p>\n\n\n\n
    upload_s3(){
    \n        ${AMAZON_S3_BIN} s3 cp ${BACKUP_DIR}\/${DATABASE_NAME}-${NOW}.sql.gz ${AMAZON_S3_BUCKET}
    \n}<\/p>\n\n\n\n
    backup_mysql
    \nupload_s3
    \nCopy<\/p>\n\n\n\n
    1. How to run?
      \nAssign execute permission to the shell script, and run it directly.<\/li><\/ol>\n\n\n\n
      Terminal
      \n$ chmod +x backup-script.sh<\/p>\n\n\n\n
      run it<\/h1>\n\n\n\n
      $ .\/backup-script.sh
      \nCopy<\/p>\n\n\n\n
      1. Run it daily
        \n3.1 cron schedule to run the script daily.<\/li><\/ol>\n\n\n\n
        Terminal
        \n$ crontab -e<\/p>\n\n\n\n
        Daily, 7pm<\/h1>\n\n\n\n
        0 19 * * * \/path.to\/backup-script.sh > \/dev\/null 2>&1<\/p>\n","protected":false},"excerpt":{"rendered":"\n
        A shell script to backup MYSQL database and upload it to Amazon S3.<\/p>\n
        Note Make sure the AWS CLI is installed properly<\/p>\n
         mysqldump + gzip + aws Dump the database with mysqldump and gzip it into a folder, later uses the aws command to upload the file to Amazon S3 <\/p>\n
        backup-script.sh<\/p>\n
         !\/bin\/bash # # […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7847"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7847"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7847\/revisions"}],"predecessor-version":[{"id":7848,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7847\/revisions\/7848"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}