Migrer la base de données backend Looker vers MySQL

Par défaut, Looker utilise une base de données en mémoire HyperSQL pour stocker sa configuration, ses utilisateurs et d'autres données. Sur une instance très sollicitée, cette base de données peut atteindre plusieurs gigaoctets, ce qui peut entraîner des problèmes de performances, une saturation de la mémoire Java et des temps de démarrage longs.

Sur une instance hébergée par le client, nous vous recommandons de remplacer la base de données HyperSQL par un backend de base de données MySQL complet lorsque la base de données HyperSQL interne dépasse 600 Mo. Pour vérifier la taille de la base de données HyperSQL, consultez la taille du fichier looker.script :

cd looker
cd .db
ls -lah

Si la taille du fichier looker.script dépasse 600 Mo, suivez les procédures ci-dessous pour migrer vers une base de données MySQL externe.

Provisionner une instance et un utilisateur MySQL

Vous pouvez provisionner une instance MySQL 8.4.X (recommandé) ou MySQL 8.0.X à utiliser comme backend. Les versions de MySQL antérieures à la version 8.0 ne sont pas compatibles.

Looker 26.6 et versions ultérieures sont compatibles avec MySQL 8.4.X pour la base de données backend Looker. Pour les instances hébergées par le client qui utilisent MySQL 8.0.X pour la base de données backend Looker, nous vous recommandons de passer à MySQL 8.4.X dès que vous mettez à jour votre instance Looker vers la version 26.6 ou une version ultérieure.

Dans AWS RDS, une instance de classe db.m5.large est probablement suffisante en tant que backend pour une seule instance Looker. Même si l'utilisation réelle de la base de données se situe probablement entre 5 et 10 Go, il est judicieux de provisionner 100 à 150 Go d'espace de stockage SSD, car les IOPS provisionnées sont basées sur la quantité d'espace de stockage demandée.

MySQL 8.4.X

Looker prend en charge l'utilisation de MySQL 8.4.X comme base de données interne à partir de Looker 26.6. Pour MySQL 8.4.X, vous pouvez utiliser l'un des plug-ins d'authentification MySQL suivants, comme décrit dans les sections ci-dessous :

Utiliser caching_sha2_password

Dans MySQL 8.4.X, le plug-in d'authentification par défaut est caching_sha2_password. Looker propose deux façons d'utiliser le plug-in caching_sha2_password :

  1. Lorsque vous utilisez une connexion SSL pour la base de données interne : aucune clé publique RSA n'est requise. Looker se connecte de manière sécurisée à la base de données avec les identifiants utilisateur fournis. Consultez la section Créer un fichier d'identifiants de base de données sur cette page pour configurer une connexion SSL à votre base de données.
  2. Si vous ne vous connectez pas avec SSL activé : par défaut, Looker utilise l'option --get-server-public-key pour se connecter à la base de données interne lorsque le plug-in caching_sha2_password est utilisé par le compte utilisateur fourni à Looker. Assurez-vous que l'environnement réseau est sécurisé si SSL n'est pas activé.

Pour l'une ou l'autre des méthodes d'utilisation du plug-in caching_sha2_password, configurez l'utilisateur en exécutant l'instruction suivante :

CREATE USER 'DB_username' IDENTIFIED WITH caching_sha2_password BY 'password';

Remplacez les éléments suivants :

  • DB_username : nom d'utilisateur
  • password : mot de passe unique et sécurisé

Utiliser mysql_native_password

Looker fonctionne avec le plug-in mysql_native_password pour l'authentification aux bases de données MySQL via le pilote JDBC. Pour que MySQL 8.4.X fonctionne avec le plug-in mysql_native_password, vous devez suivre les étapes complémentaires suivantes :

  1. Configurez la base de données MySQL pour activer le plug-in mysql_native_password. Pour cela, plusieurs options sont possibles, selon la manière dont votre base de données MySQL est déployée et le type d'accès dont vous disposez pour la configuration :

    1. Démarrez le serveur MySQL avec --mysql-native-password=ON.
    2. Définissez la propriété dans le fichier de configuration my.cnf :

      [mysqld]
      mysql_native_password=ON
      
    3. Si l'instance MySQL est hébergée sur Google Cloud, AWS ou Azure, le plug-in mysql_native_password doit être activé automatiquement. Pour obtenir des instructions détaillées, consultez la documentation de votre fournisseur.

  2. Créez l'utilisateur :

    CREATE USER 'DB_username' IDENTIFIED WITH mysql_native_password BY 'password';
    

    Remplacez les éléments suivants :

    • DB_username : nom d'utilisateur
    • password : mot de passe unique et sécurisé

MySQL 8.0.X

