-
Notifications
You must be signed in to change notification settings - Fork 1
97 lines (71 loc) · 4.63 KB
/
sql-load.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
# This workflow will Load SQL scripts to DB
name: Load SQL scripts to DB
env:
# ==== Versions ====
AZ_CLI_VERSION: 2.45.0
# ==== General settings ====
APP_NAME: petcliaks
LOCATION: westeurope
RG_APP: rg-iac-aks-petclinic-mic-srv # RG where to deploy the other Azure services: AKS, ACR, MySQL, etc.
MYSQL_DB_NAME: petclinic
MYSQL_SERVER_NAME: petcliaks
MYSQL_ADM_USR: mys_adm
MYSQL_TIME_ZONE: Europe/Paris
MYSQL_CHARACTER_SET: utf8
MYSQL_COLLATION: utf8_general_ci
MYSQL_PORT: 3306
# ==== Secrets ====
MYSQL_ADM_PWD: ${{ secrets.SPRING_DATASOURCE_PASSWORD }}
credentials: ${{ secrets.AZURE_CREDENTIALS }}
on:
workflow_dispatch:
workflow_call:
jobs:
sql-load:
runs-on: ubuntu-latest
steps:
- name: Login with GHA Runner SP
uses: azure/login@v1 # fails https://github.com/marketplace/actions/azure-login
with:
creds: ${{ secrets.AZURE_CREDENTIALS }} # ${{ env.credentials }}
- name: Checkout
uses: actions/checkout@v3 # https://github.com/actions/checkout
- name: Configure DB
run: |
az version
az mysql flexible-server parameter set --name time_zone --value ${{ env.MYSQL_TIME_ZONE }} \
-s ${{ env.MYSQL_SERVER_NAME }} -g ${{ env.RG_APP }}
# https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
az mysql flexible-server parameter set --name character_set_server --value ${{ env.MYSQL_CHARACTER_SET }} \
-g ${{ env.RG_APP }} -s ${{ env.MYSQL_SERVER_NAME }}
LOCAL_IP=$(curl whatismyip.akamai.com)
az mysql flexible-server firewall-rule create -g ${{ env.RG_APP }} -n ${{ env.MYSQL_SERVER_NAME }} --rule-name gha \
--start-ip-address $LOCAL_IP --end-ip-address $LOCAL_IP
# https://github.com/actions/runner-images/blob/main/images/linux/Ubuntu2204-Readme.md
#sudo apt install mysql-client-core-8.0
# https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connection-using-parameters.html
echo Init Vets
mysql -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} --execute 'CREATE DATABASE IF NOT EXISTS ${{ env.MYSQL_DB_NAME }};'
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-vets-service/src/main/resources/db/mysql/schema.sql
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-vets-service/src/main/resources/db/mysql/data.sql
echo Init Customers
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-customers-service/src/main/resources/db/mysql/schema.sql
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-customers-service/src/main/resources/db/mysql/data.sql
echo Init visits
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-visits-service/src/main/resources/db/mysql/schema.sql
mysql ${{ env.MYSQL_DB_NAME }} -u ${{ env.MYSQL_ADM_USR }} --password=${{ env.MYSQL_ADM_PWD }} -h ${{ env.MYSQL_SERVER_NAME }}.mysql.database.azure.com -P ${{ env.MYSQL_PORT }} < spring-petclinic-visits-service/src/main/resources/db/mysql/data.sql
shell: bash
- name: Disable local IP access to the DB
if: ${{ always() }}
run: |
az mysql flexible-server firewall-rule delete -g ${{ env.RG_APP }} -n ${{ env.MYSQL_SERVER_NAME }} --rule-name gha --yes
shell: bash
# security hardening for self-hosted agents: https://github.com/marketplace/actions/azure-login
# https://docs.github.com/en/actions/security-guides/security-hardening-for-github-actions#hardening-for-self-hosted-runners
# if the runner is self-hosted which is not github provided it is recommended to manually logout at the end of the workflow as shown below.
- name: Azure Logout security hardening
run: |
az logout
az cache purge
az account clear
shell: bash