Restoring Databases

In case of a Business Continuity restore of the Production environment to a new AWS account, follow the instructions below to restore the databases from the nightly backups stored in S3. The nightly database backups for each environment are stored in an S3 bucket for each GovWifi environment called govwifi-<subdomain-name/environment-name>-london-mysql-backup-data.

The CO/GDS IT Infrastructure team also keeps an additional copy of these backups. In the event that we loose access to our AWS accounts, we can request a copy directly from the IT team. Databases can be restored from the nightly backups by following the instructions below. (Note: gds-cli may be aliased to gds.)

Session database

Do not restore the session database. The session database is only kept for analytical purposes and for admins to view logs. A restore of the session database takes over an hour and would delay our recovery time. If you do wish restore the Session database for analysis purposes, ensure that your Bastion server has at least 100GB volume provisioned and the Session database instance has 100GB of storage allocated.

Admin database and OTP secrets

For new or different environments, the follow is necessary:

When restoring the admin database, the OTP Secret Encryption Key for that environment must also be restored so that it matches the state of the restored database.

If the OTP secret cannot be restored:

Generate a new OTP secret, by following the instructions
Reset MFA for all admin users, so they can re-enroll, by using the following the instructions Two-factor authentication (2FA).

If you do not do this, no admin users will be able to log in after the restore.

If you are restoring the admin database for an existing environment, the OTP secret should already be in place. You do not need to take any additional steps.

Retrieve GPG passphrase

The S3 backup files are encrypted with GPG.
(Locate the gpg passphrase you need in the govwifi-build repo (for example the passphrase for staging is located here).
Retrieve the secret using the following command:

PASSWORD_STORE_DIR=~/path_to_govwifi-build-repo-on-your-machine/passwords pass edit keys/govwifi-database-<environment-name>-s3-encryption-key

For example:

PASSWORD_STORE_DIR=~/path_to_govwifi-build-repo-on-your-machine/passwords pass edit keys/govwifi-database-development-s3-encryption-key

Instructions

Locate the correct database file, e.g. staging backup files:
Example using gds cli:

gds-cli aws govwifi-staging -- aws s3 ls govwifi-staging-london -mysql-backup-data

Example using cod cli

cod aws govwifi-staging -- aws s3 ls govwifi-staging-london-mysql-backup-data

Download the database backup file that you need, e.g. staging admin DB:
Example using gds cli

gds-cli aws govwifi-staging -- aws s3 cp s3://govwifi-staging-london-mysql-backup-data/govwifi-backup-admin-2023-01-25-00-30.sql.gz.gpg .

Example using cod cli

cod aws govwifi-staging -- aws s3 cp s3://govwifi-staging-london-mysql-backup-data/govwifi-backup-admin-2023-01-25-00-30.sql.gz.gpg .

Then upload the file to the staging bastion server in the eu-west-2 region e.g.:
```
scp govwifi-databasename-datetime.sql.gz.gpg bastion.staging.govwifi:/tmp
```
Login to the bastion server and decrypt the gpg file:
```
ssh bastion.staging.govwifi
```
It will ask for a passphrase, which is the one you retrieved from the password store in the first step.
Move to the working directory:
```
cd /tmp
```

Decrypt the backup file:

gpg --output govwifi-backup-databasename.sql.gz --decrypt govwifi-backup-databasename.sql.gz.gpg

Unzip the SQL file:

gzip -d govwifi-backup-admin-databasename.sql.gz

This produces:

govwifi-backup-admin-databasename.sql

Import into mysql (the database credentials are located in AWS secrets manager):

mysql -u <username> -h <hostname> -D <databasename> -p < govwifi-backup-databasename.sql

To verify the restore was successful, you can run a query to check the number of rows in a table, for example:
```
mysql -u <username> -h <hostname> -D <databasename> -p
```
Then run the following query:
```
SHOW TABLES;
```
Then select a table and run the following query to check the number of rows:
```
SELECT COUNT(*) FROM <tablename>;
```

This page was last reviewed on 24 February 2026. It needs to be reviewed again on 24 November 2026 by the page owner #govwifi .