MySQL : Meilleures pratiques | AU NOUVEAU BLOG

Introduction

De nos jours, MySQL fait partie des bases de données d’applications les plus populaires et les plus appréciées. Mais la gestion et la conception sont une tâche cruciale et importante lors de la sélection d’une base de données pour votre application. Voyons quelles sont les meilleures pratiques pour gérer la base de données.

Meilleures pratiques MySQL :

• • Conception de schéma
  • Indexage
  • Optimisation des performances
  • Sauvegarde et récupération
  • Sécurité

Conception de schéma :

• • Suivez les principes de normalisation pour réduire la redondance des données et améliorer l’intégrité des données. Les principaux objectifs de la normalisation sont d’éliminer les données en double, de garantir que les dépendances des données ont un sens et de rendre la base de données plus efficace. Gardez cela à l’esprit, lorsque l’application est conçue pour des lectures intensives ou des rapports et des analyses, n’optez pas pour la normalisation et préférez plutôt la dénormalisation.
  • Choisissez le plus petit type de données pouvant contenir vos données pour économiser de l’espace et améliorer les performances. Évitez l’allocation d’octets inutile pour les colonnes qui ne sont pas requises. Par exemple, si vous souhaitez stocker « Pays + zone + numéro séparément », utilisez VARCHAR(20) et n’essayez pas aveuglément la longueur.

Indexation :

• • Utilisez des index pour les colonnes fréquemment interrogées, identifiez les colonnes souvent utilisées dans les clauses SELECT, WHERE, ORDER BY, JOIN et GROUP BY et créez des index sur ces colonnes.
  • Bien que les index améliorent les performances de lecture, ils peuvent dégrader les performances d’écriture (opérations INSERT, UPDATE, DELETE) en raison de la surcharge liée à la maintenance de l’index. Créez uniquement les index nécessaires.
  • Lors de la création d’index composites, tenez compte de l’ordre des colonnes en fonction des modèles de requête. Les colonnes les plus fréquemment utilisées dans les conditions doivent venir en premier.
  • L’indexation de colonnes avec une faible cardinalité (peu de valeurs uniques, telles que des colonnes booléennes ou de genre) peut ne pas offrir d’avantages significatifs en termes de performances.

Optimisation des performances :

• • Divisez les requêtes complexes en sous-requêtes plus simples ou en tables temporaires pour réduire la charge de travail de l’optimiseur. Sélectionnez uniquement les colonnes dont vous avez besoin au lieu d’utiliser SELECT *, qui récupère inutilement toutes les colonnes.
  • Lorsque vous utilisez des jointures, commencez par la table la plus petite ou la plus sélective. Cela peut réduire la quantité de données traitées lors des jointures ultérieures. Utilisez le type de jointure approprié (par exemple INNER JOIN, LEFT JOIN) en fonction de l’ensemble de données requis.
  • Les sous-requêtes peuvent être moins efficaces que les jointures, notamment dans la clause WHERE. Remplacez les sous-requêtes par des jointures lorsque cela est possible.
  • Dans une clause WHERE, placez en premier les conditions les plus sélectives (celles qui filtrent le plus de lignes). Évitez d’utiliser des fonctions sur des colonnes indexées dans la clause WHERE (par exemple, WHERE date(column) = ‘2023-01-01’). Cela peut empêcher l’utilisation d’index. Utilisez les conditions de plage telles que BETWEEN, >=, <= avec précaution, car elles risquent de ne pas utiliser pleinement les index.
  • Il existe quelques optimisations au niveau de la configuration qui peuvent vous aider si vous prenez soin de :

1. 1. 1. 1. Taille du pool tampon : la innodb_buffer_pool_size Le paramètre détermine la quantité de mémoire allouée à la mise en cache des tables et des index InnoDB. Il doit être défini sur 70 à 80 % de la mémoire disponible pour la plupart des charges de travail.
         2. Cache de requêtes : si votre charge de travail implique de nombreuses requêtes identiques, l’activation du cache de requêtes peut améliorer les performances. Toutefois, pour les données qui changent fréquemment, le cache de requêtes peut entraîner une surcharge.
         3. Pooling de connexions : utilisez le pooling de connexions pour gérer les connexions à la base de données. L’ouverture et la fermeture répétées de connexions peuvent dégrader les performances.
         4. Pool de threads : ajustez les paramètres du pool de threads pour gérer efficacement plusieurs connexions sans surcharger le serveur.
         5. E/S disque : utilisez des disques SSD rapides pour le stockage des données afin de réduire la latence d’E/S. Assurez-vous que le innodb_flush_log_at_trx_commit Le paramètre est configuré en fonction de vos exigences de durabilité.

