Restore Sanitized DB #131
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 |