Comment sauvegarder les bases de données MySQL avec 5 méthodes différentes ?

Au fur et à mesure que de plus en plus de données sont stockées dans la base de données, la sauvegarde des données devient de plus en plus importante. La sauvegarde de la base de données est une tâche très critique pour s'assurer que les entreprises ou les particuliers ne subissent pas de grosses pertes en raison d'une perte de données. MySQL est un système de gestion de base de données relationnelle open source populaire, largement utilisé dans les entreprises. Cet article discutera de la façon de sauvegarder la base de données MySQL.

Types de sauvegarde de base de données MySQL

Selon la taille de la base de données, il existe quatre types de sauvegardes, qui sont utilisés dans différentes situations :

Sauvegarde complète

Ceci est une méthode courante utilisée par la plupart des personnes. Elle peut sauvegarder l'ensemble de la base de données, y compris tous les objets de base de données tels que les tables utilisateur, les tables système, les index, les vues et les procédures stockées. Cependant, cela prend plus de temps et d'espace, donc il est généralement recommandé de faire une sauvegarde complète une fois par semaine.

Sauvegarde du journal des transactions

Le journal des transactions est un fichier séparé qui enregistre les modifications apportées à la base de données. Les sauvegardes n'ont besoin de copier que les modifications apportées à la base de données depuis la dernière sauvegarde, donc cela prend très peu de temps. Pour la robustesse de la base de données, il est recommandé de faire des sauvegardes du journal des transactions à l'heure ou même plus fréquemment.

Sauvegarde différentielle

C'est une autre méthode de sauvegarde partielle de la base de données, elle n'utilise pas le journal des transactions, mais plutôt une nouvelle image de l'ensemble de la base de données. Elle est plus petite que la sauvegarde complète d'origine car elle ne contient que les bases de données qui ont changé depuis la dernière sauvegarde complète. Elle a l'avantage d'une stockage et d'une récupération rapides. Il est recommandé de faire une sauvegarde différentielle tous les jours.

Sauvegarde de fichier

Une base de données peut être constituée de nombreux fichiers sur le disque dur. Si la base de données est très volumineuse et ne peut pas être sauvegardée en une nuit, des sauvegardes de fichiers peuvent être utilisées pour sauvegarder une partie de la base de données chaque nuit. Comme les bases de données ne sont généralement pas si volumineuses qu'il faille utiliser plusieurs magasins de fichiers, ce type de sauvegarde n'est pas très courant.

Selon l'état de la base de données, elle peut être divisée en trois types :

1. Sauvegarde froide

Les sauvegardes froides sont des sauvegardes effectuées sur les données lorsque la base de données est hors ligne et inaccessible aux utilisateurs. Comme les sauvegardes sont effectuées alors que la base de données est hors ligne, les sauvegardes froides sont également souvent appelées sauvegardes hors ligne. Les sauvegardes froides consomment généralement moins de ressources et comme aucune nouvelle donnée ne peut être ajoutée (la base de données est hors ligne), le processus de sauvegarde peut sauvegarder toutes les données en une seule fois. Bien sûr, les sauvegardes froides ont une limitation dans le fait que la base de données ne peut pas être accessible lorsque l'opération de sauvegarde est en cours.

2. Sauvegarde chaude

Les sauvegardes à chaud sont des sauvegardes effectuées sur les données alors que la base de données est en ligne et accessible aux utilisateurs. Les sauvegardes à chaud sont souvent une solution très pratique car elles n'entrainent pas d'indisponibilité et la perturbation du système d'exploitation est très minime, mais elles présentent également certains inconvénients : si les données qui sont sauvegardées sont modifiées de quelque manière que ce soit pendant qu'une sauvegarde est en cours, la sauvegarde pourrait ne pas être identique à la version finale des données dans la base de données.

3. Sauvegarde logique

La sauvegarde logique utilise un logiciel pour extraire des données d'une base de données et écrit les résultats dans un fichier.

Comment sauvegarder les bases de données MySQL ?

Il existe des sauvegardes logiques et physiques pour MySQL selon la méthode de récupération. La sauvegarde logique consiste à sauvegarder des instructions SQL et à les exécuter lors de la récupération pour recréer les données de la base de données. La sauvegarde physique copie les fichiers de la base de données dans le même format qu'ils sont stockés sur le disque.

