Sauvegarder une base de données en SSH en 30 secondes

La sauvegarde de données est une composante essentielle dans le process de développement d'un site internet vous le savez bien. En effet, que cela soit pendant vos tests en préproduction ou lors de la mise en production de votre projet, vous avez constamment besoin d'effectuer des sauvegardes de votre base de données.

A plus forte raison après la mise en ligne de votre projet, puisque il serait préjudiciable en cas de crash, de n'avoir qu'une sauvegarde à J+10. Cela signifierait que vous auriez perdu toutes les modifications effectuées sur votre site sur 10 jours ! Impensable.

Si vous n'êtes pas encore familiarisé à l'utilisation des lignes de commandes sauvegarder une base de donnée est (certainement) pour vous une opération complexe, chronophage, et nécessite peut-être l'utilisation d'un logiciel tiers dont vous n'avez réellement pas besoin ! (si, si, croyez-moi ...).

Lors de mon précédent tutoriel je suis revenu sur les commandes SSH fondamentales qui vous permettent d'utiliser de façon productive le terminal, vous familiariser avec les lignes de commandes et remplir une multitude de tâches que vous faisiez peut-être manuellement auparavant. Si vous n'avez pas encore eu l'occasion de découvrir ce tutoriel je vous invite à le lire, il vous sera certainement utile.

Maintenant que nous avons vu comment travailler avec notre terminal ou que c'est quelque chose qui vous parle déjà, vous savez désormais comment effectuer les opérations principales que vous faisiez avec votre logiciel FTP très limité avouons-le. Et si il n'était pas encore K.O. patiente, ce qui suit ne laissera plus aucune place au doute.

Voilà une vérité universelle : Votre logiciel FTP est limité et ne travaillera certainement pas aussi vite que vous !

