Storage - files and databases
Our AWS environments have a file system, but this is transient and we can't rely on anything staying there. Also, we can't assume that the worker process is running on the same disk as a web process. We potentially only have the following places to store stuff:
- S3 buckets - the 'file system'
- Databases - Aurora (mysql) and DocumentDB (mongo)
In general we configure access to these with environment variables.
Local environment for developers and administrators
This is an aide-memoire for five of us who basically need access to everything.
You need two command line tools installed:
aws (be sure to run `aws configure` and give it the credentials from your IAM account online)
apppack
mysql (and mysqldump)
mongo (and mongodump, mongorestore)
To install the aws client, read this
You will then want to set these environment variables; for the values look in settings.py
AWS_ACCESS_KEY_ID
AWS_SECRET_ACCESS_KEY
DATABASE_URL
MONGODB_URL
You can obtain the values from our live environment with
apppack -a ot-main-prod config ls
Private S3 bucket, and copying database and mongo down
We have a private bucket called s3://private.opentrack.run and a public one s3://files.opentrack.run (which, for historic reasons, is available as https://file.opentrack.run)
As a convention, I've created a directory and two files which should always contain a recent copy of the databases. For now I manually copied them there with
aws s3 cp otr-mongo.tar.gz s3://privatebucket.opentrack.run/recent_live_data/
aws s3 cp otr.sql.gz s3://privatebucket.opentrack.run/recent_live_data/
Live to test nightly copy
We will make sure (somehow) that a scheduled command runs to rewrite these in the small hours. This means having an instance with both mysqldump and mongodump working, and access to the databases and s3
Copying files down for your development
To reproduce what our current update_dev_environment script does, you would run
aws s3 cp s3://privatebucket.opentrack.run/recent_live_data/*
...and you will have local copies to gunzip and restore into local databases.
Updating and dumping Amazon's Aurora databases
MySQL and Mongo client connections are not possible from outside the AWS cloud (or indeed our own virtual network within it).
Apppack provides commands for a an AWS user to dump and load the SQL data:
apppack -a ot-main-prod db shell # query, drop/create database
apppack -a ot-main-prod db dump # writes ot-main-prod.sql.qz
apppack -a ot-main-test db load ot-main-prod.sql.gz
Of course once it's the primary database, we should never load into the live database which will overwrite it.
One can also use it from within the AWS cloud by connecting to an instance.
This gets you into one of the running instances; omit --live and it takes longer as it spins up a new one. This instance can do python manage.py whatever, and will have any apt packages in the Aptfile at top of repo.
apppack -a ot-main-prod shell --live
This gets you in as root. You can then install stuff (aws cli, mongodump etc); we have a script
apppack -a ot-main-prod shell --live --root
$ bin/aws-setup-mongo
Once in and you have mysql you can get the password from the $DATABASE_URL (it's readable of form username:password@host/dbname)
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
RDSHOST="apppack-database-opentrack-cluster-1uy49ker94x74.cluster-c4z06eo8x7sa.eu-west-2.rds.amazonaws.com"
export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
mysql --host=$RDSHOST --port=3306 --ssl-ca=rds-combined-ca-bundle.pem --user=ot-main-prod --password=$PASSWORD
However, the db instances are set up so the ot-main-prod user can only access the ot-main-prod database.
admin access to the database
For a MySQL admin connection, you have to generate a one time token. This can be done from outside Amazon (assuming you are an aws cli user with permission) like this:
aws --region=eu-west-2 rds generate-db-auth-token --hostname apppack-database-opentrack-cluster-1uy49ker94x74.cluster-c4z06eo8x7sa.eu-west-2.rds.amazonaws.com --user root --port 3306
The token you get is hundreds of characters.
It could also be done within an amazon server once you have the aws command installed and have authorised. It's often convenient to stash it like this:
RDSHOST="apppack-database-opentrack-cluster-1uy49ker94x74.cluster-c4z06eo8x7sa.eu-west-2.rds.amazonaws.com"
TOKEN="$(aws --region=eu-west-2 rds generate-db-auth-token --hostname apppack-database-opentrack-cluster-1uy49ker94x74.cluster-c4z06eo8x7sa.eu-west-2.rds.amazonaws.com --user root --port 3306)"
LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
mysql --host=$RDSHOST --port=3306 --ssl-ca=rds-combined-ca-bundle.pem --user=root --password=$TOKEN
Once in, you see all databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| main |
| mysql |
| ot-main-prod |
| ot-main-test |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
Currently unsure whether we should do nightly dump-and-load as administrator, or use the above one-off grant to allow ot-main-prod permission to estore into ot-main-test
Dumping, loading and shell access to Mongo
Mongo is a little easier, as we manually set it up and there is one administrator account with equal access to the prod and test databases within it.
If transferring from 'real mongo' to DocumentDB, we need to untar the dump; edit some indices and remove full text search (grep _fts) and 2dsphere indexes, and reload it. One in AWS and copying prod to test, there ae no permissions obstacles. Mongodump and mongorestore can be installed with
apppack -a ot-main-prod shell --live --root
$bin/aws-setup-mongo # installs stuff we need
mongodump etc etc etc live
mongorestore etc etc test
Installing mongo and mysql
This worked on test server, not live:
mongodump --ssl --sslCAFile=rds-combined-ca-bundle.pem --uri=$MONGODB_URL
2021-05-07T11:31:28.538+0000 writing ot-main-test.competitor to
mongorestore --ssl --sslCAFile=rds-combined-ca-bundle.pem --uri=$MONGODB_URL dump