1 février 2012 à 00:33

Sauvegarde données dans MySQL

• MySQL

La sauvegarde de la base de données est une chose qui doit toujours être configurée pour les projets déjà en cours d'exécution directement sur les serveurs de production « en direct ».
Cette situation s’explique facilement. Au tout début, tout projet est encore vide et il n'y a tout simplement rien à copier. Dans la phase de développement rapide, les chefs de quelques développeurs se consacrent exclusivement au vissage des copeaux et des fioritures, ainsi qu'à la fixation bogues critiques avec une date limite d'avant-hier. Et ce n'est que lorsque le projet « décolle » que l'on se rend compte que la valeur principale du système est la base de données accumulée et que son échec sera un désastre.
Cet article de synthèse s'adresse à ceux dont les projets ont déjà atteint ce point, mais le coq rôti n'a pas encore mordu à l'hameçon.

1. Copie des fichiers de base de données

Base Données MySQL peut être copié si vous éteignez temporairement le serveur MySQL et copiez simplement les fichiers du dossier /var/lib/mysql/db/. Si le serveur n'est pas éteint, pour des raisons évidentes, une perte et une corruption des données sont probables. Pour les grandes bases de données chargées, cette probabilité est proche de 100 %. De plus, la première fois que vous démarrez avec une copie « sale » de la base de données, le serveur MySQL lancera le processus de vérification de l'intégralité de la base de données, ce qui peut prendre des heures.

Dans la plupart des projets en direct, arrêter régulièrement le serveur de base de données pendant une longue période est inacceptable. Pour résoudre ce problème, une astuce basée sur les instantanés du système de fichiers est utilisée. Un instantané est quelque chose comme une « photographie » d'un système de fichiers à un moment précis, prise sans copie réelle données (et donc rapidement). La copie paresseuse d'objets fonctionne de la même manière dans de nombreux langages de programmation modernes.
Le schéma général des actions est le suivant : toutes les tables sont verrouillées, le cache des fichiers de base de données est réinitialisé, un instantané du système de fichiers est pris et les tables sont déverrouillées. Après cela, les fichiers sont copiés silencieusement à partir de l'instantané, après quoi celui-ci est détruit. La partie « blocage » d’un tel processus prend environ quelques secondes, ce qui est déjà tolérable. En guise de pénalité pendant un certain temps, pendant que l'instantané est « vivant », les performances diminuent opérations sur les fichiers, ce qui affecte principalement la vitesse des opérations d'écriture dans la base de données.

Quelques systèmes de fichiers, par exemple, ZFS, prend en charge la prise d'instantanés de manière native. Si vous n'utilisez pas ZFS, mais disposez d'un gestionnaire de volumes LVM sur votre serveur, vous pouvez également copier la base de données MySQL via un instantané. Enfin, sous *nix, vous pouvez utiliser le pilote d'instantané R1Soft Hot Copy, mais cette méthode ne fonctionnera pas dans le conteneur openvz().

Pour les bases de données MyISAM, il existe un officiel utilitaire gratuit mysqlhotcopy , qui copie « correctement » les fichiers de la base de données MyISAM sans arrêter le serveur. Il existe un utilitaire similaire pour InnoDB, mais il est payant, bien qu'il ait plus de fonctionnalités.

La copie de fichiers est la chose la plus façon rapide transférer l'intégralité de la base de données d'un serveur à un autre.

2. Copie via des fichiers texte

Afin de compter dans données de sauvegarde de la base de données de production, il n'est pas nécessaire d'extraire les fichiers. Vous pouvez sélectionner des données à l'aide d'une requête et les enregistrer dans un fichier texte. Pour cela, utilisez la commande SQL SELECT INTO OUTFILE et son couple LOAD DATA INFILE . Le déchargement s'effectue ligne par ligne (vous pouvez sélectionner uniquement les lignes nécessaires à la sauvegarde, comme dans un SELECT classique). La structure des tables n'est spécifiée nulle part - le programmeur doit s'en occuper. Il doit également veiller à inclure les commandes SELECT INTO OUTFILE dans la transaction si nécessaire pour garantir l'intégrité des données. En pratique, SELECT INTO OUTFILE est utilisé pour la sauvegarde partielle de très grandes tables qui ne peuvent être copiées d'aucune autre manière.

Dans la plupart des cas, l'utilitaire mysqldump créé par Igor Romanenko est beaucoup plus pratique. L'utilitaire mysqldump génère un fichier contenant toutes les commandes SQL nécessaires à récupération complète DB sur un autre serveur. En utilisant des options distinctes, vous pouvez rendre ce fichier compatible avec presque tous les SGBD (pas seulement MySQL) ; de plus, il est possible de télécharger des données aux formats CSV et XML. Pour récupérer des données à partir de tels formats, il existe un utilitaire appelé mysqlimport.

L'utilitaire de console mysqldump. Il existe des modules complémentaires et analogues qui permettent de gérer la sauvegarde via une interface Web, par exemple l'outil ukrainien Sypex Dumper (leur représentant est sur Habré).

Défauts utilitaires universels sauvegarde dans fichiers texte- il s'agit d'une vitesse de fonctionnement relativement faible et de l'impossibilité d'effectuer des sauvegardes incrémentielles.

3. Sauvegardes incrémentielles

