Cloud Scheduler で Cloud SQL データベースのエクスポートをスケジューリングする

このチュートリアルでは、Cloud SchedulerCloud Functions を使用して、Cloud SQL for MySQL データベースを Cloud Storage に自動的にエクスポートする方法について説明します。データベースを Cloud Storage にエクスポートすると、堅牢で多様な障害復旧計画を作成できます。たとえば、データベースを別のリージョンにエクスポートできます。また、他の Cloud SQL インスタンスや他の MySQL データベースにインポートすることもできます。

アーキテクチャ

このチュートリアルでは、次の Google Cloud コンポーネントを使用します。

Cloud Scheduler ジョブは、Cloud SQL インスタンス名、データベース、プロジェクト ID、バックアップを保存する Cloud Storage のローケーションに関する情報を Pub/Sub トピックに送信します。このイベントが発生すると、Cloud Function をトリガーしてペイロードを取得し、SQL Admin API を介して Cloud SQL でデータベースのエクスポートを開始します。データベースでエクスポートが生成され、Cloud Storage に保存されます。次の図は、このプロセスを示しています。

Cloud Scheduler から Pub/Sub へのワークフロー。これにより、エクスポートを開始する Cloud Function がトリガーされます。

目標

費用

このチュートリアルでは、課金対象である次の Google Cloud コンポーネントを使用します。

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを生成できます。新しい Google Cloud ユーザーは無料トライアルをご利用いただけます。

このチュートリアルを終了した後、作成したリソースを削除すると、それ以上の請求は発生しません。詳しくは、クリーンアップをご覧ください。

始める前に

  1. Cloud Console のプロジェクト セレクタページで、Cloud プロジェクトを選択または作成します。

    プロジェクト セレクタのページに移動

  2. Google Cloud プロジェクトに対して課金が有効になっていることを確認します。プロジェクトに対して課金が有効になっていることを確認する方法を学習する

  3. Cloud Console で、Cloud Shell をアクティブにします。

    Cloud Shell をアクティブにする

  4. Cloud SQL Admin、Cloud Functions、Cloud Scheduler、App Engine API を有効にします。

    API を有効にする

このチュートリアルでは、Cloud Shell からすべてのコマンドを実行します。

環境設定

まず、サンプルデータを含むリポジトリのクローンを作成します。次に、環境を構成し、このチュートリアルで必要な権限を含むカスタムロールを作成します。

  1. サンプルデータを含むリポジトリのクローンを作成します。

    git clone https://github.com/GoogleCloudPlatform/training-data-analyst.git
    

    training-data-analyst リポジトリのデータを使用して、モックレコードを含むデータベースを作成します。

  2. 次の環境変数を構成します。

    export PROJECT_ID=`gcloud config get-value project`
    export DEMO="sql-export-tutorial"
    export BUCKET_NAME=${USER}-mysql-$(date +%s)
    export SQL_INSTANCE="${DEMO}-sql"
    export GCF_NAME="${DEMO}-gcf"
    export PUBSUB_TOPIC="${DEMO}-topic"
    export SCHEDULER_JOB="${DEMO}-job"
    export SQL_ROLE="sqlExporter"
    export STORAGE_ROLE="simpleStorageRole"
    export REGION="us-west2"
    
  3. このチュートリアルで必要な権限のみを持つ 2 つのカスタムロールを作成します。

    gcloud iam roles create ${STORAGE_ROLE} --project ${PROJECT_ID} \
        --title "Simple Storage Role" \
        --description "Grant permissions to view and create objects in Cloud Storage" \
        --permissions "storage.objects.create,storage.objects.get"
    
    gcloud iam roles create ${SQL_ROLE} --project ${PROJECT_ID} \
        --title "SQL Exporter Role" \
        --description "Grant permissions to export data from a Cloud SQL instance to a Cloud Storage bucket as a SQL dump or CSV file" \
        --permissions "cloudsql.instances.export"
    

    これらのロールは、最小権限の原則に従い、Cloud Functions と Cloud SQL サービス アカウントのアクセス範囲を制限します。

Cloud Storage バケットと Cloud SQL インスタンスの作成

このセクションでは、まず Cloud Storage バケットと Cloud SQL MySQL インスタンスを作成します。次に、サンプル データベースを作成してサンプルデータを入力します。

Cloud Storage バケットを作成する

