Restoring Databases

Note: gds-cli maybe installed and accessible via either gds-cli or gds depending on your installation. gds is also an alias belonging to github cli tools.

The nightly database backups for each environment are stored an S3 bucket for each Govwifi environment called govwifi--london-mysql-backup-data. IT also keep an additional copy of these backups. In the event that we loose access to our AWS accounts, we can request a copy directly from IT. Databases can be restored from the nightly backups by following the instructions below:

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/<s3_encryption_file_name>

Locate the correct database file, e.g. staging backup files: gds-cli 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: 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 .

Decrypt with gpg: gpg --output govwifi-backup-databasename.sql.gz --decrypt govwifi-backup-databasenam.sql.gz.gpg

Then upload to the file to the staging bastion server in the eu-west-2 region, e.g.:

scp govwifi-databasename-datetime.sql.gz bastion.staging.govwifi:/tmp

Login to the bastion server and unzip the file: cd /tmp gzip -d govwifi-backup-admin-databasename.sql.gz

Import into mysql (the database credentials are located in AWS secrets manager) mysql -u <username> -h <hostname> -D <databasename> -p < govwifi-backup-databasename.sql

This page was last reviewed on 7 September 2022. It needs to be reviewed again on 7 March 2023 by the page owner #govwifi .

This page was set to be reviewed before 7 March 2023 by the page owner #govwifi. This might mean the content is out of date.