Monday Feb 01, 2016

MySQL Enterprise Backup (MEB) and Oracle Storage Cloud

MEB 3.12.0 and above support cloud backup and restore using OpenStack-compatible object stores ("Swift"). This allows MySQL database users with Oracle Storage Cloud account to take backups and store them directly in the cloud and restore them from there.

The following steps illustrate how to set up and use MEB with Oracle Storage Cloud :

1) Create Oracle Storage Cloud account at https://cloud.oracle.com/storage . Once service gets activated, make a note of the following credentials that will be required in further steps :

  • Username

  • Password

  • Identity domain name

  • Service Instance Name : Customer-specified name of the service instance

  • Data Center Code : The identifier of the data center in which the service instance is provisioned. For example, dataCenterCode is us2 for the data center US Commercial 2 in Chicago and em2 for EMEA Commercial 2 in Amsterdam.

2) Create a container where the backup images (objects) will be stored. A container is a user-created resource, which can hold an unlimited number of objects but cannot be nested. To create container, we use çURL :

  • Oracle Storage Cloud Service requires authentication when executing operations against the service instance. Generate an Authorization Token that allows creation, deletion, and listing of containers.

    Get the Auth-Token :

         çurl -v -X GET -H 'X-Storage-User: <serviceInstanceName>-<identityDomainName>:\

    <userName>' -H 'X-Storage-Pass: <Password>' \  

    https://<dataCenterCode>.storage.oraclecloud.com/auth/v1.0

         The "Auth-Token" generated expires in 30 minutes.

  • Create a container with a name of one's choice that is not already in use in the service instance. The “container name” can be from 1 to 256 characters long and must consist of alphanumeric characters.

    To list existing container(s) :

    çurl -v -s -X GET -H 'X-Auth-Token: <Generated_Auth_Token>' \

    https://<dataCenterCode>.storage.oraclecloud.com/v1/<serviceInstanceName>-\

    <identityDomainName>

    To create a new container :

    çurl -v -X PUT -s -H 'X-Auth-Token: <Generated_Auth_Token>' \

    https://<dataCenterCode>.storage.oraclecloud.com/v1/<serviceInstanceName>-\

    <identityDomainName>/<container_name>

3) The setup is complete and MEB operations on images can now be performed with Oracle Storage Cloud. For example :

  • Take backup to cloud :

    mysqlbackup -uroot --socket=/tmp/mysql.sock --cloud-service=openstack \

    --cloud-container=<container_name> --cloud-object=<backup_image_name> \

    --cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> \

    --cloud-password=<Password> \

    --cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \

    --backup-dir=/home/user/temp_dir --backup-image=- backup-to-image


  • Extract from cloud backup :

    mysqlbackup --cloud-service=openstack --cloud-container=<container_name> \

    --cloud-object=<backup_image_name> \

    --cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> \

    --cloud-password=<Password> \

    --cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \

    --backup-dir=/home/user/bkp_dir --backup-image=- image-to-backup-dir


  • Restore from cloud backup :

    mysqlbackup --defaults-file=/home/user/bkp_dir/backup-my.cnf \

    --cloud-service=openstack --cloud-container=<container_name> \

    --cloud-object=<backup_image_name> \

    --cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> \

    --cloud-password=<Password> \

    --cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \

    --backup-dir=/home/user/tmp_dir --backup-image=- --datadir=/home/user/db/datadir  \

    copy-back-and-apply-log


All MEB operations that can be performed with cloud storage are listed here :

Backup of MySQL 5.5 and 5.6 to Cloud with MEB 3.12

Backup of MySQL 5.7 to Cloud with MEB 4.0

4) Objects or containers can be removed as follows :

  • Delete an object in a container :

          çurl -v -X DELETE -H "X-Auth-Token: <Generated_Auth_Token>" \

    https://<dataCenterCode>.storage.oraclecloud.com/v1/<serviceInstanceName>-\

    <identityDomainName>/<container_name>/<object_name>


  • Delete a container :

         çurl -v -X DELETE -H "X-Auth-Token: <Generated_Auth_Token>" \

    https://<dataCenterCode>.storage.oraclecloud.com/v1/<serviceInstanceName>-\

    <identityDomainName>/<container_name>


Figure : Sequence Diagram for Backup to Oracle Storage Cloud

Thursday Mar 19, 2015

Distributing innodb tables made simpler!

With the support for cloud backups in MEB, distributing innodb tables across multiple MySQL instances has become easier.

1. Backup - take a cloud(Amazon S3) backup of the tables to be shared using the --use-tts=with-full-locking option.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> \
--include-tables=^mycompany\.items.* --use-tts=with-full-locking \
--backup-dir=/tmp/bkups/backupdir --compress --backup-image=- \
backup-to-image


(here the regex used will backup the tables whose name match “items*” in the database “mycompany”.)

2. Change Permissions - from the Amazon S3 (web) console, change the permissions of the backup file.

For a restore by users without S3 accounts, the file “Open/Download” permissions should be granted to “Everyone”. To restrict restore only to users with valid S3 accounts grant “Open/Download” permissions for “Authenticated Users”.

3. Restore - any number users(/MySQL applications) from anywhere (with/without S3 accounts) can use MEB to restore the new tables into a running MySQL instance.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--backup-dir=/tmp/bkups/cpbackdir --uncompress \
--datadir=/home/mysql-5.6/datadir --backup-image=- \
copy-back-and-apply-log


(options --cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> needed if “Open/Download” permissions are set for “Authenticated Users”)

Make sure the tables being imported do not exist in the target database. This operation is same as importing tables from a backup taken with --use-tts option.

4. Done!

Tuesday Jul 01, 2014

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.

About

MySQL MEB Team Blog

Search

Categories
Archives
« May 2016
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today