Ces deux types de sauvegardes ont leurs propres avantages et inconvénients. En général, la vitesse de récupération de la sauvegarde physique est plus rapide mais elle prend plus d'espace, tandis que la vitesse de la sauvegarde logique est plus lente mais elle prend moins d'espace.

1. Utilisation de mysqldump

Mysqldump est un outil de sauvegarde qui vient avec MySQL, dans le répertoire bin : /usr/local/mysql/bin/mysqldump, qui prend en charge la sauvegarde chaude basée sur innoDB. Cependant, comme il s'agit d'une sauvegarde logique, la vitesse n'est pas très rapide et elle convient aux scénarios où les données à sauvegarder sont relativement petites. La sauvegarde complète avec mysqldump + journal binaire permet de réaliser une restauration à un instant précis.

Pour les tables du moteur de stockage MyISAM, seule la sauvegarde chaude peut être utilisée. À ce moment-là, l'écriture de données doit être empêchée, il faut donc d'abord ajouter un verrou de lecture. À ce moment-là, vous pouvez également entrer dans la base de données pour ajouter manuellement un verrou de lecture, mais c'est fastidieux. Vous pouvez directement avoir une option de verrouillage dans l'outil mysqldump, qui est --lock-all-tables, par exemple mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

Si vous sauvegardez une seule table, ajoutez simplement le nom de la table après le nom de la bibliothèque test.

Pour les tables du moteur de stockage InnoDB, une sauvegarde chaude peut être effectuée sans verrouiller la base de données. Ajoutez une option pour effectuer une sauvegarde chaude : --single-transaction, par exemple : mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.S. N'oubliez pas de fermer le journal binaire lors de la restauration :

mysql> set sql_log_bin=0;

Parce que cela repose sur une méthode de sauvegarde logique, l'exécution de SQL insérera des données et les enregistrera dans le journal binaire. Comme il s'agit d'une récupération, l'enregistrement du journal binaire peut être désactivé pour raccourcir le temps de récupération.

Cliquez et découvrez plus sur la sauvegarde et la récupération de la base de données MySQL avec ou sans Mysqldump.

2. Sauvegarde basée sur une instantanée LVM

En ce qui concerne la sauvegarde physique, il existe une sauvegarde physique basée sur le système de fichiers (instantanée LVM), et vous pouvez également utiliser directement des commandes telles que tar pour empaqueter et sauvegarder l'ensemble du répertoire de la base de données, mais celles-ci ne peuvent être utilisées que pour une sauvegarde à froid. Différents moteurs de stockage sauvegardent différents contenus. MyISAM sauvegarde automatiquement au niveau de la table, tandis qu'innoDB ne peut sauvegarder que l'ensemble de la base de données s'il n'active pas l'espace de tables indépendant.

Le suivant est une introduction à l'utilisation de la fonction de snapshot de LVM pour la sauvegarde.

1) Pour la sécurité, commencez par imposer un verrou de lecture sur la base de données

mysql>FLUSH TABLES WITH READ LOCK

2) Rafraîchissez le journal binaire pour la récupération à un instant donné

mysql>FLUSH LOGS

3) Ensuite créez le volume de la capture instantanée

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) Entrez dans la base de données pour libérer le verrou en lecture

UNLOCK TABLES

5) Monter le volume de snapshot pour la sauvegarde

mount –r /dev/myvg/data-snap /mnt/snap

6) Enfin, empaquetez et sauvegardez les fichiers situés sous /mnt/snap

Pour la restauration :

1) fermer mysqld

2) puis sauvegardez le journal binaire

3) restaurez le fichier de sauvegarde d'origine

4) puis restaurez jusqu'au point dans le temps de l'erreur à travers le journal binaire (n'oubliez pas de fermer temporairement le journal binaire lors de la restauration du point dans le temps via le binaire)

Entre 2010 et 2012 la base de données MySQL a été déployée dans l'environnement cloud d'Amazon Ils ont fourni des instantanés LVM qui sont très pratiques et rapides Avec les instantanés LVM la récupération est également extrêmement rapide sur le réseau 10G d'Amazon

3. Sauvegarde tar

Préparez-vous pour la première sauvegarde de données à partir de la base de données, verrouillez temporairement toutes les tables et ouvrez la fenêtre 1

mysql> flush tables with read lock;

Requête OK, 0 lignes affectées (0.00 sec)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

Cette fenêtre ne peut pas être fermée et doit être maintenue jusqu'à ce que le paquet tar soit terminé.

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)
mysql>

