Stratégies de Sauvegarde pour MySQL 8.0

La pérennité des données est une préoccupation majeure dans tout système de gestion de bases de données. Pour MySQL 8.0, plusieurs approches permettent de sauvegarder efficacement vos informations et d'assurer leur récupérabilité.

1. Sauvegardes Logiques via mysqldump

L'utilitaire mysqldump est l'outil officiel et historique de MySQL pour réaliser des sauvegardes logiques, c'est-à-dire l'exportation du schéma et des données sous forme d'instructions SQL.

Exportation d'une base de données unique

mysqldump -u votre_utilisateur -p nom_de_la_base > fichier_sauvegarde_base.sql

Cette commande générera un fichier SQL contenant toutes les instructions nécessaires pour recréer la base de données spécifiée. Le paramètre -u est pour le nom d'utilisateur, et -p pour demander le mot de passe.

Exportation de plusieurs bases de données

mysqldump -u votre_utilisateur -p --databases base1 base2 > fichier_sauvegarde_multi_bases.sql

Exportation de toutes les bases de données

mysqldump -u votre_utilisateur -p --all-databases > fichier_sauvegarde_complete.sql

Exportation de la structure (schéma) uniquement

mysqldump -u votre_utilisateur -p --no-data nom_de_la_base > fichier_structure_base.sql

Cette option est utile lorsque vous souhaitez uniquement sauvegarder la définition des tables, des vues, des procédures, etc., sans les données.

2. Sauvegardes Parallèles avec mysqlpump

Introduit avec MySQL 5.7 et amélioré en 8.0, mysqlpump est un utilitaire de sauvegarde plus moderne qui supporte l'exportation parallèle, ce qui accélère considérablement le processus pour les grandes bases de données.

Exportation d'une base de données unique

mysqlpump -u votre_utilisateur -p nom_de_la_base > fichier_export_base_rapide.sql

Exportation de plusieurs bases de données

mysqlpump -u votre_utilisateur -p --databases base1 base2 > fichier_export_multi_bases_rapide.sql

3. Sauvegardes Physiques

Les sauvegardes physiques consistent à copier directement les ficihers de données de MySQL (fichiers .frm, .ibd, etc.) depuis le système de fichiers. Elles sont souvent plus rapides pour des volumes importants et sont réalisées à l'aide d'outils comme Percona XtraBackup ou par simple copie lorsque le serveur MySQL est arrêté.

Processus de Restauration des Données MySQL

La restauration des données permet de remettre une base de données dans un état antérieur à partir d'un fichier de sauvegarde.

1. Restauration à partir de fichiers SQL (mysqldump ou mysqlpump)

Les fichiers SQL générés par mysqldump ou mysqlpump peuvent être exécutés directement via le client MySQL.

Restauration d'une base de données spécifique

mysql -u votre_utilisateur -p nom_de_la_base < fichier_sauvegarde_base.sql

Si le fichier SQL contient plusieurs bases de données (par exemple, un fichier généré avec --all-databases), vous n'avez pas besoin de spécifier de nom de base après le mot de passe :

mysql -u votre_utilisateur -p < fichier_sauvegarde_complete.sql

2. Restauration après une Sauvegarde Physique

La restauratoin d'une sauvegarde physique est plus complexe et spécifique à l'outil utilisé. Généralement, elle implique les étapes suivantes :

1. Arrêter le service MySQL.
2. Décompresser et préparer les fichiers de sauvegarde (avec XtraBackup, il faut appliquer les logs de transaction).
3. Remplacer les répertoires de données existants de MySQL par ceux de la sauvegarde.
4. Ajuster les permissions des fichiers si nécessaire.
5. Redémarrer le service MySQL.

Considérations Essentielles pour la Gestion des Sauvegardes

• Cohérence des données : Pour garantir l'intégrité des données pendant une sauvegarde logique, il est recommandé de verrouiller les tables en mode lecture. Cela peut être fait avec la commande : ``` FLUSH TABLES WITH READ LOCK;

  
   N'oubliez pas de déverrouiller après la sauvegarde.
  

• Espace disque : Assurez-vous que l'emplacement de destination dispose de suffisamment d'espace pour stocker les fichiers de sauvegarde, qui peuvent être volumineux.
• Fréquence et stratégie : Définissez une politique de sauvegarde régulière adaptée à vos besoins. Pensez également à la sauvegarde hors site et à la rétention des différentes versions.
• Sécurité des sauvegardes : Chiffrez vos fichiers de sauvegarde, surtout s'ils contiennent des données sensibles, et protégez les clés de chiffrement.

Gestion des Accès Utilisateur Root dans MySQL 8.0

La configuration des permissions pour l'utilisateur root est cruciale pour la sécurité et l'administration de votre serveur MySQL.

1. Autoriser l'accès de l'utilisateur root depuis N'importe Quelle Hôte

Pour permettre à l'utilisateur root de se connecter depuis n'importe quelle adresse IP (représentée par %), suivez ces étapes :

Connexion à MySQL

mysql -u root -p

Octroi des privilèges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mon_mot_de_passe_fort' WITH GRANT OPTION;

• *.* signifie tous les schémas et toutes les tables.
• 'root'@'%' indique l'utilisateur root depuis n'importe quelle adresse IP.
• IDENTIFIED BY 'mon_mot_de_passe_fort' définit ou met à jour le mot de passe.
• WITH GRANT OPTION permet à root de déléguer ces privilèges à d'autres utilisateurs.

Actualisation des privilèges

FLUSH PRIVILEGES;

Cette commande est essentielle pour que les changements prennent effet immédiatement.

Vérification des permissions

SHOW GRANTS FOR 'root'@'%';

Configuration du serveur MySQL (si nécessaire)

Dans le fichier de configuration de MySQL (généralement my.cnf ou my.ini), assurez-vous que la ligne bind-address est configurée pour écouter sur toutes les interfaces réseau, ou est commentée :

bind-address = 0.0.0.0

Après modification, redémarrez le service MySQL :

sudo systemctl restart mysql

Ouverture du port dans le pare-feu

Assurez-vous que le port 3306 (port MySQL par défaut) est ouvert dans le pare-feu de votre serveur. Pour ufw (Ubuntu) :

sudo ufw allow 3306/tcp

2. Restreindre l'accès de root à une Plage d'Adresses IP Spécifique

Pour une sécurité accrue, vous pouvez limiter la connexion de root à une plage d'IP spécifique, par exemple 172.16.0.0/16 (représentée par 172.16.%).

Connexion à MySQL

mysql -u root -p

Octroi des privilèges

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.%' IDENTIFIED BY 'mon_mot_de_passe_securise' WITH GRANT OPTION;

Ici, 'root'@'172.16.%' signifie que l'utilisateur root ne peut se connecter que depuis une IP commençant par 172.16..

Actualisation des privilèges

FLUSH PRIVILEGES;

Vérification des permissions

SHOW GRANTS FOR 'root'@'172.16.%';

Configuration du pare-feu

Configurez votre pare-feu pour autoriser les connexions au port 3306 uniquement depuis la plage d'adresses IP spécifiée. Pour ufw :

sudo ufw allow from 172.16.0.0/16 to any port 3306 proto tcp