Planifier des exportations de base de données Cloud SQL à l'aide de Cloud Scheduler

Ce tutoriel explique comment utiliser Cloud Scheduler et Cloud Functions pour exporter automatiquement une base de données Cloud SQL pour MySQL vers Cloud Storage. L'exportation de bases de données sur Cloud Storage vous permet de créer un plan de reprise après sinistre fiable et diversifié. Par exemple, l'exportation peut s'effectuer vers une autre région et l'importation dans d'autres instances Cloud SQL ou d'autres bases de données MySQL.

Architecture

Ce tutoriel inclut les composants Google Cloud suivants :

• Tâches Cloud Scheduler : tâches permettant d'appeler les fonctions de démarrage de l'exportation de base de données selon le calendrier défini.
• Cloud Functions : fonctions permettant d'exporter les données depuis Cloud SQL vers Cloud Storage.
• Messages Pub/Sub : messages envoyés et reçus pour chaque événement d'exportation de données.
• Buckets Cloud Storage : bucket destiné au stockage des données exportées.
• Base de données Cloud SQL : base de données source à partir de laquelle exporter les données.

Une tâche Cloud Scheduler publie un message sur un sujet Pub/Sub fournissant des informations sur le nom de l'instance Cloud SQL, la base de données, l'ID du projet et l'emplacement Cloud Storage où stocker la sauvegarde. Cet événement déclenche une fonction Cloud qui obtient cette charge utile et lance une exportation de base de données sur Cloud SQL via l'API SQL Admin. La base de données génère ensuite l'exportation et l'enregistre dans Cloud Storage. Le schéma suivant illustre ce processus.

Objectifs

• Créer un bucket Cloud Storage et une instance Cloud SQL
• Créer un sujet Pub/Sub, une fonction Cloud et une tâche Cloud Scheduler
• Déclencher l'exportation des données d'une base de données en exécutant la tâche Cloud Scheduler manuellement

Coûts

Ce tutoriel utilise les composants facturables suivants de Google Cloud :

• Cloud Scheduler
• Cloud Functions
• Pub/Sub
• Cloud Storage
• Cloud SQL

Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût. Les nouveaux utilisateurs de Google Cloud peuvent bénéficier d'un essai gratuit.

Une fois que vous avez terminé ce tutoriel, vous pouvez éviter de continuer à payer des frais en supprimant les ressources que vous avez créées. Consultez la page Effectuer un nettoyage pour en savoir plus.

Avant de commencer

1. Dans Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Cloud.

   Remarque : Si vous ne comptez pas conserver les ressources créées dans cette procédure, créez un projet au lieu de sélectionner un projet existant. Après avoir suivi ces étapes, vous pouvez supprimer le projet. Cela entraîne la suppression de toutes les ressources qui lui sont associées.

   Accéder à la page de sélection du projet

2. Vérifiez que la facturation est activée pour votre projet Google Cloud. Découvrez comment vérifier que la facturation est activée pour votre projet.

3. Dans Cloud Console, activez Cloud Shell.

   Activer Cloud Shell

4. Activez Administrateur Cloud SQL, Cloud Functions, Cloud Scheduler et API App Engine.

   Activer l'API

Dans ce tutoriel, vous allez exécuter toutes les commandes à partir de Cloud Shell.

Configurer votre environnement

Pour commencer, vous devez d'abord cloner le dépôt contenant les exemples de données. Vous devez ensuite configurer votre environnement et créer des rôles personnalisés disposant des autorisations nécessaires pour ce tutoriel.

1. Clonez le dépôt contenant les exemples de données :

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

   Vous devez créer une base de données avec des enregistrements fictifs à l'aide des données du dépôt training-data-analyst.

2. Configurez les variables d'environnement suivantes :

   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. Créez deux rôles personnalisés qui ne disposent que des autorisations nécessaires pour ce tutoriel :

   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"
   

   Remarque : Vous pouvez ignorer le message d'avertissement concernant l'API non activée.

   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"
   

   Ces rôles réduisent le champ d'application des accès aux comptes de service Cloud Functions et Cloud SQL, conformément au principe du moindre privilège.

Créer un bucket Cloud Storage et une instance Cloud SQL

Dans cette section, vous allez d'abord créer un bucket Cloud Storage et une instance MySQL Cloud SQL. Vous allez ensuite créer un exemple de base de données et le remplir avec des exemples de données.

Créer un bucket Cloud Storage

Vous créez un bucket Cloud Storage à l'aide de l'outil de ligne de commande gsutil.

• Créez le bucket Cloud Storage dans lequel vous souhaitez enregistrer les exportations de données :

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

Créer une instance Cloud SQL et octroyer des autorisations à son compte de service

À présent, vous devez créer une instance Cloud SQL et accorder à son compte de service les autorisations nécessaires pour exporter des données vers Cloud Storage.

1. Créez une instance Cloud SQL pour MySQL 5.7 :

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

   Cette opération prend quelques minutes.

