vmangos Development DB Dump #2274
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
| # this runner include the check and dump, only runs for push action | |
| name: vmangos Development DB Dump | |
| on: | |
| push: | |
| branches: | |
| - development | |
| paths: | |
| - '**.sql' | |
| - '.github/workflows/db_dump.yml' | |
| schedule: | |
| - cron: '0 1,13 * * *' | |
| # runs at minutes 0 of 1 and 13th hour of everyday | |
| # This is what will cancel the previous workflow | |
| concurrency: | |
| group: ${{ github.workflow }}-${{ github.ref }} | |
| cancel-in-progress: true | |
| env: | |
| REPO_DIR : ${{github.workspace}} | |
| WORLD_DB : "world_full_14_june_2021" | |
| REALMLIST : "INSERT INTO realmd.realmlist (name, realmbuilds) VALUES (\'Lightbringer\', \'5875 6005 6141 \');" | |
| jobs: | |
| build: | |
| runs-on: ubuntu-latest # 18.04 has mysql5.7 and latest(20.04) has mysql8 | |
| # ... some other config ... | |
| steps: | |
| - name: Shutdown Ubuntu MySQL (SUDO) | |
| run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it | |
| - name: Start docker mysql | |
| run: | | |
| sudo docker pull mysql | |
| sudo docker run --name mysqldb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.6 --max_allowed_packet=128M | |
| sudo docker start mysqldb | |
| until nc -z localhost 3306 ; do sleep 5 ; echo "Waiting for database availability..." ; done | |
| - name: Checkout update sql | |
| uses: actions/checkout@v4 | |
| - name: Checkout base sql | |
| run: | | |
| cd ${{github.workspace}} | |
| git clone https://github.com/brotalnia/database | |
| pwd | |
| 7z e -y ${{github.workspace}}/database/${{env.WORLD_DB}}.7z | |
| ls | |
| - name: Create tables | |
| run: | | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| - name: Import data | |
| run: | | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/logon.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/logs.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/${{env.WORLD_DB}}.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/characters.sql | |
| docker exec mysqldb sh -c $'exec mysql -u root -proot -e "INSERT INTO realmd.realmlist (name, realmbuilds) VALUES (\' Lightbringer \', \' 5875 6005 6141 \');"' | |
| - name: Apply Migration | |
| run: | | |
| cd ${{github.workspace}}/sql/migrations/ && chmod +x merge.sh && ./merge.sh | |
| [ -f "${{github.workspace}}/sql/migrations/world_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos' < ${{github.workspace}}/sql/migrations/world_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/characters_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters' < ${{github.workspace}}/sql/migrations/characters_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/logon_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd' < ${{github.workspace}}/sql/migrations/logon_db_updates.sql | |
| [ -f "${{github.workspace}}/sql/migrations/logs_db_updates.sql" ] && docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs' < ${{github.workspace}}/sql/migrations/logs_db_updates.sql | |
| - name: Export Dump | |
| run: | | |
| mkdir dbexport | |
| cd ${{github.workspace}}/dbexport | |
| mkdir db_dump | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot mangos' > ${{github.workspace}}/dbexport/db_dump/mangos.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot realmd' > ${{github.workspace}}/dbexport/db_dump/logon.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot logs' > ${{github.workspace}}/dbexport/db_dump/logs.sql | |
| docker exec mysqldb sh -c 'exec mysqldump -u root -proot characters' > ${{github.workspace}}/dbexport/db_dump/characters.sql | |
| # cd db_dump | |
| # mkdir update_check_only_do_not_import | |
| # cp ${{github.workspace}}/sql/migrations/world_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/world_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/logon_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logon_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/logs_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/logs_db_updates.sql | |
| # cp ${{github.workspace}}/sql/migrations/characters_db_updates.sql ${{github.workspace}}/dbexport/db_dump/update_check_only_do_not_import/characters_db_updates.sql | |
| - name: Install SQLite dependencies | |
| run: | | |
| sudo apt-get -qq update | |
| sudo apt-get -qq install mawk sqlite3 | |
| git clone https://github.com/dumblob/mysql2sqlite | |
| chmod +x ${{github.workspace}}/mysql2sqlite/mysql2sqlite | |
| - name: Create SQLite databases | |
| run: | | |
| mkdir ${{github.workspace}}/dbexport/sqlite_dump || true | |
| ${{github.workspace}}/mysql2sqlite/mysql2sqlite ${{github.workspace}}/dbexport/db_dump/mangos.sql | sqlite3 ${{github.workspace}}/dbexport/sqlite_dump/mangos.sqlite | |
| ${{github.workspace}}/mysql2sqlite/mysql2sqlite ${{github.workspace}}/dbexport/db_dump/logon.sql | sqlite3 ${{github.workspace}}/dbexport/sqlite_dump/logon.sqlite | |
| ${{github.workspace}}/mysql2sqlite/mysql2sqlite ${{github.workspace}}/dbexport/db_dump/logs.sql | sqlite3 ${{github.workspace}}/dbexport/sqlite_dump/logs.sqlite | |
| ${{github.workspace}}/mysql2sqlite/mysql2sqlite ${{github.workspace}}/dbexport/db_dump/characters.sql | sqlite3 ${{github.workspace}}/dbexport/sqlite_dump/characters.sqlite | |
| - name: Create New tables | |
| run: | | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS realmd2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS characters2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS mangos2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| docker exec mysqldb sh -c 'exec mysql -u root -proot -e "CREATE DATABASE IF NOT EXISTS logs2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"' | |
| - name: Import data back in to verify the dump data | |
| run: | | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot realmd2' < ${{github.workspace}}/dbexport/db_dump/logon.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot logs2' < ${{github.workspace}}/dbexport/db_dump/logs.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot mangos2' < ${{github.workspace}}/dbexport/db_dump/mangos.sql | |
| docker exec -i mysqldb sh -c 'exec mysql -u root -proot characters2' < ${{github.workspace}}/dbexport/db_dump/characters.sql | |
| - name: Set sha short outputs | |
| id: vars | |
| run: echo "sha_short=$(git rev-parse --short HEAD)" >> $GITHUB_OUTPUT | |
| - name: Archive files | |
| run: | | |
| cd ${{github.workspace}}/dbexport | |
| 7z a -tzip db-${{steps.vars.outputs.sha_short}}.zip db_dump | |
| 7z a -tzip db-sqlite-${{steps.vars.outputs.sha_short}}.zip sqlite_dump | |
| - name: Archive SQL artifact | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: snapshot-db-dump | |
| path: "${{github.workspace}}/dbexport/db-${{steps.vars.outputs.sha_short}}.zip" | |
| - name: Archive SQLite artifact | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: snapshot-db-sqlite-dump | |
| path: "${{github.workspace}}/dbexport/db-sqlite-${{steps.vars.outputs.sha_short}}.zip" | |
| - name: Download artifact snapshot-db-dump | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: snapshot-db-dump | |
| path: all_snapshots | |
| - name: Download artifact snapshot-db-sqlite-dump | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: snapshot-db-sqlite-dump | |
| path: all_snapshots | |
| - name: Get current date | |
| id: date | |
| uses: Kaven-Universe/github-action-current-date-time@v1 | |
| with: | |
| format: "YYYY-MM-DD" | |
| - name: Upload snapshot | |
| uses: "crowbarmaster/GH-Automatic-Releases@latest" | |
| with: | |
| repo_token: "${{ secrets.GITHUB_TOKEN }}" | |
| automatic_release_tag: "db_latest" | |
| prerelease: true | |
| title: "Development DB Dump(${{ steps.date.outputs.time }})" | |
| files: all_snapshots |