Dans MySQL 8.0.X, le plug-in d'authentification par défaut est caching_sha2_password. Looker utilise le plug-in mysql_native_password pour l'authentification aux bases de données MySQL via le pilote JDBC. Pour que MySQL 8.0.X fonctionne correctement, vous devez suivre les étapes complémentaires suivantes :

  1. Configurez la base de données MySQL pour qu'elle utilise le plug-in mysql_native_password. Pour cela, plusieurs options sont possibles, selon la manière dont votre base de données MySQL 8 est déployée le type d'accès dont vous disposez pour la configuration :

    • Démarrez le processus avec l'option --default-auth=mysql_native_password.

    • Définissez la propriété dans le fichier de configuration my.cnf :

      [mysqld]
      default-authentication-plugin=mysql_native_password
      
    • Si votre instance de base de données est hébergée via AWS RDS, définissez le paramètre default_authentication_plugin dans un groupe de paramètres appliqué à cette instance de base de données.

  2. Créez l'utilisateur :

    CREATE USER 'DB_username' IDENTIFIED WITH mysql_native_password BY 'password';
    

    Remplacez les éléments suivants :

    • DB_username : nom d'utilisateur
    • password : mot de passe unique et sécurisé

Régler MySQL

Ajustez les paramètres suivants sur votre instance MySQL.

Augmenter la taille maximale des paquets

La taille max_allowed_packet par défaut de MySQL est trop petite pour la migration de base de données et peut entraîner l'échec de la migration avec une erreur PACKET_TOO_LARGE. Définissez max_allowed_packet sur la valeur maximale autorisée de 1073741824 :

max_allowed_packet = 1073741824

Définir l'algorithme de table temporaire

MySQL 8 gère les tables temporaires internes différemment des versions précédentes. Les paramètres par défaut peuvent entraîner des problèmes lors de l'exécution de certaines requêtes nécessaires au fonctionnement de Looker, en particulier pour les instances Looker comportant de nombreux utilisateurs et projets. La bonne pratique consiste à définir le paramètre de serveur global suivant :

internal_tmp_mem_storage_engine = MEMORY

Configurer les jeux de caractères

Définissez les paramètres par défaut suivants pour utiliser UTF8mb4, qui est compatible avec les jeux de caractères UTF8. Consultez l'article In MySQL, never use "utf8". Utilisez "utf8mb4". pour savoir pourquoi nous vous recommandons d'utiliser UTF8mb4 (et non UTF8) avec MySQL.

character_set_client = utf8mb4
character_set_results = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_server = utf8mb4
collation_connection = utf8mb4_general_ci
collation_server = utf8mb4_general_ci

Sur les instances Amazon RDS, vous appliquez ce paramètre en créant ou en modifiant un groupe de paramètres, puis en modifiant les paramètres appropriés. Nous vous recommandons de copier le groupe de paramètres actuel et d'apporter les modifications à la copie, en particulier si vous partagez des groupes de paramètres entre plusieurs instances RDS. Après avoir enregistré le groupe de paramètres, appliquez-le à l'instance RDS. Un redémarrage peut être nécessaire.

Définir votre schéma d'instance répliquée

Looker s'appuie sur des fonctionnalités qui nécessitent un binlog mixed ou row. Si vous hébergez votre propre instance MySQL, définissez votre binlog_format sur mixed ou row en exécutant l'une des commandes suivantes :

SET GLOBAL binlog_format = 'MIXED';

ou

SET GLOBAL binlog_format = 'ROW';

Créer une base de données et accorder des autorisations utilisateur

Créez une base de données sur l'instance de base de données :

create database DB_name default character set DB_charset default collate DB_collation;

Accordez ensuite des autorisations à l'utilisateur que vous avez créé lorsque vous avez provisionné l'instance et l'utilisateur MySQL :

grant all on DB_name.* to 'DB_username'@'%';
grant all on looker_tmp.* to 'DB_username'@'%';

Remplacez les éléments suivants :

  • DB_name : nom de la base de données
  • DB_charset : jeu de caractères correspondant aux paramètres de votre serveur MySQL. Utilisez utf8mb4 pour une compatibilité UTF-8 complète.
  • DB_collation : classement correspondant aux paramètres de votre serveur MySQL. Looker nécessite utf8mb4_general_ci.
  • DB_username : nom d'utilisateur

La base de données looker_tmp de la dernière ligne n'a pas besoin d'exister, mais l'instruction grant est nécessaire pour les rapports internes.

Créer un fichier d'identifiants de base de données

Looker doit savoir à quelle base de données MySQL s'adresser et quels identifiants utiliser. Dans le répertoire Looker, créez un fichier nommé looker-db.yml avec le contenu suivant, en remplaçant DB_hostname, DB_username, DB_password et DB_name par les valeurs de votre base de données :