2. Vérifiez que l'instance Cloud SQL est en cours d'exécution :

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

   La sortie ressemble à ceci :

   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. Accordez à votre compte de service Cloud SQL les autorisations nécessaires pour exporter des données vers Cloud Storage avec le rôle Simple 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}
   

Remplir une instance Cloud SQL avec des exemples de données

Vous pouvez à présent importer des fichiers dans votre bucket, puis créer votre exemple de base de données et le remplir.

1. Accédez au dépôt que vous avez cloné :

   cd training-data-analyst/CPB100/lab3a/cloudsql
   

2. Importez les fichiers du répertoire dans votre nouveau bucket :

   gsutil cp * gs://${BUCKET_NAME}
   

3. Créez et remplissez l'exemple de base de données lorsque vous y êtes invité, puis cliquez sur yes pour continuer :

   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
   

Créer un sujet Pub/Sub, une fonction Cloud et une tâche Cloud Scheduler

Dans cette section, vous allez créer un compte de service personnalisé et le lier au rôle SQL personnalisé que vous créez. Vous allez ensuite créer un sujet Pub/Sub permettant de déclencher l'exécution d'une fonction Cloud. Vous allez également créer une tâche Cloud Scheduler pour exécuter régulièrement la fonction d'exportation de données.

Créer un compte de service pour la fonction Cloud

La première étape consiste à créer un compte de service personnalisé et à le lier au rôle SQL personnalisé que vous créez.

1. Créez un compte de service IAM à utiliser par la fonction Cloud :

   gcloud iam service-accounts create ${GCF_NAME} \
       --display-name "Service Account for GCF and SQL Admin API"
   

2. Accordez au compte de service Cloud Functions l'accès au rôle SQL personnalisé :

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

Créer un sujet Pub/Sub

L'étape suivante consiste à créer un sujet Pub/Sub permettant de déclencher la fonction Cloud qui interagit avec la base de données Cloud SQL.

• Créez le sujet Pub/Sub :

  gcloud pubsub topics create ${PUBSUB_TOPIC}
  

Créer une fonction Cloud

Créez à présent la fonction Cloud.

1. Créez un fichier main.py en collant le contenu suivant dans Cloud Shell :

   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. Créez un fichier requirements.txt en collant le contenu suivant dans Cloud Shell :

   cat <<EOF > requirements.txt
   google-api-python-client
   Oauth2client
   EOF
   

3. Déployez le code. Lorsque vous êtes invité à autoriser les appels non authentifiés de la nouvelle fonction, répondez no.

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

Créer une tâche Cloud Scheduler

Enfin, vous allez créer une tâche Cloud Scheduler pour exécuter régulièrement la fonction d'exportation de données.

1. Créez une instance App Engine pour la tâche Cloud Scheduler :

   gcloud app create --region=${REGION}
   

2. Créez une tâche Cloud Scheduler permettant d'exécuter régulièrement la fonction d'exportation de données :

   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'
   

   Cette tâche est programmée pour s'exécuter tous les jours à 23 heures.

Tester votre solution

La dernière étape consiste à tester votre solution. Commencez par exécuter la tâche Cloud Scheduler.

1. Exécutez la tâche Cloud Scheduler manuellement afin de déclencher un vidage MySQL de votre base de données.

   gcloud scheduler jobs run ${SCHEDULER_JOB}
   

2. Répertoriez les opérations effectuées sur l'instance MySQL et vérifiez s'il existe une opération de type EXPORT :

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

   Le résultat affiche une tâche d'exportation terminée, par exemple :

   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. Vérifiez le bucket Cloud Storage pour voir si le fichier de vidage de la base de données a bien été créé :

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

   Un fichier nommé backup-database_name-timestamp.gz s'affiche après l'opération STATUS de l'étape précédente qui renvoie le message DONE.

Nettoyer

Afin d'éviter que des frais ne soient facturés sur votre compte Google Cloud pour les ressources utilisées dans ce tutoriel, supprimez le projet que vous venez de créer. Il s'agit du moyen le plus simple d'empêcher la facturation.

Attention : La suppression d'un projet aura les effets suivants :
• Tout le contenu du projet est supprimé. Si vous avez utilisé un projet existant pour ce tutoriel et que vous le supprimez, vous supprimerez également tout autre travail effectué dans le projet.
• Les ID de projets personnalisés sont perdus. Lorsque vous avez créé ce projet, vous avez peut-être créé un ID de projet personnalisé que vous souhaitez utiliser à l'avenir. Pour conserver les URL qui utilisent l'ID de projet, telle qu'une URL appspot.com, supprimez les ressources sélectionnées dans le projet au lieu de supprimer l'ensemble du projet.
1. Dans Cloud Console, accédez à la page Gérer les ressources.

   Accéder à la page Gérer les ressources

2. Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer .
3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

Étapes suivantes

• Apprenez à planifier des instances de calcul avec Cloud Scheduler.
• Découvrez la migration de MySQL vers Cloud SQL pour MySQL.
• Testez d'autres fonctionnalités de Google Cloud. Découvrez nos tutoriels.