Restore data from a binary backup file.

Syntax

Restore from a Local or Network Drive

RESTORE [DATABASE] db_name FROM "backup_path" [WITH {SYNC | ASYNC} REPLICATION]

Restore from S3

RESTORE [DATABASE] db_name FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH {SYNC | ASYNC} REPLICATION]

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"]
}'

Restore From an S3 Compatible Storage Provider

RESTORE [DATABASE] db_name FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH {SYNC | ASYNC} REPLICATION]

configuration_json:
'{"endpoint_url":"http://storage_provider_endpoint"}'

credentials_json:
'{"aws_access_key_id": "replace_with_your_access_key_id",
  "aws_secret_access_key": "replace_with_your_secret_access_key"
}'

Restore From Google Cloud Storage using the S3 Interface

RESTORE [DATABASE] db_name FROM S3 "bucket/path" [CONFIG configuration_json] CREDENTIALS credentials_json [WITH {SYNC | ASYNC} REPLICATION]

configuration_json:
'{"compatibility_mode":true,
  "endpoint_url":"https://storage.googleapis.com"}'

credentials_json:
'{"aws_access_key_id": "replace_with_your_google_access_key",
  "aws_secret_access_key": "replace_with_your_google_secret_key"
}'

See the remarks for details on the aws_access_key_id and aws_secret_access_key.

Restore from Azure Blobs

RESTORE [DATABASE] db_name FROM AZURE "container/blob-prefix" CREDENTIALS credentials_json [WITH {SYNC | ASYNC} REPLICATION]

credentials json:
'{"account_name": "your_account_name_here",
  "account_key": "your_account_key_here"
}'

Remarks

• db_name is the name of the MemSQL database to restore to.
• If you wish to restore the backup of one database into a differently-named database, you can do so by specifying the full path to the old database’s .backup file in backup_path for local backups or path for S3 or prefix-key for Azure blobs. For example, you can do BACKUP DATABASE db TO './path/' followed by RESTORE DATABASE newdb FROM './path/db.backup'. See the Examples section for examples.
• The RESTORE command replays a binary backup file in the same manner in which MemSQL recovers a database snapshot during startup.
• WITH {SYNC | ASYNC} REPLICATION controls whether replication in high availability will be done synchronously or asynchronously. The default mode is ASYNC. The default replication mode can be changed by changign the engine variable replication_sync). Partitions replicated synchronously will ensure a transaction has reached all replicas before acknowledging the transaction as committed to the user client. While providing stronger consistency guarantees, this can negatively impact write performance. For more information, see Managing High Availability.
• A RESTORE operation temporarily puts the database being restored into the recovering snapshot state. A database in this state cannot be queried. When the RESTORE command finishes, it puts the database into the online state. (see the Database topic for more information).
• This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands).

Local Restore Remarks

• backup_path is the path used in BACKUP .
• The path backup_path needs to be accessible by the memsqld process. Paths are resolved relative to the memsqlbin/data directory.

Restore from S3

• aws_session_token is optional. This is only needed if your AWS account uses the AWS Security Token Service.
• role_arn is optional. This is only needed if your AWS security is setup to require a role.
• endpoint_url is optional.
• You should not specify the compatibility_mode setting, as you would when restoring from Google Cloud Storage using the S3 interface.

Restore From an S3 Compatible Storage Provider

• RESTORE ... FROM S3 with the endpoint_url set to the URL of the S3 compatible storage provider restores an S3 compatible backup from the storage provider.
• aws_access_key_id is optional. It is the access key id for accessing the storage provider.
• aws_secret_access_key is optional. It is the secret access key for accessing the storage provider.
• You should not specify the compatibility_mode setting, as you would when restoring from Google Cloud Storage using the S3 interface.

Restore From Google Cloud Storage using the S3 Interface

• RESTORE ... FROM S3 with the endpoint_url set to https://storage.googleapis.com and compatibility_mode set to true restores a Google Cloud Storage backup that is closely compatible with S3. Compatibility mode disables certain optimizations that work only with native S3.
• For aws_access_key_id, provide your Google access key, which is a 24 character alphanumeric string. For aws_secret_access_key, provide your Google secret key, which is a 40 character Base-64 encoded string that is linked to a specific Google access key. For more information on these keys, see the Google Cloud Storage documentation on migrating keys.

Restore from Azure

• The time limit given to download a file from Azure is 30 minutes. This should be sufficient for most files at most download speeds. If that is not sufficient to download a file, you may get an error saying “truncated tar archive”, in which case you may want to check your connection and retry the operation.

Example

Restore from a Local Drive

The following example restores from the /var/lib/memsql/data/ directory.

RESTORE DATABASE memsql_demo FROM "./";

Restore from S3

The following example restores from an S3 bucket.

RESTORE DATABASE memsql_demo FROM S3 "backup_bucket/backups/6_1_2018" CONFIG '{"region":"us-east-1"}' CREDENTIALS '{"aws_access_key_id":"replace_with_your_access_key_id","aws_secret_access_key":"replace_with_your_secret_access_key"}';

The following example restores a database(memsql_demo) from an S3 bucket to a database with a different name(new_memsql_demo).

RESTORE DATABASE new_memsql_demo FROM S3 "backup_bucket/backups/6_1_2018/memsql_demo.backup" CONFIG '{"region":"us-east-1"}' CREDENTIALS '{"aws_access_key_id":"replace_with_your_access_key_id","aws_secret_access_key":"replace_with_your_secret_access_key"}';

Restore From Google Cloud Storage using the S3 Interface

The following example uses the S3 interface to restore from a Google Cloud Storage bucket.

RESTORE DATABASE memsql_demo FROM S3 "backup_bucket/backups/12_15_2019"
  CONFIG '{"compatibility_mode":true,"endpoint_url":"https://storage.googleapis.com"}'
  CREDENTIALS '{"aws_access_key_id":"replace_with_your_google_access_key","aws_secret_access_key":"replace_with_your_google_secret_key"}';

Restore from Azure

The following example restores from an Azure Container.

RESTORE DATABASE memsql_demo FROM AZURE "backup_container/backups/6_1_2018" CREDENTIALS '{"account_name":"your_account_name_here","account_key": "your_account_key_here"}';

The following example restores a database(memsql_demo) from an Azure container to a database with a different name(new_memsql_demo).

RESTORE DATABASE new_memsql_demo FROM AZURE "backup_container/backups/6_1_2018/memsql_demo.backup" CREDENTIALS '{"account_name":"your_account_name_here","account_key": "your_account_key_here"}';

Common Errors

A common error is giving incorrect paths to the backup files to restore command. The path that you give to the RESTORE command should match the path that you gave to the BACKUP command, unless you are restoring to a database with a different name in S3 or Azure restore, in which case, /old_database_name.backup should be appended to your restore path.

Was this Article Helpful?

Thanks For Your Feedback!