{"id":6090,"date":"2021-01-30T15:41:46","date_gmt":"2021-01-30T10:11:46","guid":{"rendered":"https:\/\/pheonixsolutions.com\/blog\/?p=6090"},"modified":"2021-02-01T17:51:55","modified_gmt":"2021-02-01T12:21:55","slug":"automate-mysqldump-backup-from-serverbackend-to-amazon-s3","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/automate-mysqldump-backup-from-serverbackend-to-amazon-s3\/","title":{"rendered":"Automate MySQLdump Backup from server(backend) to Amazon S3"},"content":{"rendered":"\n

Please follow the below steps to setup backup of mysql database from server to Amazon s3.<\/p>\n\n\n\n

  1. Login to AWS console, create IAM user, download the credentials and attach AmazonS3Full access policy to the IAM user.<\/li>
  2. Install AWS CLI and configure by following the below commands.
    apt install awscli -y
    aws configure<\/span><\/li>
  3. It will ask for Access Id and secret key. Provide the same which we downloaded in step-1 and proceed.<\/li>
  4. Verify AWS S3 list by following the below command.
    aws s3 ls<\/span><\/li>
  5. Install S3fs and Mount S3 Bucket by following the below commands.
    apt install s3fs<\/span>
    echo ACCESS_KEY_ID:SECRET_ACCESS_KEY > \/home\/ubuntu\/.s3fs- creds<\/span><\/li>
  6. Your file will be saved at \/home\/ubuntu\/.s3fs-creds.<\/span><\/li>
  7. chmod 600 \/home\/ubuntu\/.s3fs-creds<\/span><\/li>
  8. Create a new folder from your home directory.
    mkdir \/home\/ubuntu\/s3_uploads<\/span><\/li>
  9. Create an Automation Backup Script for MySQLdump
    vim \/home\/ubuntu\/.config.cnf<\/span><\/li>
  10. Add the following below:
    [client]
    user = dbuser
    password = dbpassword
    host = localhost<\/span>
    Save and close the file.<\/li>
  11. create an automation backup script for MySQLdump
    vim \/home\/ubuntu\/mysqldump.sh<\/span><\/li>
  12. Add the following script
    !\/bin\/bash
    DBNAME=yourdbname
    BACKUP=\/home\/ubuntu\/s3_uploads
    Backup date
    date=$(date +’%Y%m%d%H%M%S’);
    Start exec backup..
    mysqldump –defaults-extra-file=\/home\/ubuntu\/.config.cnf $DBNAME > $BACKUP\/mysqldump_$date.sql<\/span><\/li>
  13. Save and close the file.<\/li>
  14. To make sure the file is executable, run the below command.
    chmod +x \/home\/ubuntu\/mysqldump.sh<\/span><\/li>
  15. To automate your backup, use to command below to assign the mysql dump script on cron scheduling jobs.
    crontab -e<\/span><\/li>
  16. If your first time to open the crontab, you can choose the vim editor on the screen and then add the following command to the bottom line.
    @daily sh \/home\/ubuntu\/mysqldump.sh<\/span><\/li>
  17. Save and close the file.<\/li>
  18. Testing MySQLdump to S3 Bucket<\/li>
  19. Go to the directory where mysqldump.sh is located and run the script.
    .\/mysqldump.sh<\/span><\/li>
  20. After the backups completes, open the mounted s3_uploads<\/code> folder and type the below command.
    ls -lah<\/span><\/li>
  21. On the screen, you can see the files which were uploaded.<\/li>
  22. Verify the files on S3 Bucket Console.
    <\/li><\/ol>\n\n\n\n

    <\/p>\n\n\n\n

    <\/p>\n","protected":false},"excerpt":{"rendered":"

    Please follow the below steps to setup backup of mysql database from server to Amazon s3. Login to AWS console, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1019],"tags":[300,813,812,324],"class_list":["post-6090","post","type-post","status-publish","format-standard","hentry","category-cloud-aws","tag-aws","tag-backup-of-mysql-databse-to-s3","tag-mysql-dump-to-s3","tag-s3","psol-cat-cloud-aws"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/phn2x7-1Ae","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/6090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=6090"}],"version-history":[{"count":0,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/6090\/revisions"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=6090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=6090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=6090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}