Traditionnellement, il est recommandé de conserver 10 sauvegardes : une pour chaque jour de la semaine, ainsi que des sauvegardes d'il y a deux semaines, un mois et un quart - cela vous permettra de revenir en arrière assez profondément en cas de corruption de données.
Les sauvegardes ne doivent absolument pas être stockées sur le même disque que la base de données active, ni sur le même serveur. En cas d'incendies et autres catastrophes, il est préférable de louer quelques unités dans un centre de données à proximité.

Ces exigences peuvent poser problème pour les bases de données volumineuses. Le téléchargement d'une sauvegarde d'une base de données de 100 Go sur un réseau de 100 Mbits prendra environ trois heures, pendant lesquelles le canal sera complètement obstrué.
Les sauvegardes incrémentielles peuvent résoudre partiellement ce problème lorsque sauvegarde complète Cela se fait, par exemple, uniquement le dimanche, et les autres jours, seules les données ajoutées ou modifiées au cours de la journée écoulée sont écrites. La difficulté est de savoir comment identifier ces « données qui ont changé au cours de la journée ».

Ici, le système Percona XtraBackup, qui contient un moteur InnoDB modifié, analyse les journaux binaires MySQL et extrait information nécessaire. La sauvegarde à chaud InnoDB payante mentionnée ci-dessus a presque les mêmes capacités.

Le problème général avec toutes les sauvegardes est qu'elles sont toujours en retard. En cas de panne fatale du serveur principal, il ne sera possible de restaurer le système qu'avec un certain « rollback » dans le temps, ce qui décevra très, très ses utilisateurs. Si les flux financiers dans le système étaient affectés d’une manière ou d’une autre, un tel « pot-de-vin » peut littéralement coûter une somme considérable.

4. Réplication

Le système de réplication MySQL est conçu pour éviter les restaurations. L'idée de la réplication repose sur le fait qu'en plus du serveur « principal » (« Maître »), des serveurs MySQL (« esclaves ») fonctionnent en permanence, qui reçoivent des sauvegardes incrémentielles du maître en temps réel. Ainsi, le temps de restauration est réduit presque au décalage du réseau. Si le maître échoue, vous pouvez rapidement désigner l'un des esclaves comme « nouveau maître » et y rediriger les clients. De plus, les esclaves peuvent traiter les demandes de lecture de données (SELECT) ; cela peut être utilisé pour effectuer certains calculs ou réduire la charge sur le maître. MySQL prend en charge la réplication prête à l'emploi, le processus est bien décrit par l'utilisateur

La sauvegarde de la base de données est une chose qui doit toujours être configurée pour les projets déjà en cours d'exécution directement sur les serveurs de production « en direct ».
Cette situation s’explique facilement. Au tout début, tout projet est encore vide et il n'y a tout simplement rien à copier. Dans la phase de développement rapide, les dirigeants de quelques développeurs sont exclusivement occupés à ajouter des fonctionnalités et des superflus, ainsi qu'à corriger les bugs critiques dans un délai « avant-hier ». Et ce n'est que lorsque le projet « décolle » que l'on se rend compte que la valeur principale du système est la base de données accumulée et que son échec sera un désastre.
Cet article de synthèse s'adresse à ceux dont les projets ont déjà atteint ce point, mais le coq rôti n'a pas encore mordu à l'hameçon.

1. Copie des fichiers de base de données

La base de données MySQL peut être copiée si vous éteignez temporairement le serveur MySQL et copiez simplement les fichiers du dossier /var/lib/mysql/db/. Si le serveur n'est pas éteint, pour des raisons évidentes, une perte et une corruption des données sont probables. Pour les grandes bases de données chargées, cette probabilité est proche de 100 %. De plus, la première fois que vous démarrez avec une copie « sale » de la base de données, le serveur MySQL lancera le processus de vérification de l'intégralité de la base de données, ce qui peut prendre des heures.

Dans la plupart des projets en direct, arrêter régulièrement le serveur de base de données pendant une longue période est inacceptable. Pour résoudre ce problème, une astuce basée sur les instantanés du système de fichiers est utilisée. Un instantané est quelque chose comme une « photo » du système de fichiers à un moment donné, prise sans réellement copier les données (et donc rapidement). La copie paresseuse d'objets fonctionne de la même manière dans de nombreux langages de programmation modernes.
Le schéma général des actions est le suivant : toutes les tables sont verrouillées, le cache des fichiers de base de données est réinitialisé, un instantané du système de fichiers est pris et les tables sont déverrouillées. Après cela, les fichiers sont copiés silencieusement à partir de l'instantané, après quoi celui-ci est détruit. La partie « blocage » d’un tel processus prend environ quelques secondes, ce qui est déjà tolérable. En guise de récompense, pendant un certain temps, pendant que l'instantané est « vivant », les performances des opérations sur les fichiers diminuent, ce qui affecte principalement la vitesse des opérations d'écriture dans la base de données.

Certains systèmes de fichiers, tels que ZFS, prennent en charge la prise d'instantanés de manière native. Si vous n'utilisez pas ZFS, mais disposez d'un gestionnaire de volumes LVM sur votre serveur, vous pouvez également copier la base de données MySQL via un instantané. Enfin, sous *nix, vous pouvez utiliser le pilote d'instantané R1Soft Hot Copy, mais cette méthode ne fonctionnera pas dans le conteneur openvz().

Pour les bases de données MyISAM, il existe un utilitaire gratuit officiel mysqlhotcopy, qui copie « correctement » les fichiers de la base de données MyISAM sans arrêter le serveur. Il existe un utilitaire similaire pour InnoDB, mais il est payant, bien qu'il ait plus de fonctionnalités.

La copie de fichiers est le moyen le plus rapide de transférer une base de données entière d'un serveur à un autre.

2. Copie via des fichiers texte

Afin de lire les données de la base de données de production vers une sauvegarde, il n'est pas nécessaire d'extraire les fichiers. Vous pouvez sélectionner des données à l'aide d'une requête et les enregistrer dans un fichier texte. Pour cela, utilisez la commande SQL SELECT INTO OUTFILE et son couple LOAD DATA INFILE . Le déchargement s'effectue ligne par ligne (vous pouvez sélectionner uniquement les lignes nécessaires à la sauvegarde, comme dans un SELECT classique). La structure des tables n'est spécifiée nulle part - le programmeur doit s'en occuper. Il doit également veiller à inclure les commandes SELECT INTO OUTFILE dans la transaction si nécessaire pour garantir l'intégrité des données. En pratique, SELECT INTO OUTFILE est utilisé pour la sauvegarde partielle de très grandes tables qui ne peuvent être copiées d'aucune autre manière.

Dans la plupart des cas, l'utilitaire mysqldump créé par Igor Romanenko est beaucoup plus pratique. L'utilitaire mysqldump génère un fichier contenant toutes les commandes SQL nécessaires pour restaurer complètement une base de données sur un autre serveur. En utilisant des options distinctes, vous pouvez rendre ce fichier compatible avec presque tous les SGBD (pas seulement MySQL) ; de plus, il est possible de télécharger des données aux formats CSV et XML. Pour récupérer des données à partir de tels formats, il existe un utilitaire appelé mysqlimport.

L'utilitaire de console mysqldump. Il existe des modules complémentaires et analogues qui permettent de gérer la sauvegarde via une interface Web, par exemple l'outil ukrainien Sypex Dumper (leur représentant zapimir est sur Habré).

Les inconvénients des utilitaires de sauvegarde universels pour les fichiers texte sont la vitesse de fonctionnement relativement faible et l'incapacité d'effectuer des sauvegardes incrémentielles.

3. Sauvegardes incrémentielles

Traditionnellement, il est recommandé de conserver 10 sauvegardes : une pour chaque jour de la semaine, ainsi que des sauvegardes d'il y a deux semaines, un mois et un quart - cela vous permettra de revenir en arrière assez profondément en cas de corruption de données.
Les sauvegardes ne doivent absolument pas être stockées sur le même disque que la base de données active, ni sur le même serveur. En cas d'incendies et autres catastrophes, il est préférable de louer quelques unités dans un centre de données à proximité.

Ces exigences peuvent poser problème pour les bases de données volumineuses. Le téléchargement d'une sauvegarde d'une base de données de 100 Go sur un réseau de 100 Mbits prendra environ trois heures, pendant lesquelles le canal sera complètement obstrué.
Les sauvegardes incrémentielles peuvent résoudre partiellement ce problème, lorsqu'une sauvegarde complète est effectuée, par exemple, uniquement le dimanche, et que les autres jours, seules les données ajoutées ou modifiées au cours de la journée écoulée sont écrites. La difficulté est de savoir comment identifier ces « données qui ont changé au cours de la journée ».

Ici, le système Percona XtraBackup, qui contient un moteur InnoDB modifié, analyse les journaux binaires MySQL et en extrait les informations nécessaires, est pratiquement inégalé. La sauvegarde à chaud InnoDB payante mentionnée ci-dessus a presque les mêmes capacités.

Le problème général avec toutes les sauvegardes est qu'elles sont toujours en retard. En cas de panne fatale du serveur principal, il ne sera possible de restaurer le système qu'avec un certain « rollback » dans le temps, ce qui décevra très, très ses utilisateurs. Si les flux financiers dans le système étaient affectés d’une manière ou d’une autre, un tel « pot-de-vin » peut littéralement coûter une somme considérable.

4. Réplication

Le système de réplication MySQL est conçu pour éviter les restaurations. L'idée de la réplication repose sur le fait qu'en plus du serveur « principal » (« Maître »), des serveurs MySQL (« esclaves ») fonctionnent en permanence, qui reçoivent des sauvegardes incrémentielles du maître en temps réel. Ainsi, le temps de restauration est réduit presque au décalage du réseau. Si le maître échoue, vous pouvez rapidement désigner l'un des esclaves comme « nouveau maître » et y rediriger les clients. De plus, les esclaves peuvent traiter les demandes de lecture de données (SELECT) ; cela peut être utilisé pour effectuer certains calculs ou réduire la charge sur le maître. MySQL prend en charge la réplication prête à l'emploi ; le processus de configuration de la réplication dans MySQL est bien décrit par l'utilisateur

Malgré le fait que dans nos documents précédents, nous avons déjà abordé la question de la sauvegarde des bases de données Microsoft serveur SQL, la réponse du lecteur a montré la nécessité de créer un matériel à part entière avec une étude plus approfondie de la partie théorique. En effet, réalisé en mettant l'accent sur instructions pratiques Les articles permettent de mettre en place rapidement des sauvegardes, mais n'expliquent pas les raisons du choix de certains paramètres. Essayons de corriger cet écart.

Modèles de récupération

Avant de configurer une sauvegarde, vous devez sélectionner un modèle de récupération. Pour choix optimal vous devez évaluer les exigences de récupération et le caractère critique de la perte de données, en les comparant aux frais généraux liés à la mise en œuvre d'un modèle particulier.

Comme vous le savez, la base de données MS SQL se compose de deux parties : la base de données elle-même et le journal des transactions correspondant. La base de données contient les données des utilisateurs et des services à l'heure actuelle, le journal des transactions comprend l'historique de toutes les modifications de la base de données au cours du passé. certaine période, disposant d'un journal des transactions, nous pouvons restaurer l'état de la base de données à n'importe quel moment arbitraire.

Il existe deux modèles de récupération disponibles pour une utilisation dans les environnements de production : simple et complet. Il existe également un modèle avec journalisation incomplète, mais il n'est recommandé qu'en complément du modèle complet pour les périodes d'opérations de masse à grande échelle lorsqu'il n'est pas nécessaire de restaurer la base à un moment donné.

Modèle simple prévoit la sauvegarde uniquement de la base de données ; par conséquent, nous pouvons restaurer l'état de la base de données uniquement au moment de la création de la sauvegarde ; toutes les modifications intervenues dans la période comprise entre la création de la dernière sauvegarde et l'échec seront perdues. Dans le même temps circuit simple a une faible surcharge : il vous suffit de stocker des copies de la base de données ; le journal des transactions est automatiquement tronqué et n'augmente pas en taille. De plus, le processus de récupération est le plus simple et ne prend pas beaucoup de temps.

Modèle complet vous permet de restaurer la base de données à n'importe quel moment arbitraire, mais nécessite, en plus des sauvegardes de la base de données, de stocker des copies du journal des transactions pendant toute la période pour laquelle la restauration peut être requise. Lorsque vous travaillez activement avec la base de données, la taille du journal des transactions et, par conséquent, la taille des archives peuvent atteindre des tailles importantes. Le processus de récupération est également beaucoup plus complexe et prend beaucoup de temps.

Lors du choix d'un modèle de récupération, vous devez comparer les coûts de récupération avec les coûts de stockage des sauvegardes, et vous devez également prendre en compte la disponibilité et les qualifications du personnel qui effectuera la récupération. La restauration avec un modèle complet nécessite que le personnel possède certaines qualifications et connaissances, alors qu'avec un schéma simple, il suffira de suivre les instructions.

Pour les bases de données avec une petite quantité d'informations ajoutées, il peut être plus rentable d'utiliser un modèle simple avec une fréquence de copies élevée, ce qui vous permettra de récupérer rapidement et de continuer à travailler en saisissant manuellement les données perdues. Le modèle complet doit être utilisé principalement lorsque la perte de données est inacceptable et lorsque la récupération éventuelle serait coûteuse.

Types de sauvegardes

Copie complète de la base de données- comme son nom l'indique, il représente le contenu de la base de données et une partie du journal des transactions actif au moment où la sauvegarde a été créée (c'est-à-dire des informations sur toutes les transactions en cours et incomplètes). Vous permet de restaurer complètement la base de données au moment où la sauvegarde a été créée.

Copie de la base de données Delta - copie complète en a un inconvénient majeur, il contient toutes les informations de la base de données. Si des sauvegardes doivent être effectuées assez souvent, la question du gaspillage de l'espace disque se pose immédiatement, puisque la majeure partie du stockage sera occupée par les mêmes données. Pour éliminer cet inconvénient, vous pouvez utiliser des copies différentielles de la base de données, qui contiennent uniquement ce qui a changé depuis la dernière copie complète information.

Veuillez noter qu'une copie différentielle est constituée des données de la dernière fois complet copier, c'est-à-dire Chaque copie différentielle suivante contient les données de la précédente (mais elles peuvent être modifiées) et la taille de la copie augmentera constamment. Pour restaurer, une copie complète et une copie différentielle, généralement la dernière, suffisent. Le nombre de copies différentielles doit être choisi en fonction de l'augmentation de leur taille ; dès que la taille de la copie différentielle est égale à la taille de la moitié de celle complète, il est logique de créer une nouvelle copie complète.

Sauvegarde du journal des transactions- s'applique uniquement au modèle de récupération complète et contient une copie du journal des transactions à partir du moment où la copie précédente a été créée.

Important à retenir l'instant suivant- les copies du journal des transactions ne sont en aucun cas liées aux copies de la base de données et ne contiennent pas d'informations sur les copies précédentes, donc pour restaurer la base de données, vous devez disposer d'une chaîne ininterrompue de copies pour la période pendant laquelle vous souhaitez pouvoir restaurer l'état de la base de données. Dans ce cas, le moment de la dernière copie réussie doit se situer dans ce délai.

Regardons la figure ci-dessus, si la première copie du fichier journal est perdue, alors vous ne pourrez restaurer l'état de la base de données qu'au moment de la copie complète, ce qui sera similaire au modèle de récupération simple ; vous pourra restaurer l'état de la base de données à tout moment seulement après la prochaine copie différentielle (ou complète), à ​​condition que la chaîne de copies de journaux commençant par celle précédant la copie de la base de données et au-delà soit continue (dans la figure - à partir du troisième et au-delà).

Journal des transactions

Comprendre les processus de récupération et d’affectation différents types sauvegardes, vous devriez examiner de plus près la structure et le fonctionnement du journal des transactions. La transaction est le minimum possible opération logique, ce qui est logique et ne peut être réalisé que complètement. Cette approche garantit l’intégrité et la cohérence des données dans toutes les situations, puisqu’un état intermédiaire de l’opération est inacceptable. Un journal des transactions est utilisé pour contrôler toute modification dans la base de données.

Lorsqu'une opération est effectuée, un enregistrement sur le début de la transaction est ajouté au journal des transactions, chaque enregistrement se voit attribuer un numéro unique (LSN) à partir d'une séquence ininterrompue, lorsque des données sont modifiées, une entrée correspondante est effectuée dans le journal, et une fois l'opération terminée, une marque indiquant que la transaction est fermée (corrigée) apparaît dans le journal.

À chaque démarrage, le système analyse le journal des transactions et annule toutes les transactions non validées, tout en annulant simultanément les modifications enregistrées dans le journal mais non écrites sur le disque. Cela permet d'utiliser la mise en cache et l'écriture différée sans se soucier de l'intégrité des données, même en l'absence de systèmes d'alimentation de secours.

La partie du journal qui contient les transactions actives et est utilisée pour la récupération des données est appelée partie active du journal. Il commence par un nombre appelé nombre minimum de récupération (MinLSN).

Dans le cas le plus simple, MinLSN est le numéro d'enregistrement de la première transaction en attente. Si vous regardez la figure ci-dessus, en ouvrant la transaction bleue nous obtiendrons un MinLSN égal à 321, après qu'il soit fixé dans l'enregistrement 324, le numéro MinLSN passera à 323, ce qui correspondra au numéro du vert, pas encore engagé, transaction.

En pratique, tout est un peu plus compliqué, par exemple, les données d'une transaction bleue fermée peuvent ne pas encore être vidées sur le disque et déplacer MinLSN vers 323 rendra impossible la récupération de cette opération. Afin d'éviter de telles situations, la notion de point de contrôle a été introduite. Un point de contrôle est créé automatiquement lorsque les conditions suivantes sont remplies :

• Lors de l'exécution explicite de l'instruction CHECKPOINT. Le point de contrôle est déclenché sur la base de données de connexion actuelle.
• Lorsque vous effectuez une opération avec journalisation minimale sur une base de données, par exemple lorsque vous effectuez une opération de copie en bloc sur une base de données soumise au modèle de récupération avec journalisation en masse.
• Lors de l'ajout ou de la suppression de fichiers de base de données à l'aide de l'instruction ALTER DATABASE.
• Lorsque vous arrêtez une instance de SQL Server à l'aide de l'instruction SHUTDOWN ou lorsque vous arrêtez le service SQL Server (MSSQLSERVER). Dans les deux cas, un point de contrôle sera créé pour chaque base de données de l'instance SQL Server.
• Si votre instance de SQL Server crée périodiquement des points de contrôle automatiques sur chaque base de données pour réduire le temps de récupération de la base de données.
• Lors de la création d'une sauvegarde de base de données.
• Lors de l'exécution d'une action nécessitant l'arrêt de la base de données. Les exemples incluent la définition du paramètre AUTO_CLOSE sur ON et la fermeture de la dernière connexion de l'utilisateur à la base de données, ou la modification d'un paramètre de base de données qui nécessite un redémarrage de la base de données.

Selon l'événement qui s'est produit en premier, MinLSN sera défini soit sur le numéro d'enregistrement du point de contrôle, soit sur le début de la transaction en attente la plus ancienne.

Troncation du journal des transactions

Le journal des transactions, comme tout journal, nécessite un nettoyage périodique des entrées obsolètes, sinon il grandira et occupera tout l'espace disponible. Étant donné que lorsque vous travaillez activement avec la base de données, la taille du journal des transactions peut dépasser considérablement la taille de la base de données, ce problème est pertinent pour de nombreux administrateurs.

Physiquement, le fichier journal des transactions est un conteneur pour les journaux virtuels, qui sont remplis séquentiellement à mesure que le journal grandit. Le journal logique contenant l'entrée MinLSN constitue le début du journal actif ; les journaux logiques qui le précèdent sont inactifs et ne sont pas requis pour récupération automatique socles.

Si sélectionné modèle simple récupération, puis lorsque les logs logiques atteignent une taille égale à 70% du fichier physique, nettoyage automatique partie inactive du journal, la soi-disant. troncature. Cependant, cela ne réduit pas le fichier journal physique ; cela tronque uniquement les journaux logiques, qui peuvent ensuite être réutilisés après cette opération.

Si le nombre de transactions est important et qu'au moment où 70 % de la taille physique du fichier est atteint, il n'y a aucun journal logique inactif, la taille physique du fichier sera augmentée.

Ainsi, le fichier journal des transactions avec un modèle de récupération simple grandira en fonction de l'activité de travail avec la base de données jusqu'à ce qu'il contienne de manière fiable toute la partie active du journal. Après quoi sa croissance s'arrêtera.

Avec le modèle complet, la partie inactive du journal ne peut pas être tronquée tant qu'elle n'est pas complètement sauvegardée. La troncation du journal se produit si le journal des transactions a été sauvegardé et qu'un point de contrôle a été créé.

Une configuration incorrecte de la sauvegarde du journal des transactions dans un modèle complet peut entraîner une croissance incontrôlée des fichiers journaux, ce qui constitue souvent un problème pour les administrateurs inexpérimentés. Vous rencontrez également souvent des conseils sur la troncature manuelle du journal des transactions. Avec un modèle de récupération complète, cela ne doit pas être fait de manière catégorique, car cela violerait l'intégrité de la chaîne de copies de journaux et vous ne pourrez restaurer la base de données qu'au moment de la création des copies, ce qui correspondra au modèle simple. .

Dans ce cas, il est temps de rappeler ce dont nous parlions au début de l’article : si les coûts d’un modèle complet dépassent les coûts de restauration, il faut privilégier un modèle simple.

Modèle de récupération simple

Maintenant, après avoir reçu le minimum requis connaissances, nous pouvons passer à un examen plus détaillé des modèles de rétablissement. Commençons par un simple. Disons qu'au moment de l'échec nous avons une copie complète et deux copies différentielles :

Les sauvegardes étaient effectuées une fois par jour et la dernière copie était créée dans la nuit du 21 au 22. L'échec survient le 22 au soir avant la création de la copie suivante. Dans ce cas, nous devrons restaurer séquentiellement les copies différentielles complètes et dernières, et les données du dernier jour ouvrable seront perdues. Si, pour une raison quelconque, la copie du 21 s'avère également endommagée, nous pouvons alors restaurer la copie précédente, perdant ainsi une autre journée de travail, tandis qu'en même temps, les dommages à la copie du 20 ne nous empêcheront en aucun cas de restaurer avec succès les données le 21 au soir, lorsque la copie correspondante est disponible.

Modèle de récupération complète

Considérons une situation similaire, mais en utilisant le modèle de récupération complète. Nous effectuons également des sauvegardes quotidiennement, selon le principe full + différentiel, et copions également le journal des transactions plusieurs fois par jour.

Le processus de récupération dans ce cas sera plus compliqué. Tout d'abord, vous devrez sauvegarder manuellement la fin du journal (indiquée en rouge), c'est-à-dire partie du journal depuis la création de la dernière copie jusqu'à l'accident.

Si cela n'est pas fait, il sera alors possible de restaurer la base de données uniquement dans l'état au moment où la dernière copie du journal des transactions a été créée.

Dans ce cas, l'endommagement du fichier de copie du journal de la veille ne nous empêchera pas de restaurer l'état actuel de la base de données, mais nous limitera au moment où la dernière copie a été créée, c'est-à-dire jours actuels.

Ensuite, nous restaurons séquentiellement les copies complètes et différentielles et la chaîne de copies du journal créée après la dernière sauvegarde, la dernière que nous restaurons est une copie du fragment final du journal, ce qui nous donnera la possibilité de restaurer correctement la base de données. au moment de l'accident ou à un accident arbitraire qui l'a précédé.

Si la dernière copie différentielle est endommagée, dans le cas d'un modèle simple, cela entraînera la perte d'un autre jour ouvrable, modèle complet vous permet de restaurer l'avant-dernière copie, après quoi vous devrez restaurer toute la chaîne de copies du journal des transactions depuis l'avant-dernière copie jusqu'à l'échec. La profondeur de la récupération dépend uniquement de la profondeur de la chaîne continue de grumes.

D'un autre côté, si l'une des copies du journal des transactions est endommagée, par exemple l'avant-dernière, nous pourrons alors restaurer les données uniquement au moment de la dernière sauvegarde + la période dans la chaîne intacte des copies du journal. Par exemple, si les journaux ont été réalisés à 12, 14 et 16 heures et que le journal créé à 14 heures est endommagé, alors en disposant d'une copie quotidienne, nous pouvons restaurer la base de données jusqu'à la fin de la chaîne continue, c'est-à-dire jusqu'à 12 heures.

• Mots clés:

Veuillez activer JavaScript pour afficher le

L'une des tâches les plus importantes qu'un administrateur de base de données doit constamment effectuer consiste à effectuer des sauvegardes. Si quelque chose ne va pas, il incombe à l'administrateur de base de données de remettre le serveur en marche le plus rapidement possible. Perte de productivité ou quoi pire que ça,La perte de données peut être très coûteuse pour une entreprise.

