💾 Automatiser le « backup » d'une base de donnée MySQL avec NestJS et TypeORM

Parmi les différentes causes à l'origine d'une journée cauchemardesque 😫 dans la vie d'un(e) développeu.r.se, on retrouve le célèbre « drop » inopiné (à l'effet tant redouté) d'une base de données en environnement de production qui permet à celles et ceux qui la subissent de voir passer le temps plus lentement qu'il ne l'a jamais été auparavant.

🗑️ En bref, la base de données s'est vidée de son contenu spontanément en raison d'un incident technique.

Les applications/serveurs sollicitant l'utilisation de votre base de données sont dès lors impacté(e)s de manière critique ‼️. Celles-ci sont momentanément interrompues et votre environnement de production devient « down » (inaccessible).

De nombreux incidents peuvent être à l'origine d'un « drop » de la base de données :

• Une maladresse causée durant la réalisation d'une tâche « DevOps » sur serveur
• Le datacenter hébergeant votre serveur est victime d'un incident (dernier évènement majeur en date : incendit 🔥 d'un datacenter OVHcloud à Strasbourg le 10 mars 2021)
• Le déploiement d'une mise à jour impliquant la modification structurelle des entitées internes à la base de données
• La résiliation à date échéante de votre formule d'hébergement et donc la réinitialisation du serveur intégrant la base de données

[...] Et tant d'autres.

🤷‍♂️ Comment l'appréhender ?

L'implémentation d'un système de backup 💾 est l'une des solutions parmi les plus recommandées lorsqu'il s'agit de protéger l'intégrité des données liées à votre application

Cette solution génère, selon un intervalle temporel défini, un export (partiel ou intégral) de la base de données. Un « snapshot » de la base de données est alors daté et exporté localement ou sur un serveur tiers.

Le cumul de ces snapshots va permettre de restaurer, en cas d'incident, la majeure partie, voir l'intégralité des données perdues depuis une date spécifique.

---

⚠️ Pré-requis

La réalisation de cette technique nécessite l'utilisation d'une API NestJS (framework « server-side » basé sur Node.js) ainsi que l'utilisation de TypeORM (ORM JavaScript et TypeScript majoritairement utilisé sur Node.js).

Elle a comme objectif la création d'une archive (.zip) incluant les différentes tables de votre base de données (.sql), dans un répertoire spécifique sur serveur, à date de son exécution.

Une base de données MySQL doit être disponible sur le même environnement que celui où est exécutée l'API NestJS. (Celle-ci peut parfaitement être intégrée dans un « Docker Container »)

ℹ️ Si la base de données est démarrée depuis Docker, les fonctionnalités Docker doivent être exploitables via bash sans avoir à justifier de permissions « super utilisateur ».

---

📦 Création du service dédié aux tâches

La première étape de notre implémentation requiert la création d'un nouveau service dédié aux tâches.

Les dossiers tasks et services ainsi que les fichiers tasks.module.ts et tasks.service.ts doivent être créés selon l'arborescence suivante (à ajuster selon l'architecture de votre API) :

Arborescence des fichiers 👇

   - 🗂️ src
        - 🗂️ tasks
            - 🗂️ services
                - 🔵 tasks.service.ts
            - 🟢 tasks.module.ts
        - 🟢 app.module.ts

Procédons à l'export du nouveau service fraîchement créé :

    import { Injectable } from '@nestjs/common';

    @Injectable()
    export class TasksService {

    }

Puis ajoutons la configuration minimale requise à notre nouveau module tasks.module.ts en important le fichier tasks.service.ts :

    import { Module } from '@nestjs/common';
    import { ConfigModule } from '@nestjs/config';

    import { TasksService } from './services/tasks.service';

    @Module({
        imports: [ ConfigModule ],
        providers: [ TasksService ],
        controllers: [ ],
    })
    export class TasksModule {}

Dans notre fichier app.module.ts, le module récemment créé doit être importé de la manière suivante :

    import { TasksModule } from './tasks/tasks.module';
    /* ... */

    @Module({
      imports: [ 
        TasksModule,
        /* ... */
      ],
      controllers: [],
      providers: [],
    })

    export class AppModule {}

Il ne nous reste plus qu'à implémenter de nouvelles fonctionnalités dans le nouveau service créé 🔥

---

⏰ Implémentation de la tâche CRON dédiée au « backup »

Pour planifier l'export de notre base de données, NestJS rend disponible le package @nestjs/schedule.

Ce dernier permet de programmer l'exécution d'une fonction à une date/heure fixe, à des intervalles récurrents ou une fois après un intervalle donné.

Les packages suivants doivent alors être installés :

    $ npm install --save @nestjs/schedule
    $ npm install --save-dev @types/cron

@nestjs/schedule doit, une fois installé, être importé dans le fichier app.module.ts :

• L'import import { ScheduleModule } from '@nestjs/schedule'; doit être ajouté.
• Notre AppModule doit également importer : ScheduleModule.forRoot().

    import { ScheduleModule } from '@nestjs/schedule'; // import from package here
    /* ... */

    @Module({
      imports: [ 
        ScheduleModule.forRoot(), // module import here
        /* ... */
      ],
      controllers: [],
      providers: [],
    })

    export class AppModule {}

Nous pouvons dès lors implémenter une ⏰ tâche programmée depuis le service dédié aux tâches TasksService :

    import { Injectable } from '@nestjs/common';
    import { Cron, CronExpression } from '@nestjs/schedule';

    @Injectable()
    export class TasksService {

        @Cron(CronExpression.EVERY_10_SECONDS)
        async exportDB() {
            // do something
        }

    }

L'enum CronExpression fourni une correspondance explicite des différentes expressions qui permettent de définir l'intervalle d'exécution. Il est conseillé de l'importer pour améliorer la clarté du code.

Nous utiliserons EVERY_10_SECONDS pour les phases de test.

ℹ️ Selon le comportement attendu, l'exécution du backup pourrait très bien être exécuté une fois chaque semaine de sorte à créer un point de sauvegarde sans accumuler excessivement de données de stockage.

La fonction exportDB() est à présent sollicitée depuis un intervalle donné, voyons comment exporter la base de données.

---

📤 Export des tables depuis le serveur MySQL

A la racine de notre projet, le dossier backups doit être créé, il va permettre de répertorier les archives (.zip) qui seront générées par notre système de sauvegarde.

⚙️ Déclarations principales

Commençons par déclarer les constantes essentielles à notre tâche :

    /* Settings */
    const DEBUG_MODE = false; // Pass to 'true' to print the script exec output and display errors
    const BACKUPS_DIRECTORY = 'backups'; // Directory where will be exported the snapshot
    const DOCKER_CONTAINER_NAME = false; // Add a Docker container name if your mysql server depends of Docker

ℹ️ La constante DOCKER_CONTAINER_NAME permet d'accéder au serveur MySQL d'un éventuel processus Docker.

Puis prélever les informations relatives à notre base de données courante.

    ...

    /* Database */
    const databaseCredentials: ConnectionCredentials<ConnectionOptions> = await getConnectionOptions('default');
    const tablesList = await getManager().query(`SHOW tables;`);

La commande MySQL SHOW tables; permet d'obtenir les différentes tables de notre base de données.

ℹ️ Libre à vous de paramètrer l'export d'une ou plusieurs base(s) de données selon l'architecture de votre API.

Les informations de connexion au serveur MySQL databaseCredentials nous permettront de nous authentifier à la base de données pour y ordonner l'export des tables cibles.

Les dernières constantes à déclarer sont des informations utiles au référencement de notre snapshot :

    ...

    /* Main declarations */
    const dateOfSnapshot = DateTime.now().toFormat('yyyy-LL-dd'); // Today as "2023-04-29" format
    const exportedFiles = []; // Temporarily created (.sql) files are pushed in this const
    const archiveToCreate = `${BACKUPS_DIRECTORY}/${dateOfSnapshot}_snapshot.zip`; // Relative path for snapshot from root of project

ℹ️ La librairie Luxon est utilisée dans le code ci-dessus et fournit DateTime, elle n'est cependant pas indispensable.

La déclaration de dateOfSnapshot va permettre d'ajouter la date à laquelle a été effectué notre snapshot.

Les déclarations essentielles à notre fonction sont faites, il ne reste plus qu'à importer les fonctionnalités utilisées en tête de notre fichier tasks.service.ts :

    import { DateTime } from 'luxon';
    import { getManager, getConnectionOptions, ConnectionOptions } from 'typeorm';

    ...

---

📤 Export des tables SQL

Un script programmé par l'intermédiaire de l'API et exécuté sur le même serveur que l'API, va permettre la connexion au serveur MySQL et ainsi permettre l'export des différentes tables inclues dans votre base de données.

Pour poursuivre, le package shelljs (basé sur l'utilisation du module Node child_process) permettant l'exécution de processus externes va devoir être installé.

Utilisez npm install shelljs, puis, en dessous des imports de votre service tasks.service.ts, ajoutez :

    ...
    const shell = require('shelljs');
    const fs = require('fs');

    ...

Actuellement, si l'on part du principe que le nom de notre base de données correspond à : test_database, la constante tablesList préalablement définit contiendra un tableau dont le format sera le suivant :

[
    { Tables_in_test_database: 'first_table' },
    { Tables_in_test_database: 'second_table' },
    { Tables_in_test_database: 'third_table' },
]

Nous savons dès lors quelles tables exporter, nous avons également des identifiants d'authentification pour accéder à notre serveur MySQL, l'export peut commencer.

    ...

    try {
        console.log(`💾 Trying to export the database for the ${DateTime.now().toFormat('dd LLL yyyy')}..`);
        tablesList.map(table => { // Export of differents tables to .sql format
            const nameOfTable = Object.values(table)[0];
            const fileOfSnapshot = `${dateOfSnapshot}_${nameOfTable}.sql`;
            const resultOfExport = shell.exec(`${DOCKER_CONTAINER_NAME ? `docker exec ${DOCKER_CONTAINER_NAME} ` : ''}mysqldump -u ${databaseCredentials.username} --password=${databaseCredentials.password} ${databaseCredentials.database} ${nameOfTable} > ${fileOfSnapshot}`, { silent: !DEBUG_MODE });
            if (!String(fs.readFileSync(fileOfSnapshot, 'utf8')).includes('INSERT INTO')) // Check of SQL export
                throw `Unable to export '${nameOfTable}' table from the database!\n> ${resultOfExport.stderr}`;
            else console.log(`📥 SQL Table '${fileOfSnapshot}' is successfully retrieved.`);
        });
        console.log('✅ Database saved!');
    } catch (err) {
        console.log('❌ Snapshot of the database has failed!', DEBUG_MODE ? '\n' + err : '');
    }

On ajoute ici les instructions : try et catch.

ℹ️ Si la constante DOCKER_CONTAINER_NAME a été configurée, le premier ordre d'exécution concerne Docker et permet la connexion au serveur MySQL à travers le conteneur qui l'exécute.

La requête mysqldump est exécutée pour chaque table, alimentée par les informations d'authentification fournis par databaseCredentials (connexion courante retournée par TypeORM).

Chaque table est prétendument exportée (à la racine du projet pour le moment) au format .sql et selon fileOfSnapshot.

Pour le vérifier, nous utilisons la fonction readFileSync du module File System (fs), nous vérifions ainsi que le fichier cible existe et inclut bien la chaine alphanumérique INSERT INTO.

Si c'est le cas, la table suivante est exportée jusqu'à ce que toutes le soient. Le cas échéant, une occurrence de l'instruction catch survient et l'export échoue.

    💾 Trying to export the database for the 29 Apr 2023..
    📥 SQL Table '2023-04-29_first_table.sql' is successfully retrieved.
    📥 SQL Table '2023-04-29_second_table.sql' is successfully retrieved.
    📥 SQL Table '2023-04-29_third_table.sql' is successfully retrieved.
    ✅ Database saved!

Trois fichiers .sql ont été créés à la racine du projet.

Voyons à présent comment créer un snapshot en générant une archive (.zip) dans notre dossier backups.

---

🗂️ Génération d'une nouvelle archive (.zip) en guise de snapshot

Générer une archive est idéal pour garantir la sauvegarde de notre base de données, nos données sont dès lors compressées puis enregistrées et datées depuis un fichier .zip individuel, dans le répertoire préalablement créé : backups.

ℹ️ Des fichiers/répertoires autres que notre base de données peuvent parfois intégrer notre archive : pour certaines applications, il peut être nécessaire d'enregistrer le(s) répertoire(s) dans le(s)quel(s) sont hébergées les différentes ressources exploitées (= images de profil, uploads, etc..) pour garantir la disponibilité des ressources internes référencées en base de données.

A l'heure actuelle, seul l'export des tables SQL de notre base de données est effectué, à la racine de notre projet.

Voyons comment transformer ces exports en fichiers temporaires pour procéder à la création d'une archive :

Lorsque chaque table est exportée, le nom du fichier temporaire créé (.sql) doit être référencé dans le tableau exportedFiles (préalablement défini) :

    tablesList.map(table => { // Export of differents tables to .sql format
        const nameOfTable = Object.values(table)[0];
        const fileOfSnapshot = `${dateOfSnapshot}_${nameOfTable}.sql`;
        exportedFiles.push(fileOfSnapshot); // Push the temporarily file name here
        const resultOfExport = shell.exec(`${DOCKER_CONTAINER_NAME ? `docker exec ${DOCKER_CONTAINER_NAME} ` : ''}mysqldump -u ${databaseCredentials.username} --password=${databaseCredentials.password} ${databaseCredentials.database} ${nameOfTable} > ${fileOfSnapshot}`, { silent: !DEBUG_MODE });
        if (!String(fs.readFileSync(fileOfSnapshot, 'utf8')).includes('INSERT INTO')) // Check of SQL export
            throw `Unable to export '${nameOfTable}' table from the database!\n> ${resultOfExport.stderr}`;
        else console.log(`📥 SQL Table '${fileOfSnapshot}' is successfully retrieved.`);
    });

Nos deux instructions try et catch doivent ensuite être complétées par l'instruction finally. Elle permettra d'exécuter une opération à terme des opérations initiales, quelque soit l'issue des opérations.

Grâce à l'instruction finally, les fichiers temporairement créés seront supprimés à terme des opérations, que l'archive soit créée ou non :

    try {
        ...

    } catch (err) {
        console.log('❌ Snapshot of the database has failed!', DEBUG_MODE ? '\n' + err : '');
    } finally { // Finally, delete the temporarily .sql files
        exportedFiles?.map(exportedFile => 
            (fs.existsSync(exportedFile)) && shell.exec(`rm -rf ${exportedFile}`, { silent: !DEBUG_MODE }));
    }

Il ne nous reste plus qu'à implémenter la génération d'une archive (.zip) après que soient générés les exports temporaires (.sql) dans notre instruction try puis de s'assurer que l'archive soit correctement générée :

    console.log(`📦 Trying to generate an archive (.zip) from retrieved data..`);
    const resultOfArchive = shell.exec(`zip -r ${archiveToCreate} ${exportedFiles.join(' ')}`, { silent: !DEBUG_MODE }); // Create .zip archive from .sql exports
    if (fs.existsSync(archiveToCreate)) {
        console.log(`📎 (+) ${exportedFiles.length} table(s) added to archive.`);
        console.log(`✅ Snapshot '${archiveToCreate}' is successfully created.`);
    } else throw `Unable to generate a zip snapshot of the database!\n> ${resultOfArchive.stderr}`; 

Les différentes tables exportées sont ici enregistrées dans un fichier .zip daté, dans notre répertoire backups. Si l'archive n'existe pas après l'opération : une erreur survient et un rapport d'erreurs est envoyé.

Quoi qu'il advienne de la création du snapshot au format .zip, les exports temporaires sont inévitablement supprimés en fin d'opération.

🤲 Aperçu global de notre fonction

    @Cron(CronExpression.EVERY_WEEK)
    async exportDB() {
        /* Settings */
        const DEBUG_MODE = false; // Pass to 'true' to print the script exec output and display errors
        const BACKUPS_DIRECTORY = 'backups'; // Directory where will be exported the snapshot
        const DOCKER_CONTAINER_NAME = false; // Add a Docker container name if your mysql server depends of Docker
        /* Database */
        const databaseCredentials: ConnectionCredentials<ConnectionOptions> = await getConnectionOptions('default');
        const tablesList = await getManager().query(`SHOW tables;`);
        /* Main declarations */
        const dateOfSnapshot = DateTime.now().toFormat('yyyy-LL-dd');
        const exportedFiles = [];
        const archiveToCreate = `${BACKUPS_DIRECTORY}/${dateOfSnapshot}_snapshot.zip`;
        try {
            console.log(`💾 Trying to export the database for the ${DateTime.now().toFormat('dd LLL yyyy')}..`);
            tablesList.map(table => { // Export of differents tables to .sql format
                const nameOfTable = Object.values(table)[0];
                const fileOfSnapshot = `${dateOfSnapshot}_${nameOfTable}.sql`;
                exportedFiles.push(fileOfSnapshot);
                const resultOfExport = shell.exec(`${DOCKER_CONTAINER_NAME ? `docker exec ${DOCKER_CONTAINER_NAME} ` : ''}mysqldump -u ${databaseCredentials.username} --password=${databaseCredentials.password} ${databaseCredentials.database} ${nameOfTable} > ${fileOfSnapshot}`, { silent: !DEBUG_MODE });
                if (!String(fs.readFileSync(fileOfSnapshot, 'utf8')).includes('INSERT INTO')) // Check of SQL export
                    throw `Unable to export '${nameOfTable}' table from the database!\n> ${resultOfExport.stderr}`;
                else console.log(`📥 SQL Table '${fileOfSnapshot}' is successfully retrieved.`);
            });
            console.log(`📦 Trying to generate an archive (.zip) from retrieved data..`);
            const resultOfArchive = shell.exec(`zip -r ${archiveToCreate} ${exportedFiles.join(' ')}`, { silent: !DEBUG_MODE }); // Create .zip archive from .sql exports
            if (fs.existsSync(archiveToCreate)) {
                console.log(`📎 (+) ${exportedFiles.length} table(s) added to archive.`);
                console.log(`✅ Snapshot '${archiveToCreate}' is successfully created.`);
            } else throw `Unable to generate a zip snapshot of the database!\n> ${resultOfArchive.stderr}`;
        } catch (err) {
            console.log('❌ Snapshot of the database has failed!', DEBUG_MODE ? '\n' + err : '');
        } finally { // Finally, delete the temporarily .sql files
            exportedFiles?.map(exportedFile => 
                (fs.existsSync(exportedFile)) && shell.exec(`rm -rf ${exportedFile}`, { silent: !DEBUG_MODE }));
        }
    }

L'export de notre base de données est dans cet exemple programmé chaque semaine.

A dates échéantes, le backup sera automatiquement effectué :

    💾 Trying to export the database for the 29 Apr 2023..
    📥 SQL Table '2023-04-29_first_table.sql' is successfully retrieved.
    📥 SQL Table '2023-04-29_second_table.sql' is successfully retrieved.
    📥 SQL Table '2023-04-29_third_table.sql' is successfully retrieved.
    📦 Trying to generate an archive (.zip) from retrieved data..
    📎 (+) 3 table(s) added to archive.
    📎 (+) 'uploads' directory added to archive. // can be added in the zip script execution
    ✅ Snapshot 'backups/2023-04-29_snapshot.zip' is successfully created.

Dans l'exemple ci-dessus, l'archive 2023-04-29_snapshot.zip a été créée dans le répertoire backups.

Elle inclut les trois différentes tables incluses dans notre base de données ainsi que le répertoire uploads du projet.

ℹ️ Ce sytème de backup ne permet pas la création de plusieurs snapshots dans la même journée, si l'intervale de la tâche CRON était par exemple définit à 6 heures : une archive serait créée la première fois puis mise à jour pour chaque prochaine occurence du même jour. Inclure le timestamp du snapshot deviendrait alors nécessaire.

---

🆘 Erreurs éventuelles

ℹ️ La constante DEBUG_MODE passée à true permet l'affichage des logs liées aux différentes opérations exécutées au cours de la sauvegarde de la base de données.

Une erreur liée à une insuffisance de permissions est fréquemment rencontrée sur les systèmes Linux lors de l'utilisation de Docker :

    > docker container ps -a

    > `Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: 
    Get "http://%2Fvar%2Frun%2Fdocker.sock/v1.24/containers/json?all=1": dial 
    unix /var/run/docker.sock: connect: permission denied`

Pour changer rapidement les permissions de l'utilisateur en cours d'utilisation, utilisez : sudo chown $USER /var/run/docker.sock. L'exécution de Docker devient désormais possible sans recourir à une élévation de privilèges _(fix temporaire) _

Pour que les permissions de l'utilisateur persistent après le redémarrage de votre machine, il sera nécessaire d'ajouter l'utilisateur en cours d'utilisation au groupe docker en procédant de la sorte :

Créez le groupe "docker" s'il n'existe pas : sudo groupadd docker.

Ensuite, ajoutez l'utilisateur en cours d'utilisation au groupe fraîchement créé : sudo usermod -aG docker $USER. Finalement, intégrez-le au groupe en utilisant: newgrp docker (fix permanent)