In MySQL, there are several types of backup options available. Selecting one type over the other requires, analyzing the detailed requirements of an application, compliance and meeting disaster recovery objectives such as Recovery Time Objective (RTO) and Recovery Point Objective(RPO). Before moving into details lets look at several types of backup options available out there.

Copying Physical File System or Snapshots

Physical backup involves copying the exact directories from the physical file system to back up the database. Some of the file systems support taking logical copies of the file system in the form of snapshots.

Logical Query Backups

Backup the data querying the database using tools like mysqlbackup and mysqldump. As the output, this creates files which can be restored to recreate the database structure and data.

Online and Offline Backups

Online backups are taken while the database is running and offline backups while it's stopped. These are also named as HOT and COLD backups. It is also possible to lock the database and take the backups, which is considered as WARM backups.

Complete or Incremental Backups

Complete backup includes all the data at the point of time the backup is taken while the incremental backup only contains the changes happened during a given time.

Using AWS S3 to Store MySQL Backups

In addition to the process of taking backups, we also need to consider the infrastructure being used. If we use AWS Cloud, there are fully managed services that will manage the Backup process, while allowing direct options for the point in time restoration. If we use AWS EC2 instances to store the database, the MySQL backup can be done using EBS Snapshots (Virtual File System Snapshots). In both of these scenarios, AWS uses Amazon S3 internally to store the backup files since it provides higher levels of durability and availability.

If you plan to take a backup of the MySQL server running in your corporate data center and store them in AWS Cloud for disaster recovery, still you can use AWS Amazon S3. In this tutorial, I will be considering, taking Logical and Incremental Backups and store them in Amazon S3 for durability.

1. Logical and Incremental Backups

Taking Logical and Incremental backups provides a solid foundation for disaster recovery reducing both recovery time and point objectives. This way, the Logical backups can be taken periodically using mysqldump running as a CRON job, while enabling the MySQL binary logging for incremental backups.

Logical Backups

You can use the following command using mysqldump utility. The backup can be named uniquely along with the time and date it was taken as well as the binlog file name and position for easy identification or automation.

This will create a file which contains the database schema and data together to be used for a point on time recovery. However, you might also need to consider locking the database to avoid any integrity issues or use a replica of the database to take the backups.

Incremental Backups

Enabling incremental backup requires to configure taking binary logs (binlogs) in MySQL. You can enable the binary logs by adding the following to my.conf or my.in file under the [mysqld] section. It will also require a restart of mysqld after the changes. For more information refer to replication options in binary logs.

--log-bin[=base_name]
--log-bin-index[=file_name]

After enabling the logging, it will create a set of log files as follows.

You can also view the list of log files by editing mysql-bin.index file located in MySQL data directory.

2. Saving the Logical Backups and BinLogs to Amazon S3

This operation is quite straightforward. First of all, you will need to create an AWS S3 bucket. Then you will need to install the CLI tool available from AWS in your database server or in a remote server which initiates backups and restore. After that, you will need to create an AWS IAM user and setup credentials granting CLI access to AWS resources. Here make sure you attach an IAM policy that allows reading and writing to the particular bucket.

Then you can use the following AWS S3 CLI command to sync the log files to S3.

aws s3 sync . s3://mybucket

Note: Make sure you sync both incremental and dump directories.

3. Restoring the Database

Taking backups and storing them in a secured place is just a part of the story. To restore the database, it will require to download the required dump and binlog files depending on the point of recovery.

Let's assume, you need to restore the database to a particular date and time. First, you will need to identify the closest dump taken before the date and time given. You can use the following AWS CLI command to download the file to your server.

# aws s3 cp s3://mybucket/000005_210_02122016_2355_database_dump.sql

After downloading the file, restore the MySQL database using the dump file as follows.

# mysql -uroot -p < 000005_210_02122016_2355_database_dump.sql

Then on top of the dump file, you will need to load the changes from binlog(s). For this first, identify the binlogs taken after the database dump and download them from AWS S3 to your server.

Some name

I'm Ashan Fernando, currently working as a Technical Architect at 99XTechnology. I travel often and enjoy leisure time with my family and friends. Writing articles and playing badminton are two of my favorite hobbies.