How to Backup MySQL Databases to Amazon S3 On CentOS/Ubuntu VPS

Prerequisites:

Step 1:

sudo yum install python-pip
sudo apt-get python-pip
brew install python3 && curl -O https://bootstrap.pypa.io/get-pip.py

Step 2:

sudo pip install awsclipip3 install awscli — upgrade — user
aws — version
pip install awscli — upgrade — user

Step 3:

aws configureAWS Access Key ID [None]: 
AWS Secret Access Key [None]:
Default region name [None]:
Default output format [None]:
ls .aws
config credentials

Step 4:

export DB_USER=”root”
export DB_PASSWORD=””
export DB_HOST=”localhost”
export date_format=`date +%a`
export db_dir=”/tmp/databases/$date_format”
export dest_backup_file=”/tmp/mysql-databases-$date_format.tgz”
export s3_bucket=”s3://bucket-name”
mkdir -p $db_dir 2>/dev/null
if [ ! -d $mydb_aws ]; then
mkdir -p $mydb_aws
fi
databases=`mysql -u $USER -h $HOST -p$PASSWORD -e “SHOW DATABASES;” | tr -d “| “ | grep -v Database`for db in $databases; do
if [[ “$db” != “information_schema” ]] && [[ “$db” != “performance_schema” ]] && [[ “$db” != “mysql” ]] && [[ “$db” != _* ]] ; then
echo “Dumping database: $db”
mysqldump -u $USER -h $HOST -p$PASSWORD — databases $db > $mydb_aws/$db-$date_format.sql
fi
done

Step 5:

tar -zcvf $dest_backup_file -C $mydb_awsaws s3 cp $dest_backup_file ${s3_bucket}
aws s3 ls ${s3_bucket_name}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store