You are viewing an older version of this section. View current production version.
BACKUP DATABASE
Backs up a database. The backup files are saved on the master aggregator and on the leaves.
Syntax
Backup to a Local or Network Drive
BACKUP [DATABASE] db_name TO "backup_path"
Backup to S3
BACKUP [DATABASE] db_name TO S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json
configuration_json:
'{"region":"your_region",
"endpoint_url":"http://other_endpoint/"}'
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"]
}'
Optional S3 Configuration Values
region
: AWS region where S3 bucket resides.endpoint_url
: Utilizes S3 backup to target S3-compatible storage targets.
Remarks
db_name
is the name of a MemSQL database.- The
BACKUP DATABASE
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 theBACKUP DATABASE
operation started (all committed transactions at the time theBACKUP DATABASE
started will be stored in the backup). Backup files containing the database’s reference tables are saved on the master aggregator. Backup files are also saved on the leaves.InfoBacking up a database does not backup the users and grants for objects in the database. You must do this as a separate action. In addition, backing up a database does not back up system variables, so the user must back up the
memsql.cnf
file and output ofSHOW _SYNC VARIABLES LIKE '%'
command from each node. - A backup of system databases cannot be created.
BACKUP DATABASE
is an online operation, which means that writes will continue to run while theBACKUP DATABASE
is in progress. Any writes executed after theBACKUP DATABASE
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 DATABASE
operation. The backup file is created on the server and not the client, unlikemysqldump
. - 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 DATABASE
. Once theBACKUP DATABASE
is running, queries can run against the cluster normally. If there is a long-running write query executing at the time aBACKUP DATABASE
operation is run, theBACKUP DATABASE
will wait for the query to finish. - MemSQL does not support
BACKUP DATABASE
on remote databases.
Back up to a Local or Network Drive
- The
backup_path
is relative to the directory stored in thedatadir
system variable, by default. You can include/
at the front of thebackup_path
to override the default and use an absolute path.
Backup to S3
BACKUP ... TO S3
writes the backup files to the specified S3 bucket.- MemSQL will add
db_name.backup
to the path and put the objects in that path. 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 specifyingaws_access_key_id
andaws_secret_access_key
values. This is only needed if your AWS security is setup to require a role.
Back up to Azure Blobs
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 backs up a database to /my-backup-dir
.
BACKUP DATABASE memsql_demo to "/my-backup-dir";
The following example backs up a database to the directory stored in the datadir
system variable.
BACKUP DATABASE memsql_demo to "./";
The following example backs up a database to the my-relative-backup-dir
directory, which is relative to the directory stored in the datadir
system variable. In this example, if datadir
contained the directory /var/lib/memsql/leaf-3308-aaaaaaaaaa/data
, the backup would be saved to the /var/lib/memsql/leaf-3308-aaaaaaaaaa/data/my-relative-backup-dir
directory.
BACKUP DATABASE memsql_demo to "./my-relative-backup-dir";
S3 Backup
The following example backs up a database 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 backs up a database 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"}';
This MemSQL feature is only available in MemSQL Enterprise Edition. MemSQL Enterprise Edition includes 24x7 support and several enterprise-only features for critical deployments. For more information about MemSQL Developer and MemSQL Enterprise, see the MemSQL Editions page.