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

3 min readJul 1, 2021

Amazon S3 is a service offered by Amazon Web Services (AWS). It provides storage through interface and provides APIs for storage. It helps with computational scaling. It is very important to make regular backups of your data. S3 is a highly available distributed object storage service by AWS. It is a perfect environment to store critical and sensitive data like databases which doesn’t consume large space. This guide will explain you the process of backing up your MySQL databases to Amazon S3 on CentOS/Ubuntu server.

Prerequisites:

Amazon S3 account
MySQL database
sudo user

Step 1:

Login or ssh to your webserver as a sudo user. Install awscli on your terminal. Here, we will use the python package pip to install awscli

sudo yum install python-pip

For CentOS,

sudo apt-get python-pip

For Debian/Ubuntu,

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

Step 2:

Install awscli from pip.

sudo pip install awsclipip3 install awscli — upgrade — user

Verify the installation by running the following command.

aws — version

The awscli is updated on a regular basis to facilitate new services and commands. The awscli can be updated using the following command.

pip install awscli — upgrade — user

Step 3:

You need to set the credentials in the cli to authenticate the API connections. The AWS can be configured with the user’s credentials using the aws configure command.

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

Enter the Access Key ID & Secret Access Key. It will create a folder in ~/.aws using your credentials.

ls .aws
config credentials

Step 4:

Create a backup of your database to upload to Amazon S3.

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

Now you can create mydb_aws

if [ ! -d $mydb_aws ]; then
 mkdir -p $mydb_aws
fi

Dump the database using the following command.

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

Your database (excluding mysql and performance_schema) has now been exported to mydb_aws folder.

Step 5:

After exporting the databases you can compress your backup file before exporting to S3. Compressing before making a backup is always advised.

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

This will upload the compressed the file to S3.

aws s3 ls ${s3_bucket_name}

This concludes the backup of MySQL databases to Amazon S3.

Congratulations, you successfully exported your MySQL databases to Amazon S3 on your CentOS / Ubuntu VPS.

===============================

If you find this story helpful, feel free to buy me a virtual coffee 😉👇🏻

☕ https://ko-fi.com/mnzel1☕