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. In case of a Business Continiuity restore of the Production environmnet to the new AWS account, ensure that your Bastion server has at least 100GB volume provisioned and the Session database instance has 100GB of storage allocated.
The nightly database backups for each environment are stored in an S3 bucket for each Govwifi environment called govwifi-
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
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 .
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:
cd /tmp
gpg --output govwifi-backup-databasename.sql.gz --decrypt govwifi-backup-databasenam.sql.gz.gpg
Unzip the file:
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