Sauvegarde logique de sauvegarde de données MySQL

Quel type de sauvegarde est une sauvegarde logique de base de données ?

Comme nous le savons tous, lorsque la base de données renvoie des données que nous pouvons utiliser, elle affiche les données une par une sous la forme d'un certain format d'association logique que nous avons initialement conçu et attendu, et possède certains attributs de logique métier. Au niveau du stockage physique, le logiciel de base de données stocke les données après certains traitements dans un format spécifique conçu par le logiciel de base de données.

La sauvegarde logique de la base de données signifie que le logiciel de sauvegarde génère les fichiers texte pertinents un par un en fonction de la relation logique de la base de données et de l'objet de structure logique de la base de données selon le format d'association logique prédéfini à des fins de sauvegarde.

Sauvegarde logique couramment utilisée

La sauvegarde logique peut être considérée comme la méthode de sauvegarde la plus simple et la plus couramment utilisée pour les systèmes de petite et moyenne taille. Il existe deux principaux types de sauvegardes logiques que nous utilisons couramment dans MySQL. L'une consiste à générer des instructions INSERT qui peuvent reproduire complètement les données de la base de données actuelle. L'autre consiste à transmettre les données via un logiciel de sauvegarde logique pour séparer les données de notre table de base de données de manière spécifique. séparés par des caractères et enregistrés dans un fichier texte.

1. Générer une sauvegarde de l'instruction INSERT

Les deux sauvegardes logiques ont leurs propres avantages et inconvénients, et les scénarios d'utilisation qu'elles ciblent sont légèrement différents. Jetons d'abord un coup d'œil à la sauvegarde logique de la génération d'INSERT. déclarations.

Dans la base de données MySQL, nous utilisons généralement mysqldump dans le programme outil fourni avec le logiciel de base de données MySQL pour réaliser le fichier de sauvegarde logique réclamant l'instruction INSERT. L'utilisation est essentiellement la suivante :

Définition de dumping et données de la base de données ou de la table mysql

Utilisation : mysqldump [OPTIONS] base de données [tables]