Copiez le paquet tar sur un autre serveur de bibliothèque MySQL, écrasez le répertoire de données, puis redémarrez le service de base de données MySQL.

4. xTraBackup fourni par Percona

Il prend en charge la sauvegarde physique à chaud d'innoDB, prend en charge les sauvegardes complètes et incrémentielles, et la vitesse est très rapide, prend en charge la migration de données entre différentes bases de données causées par le stockage innoDB, et prend en charge la sauvegarde et la restauration des esclaves en mode de réplication. Pour que xTraBackup prenne en charge plus d'extensions fonctionnelles, vous pouvez configurer un espace de table indépendant et activer la fonction innoDB_file_per_table. Ensuite, vous pouvez prendre en charge les sauvegardes individuelles de tables.

Principe de la sauvegarde xTraBackup :

xTraBackup est basé sur la fonction de récupération après plantage d'innoDB. Il copiera le fichier de données d'innoDB. Comme la table n'est pas verrouillée, les données copiées sont incohérentes. Lors de la récupération, utilisez la récupération après plantage pour rendre les données cohérentes.

innoDB maintient un journal de redo, également connu sous le nom de journal de transactions, qui contient toutes les modifications apportées aux données innoDB. Lorsque innoDB démarre, il vérifiera d'abord le fichier de données et le journal de transactions, puis effectuera les étapes suivantes :

Lorsque xTraBackup effectue une sauvegarde, il copie les pages de données InnoDB une par une sans verrouiller la table. En même temps, xTraBackup a un autre thread pour surveiller le journal des transactions. Dès que le journal change, il copie les pages de journal modifiées. Comme la taille du fichier de journal des transactions est limitée, il commence à écrire à partir du début une fois qu'il est plein, donc les nouvelles données peuvent écraser les anciennes données.

Lors du processus de préparation, xTraBackup utilise le journal de transactions copié pour effectuer une récupération après un plantage sur le fichier de données InnoDB sauvegardé.

5. Sauvegarde des bases de données MySQL avec Vinchin Backup & Recovery

Vinchin Backup & Recovery offre des fonctionnalités puissantes pour protéger vos bases de données tant dans les machines virtuelles que dans les serveurs physiques. En coopérant bien avec les sauvegardes au niveau des machines virtuelles, une double assurance est fournie aux utilisateurs d'environnements virtuels pour leurs données et systèmes d'information clés. Vinchin Backup & Recovery prend en charge la protection de Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro et MariaDB installés à la fois sur des machines physiques et virtuelles, avec des fonctionnalités puissantes de sauvegarde et de restauration de bases de données. Il fournit également des stratégies de sauvegarde complète, différentielle, incrémentielle et de journal de transactions pour vous permettre de définir votre propre plan de sauvegarde selon vos besoins.

Vinchin Backup & Recovery prend en charge la sauvegarde chaude efficace de MySQL, SQL Server et Oracle sans affecter le fonctionnement normal des bases de données.

L'opération de Vinchin Backup & Recovery est très simple, il suffit de quelques étapes simples. 

1. Sélectionnez l'objet de la sauvegarde.

2. Sélectionnez la destination de la sauvegarde.

3. Configurez les stratégies de sauvegarde.

4. Vérifiez et soumettez le travail.

Vous pouvez commencer à utiliser ce système puissant avec une période d'essai gratuite de 60 jours avec toutes les fonctionnalités. Cliquez simplement sur le bouton pour obtenir le package d'installation. Vous pouvez cliquer ici pour en savoir plus sur comment sauvegarder MySQL avec Vinchin Backup & Recovery.

Résumé

La base de données MySQL est largement utilisée dans de nombreuses industries. Pour garantir sa sécurité, nous partageons avec vous cinq méthodes différentes de sauvegarde des données MySQL dans cet article. Vous pouvez choisir une méthode plus adaptée en fonction de votre situation réelle. Alors, à quoi attendez-vous ? Vite, faites une sauvegarde de votre base de données MySQL !