Wednesday, December 4, 2013

Backup of Mysql on AWS to local directory and then to S3

Here is a method of backing up a MySQL database using the standard MySQL dump command and then moving the database dump to S3. This backups up the entire database each time which may not be what you want to do for a large production database.

1. Backup to local EC2 directory
sudo chown ec2-user /backup
sudo echo "mysqldump -uroot -ppassw-lab awslab > /backup/db_backup\`date '+%Y%m%d.%H%M'\`.sql" > mysqlbackup.sh
chmod +x mysqlbackup.sh

2. Create cron job to backup on a schedule. 
echo "* * * * * /home/ec2-user/mysqlbackup.sh" > ec2cron sudo crontab -u ec2-user ec2cron
sudo crontab -u ec2-user ec2cron
crontab -l
3. Send to S3
echo "aws s3 mv /backup/db_backup\`date '+%Y%m%d.%H%M'\`.sql s3://sysopsmysqlbackup/backups/db_backup\`date '+%Y%m%d.%H%M'\`.sql --region us-west-2 " >> /home/ec2-user/mysqlbackup.sh
aws s3 ls s3://sysopsmysqlbackup/backups --region us-west-2



1 comment:

  1. Hi i have EC2 instance and i executed first 2 steps. ? it created two new files mysqlbackup.sh,ec2cron in the local directory is that enough for backup process ? is this is the real time sample ?!

    ReplyDelete