OU mysqldump [OPTIONS] -- bases de données [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

Étant donné que l'utilisation de mysqldump est relativement simple, la plupart des éléments requis Ces informations peuvent être obtenues en exécutant "mysqldump --help". Ici, je veux juste combiner certains concepts et principes de la base de données MySQL pour discuter avec vous des techniques dont nous disposons et de ce à quoi nous devons prêter attention lorsque nous utilisons mysqldump pour effectuer une sauvegarde logique de la base de données.

Nous savons tous que la plupart des logiciels ou sites Web qui utilisent des bases de données espèrent que leurs bases de données pourront fournir la plus haute disponibilité possible, plutôt que de devoir fermer et cesser de fournir des services de temps en temps. Car une fois que la base de données ne peut plus fournir de services, le système ne peut plus fournir certaines fonctions dynamiques en accédant aux données. Par conséquent, pour la plupart des systèmes, il n'est peut-être pas acceptable d'arrêter chaque sauvegarde. Cependant, le principe d'implémentation du programme mysqldump consiste à utiliser les informations sur les paramètres que nous donnons ainsi que les informations sur la table système dans la base de données pour créer une table une par une. Obtenez les données, puis générez une instruction INSERT et écrivez-la dans le fichier de sauvegarde. Cela crée un problème. Lors du fonctionnement normal du système, les demandes de modification de données sont susceptibles d'être exécutées en continu, ce qui peut entraîner une incohérence dans les données sauvegardées par mysqldump. En d’autres termes, les données de sauvegarde peuvent ne pas se trouver au même moment et peuvent même ne pas satisfaire aux contraintes d’intégrité. Un tel jeu de sauvegarde n'est peut-être pas un gros problème pour certains systèmes, mais pour certains systèmes qui ont des exigences strictes en matière de cohérence et d'intégrité des données, c'est un gros problème et il s'agit d'un jeu de sauvegarde complètement invalide.

Que devons-nous faire dans un tel scénario ? Nous savons que si nous voulons que les données de la base de données soient cohérentes, il n’existe que deux situations dans lesquelles cela peut être réalisé.

Premièrement, toutes les données sont supprimées en même temps

Deuxièmement, les données de la base de données sont dans un état statique ;

Pour la première situation, tout le monde se demandera certainement : est-ce possible ? Quoi qu'il en soit, tant qu'il y a plus de deux tables, quelle que soit la façon dont on écrit le programme, il est impossible d'obtenir le même nombre à la même heure la nuit dernière. Oui, il est vrai que nous ne pouvons pas rendre les moments de récupération des données exactement les mêmes avec les méthodes conventionnelles, mais n'oubliez pas que dans la même transaction, la base de données peut garantir que les données lues se font au même moment. Par conséquent, pour les moteurs de stockage prenant en charge les transactions, tels que Innodb ou BDB, nous pouvons contrôler l'ensemble du processus de sauvegarde dans la même transaction pour obtenir la cohérence et l'intégrité des données de sauvegarde, et le programme mysqldump nous fournit également les options de paramètres pertinentes pour prennent en charge cette fonction via l'option "--single-transaction", qui n'affecte aucun service normal de la base de données.

Pour la deuxième situation, je pense que la première chose à laquelle tout le monde pense est de verrouiller la table qui doit être sauvegardée, autorisant uniquement la lecture mais pas l'écriture. Oui, c'est vraiment tout ce que nous pouvons faire. Nous ne pouvons utiliser qu'une méthode de compromis, afin que la base de données fournisse uniquement des services de requête de données pendant le processus de sauvegarde et verrouille le service d'écriture, afin que les données soient temporairement dans un état cohérent qui ne sera pas modifié et attende que mysqldump termine le processus. sauvegarde. Supprimez le verrou en écriture et reprenez le service complet. Le programme mysqldump lui-même fournit également des options associées telles que "--lock-tables" et "--lock-all-tables", qui verrouilleront la table avant l'exécution et libéreront automatiquement le verrou après l'exécution. Une chose à noter ici est que "--lock-tables" ne verrouille pas toutes les tables qui doivent être sauvegardées en même temps, mais verrouille uniquement une table de base de données à la fois si vous devez vider les tables dans plusieurs fichiers différents. base de données, "--lock-all-tables" doit être utilisé pour garantir l'intégrité cohérente des données.

Lors de la génération d'un fichier de sauvegarde logique d'une instruction INSERT via mysqldump, il existe une option très utile que nous pouvons utiliser, à savoir "--master-data[=value]". Lorsque "--master-data=1" est ajouté, mysqldump enregistrera le nom et l'emplacement du journal binlog actuellement utilisé par MySQL dans le fichier de vidage, et il sera enregistré sous la forme d'une instruction CHANGE_MASTER si vous l'utilisez simplement. "- -master-data" ou "--masterdata=2", l'instruction CHANGE_MASTER existera sous la forme d'un commentaire. Cette option est très utile lors de la mise en œuvre de la construction en ligne de l'esclave. Même si l'esclave n'est pas construit en ligne, d'autres opérations de récupération peuvent être effectuées via le journal binaire de sauvegarde pendant le processus de récupération dans certains cas.

Dans certains scénarios, nous pouvons simplement exporter des données spéciales vers d'autres bases de données, mais nous ne voulons pas y parvenir en construisant d'abord une table temporaire. Nous pouvons également utiliser le programme mysqldump pour "-where='wherecondition'. " peut être implémenté, mais il ne peut être utilisé que lors du dump d'une seule table. En fait, en plus des conseils ci-dessus, mysqldump fournit également de nombreuses autres options utiles que vous pouvez utiliser dans différents scénarios, comme utiliser "--no-data" pour vider uniquement le script de création de la structure de la base de données, et utiliser "--no - createinfo" supprime les commandes permettant de créer des structures de table dans le fichier dump, etc. Les lecteurs intéressés peuvent lire en détail l'introduction à l'utilisation du programme mysqldump et ensuite le tester par vous-même.

2. Générer une sauvegarde de fichier de données de sauvegarde en texte brut dans un format spécifique

En plus de générer des sauvegardes logiques en générant des commandes INSERT, nous pouvons également utiliser une autre façon de sauvegarder les données dans la base de données. Des caractères délimiteurs spécifiques séparent les enregistrements de données dans des fichiers texte pour obtenir l'effet de sauvegarde logique. Par rapport au fichier de commande INSERT, ces données de sauvegarde nécessitent un espace de stockage plus petit, ont un format de données plus clair et sont plus faciles à modifier. Cependant, l'inconvénient est que les données de sauvegarde de plusieurs tables ne peuvent pas exister dans le même fichier de sauvegarde et qu'il n'existe aucune commande pour reconstruire la structure de la base de données. Le jeu de sauvegarde nécessite plusieurs fichiers, et l'impact sur nous n'est rien de plus que l'augmentation des coûts de maintenance et de récupération due à un plus grand nombre de fichiers, mais ceux-ci peuvent essentiellement être obtenus en écrivant quelques scripts simples

Alors que pouvons-nous généralement Comment générer un tel fichier de jeu de sauvegarde ? En fait, MySQL a également implémenté les fonctions correspondantes pour nous.

Dans MySQL, les deux méthodes suivantes sont généralement utilisées pour obtenir des fichiers de sauvegarde en texte brut avec des délimiteurs personnalisables.

1. En exécutant la commande SELECT ... TO OUTFILE FROM ...

fournit une syntaxe SELECT dans MySQL, conçue pour permettre aux utilisateurs de sélectionner certaines données via des instructions SQL. sortie dans un fichier texte au format spécifié. Il fournit également des outils pratiques et des commandes associées pour importer facilement le fichier exporté tel quel dans la base de données. N'est-ce pas exactement ce dont nous avons besoin pour la sauvegarde ?

Cette commande a plusieurs paramètres qui doivent être notés comme suit :

Le "FIELDS ESCAPED BY ['name']" qui implémente la fonction d'échappement de caractères échappera aux caractères qui doivent être échappé dans l'instruction SQL. Signification ;

"FIELDS [OPTIONALLY] ENCLOSED BY 'name'" qui peut "emballer" le contenu du champ Si "OPTIONALLY" n'est pas utilisé, tous les types de données, y compris numériques. les types seront "compactés". Après avoir utilisé "OPTIONNELLEMENT", les données de type numérique ne seront pas "encapsulées" par le caractère spécifié.

Utilisez "FIELDS TERMINATED BY" pour définir le délimiteur entre chaque deux champs ;

Utilisez "LINES TERMINATED BY" pour indiquer à MySQL d'afficher le fichier à la fin de chaque enregistrement. tu dois ajouter
?

Comme dans l'exemple suivant :

root@localhost : test 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'

-> CHAMPS TERMINÉS PAR ',' FERMÉS EN OPTION PAR '"'

-> LIGNES TERMINÉES PAR 'n'

-> FROM test_outfile limite 100;

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

root@localhost : test 10:02:11> exit

Au revoir

root@sky:/tmp# cat dump.text

350021,21,"A","abcd"

350022,22,"B","abcd"

350023,23,"C","abcd"

350024,24,"D","abcd"

350025,25,"A","abcd"

... ...

2 , Exporter via mysqldump

Peut-être que nous savons tous que mysqldump peut générer des fichiers de sauvegarde associés sous la forme d'instructions INSERT à partir des données de la base de données. En fait, en plus de générer des instructions INSERT, mysqldump peut également implémenter ce qui précède. "SELECT... TOOUTFILE FROM...", et génère également un script de création correspondant à la structure de la base de données concernée.

Comme dans l'exemple suivant :

root@sky:~# ls -l /tmp/mysqldump

total 0

root@sky:~ # mysqldump -uroot -T/tmp/mysqldump test test_outfile --fieldsenclosed-by=" --fields-terminated-by=,

root@sky:~# ls -l /tmp/mysqldump

total 8

-rw-r--r-- 1 racine racine 1346 2008-10-14 22:18 test_outfile.sql

-rw-rw-rw- 1 mysql mysql 2521 2008-10-14 22:18 test_outfile.txt

root@sky:~# cat /tmp/mysqldump/test_outfile.txt

350021,21,"A","abcd "

350022,22,"B","abcd"

350023,23,"C","abcd"

350024,24,"D"," abcd"

350025,25,"A","abcd"

... ...

root@sky:~# cat /tmp/mysqldump/test_outfile .sql

-- MySQL dump 10.11

---- Hôte : localhost Base de données : test

-- ------------- -- ---------------------------------------------------- Serveur version 5.0.51a-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @ OLD_COLLATION_CONNECTION= @@COLLATION_CONNECTION */;

/*!40101 DÉFINIR LES NOMS utf8 */;

/*!40103 DÉFINIR @OLD_TIME_ZONE=@@TIME_ZONE */;

/* !40103 SET TIME_ZONE=' 00:00' */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;

/*!40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

---- Structure de table pour la table `test_outfile`

--DROP TABLE IF EXISTS `test_outfile`;

SET @saved_cs_client = @@character_set_client;

SET Character_set_client = utf8;

CREATE TABLE `test_outfile` (

`id` int(11) NOT NULL par défaut '0 ',

`t_id` int(11) NULL par défaut,

`a` char(1) NULL par défaut,

`mid` varchar(32) NULL par défaut

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SET Character_set_client = @saved_cs_client;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump terminé le 2008-10 - 14 14:18:23

Ce type de structure de sortie nous convient très bien comme sauvegarde. Bien sûr, s'il y a plusieurs tables qui doivent être vidées à la fois, deux tables seront générées. pour chaque table correspondant aux fichiers.

Ce qui précède est le contenu de la sauvegarde logique de la sauvegarde des données Mysql. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !