A MySQL database backup can be stored in an S3 bucket in a number of different ways. The S3 bucket can be mounted as a local drive and the backup file can be stored directly in the S3 bucket, but if you want to use a simpler technique, this is for you.
Steps:
1) Back up the database in your local drive of the EC2 instance.
2) Create the backup folders in S3
3) Move the backup files from EC2 to S3.
First, create the required directories in S3. Go to your EC2 instance and check if you are able to access the S3 bucket. Use the below command to get the files/directories information from the S3 bucket.
aws s3 ls
Use the mysqldump to create the backup file as shown below.
mysqldump
–no-create-db myUserDB
–set-gtid-purged=OFF
–single-transaction
–compress
–order-by-primary
–host yourinstance.zone.rds.amazonaws.com
-u myUserName
-pmyPassword
–default-character-set=utf8mb4
–hex-blob
–triggers
–routines -P 3306 > backup.sql
Once the backup is completed, use the below command to move the backup file into S3 bucket as shown below:
aws s3 mv backup.sql s3://yourbucket.mysqlbackups/yourinstancebucket/
This will move (not copy) the file from the local file system to S3 bucket.
Hope you find this article helpful.
Big Data and SQL 