Si vous souhaitez disposer d'une sauvegarde de base de données à jour, vous souhaitez utiliser la configuration Disques RAID, qui prévoit la création d'un miroir à des fins de protection des données, mais ce n'est pas une panacée. Les matrices RAID ne constituent que la première étape dans la protection des données contre la perte. Selon la configuration de la matrice RAID, un, voire plusieurs disques durs doit échouer avant qu’une perte permanente de données ne se produise. De plus, l'utilisation de disques remplaçables à chaud et de rechange à chaud peut être utilisée pour garantir que le serveur continue de fonctionner sans s'arrêter, même en cas de panne. construire un dur disque. La chose clé que vous devez comprendre à propos de DBA est que Matrices RAID peut protéger les données en cas de panne du disque dur. Que se passe-t-il en cas d'urgence (incendie, inondation, vol, etc.) ? Les fichiers de la base de données seront endommagés à la suite d'une erreur logiciel ou panne matérielle ? Et enfin, que se passe-t-il si l'utilisateur (ou l'administrateur de base de données lui-même) supprime par erreur des données qui seront soudainement nécessaires à l'avenir ? Une configuration RAID n'aidera pas dans ces cas.

Le plus important est que l'administrateur peut toujours remplacer le serveur, mais les données sur ce serveur sont extrêmement difficiles à récupérer, parfois tout simplement impossibles.

Examinons quelques types de sauvegardes de bases de données.

SQL Server prend en charge trois types de sauvegarde : complète, différentielle ou différentielle et copie du journal. En plus des trois principaux types de sauvegardes, qui fonctionnent sur l'ensemble de la base de données, il existe également plusieurs types supplémentaires de sauvegardes utilisées pour copier un seul fichier ou un groupe de fichiers.

Sauvegarde complète - Crée une sauvegarde complète de toutes les extensions de base de données. Si le DBA restaure la base de données à l’aide d’une sauvegarde complète, il n’aura besoin que de la copie la plus récente qu’il a créée. Cependant, une sauvegarde complète est le type de sauvegarde le plus lent.

Sauvegarde différentielle : sauvegarde uniquement les extensions qui ont changé depuis la dernière sauvegarde complète. Si l'administrateur de base de données restaure la base de données à l'aide d'une sauvegarde différentielle, il aura besoin de la dernière sauvegarde complète et de la dernière sauvegarde différentielle qu'il a créée. Les sauvegardes différentielles sont beaucoup plus rapides, mais nécessitent plus de temps de récupération car elles nécessitent à la fois une sauvegarde complète et une sauvegarde différentielle.

Sauvegarde du journal - Crée une sauvegarde du journal des transactions à partir de la dernière sauvegarde complète ou de la copie précédente du journal des transactions. L'administrateur de base de données devra (ou non) créer des sauvegardes du journal des transactions en fonction du modèle de récupération qu'il utilise. S'il restaure la base de données à l'aide d'une sauvegarde complète et d'une copie du journal des transactions, il aura besoin de la dernière sauvegarde complète et de toutes les sauvegardes (dans l'ordre) du journal des transactions pour récupérer.

Il convient de noter que la sauvegarde est généralement effectuée pendant que la base de données est en cours d'exécution (en ligne). Ce processus est appelé « sauvegarde floue » car il s'effectue sur une période de temps donnée. Si des modifications surviennent pendant la sauvegarde des extensions de la base de données, le processus de copie se poursuivra bien entendu. Pour maintenir l'intégrité, les sauvegardes complètes et différentielles capturent la partie du fichier journal des transactions qui correspond à l'heure comprise entre le début et la fin de la sauvegarde.

SQL Server peut sauvegarder un fichier situé sur votre disque dur sur lecteur réseau, sur un appareil à bande magnétique.

Toutes les modifications apportées à la base de données sont nécessairement enregistrées dans le journal des transactions. En cas de sinistre (comme une panne de courant ou un écran bleu), le journal des transactions peut être utilisé pour récupérer les modifications apportées à la base de données. De plus, des points de contrôle sont utilisés pour écrire toutes les pages mémoire vive sur Disque dur, réduisant ainsi le temps nécessaire à la restauration de la base de données. Donc, si dans point de contrôle Toutes les pages de données sont écrites sur le disque dur, pourquoi devons-nous stocker les informations de transaction ? Pour répondre à cette question, nous devrons parler de modèles de relance.

Chaque base de données exécutée sur SQL Server peut adhérer à l'un des trois modèles de récupération suivants : Complet, Bulk_Logged et Simple.

Le modèle de récupération complète offre le plus d'options en cas de corruption des données. Si toutes les transactions sont enregistrées et que le mode de récupération complète est utilisé, les transactions sont conservées dans le journal jusqu'à ce qu'elles soient sauvegardées. Une fois la base de données sauvegardée, espace disque, qui était utilisé pour stocker les informations sur les transactions, devient gratuit et peut ensuite être utilisé pour enregistrer de nouvelles transactions. Puisque toutes les transactions sont sauvegardées, une sauvegarde complète permet de restaurer la base de données à " point donné moment" en utilisant uniquement les transactions qui ont eu lieu avant ce moment. Par exemple, nous pouvons effectuer une restauration à l'aide d'une sauvegarde complète, puis restaurer toutes les copies du journal des transactions jusqu'à un certain point avant que les données importantes ne soient supprimées.

Si le modèle de récupération complète garde une trace de toutes les modifications apportées à la base de données et nous permet de récupérer toutes les transactions à tout moment, alors la question se pose : pourquoi ne pas toujours utiliser ce modèle ? Étant donné que toutes les instructions doivent être entièrement validées, le résultat peut être un fichier journal assez « lourd ». De plus, des commandes telles que BULK INSERT ralentiront le serveur car toutes les modifications qu'elles apportent doivent être enregistrées.

Le modèle de journalisation par lots (bulk_logged) est assez similaire au modèle de récupération complète, avec quelques ajouts et avantages. Comme le modèle de récupération complète, le modèle de journalisation par lots capture également toutes les instructions UPDATE, DELETE et INSERT. Cependant, pour certaines commandes, ce modèle enregistre uniquement que l'opération a eu lieu. Cela est vrai pour les commandes telles que BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT et UPDATETEXT. Le modèle de journalisation par lots est similaire au modèle de récupération complète dans le sens où il ne réutilise pas (n'écrase pas) l'espace du journal tant que toutes les transactions n'ont pas été sauvegardées.

Contrairement au modèle de récupération complète, si le journal des transactions contient des instructions par lots, vous ne pouvez pas effectuer de récupération à un moment précis ; vous devez uniquement restaurer jusqu'à la fin de l'intégralité du journal. En outre, la taille d'une sauvegarde de journal de base de données peut être importante, car dans le modèle de journalisation par lots, le processus de sauvegarde de journal doit copier toutes les extensions qui ont été modifiées.

Les avantages de ce modèle sont que les fichiers journaux des transactions des bases de données peuvent être plus petits si vous utilisez de nombreuses instructions batch. De plus, les instructions batch sont exécutées plus rapidement, puisque seul le fait que l'exécution de cette instruction a eu lieu, et non la modification elle-même dans la base de données, doit être enregistrée.

Un modèle de récupération simple. Contrairement aux modèles de journalisation complète et par lots, le modèle de récupération simple n'utilise pas de sauvegardes du journal des transactions. Dans ce modèle, les journaux de transactions sont souvent tronqués automatiquement (la troncature est le processus de suppression des anciennes transactions du journal). Modèle récupération facile peut utiliser des sauvegardes complètes et différentielles.

gestion de documents d'administration de base de données

Copie de bases de données

Les bases de données utilisées sont réparties en deux catégories : 3 bases de données système (oktell, oktell_cc_temp et oktell_settings) et une base de données pour les modules client web Okapp. Pour démarrer Oktell après la récupération, seules les bases de données système sont nécessaires. Les bases de données restantes ne sont nécessaires que si vous souhaitez enregistrer les paramètres de votre module Web.

Par exemple, la base de données WO_Module_journal est utilisée par le module Revue stocke les balises des enregistrements de conversation. La base de données WO_Module_dashboards est nécessaire au fonctionnement du module Okboard Dashboards et contient les paramètres des indicateurs utilisés.

Étape 1. Copies tables système sont créés automatiquement chaque jour, par défaut à 02h00, heure du serveur, sauf si le paramètre DBAutoDailyBackup est désactivé. La création de copies se produit d'une manière particulière, laissant des copies

• deux dernières semaines - tous les jours
• puis trois mois - une fois par semaine
• puis deux ans - chaque mois
• puis une fois par an

Toutes les copies se trouvent dans le dossier \oktell\server\Backup, sauf si elles sont remplacées dans le paramètre DBBackupDir.

À votre tour, vous pouvez effectuer des sauvegardes à tout moment. Pour ce faire, rendez-vous sur Administration/Paramètres généraux/Gestion de base de données. Cliquez sur le bouton Effectuer une sauvegarde de base de données.

Une fois la sauvegarde terminée, les sauvegardes créées seront disponibles à la racine du dossier oktell\serveur\sauvegarde.

Étape 2. Pour créer des copies d'autres bases de données, ouvrez SQL Server Management Studio. Cliquez sur clic-droit sur la base de données souhaitée et dans menu contextuel sélectionnez Tâches, puis Créer une sauvegarde. Dans la fenêtre qui s'ouvre, vous pouvez modifier le chemin de création d'une sauvegarde ; pour lancer la copie, cliquez sur OK. Par défaut, les copies sont créées dans le dossier C:\Programmes\ MicrosoftSQL Serveur\MSSQL11.OKTELL\MSSQL\Sauvegarde\.

Récupération de base de données

Les bases de données ne peuvent être restaurées que vers la même version du serveur SQL ou supérieure. Si les bases de données ont été créées sur SQL Server 2008 R2, elles ne peuvent pas être restaurées vers SQL Server 2008.

Étape 1. Arrêtez le service oktellserver. Lancez une invite de commande avec les droits d'administrateur et saisissez la ligne suivante :

Arrêt net du serveur oktell

Étape 2. Démarrez SQL Server Management Studio avec le compte sa :

• Connexion: sa
• Mot de passe : 123 Oktell321

Étape 3. Si vous avez déjà bases installées Oktell données, elles doivent alors être supprimées. Cela s'applique aux bases de données système et aux bases de données utilisées par les modules Web.

Étape 4. Commençons la procédure de récupération. Faites un clic droit sur la base de données système et cliquez Restaurer la base de données(Restaurer la sauvegarde).

Sélectionnez le fichier avec une copie des bases de données. Pour ce faire, sélectionnez l'élément Appareil dans la fenêtre Ajouter qui s'ouvre et sélectionnez votre fichier avec copie de sauvegarde, Par exemple db_ok_130628.bak(dans ce cas, il s'agit de la base de données oktell).

Tapez le nom de la base de données en haut de la fenêtre que vous restaurez. Vous pouvez voir le nom de la base de données en bas de la fenêtre. N'oubliez pas de cocher la case Restaurer(Restaurer).

Répétez la même chose avec les bases de données restantes.

Étape 5. Après avoir restauré les bases de données pour travail à part entière vous devez créer des utilisateurs de base de données. Pour ce faire, téléchargez et exécutez la requête suivante.

Étape 6. Si vous avez transféré la base de données sur un serveur tiers, vérifiez les paramètres dans le fichier de configuration du serveur \oktell\server\oktell.ServerService.exe.config. Assurez-vous que dans la ligne contenant la clé DBConnectionString, le lien vers la base de données, le login et le mot de passe sont corrects. Par défaut, la chaîne de connexion ressemble à ceci :

Serveur=(local)\OKTELL;database=oktell;uid=AutelService;pwd=;pooling=true

Le nouveau nom du serveur doit être spécifié à la place de la valeur (local)\OKTELL. Par exemple, le serveur SQL est déplacé vers le serveur WORK avec l'adresse IP 192.168.0.3. Par conséquent, dans le paramètre, vous devez spécifier TRAVAIL\OKTELL. Si le serveur ne démarre pas avec ce paramètre, essayez de spécifier uniquement le nom du serveur sans l'instance - TRAVAIL. Au lieu du nom du serveur, vous pouvez spécifier l'adresse IP - 192.168.0.3/OKTEL ou juste 192.168.0.3 .

Si vous avez changé le principal compte AutelService, vous devez alors spécifier un nouveau login et mot de passe dans les champs uide Et mot de passe respectivement.

Vous pouvez toujours connaître le nom de votre serveur (instance) à l'aide de la commande

Sqlcmd.exe -L

V ligne de commande Les fenêtres.

Étape 7 Démarrer le service serveur oktell. Pour ce faire, sur la ligne de commande, exécutez