Sauvegarde et récupération :

La sauvegarde et la restauration dans MySQL sont des processus critiques pour garantir l’intégrité, la disponibilité et la reprise après sinistre des données. Voici un guide des meilleures pratiques et techniques de sauvegarde et de restauration dans MySQL :

• • Sauvegardes logiques : Les sauvegardes logiques consistent en des instructions SQL qui recréent la structure et les données de la base de données. mysqldump est l’un des outils permettant d’exporter des bases de données vers un fichier .sql contenant CREATE TABLE, INSERT et d’autres instructions SQL. pompe mysql est également une alternative plus rapide à mysqldump, notamment pour les grandes bases de données. Prend en charge le traitement parallèle.
  • Sauvegardes physiques : Les sauvegardes physiques impliquent la copie des fichiers de base de données réels, y compris les fichiers de données, les journaux et les fichiers de configuration. sauvegarde mysql est l’un des outils de sauvegarde physique les plus populaires que vous puissiez utiliser. Cet outil appartient à MySQL Enterprise Backup et n’est pas open source. Mais il existe des outils open source disponibles comme Percona XtraBackup.

Stratégies de sauvegarde :

• • Sauvegardes complètes : Une sauvegarde complète de l’intégralité de la base de données. Utilisez cette méthode sur une base régulière ou hebdomadaire, cela dépend de la taille de votre coût/base de données. En outre, vous devez les utiliser avant la migration/mise à niveau/ou tout déploiement majeur de base de données.
  • Sauvegardes incrémentielles : sauvegarde uniquement les données modifiées depuis la dernière sauvegarde complète ou incrémentielle. Sauvegardes quotidiennes entre les sauvegardes complètes hebdomadaires. Si vous effectuez une sauvegarde complète quotidiennement, vous pouvez configurer une sauvegarde incrémentielle horaire pour réduire la perte de données lors de la récupération. (mysqlbinlog)
  • Sauvegardes différentielles : sauvegarde les données modifiées depuis la dernière sauvegarde complète. C’est une alternative aux sauvegardes incrémentielles.

Meilleure pratique de sauvegarde :

• • Automatiser les sauvegardes: utilisez des tâches cron ou d’autres outils de planification pour automatiser le processus de sauvegarde. Cela garantit des sauvegardes régulières sans intervention manuelle.
  • Stockage hors site : stockez les sauvegardes dans un emplacement physique différent ou utilisez le stockage cloud (par exemple, AWS S3, Google Cloud Storage) pour vous protéger contre les catastrophes spécifiques au site.
  • Chiffrer les sauvegardes: Protégez les données sensibles en chiffrant les sauvegardes, surtout si elles sont stockées hors site ou dans le cloud.
  • Compresser les sauvegardes: utilisez la compression pour économiser de l’espace de stockage, en particulier pour les sauvegardes logiques. par exemple, gzip
  • Vérifier les sauvegardes: Testez régulièrement les sauvegardes en les restaurant dans un environnement hors production pour vous assurer de leur fiabilité.

Procédure de récupération :

• • Restauration à partir de sauvegardes logiques : Assurez-vous que la base de données existe ou créez-la avant la restauration. mysql -u [username] -p [database_name] < sauvegarde.sql9 mysqlpump -u [username] -p [database_name] < sauvegarde.sql

• • Restauration à partir de sauvegardes physiques : mysqlbackup –backup-dir=/path/to/backup –datadir=/path/to/datadir copie ( cette commande copie les fichiers de sauvegarde dans le répertoire de données MySQL) innobackupex –apply-log /chemin/vers/sauvegarde | innobackupex –copier /chemin/vers/sauvegarde (--appliquer-log prépare la sauvegarde pour la restauration et –copy-back restaure la sauvegarde dans le répertoire de données MySQL)

