Table des matières de l'article :
L'un des cas peu connus de l'utilisateur final d'un site Internet est celui inhérent aux problèmes de disponibilité du site qui surviennent lorsque le serveur effectue le Sauvegarde de la base de données MySQL.
Bien que les opérations de maintenance et de sauvegarde soient planifiées la nuit où un faible trafic et une charge de serveur plus faible sont attendus, que se passe-t-il lorsque les robots d'exploration de Google passent pour analyser le nouveau contenu et les indexer à ce moment-là ?
Si la sauvegarde est conçue correctement, avec les bonnes technologies et sur un jeu de données de taille adéquate, tout ira simplement bien et Google pourra scanner notre site, les nouveaux contenus puis les indexer plus tard, sinon sera frappé par une série de 500 erreurs de type et une fois le budget de crawl pour les activités de crawl sur votre site dépensé il s'en ira sans récupérer le nouveau contenu et en effet détecter de nombreuses erreurs de type 500 qui à moyen/long terme cela pourrait également compromettre le classement de votre site étant donné le taux d'erreur très élevé.
Mais pourquoi cela arrive-t-il ?
Pour comprendre les raisons pour lesquelles ces problèmes surviennent, qui ne sont pas fréquents mais pas trop rares, il faut comprendre comment normalement un hébergeur se charge d'effectuer les activités de sauvegarde au niveau du serveur et plus précisément quand il va sauvegarder la base de données qui est la nôtre placer. Que se passe-t-il, par exemple, si, pendant la copie, une requête arrive sur notre site qui, via PHP, traite l'exécution de plusieurs requêtes SQL dans la base de données ?
Dans un contexte d'idiotie parfaite, nous allons salir la sauvegarde en générant une situation incohérente puisque peut-être dans cette milliseconde nous aurions mis à jour un nouvel enregistrement dans la table de données personnelles hypothétique sans écrire l'enregistrement relatif dans la table des codes fiscaux.
Dans un contexte standard, l'administrateur système ou le panneau de contrôle exécutera mysqldump avec les paramètres de verrouillage qui bloqueront les tables à la fois en lecture et en écriture, garantiront un dump intact et cohérent mais généreront inévitablement des problèmes de verrouillage, c'est-à-dire que nous aurions bloqué des ressources et donc la requête ne réussira pas dans le temps établi par le serveur web, déclenchant au mieux un timeout, au pire "suspendre" le crawler pendant plusieurs dizaines de secondes et générant un timeout côté crawler.
Eviter ces situations est possible en prenant les bonnes précautions et en sachant évaluer le scénario en fonction de la technologie, de la taille du jeu de données, du type de tables et essentiellement du budget du client afin de pouvoir planifier en toute sérénité la meilleure solution pour leurs poches.
Sauvegarde MySQL via mysqldump
C'est probablement la méthode de sauvegarde la plus simple et la plus courante ainsi que le plus incorrect, en partie parce qu'il fait partie des programmes clients MySQL, il sera donc installé par défaut. Il effectue des sauvegardes logiques et peut sauvegarder toutes les bases de données, une base de données et toutes les tables ou une ou plusieurs tables dans la même base de données. Le vidage est imprimé sur la sortie standard, de sorte que la sortie puisse être envoyée à d'autres programmes pour l'interopérabilité. Par exemple, vous pouvez lier la sortie à gzip, car mysqldump ne compresse pas la sortie. Le plus gros inconvénient est qu'il est extrêmement lent sur les bases de données volumineuses, non seulement parce qu'il effectue des sauvegardes logiques, mais aussi parce que le processus de sauvegarde et de restauration est à thread unique. Le vidage contient des instructions SQL pour créer les tables, les remplir de données, ou les deux.
Comme nous l'avons mentionné ci-dessus, c'est la manière la plus incorrecte que vous puissiez utiliser pour effectuer une sauvegarde d'une base de données MySQL, Percona Server ou MariaDB, c'est-à-dire utiliser l'utilitaire système mysqldump. Si vous ne l'avez pas encore remarqué, nous sommes en 2019 et non plus en 2005 et il est peut-être temps pour vous et votre hébergeur de vous réveiller en prenant note du nombre de problèmes que cet utilitaire crée, en particulier sur les grandes bases de données de plusieurs gigaoctets.
Si ce n'est pas le cas, n'hésitez pas à changer de fournisseur si votre projet est extrêmement ambitieux et important pour vous.
Mysqldump peut extraire et copier le contenu des tables ligne par ligne, ou il peut l'extraire entièrement de la table et le mettre dans une mémoire tampon avant de le copier. L'utilisation de la mise en mémoire tampon peut être un problème lors de la copie de grandes tables.
Le but de mysqldump est essentiellement de générer un fichier .sql dans lequel il y a des instructions pour recréer la base de données appelée dump comme nous l'avons dit précédemment.
Comme nous l'avons dit précédemment pour assurer une sauvegarde cohérente de la base de données, nous utilisons des paramètres pour le verrouillage des tables :
- Pour les tableaux, InnoDB est utilisé
--single-transactionComme une option. - Pour les tables MyISAM, l'option est utilisée à la place
--lock-tables
Si vous avez une base de données de quelques mégaoctets ou dix mégaoctets, vous pouvez "en toute sécurité" utiliser cette approche qui prend quelques secondes environ pour produire un dump .sql, mais que se passe-t-il si nous sommes confrontés à une base de données de plusieurs gigaoctets ? Cela peut prendre des dizaines de minutes voire des heures.
À ce moment-là, la base de données sera dans un état verrouillé et personne ne pourra écrire ou lire. Même pas Google ne vous renvoie ce type d'erreur.
Percona XtraBackup. Comment faire une sauvegarde MySQL comme un pro.
Alors que la méthode énumérée ci-dessus effectue des sauvegardes logiques, avec une lenteur exténuante, une telle approche devient inutilisable lorsqu'une base de données commence à croître de quelques gigaoctets. Xtrabackup est le logiciel de sauvegarde physique MySQL le plus populaire. Il peut créer une sauvegarde à chaud (ce qui signifie qu'il n'est pas nécessaire d'arrêter le serveur de base de données), est beaucoup plus rapide et prend en charge des sauvegardes entièrement non bloquantes, tant que toutes les tables sont InnoDB ou XtraDB. Il peut créer des fichiers de sauvegarde locaux ou un flux de sortie standard, il est donc très polyvalent. Par exemple, à l'aide d'outils tels que gof3r, vous pouvez diffuser des sauvegardes sur Amazon S3 et télécharger la sauvegarde à la volée (sans la stocker localement). La taille des sauvegardes est presque aussi grande que la base de données entière, mais xtrabackup prend en charge les sauvegardes compressées à l'aide de qpress, réduisant considérablement la taille finale. Une autre fonctionnalité intéressante est qu'il peut crypter les sauvegardes ou le streaming avec AES128, AES192 et AES256.
En plus d'avoir de nombreux autres avantages, tout d'abord une restauration pratiquement immédiate de l'intégralité de la base de données et de nombreuses autres fonctionnalités qui dépasseraient largement notre champ d'application axées sur des problèmes systémiques qui se traduisent par des problèmes de référencement, le principal avantage est celui de ne pas verrouiller la base de données et donc continuer à répondre à toutes les questions des visiteurs nocturnes, des araignées et des robots.
En termes simples, alors que mysqldump doit verrouiller les tables, les sauvegarder puis les déverrouiller, Percona XtraBackup utilise une fonction native de MySQL et un logiciel de SGBD dérivé tel que MariaDB ou Percona Server appelé journal binaire où toutes les instructions d'écriture et de modification sont enregistrées. venir à la DB.
Percona XtraSauvegarde en bref, il s'occupe de copier les données de manière grossière, en continuant à enregistrer les instructions de modification dans ces journaux binaires, puis à lire ces fichiers journaux et à appliquer les instructions qu'ils contiennent (c'est-à-dire les modifications de base de données) à la copie de la base de données de la sauvegarde, générant ainsi une « photographie » parfaitement intacte et conforme à la situation initiale.
Performances MySQLdump VS Percona XtraBackup
Si on veut toucher des nombres réels ou mesurer pour décider, on peut voir brièvement un benchmark entre les deux outils pour comprendre de quoi on parle et pourquoi mysqldump doit être considéré comme un jouet inapproprié pour les systèmes en production, allant créer des sauvegardes mysql lentes .
Comme nous pouvons le voir en travaillant sur le même jeu de données de 73 Go une sauvegarde faite avec mysqlsump est 50 fois plus lente qu'une sauvegarde faite avec Percona XtraBackup.
Il faut aussi dire que Percona XtraBackup est absolument gratuit sans aucune limitation et actuellement la meilleure solution de sauvegarde existante pour les systèmes basés sur MySQL et dérivés. Tellement bon qu'il reste encore meilleur que MySQL Enterprise Backup réalisé par la maison mère de MySQL elle-même et proposé à la modique somme (ton évidemment sarcastique) de 5000 XNUMX $ par an et par serveur.
Bref, il n'y a aucune raison de ne pas utiliser Percona XtraBackup autre que la superficialité et l'ignorance manifeste.
Utilisez-vous des panels comme cPanel ou Plesk ? Attention, ils utilisent aussi mysqldump
Malheureusement, l'amateurisme et la négligence ne concernent pas seulement les ingénieurs système du dimanche et les bricoleurs improvisés mais aussi les entreprises d'une certaine profondeur qui produisent des panneaux de contrôle commerciaux tels que cPanel ou Plesk.
En fait, en lisant sur leur site Web, nous pouvons lire en janvier 2019 concernant l'utilisation de Percona :
De plus, même par rapport à d'autres types de sauvegardes, voici un résumé simple qui montre pourquoi il faut utiliser cette façon de travailler au lieu d'improviser avec des utilitaires vétustes et très peu performants.
Percona XtraBackup est la seule solution capable de satisfaire toutes les exigences, il peut être utilisé de manière absolument rentable même avec une réplique MASTER/SLAVE mais c'est une autre affaire.
conclusion
Si vous avez l'habitude de trouver des sauvegardes de bases de données individuelles et des différents .sql dans votre espace de sauvegarde quotidien ou hebdomadaire, commencez à vous inquiéter. Si vous remarquez que la sauvegarde MySQL est très lente, posez-vous les bonnes questions. Si vous voyez des erreurs d'araignée de Google, commencez à vous inquiéter, si vous vous rendez compte que votre hébergeur utilise mysqldump, commencez à vous inquiéter, si votre projet web vous fait vivre et que vous êtes sûr qu'il n'utilise pas Percona XtraBackup, changez de fournisseur immédiatement. Nous n'aimons pas discréditer qui que ce soit, mais un amateur en difficulté est un danger pour lui-même et votre entreprise.