Cloner une base de données MySQL sur Compute Engine

Configurer l'environnement

Pour suivre ce tutoriel, vous devez configurer votre environnement informatique avec les éléments suivants :

• Une instance MySQL sur Compute Engine (nommée mysql-prod) pour représenter votre serveur de base de données de production.
• Un disque supplémentaire (nommé mysql-prod-data) associé à votre serveur de production pour stocker votre base de données de production.
• Une copie de la base de données Employees importée dans mysql-prod pour simuler la base de données de production que vous souhaitez cloner.
• Une instance MySQL sur Compute Engine (nommée mysql-test) pour représenter votre serveur de base de données de test. Vous clonez votre base de données sur ce serveur.

Cette architecture est décrite dans le schéma suivant.

Créer l'instance de VM de production

Pour simuler un environnement de production, vous devez configurer une instance de VM Compute Engine exécutant MySQL sur Debian Linux.

L'instance de VM de ce tutoriel utilise deux disques : un disque de 50 Go pour le système d'exploitation et les comptes utilisateur, et un disque de 100 Go pour le stockage de la base de données.

Dans Compute Engine, l'utilisation de disques distincts n'offre aucun avantage en termes de performances. Les performances des disques sont déterminées par la capacité de stockage totale de tous les disques installés sur une instance et par le nombre total de processeurs virtuels sur votre instance de VM. Par conséquent, la base de données et le fichier journal peuvent résider sur le même disque.

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Définissez la zone de votre choix :

   ZONE=us-east1-b
   REGION=us-east1
   gcloud config set compute/zone "${ZONE}"
   

3. Créez une instance Compute Engine :

   gcloud compute instances create mysql-prod \
       --machine-type=n1-standard-2 \
       --scopes=cloud-platform \
       --boot-disk-size=50GB \
       --boot-disk-device-name=mysql-prod \
       --create-disk="mode=rw,size=100,type=pd-standard,name=mysql-prod-data,device-name=mysql-prod-data"
   

   Cette commande accorde à l'instance un accès complet aux API Google Cloud, crée un disque secondaire de 100 Go et installe le disque sur l'instance. Ignorez l'avertissement sur les performances du disque, car vous n'avez pas besoin de performances élevées pour ce tutoriel.

Configurer le disque supplémentaire

Le deuxième disque installé sur l'instance de production sert à stocker votre base de données de production. Ce disque est vide. Vous devez donc le partitionner, le formater et l'installer.

1. Dans Google Cloud Console, accédez à la page Instances de VM.

   Accéder à la page "Instances de VM"

2. Assurez-vous qu'une coche verte check s'affiche à côté du nom de votre instance mysql-prod, indiquant que l'instance est prête.

3. Cliquez sur le bouton SSH à côté de l'instance mysql-prod. Le navigateur ouvre une connexion de terminal vers l'instance.

4. Dans la fenêtre du terminal, affichez la liste des disques installés sur votre instance :

   lsblk
   

   Le résultat est le suivant :

   NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
   sda      8:0    0   50G  0 disk
   └─sda1   8:1    0   50G  0 part /
   sdb      8:16   0  100G  0 disk
   

   Le disque nommé sdb (100 Go) est votre disque de données.

5. Formatez le disque sdb et créez une partition unique avec un système de fichiers ext4 :

   sudo mkfs.ext4 -m 0 -F -E lazy_itable_init=0,lazy_journal_init=0,discard \
       /dev/sdb
   

6. Créez le répertoire de données MySQL servant de point d'installation au disque de données :

   sudo mkdir -p /var/lib/mysql
   

7. Pour installer automatiquement le disque au point d'installation que vous avez créé, ajoutez une entrée dans le fichier /etc/fstab :

   echo "UUID=`sudo blkid -s UUID -o value /dev/sdb` /var/lib/mysql ext4 discard,defaults,nofail 0 2" \
      | sudo tee -a /etc/fstab
   

8. Installez le disque :

   sudo mount -av
   