• • Récupération ponctuelle (PITR) : restaure la base de données à un moment précis, généralement en appliquant des journaux binaires après la restauration d’une sauvegarde. Assurez-vous que la journalisation binaire est activée (log_bin = activé). Appliquez les journaux binaires à l’aide de l’outil mysqlbinlog présenté ci-dessous. Vous pouvez spécifier un –stop-datetime ou –stop-position pour récupérer jusqu’à un point spécifique. : mysqlbinlog /chemin/vers/binlogs.000001 | mysql -u [username] -p [database_name]

• • Planification de reprise après sinistre : Testez régulièrement votre plan de reprise après sinistre pour vous assurer que les sauvegardes peuvent être restaurées dans les délais requis. Mettez en œuvre une surveillance pour garantir que les sauvegardes sont effectuées avec succès et sans erreurs. Pensez à utiliser la réplication MySQL, le clustering ou d’autres solutions HA pour minimiser les temps d’arrêt en cas de panne.

Sécurité:

La sécurité est un aspect essentiel de la gestion des bases de données MySQL. Garantir la sécurité de votre serveur MySQL implique de mettre en œuvre une combinaison de bonnes pratiques. Voici un guide détaillé sur la sécurité MySQL :

Gestion des utilisateurs et authentification :

• • Accordez uniquement aux utilisateurs les autorisations minimales nécessaires pour effectuer leurs tâches. Évitez d’utiliser le compte root ou d’autres comptes hautement privilégiés pour accéder aux applications.
  • Utilisez l’instruction CREATE USER pour créer des utilisateurs spécifiques pour différentes applications ou rôles. Vous trouverez ci-dessous les commandes permettant de créer un utilisateur, d’attribuer des privilèges et de révoquer des privilèges.
    CRÉER UN UTILISATEUR ‘app_user’@’localhost’ IDENTIFIÉ PAR ‘strong_password’ ;
  • Attribuez uniquement les privilèges nécessaires aux utilisateurs. GRANT SELECT, INSERT ON nom_base de données.* TO ‘app_user’@’localhost’;
  • Examinez et révoquez régulièrement les privilèges inutiles. REVOKE INSERT ON nom_base de données.* FROM ‘app_user’@’localhost’;
  • Assurez-vous que tous les comptes d’utilisateurs utilisent des mots de passe forts et complexes. Appliquez les politiques de mot de passe à l’aide du plugin validate_password. INSTALLER LE PLUGIN validate_password SONAME ‘validate_password.so’; SET GLOBAL validate_password_policy=STRONG ;
  • Définissez des politiques d’expiration des mots de passe pour obliger les utilisateurs à modifier régulièrement leurs mots de passe. MODIFIER L’UTILISATEUR ‘app_user’@’localhost’ INTERVALLE D’EXPIRATION DU MOT DE PASSE 90 JOURS ;

Cryptage de la base de données :

• • Chiffrement des données en transit : Utilisez SSL/TLS pour crypter les données transmises entre le serveur MySQL et les clients.
  • Chiffrement des données au repos : Chiffrez les tablespaces InnoDB pour sécuriser les données au repos. Chiffrez également les journaux binaires pour empêcher tout accès non autorisé aux données de réplication. Chiffrez les sauvegardes à l’aide d’outils tels que mysqlbackup ou d’outils tiers tels que Percona XtraBackup.

Audit et journalisation :

• • Journalisation binaire: activez la journalisation binaire pour conserver un enregistrement de toutes les modifications apportées à la base de données.
  • Journalisation basée sur les lignes: utilisez la journalisation binaire basée sur les lignes pour une réplication et un audit plus précis.
  • Journal des requêtes générales: Enregistre toutes les requêtes SQL reçues par le serveur en l’activant. Assurez-vous que les journaux doivent être stockés dans un emplacement différent de celui de la base de données, sinon des problèmes de stockage peuvent survenir car les journaux généraux occupent beaucoup de stockage.
  • Journal des requêtes lentes : enregistre les requêtes qui dépassent un certain temps d’exécution, ce qui peut indiquer des problèmes de performances.
  • Activer l’audit d’entreprise MySQL : Fournit des fonctionnalités d’audit avancées, notamment le suivi des actions et requêtes spécifiques des utilisateurs.

Conclusion

La mise en œuvre de ces meilleures pratiques MySQL garantit un environnement de base de données robuste, sécurisé et bien entretenu, protégeant vos données contre les accès non autorisés, les vulnérabilités et les violations, tout en maintenant des performances optimales et la conformité aux normes de l’industrie.