How to restore a database backup?

Owned by Steffen Drya
Last updated: Oct 24, 2022 by Martin Reiss (Unlicensed)
3 min read

If you like to restore data from an older backup (snapshot) in case of, for example:

  • data loss

  • data transfer from one database to another without influencing the former by this operation,

you can start the restore process on your own.

Process and Cost

A restore is made from a snapshot created in advance and creates a new database instance or a new database cluster separate from the origin database!

The restore is not done onto your existing database instances. This means:

  • actively used data is not overwritten

  • data have to be copied from one database to another manually to replace as necessary

  • additional cost will arise for the time the restore database instances exists based on their size (which by default equals the size of the origin database)

You are able to log into your projects environment, e.g. prod and initiate a restore to the same environment prod by default. By using the optional --environment argument you are also allowed to initiate the restore database to be placed in another environment of the same project, e.g. test. However, you are not able to intiate a restore process for a database which is not part of your current project and environment. In other words you are able to push databases into other environments, but not to pull from them. The later is prohibited for security reasons (privilege escalation).

Backup Snapshot ‘Safety copy’

When a database restore is initiated a new snapshot is created and listed as "safety copy". This is done to retain the snapshot data from which the restore database was created. Otherwise it would be deleted according to the project specific backup retention.

Imagine you have ordered a database restore of a snapshot, which had been deleted afterwards, because it's retention has ended. There was also a problem for whatever reason and the restored data is not usable anymore. You can reproduce the data anyway, using the identifier of the safety copy.

Preconditions

General usage instructions

The general usage instructions for the cloud management tool suite r3 can be found here.

Step 1 - Show help context

Usage of r3 db restore

~$ r3 db restore -h Create a Restore Database Server from a Snapshot.   optional arguments:   -h, --help            show this help message and exit   --env ENVIRONMENT, --environment ENVIRONMENT                         The short name of the desired Environment (default:                         this).   --loglevel {INFO,ERROR}                         Define Loglevel for Execution (default: INFO)     snapshot_id           SnapshotIdentifier of a Snapshot to restore a Database                         Server from.   --db-instance-class INSTANCE_CLASS                         DB instance class of restore database

Step 2 - Restore a database from a snapshot

  1. Obtain the identifier of the snapshot, whose data you want to restore using "r3 db list".

  2. Initiate the restore process and wait until it finishes: r3 db restore <snapshot-id>

  3. Retrieve the database endpoint provided at the end of the process

    database restore example

    ~$ r3 db restore restore-snapshot-2017-10-23-10-31   # Response 2017-10-23 12:45:37,051 - r3-osumi - INFO - Starting DB Restore: This process can take up to 15 Minutes! 2017-10-23 12:45:37,051 - r3-osumi - INFO - Starting restore for Snapshot restore-snapshot-2017-10-23-10-31 2017-10-23 12:45:37,261 - r3-osumi - INFO - Got details on restore-snapshot-2017-10-23-10-31 2017-10-23 12:45:37,261 - r3-osumi - INFO - Got VPCID vpc-75e3561c for Target Env {'Environment': 'test', 'Company': 'root360', 'Project': 'backend'} 2017-10-23 12:45:37,390 - r3-osumi - INFO - Got Param Group ralf-osumi-test-rds-r3rdsparamgrouputf8-5j7zdzwn3ypf 2017-10-23 12:45:37,881 - r3-osumi - INFO - Got Security Groups ['sg-2146d148'] 2017-10-23 12:45:37,917 - r3-osumi - INFO - Got DBSubnetGroup root360-backend-test-vpc-rdsdbsubnetgroup-1kztrdy6yo728 2017-10-23 12:45:37,917 - r3-osumi - INFO - Waiting for restore-snapshot-2017-10-23-10-31 to be available 2017-10-23 12:45:37,917 - r3-osumi - INFO - Creating Safety copy restore-snapshot-2017-10-23-12-45 of restore-snapshot-2017-10-23-10-31 2017-10-23 12:45:38,122 - r3-osumi - INFO - Waiting for Snapshot Copy restore-snapshot-2017-10-23-12-45 2017-10-23 12:46:55,542 - r3-osumi - INFO - Started Restore for restore-db-2017-10-23-12-45. Waiting on return signal... 2017-10-23 12:54:27,451 - r3-osumi - INFO - Modify after Restore for restore-db-2017-10-23-12-45 2017-10-23 12:54:27,824 - r3-osumi - INFO - Wating to perform Restart after modify. 2017-10-23 12:54:57,983 - r3-osumi - INFO - Updating status for restore-db-2017-10-23-12-45 2017-10-23 12:55:28,173 - r3-osumi - INFO - Updating status for restore-db-2017-10-23-12-45 2017-10-23 12:55:58,355 - r3-osumi - INFO - Updating status for restore-db-2017-10-23-12-45 2017-10-23 12:56:28,546 - r3-osumi - INFO - Updating status for restore-db-2017-10-23-12-45 2017-10-23 12:56:28,626 - r3-osumi - INFO - Initiated reboot of Instance.   Environment: test Company: root360 Project: backend +-------------+-------------------------------------------------------------------------+ | Resource    | Identifier                                                              | +-------------+-------------------------------------------------------------------------+ | Endpoint    | restore-db-2017-10-23-12-45.cw8zdc1gbk6g.eu-central-1.rds.amazonaws.com | | Safety Copy | restore-snapshot-2017-10-23-12-45                                       | +-------------+-------------------------------------------------------------------------+

  4. Using the provided endpoint you can now proceed, e.g. connect to the database from the Jump-Server or any application instance.

  5. To transfer a database schema or table from the restore database instance to your permanent database instance use mysqldump/mysql. Some common issues:

    • mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces: use --no-tablespaces option of mysqldump

    • ERROR 1227 (42000) at line 7692: Access denied; you need (at least one of) the SUPER privilege(s) for this operation.: see

Database User and Password

On database restore, database users and passwords are inherited from the origin database, so you can connect seamlessly with your known credentials.

 

Related tutorials

Related components

 

 

root360 Knowledge Base - This portal is hosted by Atlassian (atlassian.com | Privacy Policy)