gsutil コマンドライン ツールを使用して Cloud Storage バケットを作成します。

  • エクスポートしたデータを保存する Cloud Storage バケットを作成します。

    gsutil mb -l ${REGION} gs://${BUCKET_NAME}
    

Cloud SQL インスタンスを作成してサービス アカウントに権限を付与する

次に、Cloud SQL インスタンスを作成し、そのサービス アカウントに権限を付与して Cloud Storage へのデータのエクスポートを許可します。

  1. Cloud SQL for MySQL 5.7 のインスタンスを作成します。

    gcloud sql instances create ${SQL_INSTANCE} --database-version MYSQL_5_7 --region ${REGION}
    

    この処理が完了するまでに数分かかります。

  2. Cloud SQL インスタンスが実行されていることを確認します。

    gcloud sql instances list --filter name=${SQL_INSTANCE}
    

    出力は次のようになります。

    NAME                     DATABASE_VERSION  LOCATION    TIER              PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
    sql-export-tutorial-sql  MYSQL_5_7         us-west2-b  db-n1-standard-1  34.94.173.98     -                RUNNABLE
    
  3. Simple Storage ロールを使用して、Cloud SQL サービス アカウントに Cloud Storage へのデータのエクスポートを許可します。

    export SQL_SA=(`gcloud sql instances describe ${SQL_INSTANCE} \
        --project ${PROJECT_ID} \
        --format "value(serviceAccountEmailAddress)"`)
    
    gsutil iam ch serviceAccount:${SQL_SA}:projects/${PROJECT_ID}/roles/${STORAGE_ROLE} gs://${BUCKET_NAME}
    

Cloud SQL インスタンスにサンプルデータを入力する

これで、ファイルをバケットにアップロードし、サンプル データベースを作成してデータを入力できるようになりました。

  1. クローンを作成したリポジトリに移動します。

    cd training-data-analyst/CPB100/lab3a/cloudsql
    
  2. ディレクトリ内のファイルを新しいバケットにアップロードします。

    gsutil cp * gs://${BUCKET_NAME}
    
  3. サンプル データベースを作成してデータを入力します。プロンプトが表示されたら、[yes] をクリックして続行します。

    gcloud sql import sql ${SQL_INSTANCE} gs://${BUCKET_NAME}/table_creation.sql --project ${PROJECT_ID}
    
    gcloud sql import csv ${SQL_INSTANCE} gs://${BUCKET_NAME}/accommodation.csv \
        --database recommendation_spark \
        --table Accommodation
    
    gcloud sql import csv ${SQL_INSTANCE} gs://${BUCKET_NAME}/rating.csv \
        --database recommendation_spark \
        --table Rating
    

Pub/Sub トピック、Cloud Function、Cloud Scheduler ジョブの作成

このセクションでは、カスタム サービス アカウントを作成してカスタム SQL ロールにバインドします。次に、Cloud Function の実行をトリガーする Pub/Sub トピックを作成します。また、データのエクスポートを定期的に行う Cloud Scheduler ジョブも作成します。

Cloud Function のサービス アカウントを作成する

まず、カスタム サービス アカウントを作成してカスタム SQL ロールにバインドします。

  1. Cloud Function で使用する IAM サービス アカウントを作成します。

    gcloud iam service-accounts create ${GCF_NAME} \
        --display-name "Service Account for GCF and SQL Admin API"
    
  2. Cloud Function サービス アカウントにカスタム SQL ロールの権限を付与します。

    gcloud projects add-iam-policy-binding ${PROJECT_ID} \
        --member="serviceAccount:${GCF_NAME}@${PROJECT_ID}.iam.gserviceaccount.com" \
        --role="projects/${PROJECT_ID}/roles/${SQL_ROLE}"
    

Pub/Sub トピックを作成する

次のステップでは、Cloud SQL データベースとやり取りする Cloud Function をトリガーする Pub/Sub トピックを作成します。

  • Pub/Sub トピックを作成します。

    gcloud pubsub topics create ${PUBSUB_TOPIC}
    

Cloud Function を作成する

