Backup MySQL database and directly upload to AWS S3 bucket
In this post we will learn how to backup MySQL database and upload it to Amazon S3 server. We will be using Ubuntu Server 18.04.
sudo apt-get update sudo apt-get -y install python-pip sudo pip install awscli
Before we start using AWS CLI tool, we need to configure it by running the following command.
This will ask you to provide your
It looks like:
AWS Access Key ID [****************PQLL]: AWS Secret Access Key [****************KMA5]: Default region name [us-east-1]: Default output format [json]:
If you have done everything correctly you should be able to see the list of S3 buckets by running below command
aws s3 ls
Note: I recommend you read AWS CLI reference guide to learn how to use AWS CLI with various services.
We will create a shell script used to back up MySQL database and upload it to S3 bucket.
Copy the following content to the script file. This script uses mysqldump to dump the database to a temporary file, and It uploads the file to S3 by using the AWS CLI
#!/bin/bash # Database credentials USER="YourDatabaseUserName" PASSWORD="YourPassword" HOST="localhost" DB_NAME="database that you want to backup" #Backup_Directory_Locations BACKUPROOT="/tmp" TSTAMP=$(date +"%d-%b-%Y") S3BUCKET="your-s3-bucket" mysqldump -h$HOST -u$USER $DB_NAME -p$PASSWORD | gzip -9 > $BACKUPROOT/$DB_NAME-$TSTAMP.sql.gz # Move file from server to your S3 bucket aws s3 mv $BACKUPROOT/$DB_NAME-$TSTAMP.sql.gz s3://$S3BUCKET/
chmod +x mysql_backup.sh bash mysql_backup.sh
Now check your s3 bucket, you can see a backup file uploaded.
To add a cron job on Ubuntu 18.04 server, run the following command
Add following line at the end of the file
#Run the database backup script every day at 12.30 AM 29 0 * * * /path-to-your-script/mysql_backup.sh
In this post, we learned how to backup MySQL database and directly upload it in to AWS S3 bucket. Just schedule a cron job based on your requirement and you are done. This script will backup only one database. You can easily modify the shell script to backup all databases on your server.