Backing up a MySQL database to S3 storage

Attention The article is in development status

Description

This article is general and describes the basic steps for

Basic information

MySQL is the solution for small to medium sized applications. Part of WAMP, AppServ, LAMP servers. Typically MySQL is used as a server accessed by local or remote clients. Also MySQL is one of the most popular management systems open source databases for developing interactive websites.

If your site or application stores its sensitive data in a MySQL database, you probably want to back up this information so you can was recovered in the event of any accident or other unforeseen situations.

There are several ways to back up your MySQL data. In this article, we will look at how to create database backups and create an automatic backup solution to streamline the process. We’ll look at an example using mysqldump, including backing up your database. data to a file, a compressed gzip file and sending it to a remote S3 storage.

Using S3 storage to store database copies remotely is simple backup solution for people who use MySQL servers in cloud infrastructures, as very often a service provider, providing IaaS provides separate S3 storage.

The main benefit of using S3 backup is data loss protection. You will need to back up first each database using the “mysqldump” command, automating backup tasks copying with tar, setting up a cron job, and creating a backup script copy to S3.

Important

The main advantages of using S3:
  • Data is available when it is needed and protected from disruptions, errors and threats.
  • S3 storage supports version control capabilities.
  • Data is transferred using the secure https protocol.
  • Has a high “throughput” in the network, can work through firewalls as it is identified as web traffic.
  • S3 protocol allows downloading files in case of disconnection.
  • High performance due to multi-threaded data loading.
  • Stores data in its original format.
  • S3 is much cheaper than SSD storage and requires no maintenance.
  • A large number of available cross-platform clients and plugins.
  • With S3, you only pay for the storage you actually use.

Setting up MySQL backups to S3

Step 1. Install S3cmd: Download the latest version of s3cmd using this link « https://sourceforge.net/projects/s3tools/?source=typ_redirect ».

sudo apt-get -y install python-setuptools
wget http://netix.dl.sourceforge.net/project/s3tools/s3cmd/1.6.0/s3cmd-1.6.0.tar.gz
tar xvfz s3cmd-1.6.0.tar.gz
cd s3cmd-1.6.0
sudo python setup.py install
During the setup, you must enter two keys:
  • Access key
  • Secret key - your identifiers in the S3 storage.

Attention

“Access key” и “Secret key” are case sensitive and must be entered
for sure, otherwise you will get invalid signature errors.

Other advanced settings can be changed (if necessary) by editing config file manually. Some settings contain default values for s3cmd.

Step 2. Script to back up MySql database to S3

A shell script used to back up your database and load it to S3.

The idea is to create the following script - script and run it with appropriate environment variables and then uses mysqldump to dump the database to a temporary file and load the file into S3:

#Save dbbacku.sh

#!/bin/bash
## Specify the name of the database that you want to backup

# Database credentials
USER="DB-USER"
PASSWORD="PASSWORD"
HOST="DB-host-name"
DB_NAME="Database-name"

#Backup_Directory_Locations
BACKUPROOT="/tmp/backups"
TSTAMP=$(date +"%d-%b-%Y-%H-%M-%S")
S3BUCKET="s3://s3bucket"
#LOG_ROOT="logs/dump.log"

#mysqldump  -h <HOST>  -u <USER>  --database <DB_NAME>  -p"password" > $BACKUPROOT/$DB_NAME-$TSTAMP.sql

#or

mysqldump -h$HOST -u$USER $DB_NAME -p$PASSWORD | gzip -9 > $BACKUPROOT/$DB_NAME-$TSTAMP.sql.gz

if [ $? -ne 0 ]
    then
     mkdir /tmp/$TSTAMP
    s3cmd put -r /tmp/$TSTAMP $S3BUCKET/
    s3cmd sync -r $BACKUPROOT/ $S3BUCKET/$TSTAMP/
    rm -rf $BACKUPROOT/*
else
    s3cmd sync -r $BACKUPROOT/ $S3BUCKET/$TSTAMP/
    rm -rf $BACKUPROOT/*
fi

Step 3. Let’s run the script now:

# chmod +x dbbackup.sh
# Run the script to make sure it's all good
# bash dbbackup.sh

Step 4. Schedule the script to run on a schedule:

Assuming the backup script is stored in the / opt / scripts directory, we need to add a crontab task to run it automatically on a weekly basis. To do this, you need to edit the crontab file:

#vim /etc/crontab
#Add the following lines:
#Run the database backup script on every week at 12.00

0 0 * * 0  bash /opt/scripts/mysqlbackupS3.sh to  >/dev/null 2>&1

Conclusion

In this article, we have explained how to automate the backup process. MySQL directly to S3 storage. Just schedule and customize the script on your MySql server and you’re done. This service regularly launches a process in according to the pre-configured schedule. Feel free to ask any questions, glad to hear from you.