Streaming backups onto the Cloud Storage

MySQL Enterprise Backup(MEB) has been widely used as the most efficient tool to take backups of huge databases. The storage of backups can be done on-premises – viz, local hard disk, external hard disk drives, network mounted disk. MEB can also stream backups to tape by supporting different Media Management Software like Symantec NetBackup, EMC2 and Oracle Secure Backup. Streaming of backups can also be done using 'ssh' to another machine.

As data keeps growing, instead of expanding the storage, database users are choosing to rely on the growing trend which is the Cloud Storage.

A few facts about Cloud Storage:

1. Offers off-premises storage with AAA(Anytime, Anywhere Access)
2. Sharing of data to make a collaborative effort.
3. Highly fault tolerant, durable and scalable and secure.
4. Cost-effective

To the benefit of our MySQL database users, Mysql Enterprise Backup (MEB) has come up with the support for taking their backups and storing directly on the cloud.

MEB 3.10.2 provides this great feature of storing image backups on the cloud with its simplified command-line options. The backups can be restored from the cloud as well.

How does it work?


    MEB streaming backup from Remote Machine /  EC2 Instance to Amazon S3      

As illustrated in the picture, MEB can be hosted along with Server either on a remote machine or on the Amazon EC2 instance to take backup of MySQL databases/tables which will be stored in Amazon S3.

The list of cloud options introduced are:

• --cloud-service : Cloud service for data backup or restoration. Currently, only the Amazon S3 service is supported, and “s3” is the only value mysqlbackup accepts for this option.
• --cloud-bucket : The storage bucket on Amazon S3 for the backup image.
• --cloud-object-key : The Amazon S3 object key for the backup image.
• --cloud-access-key-id : AWS access key ID for logging onto Amazon S3.
• --cloud-secret-access-key : AWS secret access key that goes with the AWS access key id used in --cloud-access-key-id.
• --cloud-aws-region : Region for Amazon Web Services that mysqlbackup accesses for S3.
• --cloud-trace : Print trace information for cloud operations. It works independently of --trace, which specifies the trace level for the non-cloud operations of mysqlbackup. Any non-zero value for the option enables the trace function. Default value is “0.”
• --cloud-proxy=proxy-url:port : Proxy address and port number for overriding the environment's default proxy settings for accessing Amazon S3.

Lets dive into the MEB command-line with the options to be used for cloud storage.

For taking a simple image backup of the whole server:

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion --backup-dir=/backuptmp --backup-image=- backup-to-image  

For taking a image backup of selective tables:

This is very useful to allow access to specific table(s) to other users. As you are aware, the users can restore these tables onto their running server.

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion –include-tables=db1.tab1 --use-tts=with-full-locking --backup-dir=/backuptmp --backup-image=- backup-to-image  

MEB also supports compressed, encrypted and incremental image backups onto the cloud.

For example, to perform an encrypted image backup, the command-line is:

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion –encrypt –key-file=encryptKeyFile --backup-dir=/backuptmp --backup-image=- backup-to-image  

All operations supported for non-cloud image backups can be performed.
Here goes the list:

extract
image-to-backup-dir
validate
copy-back-and-apply-log

The image stored on the cloud can be directly restored onto a (running) server.

For restoring the selective image backup into a running server:

./mysqlbackup –defaults-file=/backuptmp/backup-my.cnf --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion --backup-dir=/restoretmp --backup-image=- --datadir=/datadir copy-back-and-apply-log  
Note: This is the image backup taken with –use-tts option.

For more options and usage samples, please refer MEB Manual.

Thus MEB helps the user to take backups and stream it to cloud using simple command-line WITHOUT the need for any external tool or script.