backupDB.yml

name: Backup db

on:
  schedule:
    - cron:  '0 0 * * *'
jobs:
  backup-db:
    name: backup-db
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3

    - name: Install venv
      uses: actions/setup-python@v2
      with:
        python-version: '3.9.12'

    - name: Setup aws-cli
      uses: chrislennon/action-aws-cli@v1.1
      env:
        ACTIONS_ALLOW_UNSECURE_COMMANDS: true

    - name: Install mysqldump
      run: |
        sudo apt-get update
        sudo apt-get -y install mysql-client

    - name: Dump DB
      run: |
        tables=( 'comics' 'episodes' 'images' )
        echo 'USE `comicdb`;' > prod_db_build.sql
        for table in "${tables[@]}"
        do
          mysqldump ${{ secrets.DB_NAME }} $table \
            -h${{ secrets.DB_HOST }} \
            -u${{ secrets.DB_USER }} \
            -p${{ secrets.DB_PASS }} \
            --skip-column-statistics \
            >> prod_db_build.sql 2>/dev/null
        done
        sed -i '/^--/d; /^\/\*/d;' prod_db_build.sql

    - name: Upload prod_db_build.sql
      run: |
        aws s3 cp prod_db_build.sql s3://comiccrawler-data/v4/prod_db_build.sql
      env:
        AWS_ACCESS_KEY_ID:     ${{ secrets.AWS_ACCESS_KEY_ID }}
        AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}