dialect: mysql_8
host: DB_hostname
username: DB_username
password: DB_password
database: DB_name
port: 3306

Si votre base de données MySQL nécessite une connexion SSL, ajoutez la ligne suivante à looker-db.yml :

ssl: true

Si vous souhaitez également activer la validation du certificat SSL, ajoutez la ligne suivante à looker-db.yml :

verify_ssl: true

Vous pouvez également spécifier d'autres paramètres JDBC supplémentaires compatibles avec le pilote JDBC MariaDB en ajoutant jdbc_additional_params. Par exemple, si vous devez utiliser un fichier de magasin de confiance spécifique, vous pouvez ajouter le paramètre suivant à la chaîne de connexion JDBC MySQL :

jdbc_additional_params: trustStore=/path/to/my/truststore.jks&keyStore=/path/to/my/keystore.jks

Pour les installations hébergées par le client, vous pouvez éventuellement spécifier le nombre maximal de connexions que Looker peut établir avec votre base de données en ajoutant max_connections. Par exemple, pour limiter à 10 le nombre de connexions simultanées à votre base de données, ajoutez ce qui suit :

max_connections: 10

Dans le schéma de chiffrement de Looker, toutes les données sensibles de la base de données sont chiffrées au repos. Même si quelqu'un parvenait à accéder aux identifiants de la base de données en texte brut et à la base de données, Looker chiffre ou hache les données sensibles avant de les stocker. Cela s'applique aux éléments tels que les mots de passe, les identifiants de base de données d'analyse et le cache de requêtes. Toutefois, si vous ne souhaitez pas stocker le mot de passe en texte clair pour cette configuration dans le fichier looker-db.yml sur le disque, vous pouvez configurer la variable d'environnement LOOKER_DB pour qu'elle contienne une liste de clés et de valeurs pour chaque ligne du fichier looker-db.yml. Exemple :

export LOOKER_DB="dialect=mysql_8&host=localhost&username=root&password=&database=looker&port=3306"

Sauvegardez le répertoire .db.

Sauvegardez le répertoire .db, qui contient les fichiers nécessaires à la création de la base de données HyperSQL en mémoire, au cas où vous devriez restaurer HyperSQL :

cp -r .db .db-backup
tar -zcvf db-backup.tar.gz ./.db-backup

Migrer la base de données

La migration de la base de données vers MySQL peut prendre plusieurs heures sur une instance de taille moyenne ou grande, en particulier si la base de données HyperSQL fait 1 Go ou plus. Nous vous recommandons de mettre temporairement à niveau l'instance EC2 vers une instance m5.2xlarge (avec 32 Go de RAM pour permettre le tas de mémoire de 26 Go spécifié dans la procédure) pendant la migration, ce qui réduit le temps requis à environ 10 minutes.

  1. Sur l'hôte Looker :

    cd looker
    ./looker stop
    vi looker
    
  2. Dans le script de démarrage Looker, ajoutez une deuxième ligne au fichier :

    exit
    
  3. Arrêtez l'instance dans la console AWS. Une fois l'instance arrêtée, remplacez sa taille par m5.2xlarge. Ensuite, redémarrez l'instance.

  4. Connectez-vous en SSH à l'hôte en tant qu'utilisateur Looker. Assurez-vous d'abord que Java n'est pas en cours d'exécution, puis exécutez la commande suivante :

    cd looker
    java -Xms26000m -Xmx26000m -jar looker.jar migrate_internal_data  looker-db.yml
    

    Lors de l'exécution de l'étape migrate_internal_data, il est possible que libcrypt ne soit pas trouvé et qu'une trace de la pile s'affiche, en commençant par :

    NotImplementedError: getppid unsupported or native support failed to load
    ppid at org/jruby/RubyProcess.java:752
    ppid at org/jruby/RubyProcess.java:749
    

    Dans ce cas, définissez manuellement LD_LIBRARY_PATH avant d'exécuter la commande Java :

    export LD_LIBRARY_PATH=$HOME/looker/.tmp/:$LD_LIBRARY_PATH
    
  5. Une fois l'opération terminée, arrêtez l'instance depuis la console AWS.

  6. Vous pouvez maintenant restaurer l'instance à sa taille d'origine.

  7. Redémarrer l'instance.