Voyons tout de suite comment effectuer des opérations simples en lignes de commandes pour travailler avec un serveur de base de données MySQL et effectuer des sauvegardes en un claquement de doigts (Clin d'œil Thanos!).

Comme il faut bien commencer quelque part, je vous propose de commencer par le plus simple, se connecter à un serveur de base de données, et effectuer des requête SQL simples. Nous verrons ensuite (en bas de page) comment effectuer une sauvegarde en quelques secondes. En bonus je vous explique même comment avoir une jolie petite barre de progression !

Se connecter à la base de données

Voyons les arguments de cette commande ensemble :

• mysql : indique que l'on travaille sur un serveur de base de données
• -h ou --host : indique que l'on fait passer un host à notre commande
• dbhost : C'est l'adresse IP de votre serveur (localhost si votre base de donnée est sur le même serveur que celui auquel vous êtes connecté)
• -u : indique que l'on précise un utilisateur
• dbuser : correspond à votre identifiant pour accéder à la base de données
• dbpassword : (optionnel) correspond au mot de passe de votre identifiant pour accéder à la base de données. Lisez le paragraphe suivant sur ce point, c'est vraiment important !
• dbname : indique la table sur laquelle vous souhaitez vous connecter

Avec la commande ci-dessus vous êtes prêt à interagir avec votre base de données et effectuer des requêtes (ex. Select, Update, etc...).

Petite note de sécurité : Si vous ne le saviez pas déjà, sachez que chaque commande que vous tapez en SSH est stockée dans un fichier d'historique de commandes. Il est donc assez simple pour un administrateur réseau un peu qualifié d'arriver à retrouver les commandes que vous auriez pu saisir.

Dans cette optique je vous recommande de remplacer le « --password=dbpassword » par « -p » pour indiquer que votre commande nécessite un mot de passe. Votre mot de passe ne sera donc pas renseigné dans l'historique de commandes cependant vous serez invité à le saisir lorsque vous appuierez sur Entrée pour executer votre commande.

Disons qu'en terme de sécurité il est beaucoup plus propre de masquer ce type d'éléments critiques. Je souhaitait tout de même vous montrer la commande pour que l'on fasse ce petit point sécurité car pour les commandes suivantes j'utiliserais le « -p » sur les commandes nécessitant ce type d'informations critiques, ne soyez donc pas étonnés.

Lister toutes les tables de votre base de données

Maintenant que vous êtes connecté, vous disposez dans votre terminal de l'accès aux requêtes SQL.

Vous n'avez pas d'interface mais il s'agit de la même chose que l'onglet « SQL » d'un phpMyAdmin de base.

Pour lister toutes les tables de votre base de données vous taperez donc la commande qui suit, attention, chaque requête devra se terminer par un point-virgules « ; » sans quoi vous verrez une erreur de syntaxe apparaitre.

Effectuer une requete simple sur une table

Pour effectuer une requête SQL en SSH cela se passera exactement de la même manière que sur phpMyAdmin dans votre onglet « SQL ».

Disons par exemple que je veux sélectionner toutes les informations d'une table « users » je taperais donc la commande suivante dans mon terminal :

Un tableau apparait avec toutes les informations, disons maintenant que je ne veux que les utilisateurs de ma table « users » qui ont pour adresse email une adresse en « @gmail » je taperais donc exactement la même commande en rajoutant un WHERE avec les arguments dont j'ai besoin, par exemple :

Si vous n'êtes de base pas très à l'aise avec MySQL je vous recommande de vous renseigner sur cette partie car c'est vraiment primordial de pouvoir effectuer des requêtes de base. J'aurais peut-être l'occasion de faire un petit tutoriel sur cette partie plus tard !

Exporter une base de donnée en « .sql »

Bon, nous allons attaquer la partie la plus importante de ce tutoriel ! Un peu plus haut je vous expliquais que l'on pouvait effectuer des backups (fr: sauvegardes) de la base de données en quelques secondes.

Dans mon cas, j'aime bien avoir une information sur la progression de ma commande. Bah oui ! Admettons qu'on sauvegarde une base de données de 2GB (Gigas)... si vous n'avez pas de barre de progression ou un indicateur visuel qui vous permet d'indiquer l'état de votre commande, comment savoir si vous avez le temps de passer un coup de fil, ou boire un café ?!

Plus sérieusement, dans notre métiers le temps c'est de l'argent. Pardon d'être pragmatique mais la rentabilité est une notion fondamentale dans la recherche d'optimisation.

Imaginez que vous deviez produire un planning précis à votre client en lui indiquant un temps de migration pour la mise en production de son site. Si vous n'avez même pas cette information... bref vous m'avez compris.

Nous allons donc commencer par ajouter une petite fonctionnalité qui nous permettra visuellement d'obtenir une barre de progression indiquant l'état d'avancement de notre commande d'export. Pour cela ouvrez votre terminal et tentez de taper la commande suivante :

Dans la plupart des cas vous devriez obtenir par défaut le message d'erreur suivant : « pv: command not found ». Pas de panique, c'est normal. Cette commande ne fait pas partie des paquets installés par défaut alors installons-là ensemble. Toujours dans votre terminal tapez :

Une fois l'installation terminée (cela ne prend que quelques secondes), re-tentez de taper la commande « pv » et là vous devriez voir apparaitre une barre de progression vide. Normal puisqu'elle n'indique la progression d'aucune tâche... pour le moment !

Maintenant que notre petit bonus est en place, nous pouvons effectuer une requête d'export SQL, je vous laisse découvrir la commande d'export ci-dessous :

Alors, revenons un instant dessus. Comme pour nous connecter à notre base de données, nous devons ici envoyer quelques arguments à notre commande pour lui indiquer sur quel serveur travailler, avec quels identifiants, etc etc. Vous devriez avoir compris la majorité des arguments présents dans la commande je ne vais donc me concentrer que sur les arguments que nous n'avions pas encore vu jusque là :

• mysqldump : l'export se fait avec mysqldump, l'import avec mysql.
• -e : (optionnel) permet d'indiquer que l'export s'effectue avec de multiples INSERT
• | : le séparateur pipe (ou la barre droite pour les néophytes) à ne pas confondre avec un L minuscule (séparateur pipe : | vs la lettre minuscule L : l ), il permet dans des commandes SSH de filtrer ou effectuer plusieurs opérations sur une même commande
• pv : c'est du coup notre petit bonus « Barre de progression »
• > : le chevron vers la droite indique que nous envoyons le résultat de notre commande vers un fichier
• nomDuFichier.sql : c'est le nom du fichier en sortie que nous obtiendrons. Précédé du chevron vers la droite il indique à notre commande « Envoi le résultat de ma commande dans le fichier nomDuFichier.sql ».

Petite précision : Pour votre fichier en sortie vous pouvez préciser un chemin relatif vers un dossier précis. Par exemple si vous vouliez envoyer votre backup dans un dossier précis, en partant de l'exemple ci-dessus l'argument « nomDuFichier.sql » pourrait être « /var/www/vhost/backups/nomDuFichier.sql ».

Importer une base de donnée depuis un fichier en « .sql »

OK ! Maintenant que vous savez effectuer un backup en quelques secondes vous allez voir que le restaurer, ou tout simplement l'installer est tout aussi simple. La commande est quasiment similaire à la précédente seul l'ordre change donc vous devriez arriver à la comprendre assez facilement.

Cette syntaxe d'import est vraiment propre à l'utilisation de la commande « pv » je vais donc tout de même vous donner la commande d'import SQL sans le pipe, sait-on jamais, vous n'aimez peut-être pas les barres de progression :

Comme je le disais plus haut, la commande est identique à celle de l'export. Rappelez-vous que « mysqldump » correspond à un export, et « mysql » est utilisé pour se connecter (et effectuer des requêtes SQL) où importer un fichier « .sql ».

Le sens du chevron (chevon vers la gauche) indique ici que nous importons le contenu de notre fichier dans notre base de données. La ligne de commande fait sa tambouille et hop, cela devient un import SQL !

La Conclusion de tout ça ?

Vous êtes maintenant un pro de l'export/import de fichier MySQL, mais allons un peu plus loin et mettons définitivement K.O votre logiciel FTP. Disons que vous avez un site de Préproduction, vous avez travaillé dessus de longs mois, maintenant il est prêt ! Vous devez effectuer la migration, mais voici quelques problématiques :

• Vous avez travaillé votre version de Préprod sur un serveur A
• Vous devez migrer votre travail sur le site de production, mais il se trouve sur un serveur B
• Vous devez migrer vos bases de données, et les réinstaller !

Alors en temps normal vous auriez procédé de la manière suivante :

• Vous transférez avec un logiciel FTP tous vos fichiers
• Vous les importez sur le serveur B
• Vous faites un backup SQL du serveur A
• Vous le téléchargez puis le renvoyez sur le serveur B ou arrivez à l'installer sans trop de difficulté avec un logiciel de base de données type Navicat

OK. Génial, mais. Parce que Oui il y à un mais. Toutes ces étapes sont chronophages, il vous aura fallu au moins deux heures si les fichiers sont un peu lourds !

Si vous n'aviez pas d'autres méthodologie, voici ce que vous devriez faire, et aucune de ces étapes ne nécessite d'utiliser autre chose que votre terminal :

• Exporter votre base de données avec la commande « mysqldump » que vous venez d'apprendre
• Créer une archive « .tar.gz » de vos fichiers
• Transférer votre archive et votre backup SQL directement vers votre serveur de Prod avec la commande « scp »
• Importer votre base de données en ligne de commandes

Ce process en plus d'être beaucoup plus rapide, vous permet de faire un échange de données direct entre vos deux serveur.

Ô magie, Ô stupéfaction ! Vous venez d'importer en quelques secondes vos fichiers de Préprod et votre base de données et n'avez même pas eu à ouvrir une seule fois votre logiciel FTP ! Il en dit quoi FileZila ? xD