次に、Cloud Function を作成します。

  1. Cloud Shell に次のコードを 貼り付けて main.py ファイルを作成します。

    cat <<EOF > main.py
    
    import base64
    import logging
    import json
    
    from datetime import datetime
    from httplib2 import Http
    
    from googleapiclient import discovery
    from googleapiclient.errors import HttpError
    from oauth2client.client import GoogleCredentials
    
    def main(event, context):
        pubsub_message = json.loads(base64.b64decode(event['data']).decode('utf-8'))
        credentials = GoogleCredentials.get_application_default()
    
        service = discovery.build('sqladmin', 'v1beta4', http=credentials.authorize(Http()), cache_discovery=False)
    
        datestamp = datetime.now().strftime("%Y%m%d%H%M") # format timestamp: YearMonthDayHourMinute
        uri = "{0}/backup-{1}-{2}.gz".format(pubsub_message['gs'], pubsub_message['db'], datestamp)
    
        instances_export_request_body = {
          "exportContext": {
            "kind": "sql#exportContext",
            "fileType": "SQL",
            "uri": uri,
            "databases": [
              pubsub_message['db']
            ]
          }
        }
    
        try:
          request = service.instances().export(
                project=pubsub_message['project'],
                instance=pubsub_message['instance'],
                body=instances_export_request_body
            )
          response = request.execute()
        except HttpError as err:
            logging.error("Could NOT run backup. Reason: {}".format(err))
        else:
          logging.info("Backup task status: {}".format(response))
    EOF
    
  2. Cloud Shell に次のコードを貼り付けて requirements.txt ファイルを作成します。

    cat <<EOF > requirements.txt
    google-api-python-client
    Oauth2client
    EOF
    
  3. コードをデプロイします。新しい関数の未認証呼び出しを許可するかどうかを確認するメッセージが表示されたら、no を選択します。

    gcloud functions deploy ${GCF_NAME} \
        --trigger-topic ${PUBSUB_TOPIC} \
        --runtime python37 \
        --entry-point main \
        --service-account ${GCF_NAME}@${PROJECT_ID}.iam.gserviceaccount.com
    

Cloud Scheduler ジョブを作成する

最後に、データのエクスポートを定期的に行う Cloud Scheduler ジョブを作成します。

  1. Cloud Scheduler ジョブの App Engine インスタンスを作成します。

    gcloud app create --region=${REGION}
    
  2. データのエクスポートを定期的に行う Cloud Scheduler ジョブを作成します。

    gcloud scheduler jobs create pubsub ${SCHEDULER_JOB} --schedule '0 23 * * *' --topic ${PUBSUB_TOPIC} --message-body '{"db":"recommendation_spark","instance":'\"${SQL_INSTANCE}\"',"project":'\"${PROJECT_ID}\"',"gs":'\"gs://${BUCKET_NAME}\"'}' --time-zone 'America/Los_Angeles'
    

    このジョブは毎日午後 11 時に実行されるようにスケジューリングされています。

ソリューションのテスト

最後に、ソリューションをテストします。まず、Cloud Scheduler ジョブを実行します。

  1. Cloud Scheduler ジョブを手動で実行して、データベースの MySQL ダンプをトリガーします。

    gcloud scheduler jobs run ${SCHEDULER_JOB}
    
  2. MySQL インスタンスで実行されたオペレーションを一覧表示し、EXPORT タイプのオペレーションがあることを確認します。

    gcloud sql operations list --instance ${SQL_INSTANCE} --limit 1
    

    出力には、完了したエクスポート ジョブが表示されます。次に例を示します。

    NAME                                  TYPE    START                          END                            ERROR  STATUS
    8b031f0b-9d66-47fc-ba21-67dc20193749  EXPORT  2020-02-06T21:55:22.240+00:00  2020-02-06T21:55:32.614+00:00  -      DONE
    
  3. Cloud Storage バケットで、データベースのダンプファイルが作成されているかどうかを確認します。

    gsutil ls gs://${BUCKET_NAME} | grep backup-recommendation_spark
    

    前のステップの STATUS オペレーションで DONE が返された後、backup-database_name-timestamp.gz という名前のファイルが表示されます。

クリーンアップ

このチュートリアルで使用したリソースについて、Google Cloud アカウントに課金されないようにするには、次の手順に従います。課金を停止する最も簡単な方法は、チュートリアル用に作成したプロジェクトを削除することです。

  1. Cloud Console で [リソースの管理] ページに移動します。

    [リソースの管理] ページに移動

  2. プロジェクト リストで、削除するプロジェクトを選択し、[削除] をクリックします。
  3. ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

次のステップ