9. Supprimez tous les fichiers du disque de données afin que MySQL puisse l'utiliser en tant que répertoire de données :

   sudo rm -rf /var/lib/mysql/*
   

Installer le serveur MySQL

Vous devez télécharger et installer MySQL Community Edition. Le répertoire de données MySQL est créé sur le disque supplémentaire.

1. Dans la session SSH connectée à l'instance mysql-prod, téléchargez et installez le package de configuration MySQL :

   wget http://repo.mysql.com/mysql-apt-config_0.8.13-1_all.deb
   sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
   

2. Lorsque vous y êtes invité, sélectionnez l'option Serveur et cluster MySQL, puis sélectionnez mysql-5.7.

3. Dans la liste, sélectionnez l'option OK pour terminer la configuration du package.

4. Actualisez le cache du dépôt et installez les packages mysql-community :

   sudo apt-get update
   sudo apt-get install -y mysql-community-server mysql-community-client
   

5. Lorsque vous êtes averti que le répertoire de données existe déjà, sélectionnez OK.

6. Lorsque vous êtes invité à fournir un mot de passe racine, créez et saisissez un mot de passe. Notez le mot de passe ou stockez-le temporairement dans un endroit sûr.

Télécharger et installer l'exemple de base de données

1. Dans la session SSH connectée à l'instance mysql-prod, installez git :

   sudo apt-get install -y git
   

2. Clonez le dépôt GitHub contenant les scripts de la base de données Employees :

   git clone https://github.com/datacharmer/test_db.git
   

3. Remplacez le répertoire par celui du script de la base de données Employees :

   cd test_db
   

4. Exécutez le script de création de la base de données Employees :

   mysql -u root -p -q < employees.sql
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine que vous avez créé précédemment.

5. Pour vérifier que l'exemple de base de données fonctionne, vous pouvez exécuter une requête qui compte le nombre de lignes dans la table employees :

   mysql -u root -p -e "select count(*) from employees.employees;"
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine que vous avez créé précédemment.

   Le résultat est le suivant :

   +----------+
   | count(*) |
   +----------+
   |   300024 |
   +----------+
   

Créer l'instance de VM de test

Dans cette section, vous allez créer une instance de VM MySQL nommée mysql-test comme destination de la base de données clonée. La configuration de cette instance est identique à celle de l'instance de production. Cependant, vous n'allez pas créer de deuxième disque de données. À la place, vous allez installer le disque de données comme décrit plus loin dans ce tutoriel.

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Créez l'instance MySQL de test :

   gcloud compute instances create mysql-test \
     --machine-type=n1-standard-2 \
     --scopes=cloud-platform \
     --boot-disk-size=50GB \
     --boot-disk-device-name=mysql-test
   

   Vous pouvez ignorer l'avertissement sur les performances du disque, car vous n'avez pas besoin de performances élevées pour ce tutoriel.

Installer le serveur MySQL sur l'instance de VM de test

Vous devez également télécharger et installer MySQL Community Edition sur l'instance de VM mysql-test.

1. Dans la session SSH connectée à l'instance mysql-test, téléchargez et installez le package de configuration MySQL :

   wget http://repo.mysql.com/mysql-apt-config_0.8.13-1_all.deb
   sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
   

2. Lorsque vous y êtes invité, sélectionnez l'option Serveur et cluster MySQL, puis sélectionnez mysql-5.7.

3. Dans la liste, sélectionnez l'option OK pour terminer la configuration du package.

4. Actualisez le cache du dépôt et installez les packages mysql-community :

   sudo apt-get update
   sudo apt-get install -y mysql-community-server mysql-community-client
   

5. Lorsque vous êtes invité à fournir un mot de passe racine, créez et saisissez un mot de passe. Notez le mot de passe ou stockez-le temporairement dans un endroit sûr.

Cloner la base de données à l'aide d'instantanés de disque Compute Engine

L'une des méthodes pour cloner une base de données MySQL exécutée sur Compute Engine consiste à stocker la base de données sur un disque de données distinct et à utiliser des instantanés de disque persistant pour créer un clone de ce disque.

Les instantanés de disque persistant vous permettent d'obtenir une copie des données sur disque à un moment précis. La programmation des instantanés de disque permet de sauvegarder automatiquement vos données.

Dans cette section du tutoriel, vous allez :

• réaliser un instantané du disque de données du serveur de production ;
• créer un disque à partir de l'instantané ;
• Installer le nouveau disque sur le serveur de test
• Redémarrer le serveur MySQL sur l'instance de test de sorte que le serveur utilise le nouveau disque comme disque de données

Le schéma suivant montre comment une base de données est clonée à l'aide d'instantanés de disque.

Créer l'instantané de disque

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Créez un instantané de votre disque de données dans la même zone que l'instance de VM :

   gcloud compute disks snapshot mysql-prod-data \
        --snapshot-names=mysql-prod-data-snapshot \
        --zone="${ZONE}"
   

   Au bout de quelques minutes, votre instantané est créé.

Installer l'instantané de disque sur l'instance de test

Vous devez créer un disque de données à partir de l'instantané que vous avez créé, puis l'installer sur l'instance mysql-test.

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Créez un disque persistant en utilisant le contenu de l'instantané du disque de production :

   gcloud beta compute disks create mysql-test-data \
        --size=100GB \
        --source-snapshot=mysql-prod-data-snapshot \
        --zone="${ZONE}"
   

3. Installez le nouveau disque sur votre instance mysql-test avec des autorisations de lecture/écriture :

   gcloud compute instances attach-disk mysql-test \
       --disk=mysql-test-data --mode=rw
   

Installer le nouveau disque de données sous Linux

Pour utiliser le disque de données cloné en tant que répertoire de données MySQL, vous devez arrêter l'instance MySQL et installer le disque.

1. Dans la session SSH connectée à mysql-test, arrêtez le service MySQL :

   sudo service mysql stop
   

2. Dans la fenêtre du terminal, affichez la liste des disques associés à votre instance :

   lsblk
   

   Le résultat est le suivant :

   NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
   sda      8:0    0   50G  0 disk
   └─sda1   8:1    0   50G  0 part /
   sdb      8:16   0  100G  0 disk
   

   Le disque nommé sdb (100 Go) est votre disque de données.

3. Installez le disque de données MySQL dans le répertoire de données MySQL :

   sudo mount -o discard,defaults /dev/sdb /var/lib/mysql
   

   L'installation de ce disque permet de masquer les fichiers de configuration et les espaces de table MySQL en les remplaçant par le contenu du disque.

   Avec cette commande, le disque est temporairement installé et n'est pas réinstallé au démarrage du système. Si vous souhaitez installer le disque au démarrage du système, créez une entrée fstab. Pour en savoir plus, consultez la section Configurer le disque supplémentaire plus haut dans ce tutoriel.

Démarrer MySQL dans l'instance de test

1. Dans la session SSH connectée à mysql-test, démarrez le service MySQL :

   sudo service mysql start
   

2. Pour vérifier que la base de données clonée fonctionne, exécutez une requête pour compter le nombre de lignes dans la table employees :

   mysql -u root -p -e "select count(*) from employees.employees;"
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de la base de données mysql-prod. Le mot de passe racine de l'instance de production est obligatoire, car l'intégralité du répertoire de données MySQL est un clone du répertoire de données de l'instance mysql-prod. Par conséquent, toutes les bases de données, ainsi que tous les utilisateurs de la base de données et leurs mots de passe sont copiés.

   +----------+
   | count(*) |
   +----------+
   |   300024 |
   +----------+
   

   Le nombre de lignes est identique à celui de l'instance mysql-prod.

Maintenant que vous savez comment cloner une base de données à l'aide d'instantanés de disque persistant, vous pouvez essayer de cloner une base de données en utilisant l'exportation et l'importation. Pour suivre le tutoriel de cette seconde approche, vous devez désinstaller le disque cloné.

Désinstaller le disque cloné

Pour désinstaller le disque cloné que vous avez créé à l'aide d'instantanés de disque, procédez comme suit :

1. Dans la session SSH connectée à votre instance mysql-test, arrêtez le service MySQL :

   sudo service mysql stop
   

2. Désinstallez le disque de données cloné à partir du répertoire de données MySQL :

   sudo umount /var/lib/mysql
   

3. Redémarrez le service MySQL :

   sudo service mysql start
   

Cloner la base de données à l'aide de l'exportation et de l'importation

Une deuxième méthode pour cloner une base de données MySQL exécutée sur Compute Engine consiste à utiliser l'exportation (à l'aide de mysqldump) et l'importation natives MySQL. Avec cette approche, vous transférez le fichier d'exportation à l'aide de Cloud Storage.

Cette section du tutoriel utilise les ressources que vous avez créées dans la section Cloner la base de données à l'aide d'instantanés de disque Compute Engine. Si vous n'avez pas terminé cette section, vous devez le faire avant de continuer.

Dans cette section du tutoriel, vous allez :

• créer un bucket Cloud Storage ;
• exporter la base de données sur l'instance de production en l'écrivant dans Cloud Storage ;
• importer le fichier d'exportation dans l'instance de test en le lisant à partir de Cloud Storage.

Le schéma suivant illustre comment cloner une base de données en transférant un fichier d'exportation à l'aide de Cloud Storage.

Les systèmes en dehors de Google Cloud peuvent accéder à Cloud Storage. Vous pouvez donc utiliser cette méthode pour cloner des bases de données à partir d'instances MySQL externes.

Créer un bucket Cloud Storage

Vous devez créer un bucket Cloud Storage qui stocke les fichiers d'exportation pendant que vous les transférez de l'instance mysql-prod vers l'instance mysql-test.

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Créez un bucket Cloud Storage dans la même région que vos instances de VM :

   gsutil mb -l "${REGION}" "gs://$(gcloud config get-value project)-bucket"
   

Exporter la base de données

Dans votre environnement de production, il est possible que vous puissiez déjà effectuer des sauvegardes à l'aide de fichiers d'exportation mysqldump. Vous pouvez utiliser ces sauvegardes comme base pour cloner votre base de données.

Dans ce tutoriel, vous allez créer un fichier d'exportation en utilisant mysqldump, ce qui n'a aucune incidence sur les programmations de sauvegarde complète ou incrémentielle existantes.

• Dans la session SSH connectée à l'instance mysql-prod, exportez la base de données Employees en la transférant dans un objet Cloud Storage vers le bucket que vous avez créé précédemment :

  mysqldump --user=root -p --default-character-set=utf8mb4 --add-drop-database --verbose  --hex_blob \
      --databases employees |\
       gsutil cp - "gs://$(gcloud config get-value project)-bucket/employees-dump.sql"
  

  Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-prod.

  Vous utilisez le jeu de caractères utf8mb4 lors de l'exportation pour éviter tout problème d'encodage de caractères.

  L'option --add-drop-database permet d'inclure les instructions DROP DATABASE et CREATE DATABASE dans l'exportation.

Importer le fichier exporté

1. Dans la session SSH connectée à l'instance mysql-test, transférez le fichier exporté de votre bucket Cloud Storage vers l'application de ligne de commande mysql :

   gsutil cat "gs://$(gcloud config get-value project)-bucket/employees-dump.sql" |\
       mysql --user=root -p --default-character-set=utf8mb4
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-test.

   Vous utilisez le jeu de caractères utf8mb4 lors de l'importation pour éviter tout problème d'encodage de caractères.

2. Pour vérifier que la base de données clonée fonctionne, exécutez une requête pour compter le nombre de lignes dans la table employees :

   mysql -u root -p -e "select count(*) from employees.employees;"
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-test.

   +----------+
   | count(*) |
   +----------+
   |   300024 |
   +----------+
   

   Le nombre de lignes est identique à celui de l'instance mysql-prod.

Utiliser Cloud SQL comme destination de clonage

Si votre base de données de destination est hébergée sur Cloud SQL et que la base de données d'origine se trouve sur Compute Engine, le seul mécanisme compatible pour le clonage consiste à exporter la base de données vers Cloud Storage, puis à l'importer dans Cloud SQL.

Comme expliqué dans la documentation de Cloud SQL, Cloud SQL ne peut importer le fichier exporté que s'il ne contient aucun déclencheur, ni aucune procédure stockée, vue ou fonction.

Si votre base de données repose sur l'un de ces éléments, vous devez les exclure de l'exportation à l'aide des arguments de ligne de commande --skip-triggers et --ignore-table [VIEW_NAME], puis les recréer manuellement après l'importation.

Créer une instance Cloud SQL pour MySQL

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Créez une instance Cloud SQL pour MySQL exécutant la même version de base de données que votre instance mysql-prod :

   gcloud sql instances create mysql-cloudsql \
       --tier=db-n1-standard-2 --region=${REGION} --database-version MYSQL_5_7
   

   Après quelques minutes, votre base de données Cloud SQL est créée.

3. Réinitialisez le mot de passe utilisateur racine en saisissant une valeur connue :

   gcloud sql users set-password root \
       --host=% --instance=mysql-cloudsql  --prompt-for-password
   

   Lorsque vous êtes invité à fournir un mot de passe racine, créez et saisissez un mot de passe. Notez le mot de passe ou stockez-le temporairement dans un endroit sûr.

Exporter la base de données

Pour exporter la base de données dans un format adapté à l'importation dans Cloud SQL, vous devez exclure toutes les vues de la base de données.

1. Dans la session SSH connectée à l'instance mysql-prod, définissez une variable d'environnement contenant un ensemble d'arguments de ligne de commande pour la commande mysqldump afin qu'elle ignore les vues de la base de données Employees :

   DATABASE_NAME=employees
   IGNORE_TABLES_ARGS="`mysql -u root -p -s -s -e \"
       SELECT CONCAT('--ignore-table ${DATABASE_NAME}.',TABLE_NAME)
       FROM information_schema.TABLES
       WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA = '${DATABASE_NAME}';
       \"`"
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-prod.

2. Affichez le contenu des variables pour vérifier qu'elles ont été définies correctement :

   echo "${IGNORE_TABLES_ARGS}"
   

   --ignore-table employees.current_dept_emp
   --ignore-table employees.dept_emp_latest_date
   

3. Exportez la base de données Employees, à l'exclusion des déclencheurs et des vues, en la transférant directement dans un objet Cloud Storage du bucket que vous avez créé précédemment :

   mysqldump --user=root -p --default-character-set=utf8mb4 --add-drop-database --verbose \
       --hex-blob --skip-triggers --set-gtid-purged=OFF \
       $IGNORE_TABLES_ARGS \
       --databases employees |\
       gsutil cp - "gs://$(gcloud config get-value project)-bucket/employees-cloudsql-import.sql"
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-prod.

Mettre à jour les autorisations des objets

Vous devez définir les autorisations appropriées à la fois sur le bucket Cloud Storage et sur l'objet d'exportation pour que le compte de service Cloud SQL puisse les lire. Ces autorisations sont définies automatiquement lorsque vous utilisez la console Google Cloud pour importer l'objet. Vous pouvez également les définir à l'aide des commandes gcloud.

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Définissez une variable d'environnement contenant l'adresse du compte de service de votre instance Cloud SQL :

   CLOUDSQL_SA="$(gcloud sql instances describe mysql-cloudsql --format='get(serviceAccountEmailAddress)')"
   

3. Ajoutez le compte de service à la liste de contrôle d'accès du bucket avec l'autorisation "writer" (rédacteur) et à l'objet d'exportation avec l'autorisation "reader" (lecteur) :

   gsutil acl ch -u "${CLOUDSQL_SA}":W "gs://$(gcloud config get-value project)-bucket/"
   gsutil acl ch -u "${CLOUDSQL_SA}":R \
       "gs://$(gcloud config get-value project)-bucket/employees-cloudsql-import.sql"
   

Importer la base de données exportée

1. Ouvrez Cloud Shell.

   Ouvrir Cloud Shell

2. Importez le fichier exporté dans votre instance Cloud SQL :

   gcloud sql import sql mysql-cloudsql \
       "gs://$(gcloud config get-value project)-bucket/employees-cloudsql-import.sql"
   

   Lorsque vous y êtes invité, saisissez y.

3. Pour vérifier que la base de données clonée fonctionne, exécutez une requête pour compter le nombre de lignes dans la table employees :

   echo "select count(*) from employees.employees;" |\
       gcloud sql connect mysql-cloudsql --user=root
   

   Lorsque vous y êtes invité, saisissez le mot de passe racine du serveur de base de données mysql-cloudsql.

   Le résultat est le suivant :

   Connecting to database with SQL user [root].Enter password:
   count(*)
   300024
   

   Le nombre de lignes est identique à celui de l'instance mysql-prod.

Informations supplémentaires pour les systèmes de production

Utiliser des instantanés de disque

Pour les sauvegardes physiques, telles que les instantanés de disque, la documentation MySQL recommande de suspendre les écritures dans la base de données avant de prendre un instantané. Pour ce faire, exécutez la commande FLUSH TABLES WITH READ LOCK. Une fois l'instantané terminé, vous pouvez utiliser UNLOCK TABLES pour redémarrer les écritures.

Pour les bases de données qui utilisent des tables InnoDB, nous vous recommandons de prendre directement l'instantané sans exécuter la commande FLUSH TABLES WITH READ LOCK. Cela permet à la base de données de continuer à fonctionner correctement. Cependant, l'état de l'instantané peut être incohérent. Si cela se produit, le moteur InnoDB peut recréer les tables dans un état cohérent au démarrage du clone.

Pour les bases de données utilisant des tables MyISAM, l'exécution de la commande FLUSH TABLES WITH READ LOCK bloque toutes les écritures dans les tables. La base de données est donc en mode lecture seule jusqu'à ce que la commande UNLOCK TABLES soit exécutée.

Si vous prenez un instantané sans d'abord vider et verrouiller les tables, la base de données nouvellement clonée risque de contenir des données incohérentes ou d'être corrompue.

Par conséquent, pour obtenir un instantané cohérent sur les bases de données utilisant des tables MyISAM, nous vous recommandons d'exécuter la commande FLUSH TABLES WITH READ LOCK sur une instance dupliquée avec accès en lecture et de capturer un instantané de cette instance, afin que les performances de la base de données principale (maîtresse) ne soient pas affectées.

Utiliser la commande mysqldump

Pour créer un fichier d'exportation cohérent avec la base de données source, la commande mysqldump verrouille toutes les tables au cours de l'opération d'exportation. Cela signifie que les écritures dans la base de données sont bloquées pendant son exportation.

Par conséquent, nous vous recommandons d'exécuter la commande mysqldump sur une instance dupliquée avec accès en lecture afin que l'instance de la base de données principale ne soit pas bloquée.