BACKUP

Back up data to a binary file.

Syntax

Local Backup

BACKUP DATABASE db_name TO "backup_path"

S3 Backup

BACKUP DATABASE db_name TO S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json

configuration_json:
'{"region":"your_region"}'

credentials_json:
'{"aws_access_key_id": "replace_with_your_access_key_id", 
  "aws_secret_access_key": "replace_with_your_secret_access_key", 
  ["aws_session_token": "replace_with_your_temp_session_token",] 
  ["role_arn":"replace_with_your_role_arn"]
}'

Azure Blobs Backup

BACKUP DATABASE db_name TO AZURE "container/blob-prefix" CREDENTIALS credentials_json

credentials_json:
'{"account_name": "your_account_name_here", 
  "account_key": "your_account_key_here"
}'

Remarks

  • db_name is the name of a MemSQL database.
  • The BACKUP command writes a consistent snapshot of the database to disk. This means a backup stores the data in the database as it existed at the time the BACKUP operation started (all committed transactions at the time the BACKUP started will be stored in the backup).
  • BACKUP is an online operation, which means that writes will continue to run while the BACKUP is in progress. Any writes executed after the BACKUP has started will not be reflected in the stored backup.
  • All partitions of the database need to be in the online or replicating state to run a BACKUP operation. The backup file is created on the server and not the client, unlike mysqldump. For local backups, backup_path is specified relative to memsqlbin/data.
  • This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands).
  • The aggregator needs to briefly block new queries from running across the cluster to start a BACKUP. Once the BACKUP is running, queries can run against the cluster normally. If there is a long-running write query executing at the time a BACKUP operation is run, the BACKUP will wait for the query to finish.
  • MemSQL does not support BACKUP on remote databases.

Local Backup Remarks

  • The easiest way to BACKUP and RESTORE databases is using a network file system (NFS) mounted on every node in the cluster or using an S3 bucket as described here: Backing Up and Restoring Data.

S3 Backup Remarks

  • Backup to S3 writes the backup files to the specified S3 bucket.
  • MemSQL backup will add db_name.backup to the path and put the objects in the path path/db_name.backup/.
  • bucket/path/db_name.backup must not currently exist; otherwise, an error will be returned.
  • aws_session_token is optional. This is only needed if your AWS account uses the AWS Security Token Service.
  • role_arn is optional and can be used instead of specifying aws_access_key_id and aws_secret_access_key values. This is only needed if your AWS security is setup to require a role.

Azure Blobs Backup Remarks

  • Backup to Azure writes the backup files to the specified Azure container.
  • MemSQL backup will append db_name.backup to the blob prefix and and will put objects into the “path” blob-prefix/db_name.backup/.
  • container/blob-prefix/db_name.backup must not currently exist; otherwise, an error will be returned.

Examples

Local Backup

The following example creates a backup on each node in the /var/lib/memsql/data directory.

BACKUP DATABASE memsql_demo to "./";

S3 Backup

The following example backups to an S3 bucket.

BACKUP DATABASE memsql_demo TO S3 "backup_bucket/backups/6_1_2018" 
CONFIG '{"region":"us-east-1"}' 
CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';

The following example backups to an S3 bucket using an Amazon Resource Name (ARN) for AWS Identity and Access Management (IAM).

BACKUP DATABASE mydatabase TO S3 'my-bucket/memsql-backup/'
CONFIG '{"region": "us-east-1"}'
CREDENTIALS '{"role_arn": "arn:aws:iam::<AccountID>:role/EC2AmazonS3FullAccess"}';

Azure Blobs Backup

The following example backups to an Azure bucket.

BACKUP DATABASE memsql_demo TO Azure "backup_container/backups/6_1_2018" 
CREDENTIALS '{"account_name":"your_account_name_here","account_key":"your_account_key_here"}';
Was this article useful?