MySQL incremental backup on S3 Storage bucket

Steps to take incremental backup of MySQL database and upload it on S3 bucket:

1. Enable binary loging in mysql for our database. Ad
ded below entries in /etc/my.cnf under [mysqld]
log-bin=mysql-bin-log
binlog-do-db=Database_name
expire_logs_days=2
max_binlog_size=100M



2. R
estart mysql server
systemctl restart mysqld

3. Installed s3cmd & configured it
yum install s3cmd

s3cmd –configure
Enter AWS access_key and secret_access_key

4. Below script to execute incremental backup and put it on S3 Bucket using s3cmd

#!/bin/bash

filename=mysql-db-backup-`date +”%m-%d-%y”`.sql
echo $filename

mysqldump –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs -u DB_username -pDB_PASSWORD > /path/to/$filename

bzip2 /path/to/$filename

s3cmd put /path/to/$filename.bz2 s3://bucket_name

Neelesh Gurjar has written 120 articles