Skip to main content

Databases Backup And Restore

Backup and Restore a SingleStore database to Amazon S3​

SingleStore allows to backup a database to multiple destinations, including Amazon S3. Those backups can later be restored in a simple and quick operation.

  1. Setup

To be able to run the BACK and RESTORE commands, the user needs to be granted the OUTBOUND permisson.

-- First, you may need to check which user your are connected with
SELECT USER(), CURRENT_USER();
-- Grant the OUTBOUND permission to the user,
-- you may also specify a particular database
GRANT OUTBOUND ON *.* TO 'user'@'%';
  1. Backup and Restore
-- Run the BACKUP command specifying the source database, storage destination, backup name and storage credentials
BACKUP DATABASE farfalla_staging_20220717 TO S3 "s3://singlestore-backup/staging-20240627" CONFIG '{"region":"us-east-1"}' CREDENTIALS '{"aws_access_key_id": "in_1pass", "aws_secret_access_key": "in_1pass"}';


-- Run the RESTORE command specifying the new database name, storage source, backup name and storage credentials.
-- We've seen a slight performance increase when specifying ASYNC REPLICATION
RESTORE DATABASE farfalla_staging_gateways_refactor_1 FROM S3 "s3://singlestore-backup/staging-20240627/farfalla_staging_20220717.backup" 
CONFIG '{"region":"us-east-1"}' 
CREDENTIALS '{"aws_access_key_id": "in_1pass", "aws_secret_access_key": "in_1pass"}'
WITH ASYNC REPLICATION;
info

We have a user singlestore-backup with access rights to the bucket singlestore-backup for this backups. The keys are available in 1password.

  1. Grant access, optional
-- You may need to GRANT permissions on the newly restored database, you can do so by...
-- 1. Check the GRANTS for a user that has the intended access on the source database:
SHOW GRANTS FOR 'farfalla_staging'@'%';
-- 2. GRANT the user the same permissions to the newly restored database:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `farfalla_staging_gateways_refactor_1`.* TO 'farfalla_staging'@'%';

Related:

X

Graph View