Démarrer Looker

  1. Modifiez le script de démarrage Looker et supprimez la ligne exit que vous avez ajoutée précédemment.

  2. Assurez-vous qu'aucun argument n'est défini dans LOOKERARGS dans le script de démarrage. À la place, tous les arguments doivent être déplacés vers le fichier lookerstart.cfg afin qu'ils ne soient pas écrasés par les nouvelles versions du script de démarrage. Enregistrez et quittez le script de démarrage.

  3. Modifier lookerstart.cfg. Le résultat doit ressembler à ceci :

    LOOKERARGS="-d looker-db.yml"
    

    Si le script de démarrage de Looker contenait d'autres arguments, ajoutez-les au fichier lookerstart.cfg.

  4. Archivez le répertoire .db s'il ne l'est pas déjà.

    mv .db .db-backup
    tar -zcvf db-backup.tar.gz ./.db-backup
    rm -rf ./.db-backup/
    
  5. Démarrez Looker :

    ./looker start
    

Vérifier que Looker utilise la nouvelle base de données

Si Looker utilise correctement le backend MySQL, vous devriez voir des connexions réseau entre l'instance Looker et la nouvelle instance de base de données. Pour ce faire, exécutez la commande suivante sur l'instance Looker :

netstat -na | grep 3306

Vous devriez voir des connexions à l'instance de base de données. Voici un exemple de résultat, montrant une instance de base de données à l'adresse IP 10.0.3.155 :

looker@instance1:~$ netstat -na | grep 3306
tcp6       0      0 10.0.5.131:56583        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56506        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56582        10.0.3.155:3306         ESTABLISHED
tcp6       0      0 10.0.5.131:56508        10.0.3.155:3306         ESTABLISHED

Sauvegarder Looker

Une fois que vous aurez migré vers un backend MySQL, les sauvegardes S3 automatisées de Looker ne fonctionneront plus. Nous recommandons d'effectuer au moins une sauvegarde nocturne de la base de données MySQL, ainsi qu'une sauvegarde nocturne du système de fichiers du répertoire de travail Looker. Le répertoire looker/log/ peut être exclu des sauvegardes du système de fichiers. Pour en savoir plus, consultez la page de documentation Créer des sauvegardes.

Dépannage

Cette section explique comment résoudre les problèmes courants qui peuvent survenir lorsque vous utilisez une base de données backend MySQL avec Looker.

Dossier partagé vide ou erreurs Illegal mix of collations après une mise à jour

Après avoir mis à jour une instance Looker hébergée par le client qui utilise une base de données backend MySQL 8.0.x ou 8.4.x, le dossier Partagé peut apparaître vide dans l'UI de Looker. Il est également possible que des tableaux de bord et des présentations n'apparaissent pas dans les résultats de recherche, même si vous pouvez toujours les ouvrir à l'aide de leurs URL directes. Dans les journaux du backend ou les réponses réseau du navigateur, les requêtes d'API internes échouent avec une exception SQL HTTP 500 Internal Server Error et Illegal mix of collations.

Ce problème se produit lorsque le classement par défaut de la base de données backend MySQL (@@collation_database) est défini sur la valeur par défaut de MySQL 8 (utf8mb4_0900_ai_ci) au lieu de utf8mb4_general_ci. Sur chaque connexion MySQL, Looker définit la variable de session collation_connection sur @@collation_database. Même si collation_server est défini sur utf8mb4_general_ci et que les tables existantes utilisent utf8mb4_general_ci, laisser le classement par défaut de la base de données défini sur utf8mb4_0900_ai_ci amène MySQL à attribuer utf8mb4_0900_ai_ci aux nouvelles tables créées lors d'une mise à jour et aux tables temporaires générées en mémoire lors de l'affichage du contenu du dossier. MySQL rejette alors les opérations JOIN entre ces tables et les tables utf8mb4_general_ci existantes.

Pour résoudre ce problème, alignez les classements de la base de données et de la table sur utf8mb4_general_ci :

  1. Sauvegardez votre base de données backend MySQL avant de modifier les classements de la base de données ou des tables.
  2. Vérifiez que collation_connection et collation_server sont définis sur utf8mb4_general_ci dans votre configuration ou groupe de paramètres MySQL, comme décrit dans Configurer les jeux de caractères.
  3. Définissez le jeu de caractères et le classement par défaut sur la base de données backend Looker :

    ALTER DATABASE DB_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    

    Remplacez DB_name par le nom de votre base de données backend Looker.

  4. Recherchez les tables permanentes de la base de données backend Looker qui n'utilisent pas le classement utf8mb4_general_ci :

    SELECT TABLE_NAME, TABLE_COLLATION
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'DB_name'
      AND TABLE_TYPE = 'BASE TABLE'
      AND TABLE_COLLATION != 'utf8mb4_general_ci';
    
  5. Pour chaque table concernée renvoyée par la requête, convertissez-la pour utiliser utf8mb4 et utf8mb4_general_ci :

    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    

    Remplacez table_name par le nom de la table concernée.

  6. Redémarrez l'instance Looker ou chaque nœud d'un déploiement en cluster afin que les nouvelles connexions à la base de données et les tables temporaires héritent du classement utf8mb4_general_ci.