Restore Sanitized DB #143
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Restore Sanitized DB | |
| concurrency: stage | |
| on: | |
| workflow_dispatch: | |
| workflow_call: | |
| schedule: | |
| - cron: '50 1 * * MON' # At 01:50 UTC every Monday | |
| jobs: | |
| sanitize-db: | |
| runs-on: ubuntu-latest | |
| environment: ProductionRO | |
| env: | |
| MYSQL_ROOT_PASSWORD: root | |
| services: | |
| mysql: | |
| image: mysql:5.7 | |
| env: | |
| MYSQL_ROOT_PASSWORD: root | |
| ports: | |
| - "3306:3306" | |
| options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=10s --health-retries=10 | |
| steps: | |
| - name: Checkout | |
| uses: actions/checkout@v3 | |
| - name: Download DB backup | |
| id: get-db | |
| uses: ./.github/actions/get-db | |
| with: | |
| workflow: backup-db.yml | |
| zip-password: ${{ secrets.BACKUP_PASSWORD }} | |
| - name: Load DB | |
| run: cat ${{ steps.get-db.outputs.db-dump-file }} | /usr/bin/mysql -h 127.0.0.1 -u root -p$MYSQL_ROOT_PASSWORD | |
| - uses: actions/checkout@v3 | |
| - name: Remove PII | |
| run: | | |
| # execute SQL script with PII removal | |
| cat $GITHUB_WORKSPACE/mysql/sanitize-db.sql | \ | |
| /usr/bin/mysql -h 127.0.0.1 -u root -p$MYSQL_ROOT_PASSWORD --database orbitar_db | |
| - name: Dump database | |
| run: | | |
| mysqldump --host 127.0.0.1 --column-statistics=0 --default-character-set=utf8mb4 --single-transaction \ | |
| --add-drop-database --databases orbitar_db activity_db -u root -p$MYSQL_ROOT_PASSWORD > stage-backup.sql | |
| - name: Compress database | |
| env: | |
| STAGING_BACKUP_PASSWORD: ${{ secrets.STAGING_BACKUP_PASSWORD }} | |
| run: | | |
| pwd | |
| ls -la | |
| zip -m -P $STAGING_BACKUP_PASSWORD stage-backup.zip stage-backup.sql | |
| - name: Upload artifact | |
| uses: actions/upload-artifact@v3 | |
| with: | |
| name: stage-backup | |
| path: stage-backup.zip | |
| if-no-files-found: error | |
| restore-db: | |
| needs: sanitize-db | |
| environment: Staging | |
| runs-on: [self-hosted, stage] | |
| steps: | |
| - uses: actions/checkout@v3 | |
| - name: Download DB backup | |
| uses: ./.github/actions/get-db | |
| id: get-db | |
| with: | |
| zip-password: ${{ secrets.STAGING_BACKUP_PASSWORD }} | |
| - name: Stop backend | |
| working-directory: /orbitar | |
| run: docker compose -f docker-compose.ssl.local.yml stop backend | |
| - name: Restore backup | |
| working-directory: /orbitar | |
| env: | |
| MYSQL_ROOT_PASSWORD: ${{ secrets.MYSQL_ROOT_PASSWORD }} | |
| run: | | |
| docker compose -f docker-compose.ssl.local.yml exec -T mysql /usr/bin/mysql \ | |
| -u root --password=$MYSQL_ROOT_PASSWORD --default-character-set=utf8mb4 \ | |
| < ${{ steps.get-db.outputs.db-dump-file }} | |
| - name: Flush Redis cache | |
| working-directory: /orbitar | |
| env: | |
| REDIS_PASSWORD: orbitar | |
| run: docker compose -f docker-compose.ssl.local.yml exec -e REDISCLI_AUTH=$REDIS_PASSWORD redis redis-cli FLUSHALL | |
| - name: Restart backend | |
| working-directory: /orbitar | |
| run: docker compose -f docker-compose.ssl.local.yml start backend |