[5.1.30] mysqlbinlog et erreurs

Version imprimable

05/12/2017, 14h22
macben

[5.1.30] mysqlbinlog et erreurs

Bonjour,

J'ai besoin de restaurer une base MySQL. J'ai effectué la dernière restauration complète que j'ai et à partir de ce point je veux rejouer les binlog.

Or j'ai les erreurs suivantes :

ERROR 1062 (23000) at line 13165: Duplicate entry '822062267' for key 'PRIMARY'

Que j'ai solutionnée en reprenant le point de reprise exact, mais j'ai également :

ERROR 1054 (42S22) at line 638: Unknown column 'inf' in 'field list'

Qui est issue d'une requête ayant été effectuée avec une syntaxe fausse.

Or ayant 480 bin log à rejouer, je cherche comment faire pour les appliquer en ignorant les erreurs. D'après mes recherches ce n'est pas possible, mais vu la quantité de bin log que j'ai à rejouer, je ne peux industrialiser les restaurations si des actions manuelles d'éviction sont nécessaires à chaque erreur.

Comment faites-vous dans ce cas ?

Macben
05/12/2017, 14h44
CinePhil

Citation:

ERROR 1054 (42S22) at line 638: Unknown column 'inf' in 'field list'

Qui est issue d'une requête ayant été effectuée avec une syntaxe fausse.

Ça veut dire que MySQL enregistre dans son binlog les requêtes DML ou DDL fausses ?
C'est normal, ça ? 8O
06/12/2017, 01h23
Artemus24

Salut à tous.

Citation:

Envoyé par macben

J'ai besoin de restaurer une base MySQL.

J'espère pour vous que la base en question a été sauvegardée sous la forme d'un fichier ".sql".
Et c'est celui-ci qui va vous servir à effectuer votre importation sous PhpMyAdmin.

Je fais cette remarque car des petits malins font une sauvegarde des fichiers ".frm" et ".ibd" et viennent se plaindre ensuite que cela ne fonctionne pas.
La seule bonne façon de créer une sauvegarde de votre base de données est de passer par l'export de phpmyadmin !

Citation:

Envoyé par macben

J'ai effectué la dernière restauration complète que j'ai ...

Avez-vous pensez à vérifier l'état de votre base de données après la restauration ?
Autrement dit, avez-vous pratiqué une maintenance en faisant un :
--> repair table + OPTIMIZE TABLE sur les tables MyIsam.
--> ALTER TABLE sur les tables InnoDB.

Citation:

Envoyé par macben

... et à partir de ce point je veux rejouer les binlog.

Désirez-vous récupérer (recovery) dans la base de données nouvellement installée, toutes les modifications qui sont intervenues après ?

Citation:

Envoyé par macben

Or ayant 480 bin log à rejouer

N'y aurait-il pas un peu trop de fichiers "binary log" ? La réponse est certainement oui ! Pourquoi ?
Quand vous faites une sauvegarde de votre base de données, celle-ci devient un point stable.
Donc tout ce qui va se faire dans cette base de données après ce point stable se trouve dans vos fichiers "binary log".
Autrement dit, vos fichiers "binary log" servent de sauvegarde incrémentielle, par rapport à votre dernier point stable.

Quand on fait une sauvegarde stable, on remet ces fichiers "binary log" à zéro ! Pourquoi ?
Car ce qui se trouvait dans vos fichiers "binary log" à cet instant là, se trouve maintenant dans votre base de données.
Donc ce qui se trouve avant dans votre fichier "binary log" ne vous sert à plus rien. Par "avant", je parle de la date de la sauvegarde du point stable.
Il est conseiller d'avoir une volumétrie faible pour vos "binary log" sinon vous allez perdre beaucoup de temps pour revenir juste avant votre plantage.
Il me semble que cela va être le cas !

Comment ? Il suffit de relancer votre serveur MySql afin d'incrémenter votre fichier "binary log".

Pour effectuer cette reprise (recovery), vous devez être seul sur le serveur MySql (mode standalone : option --skip-networking dans le fichier my.ini).
Ne pas oublier de redémarrer votre serveur MySql pour que le standalone puisse prendre effet !

Vous restaurez votre base de données, en sachant à quelle date cette sauvegarde a été effectuée.
Ou plutôt à partir d'une position (at) dans le fichier "binary log".

Vous devez nécessairement connaitre le fichier "binary log" (mysql_binary.000032) de ce point stable.
C'est pourquoi, on effectue un redémarrage du serveur MySql, au moment où l'on fait cette sauvegarde stable afin de produire un nouveau fichier "binary log".
Il se peut que vous ayez plusieurs fichier "binary log" après la date du point stable (ou de restauration).
Vous devez connaitre tous les fichier "binary log" qui sont après la date du point stable.

Vous devez aussi connaitre le moment où vous désirez commencer votre reprise (recovery).
Par exemple, utiliser la position (at) à partir de laquelle vous désirez faire votre recovery.
Pour ce faire, vous utilisez le paramètre "start-position".

De même, vous devez aussi connaitre aussi la position d'arrêt, sinon vous allez reproduire l
Pour ce faire, vous utilisez le paramètre "stop-position".

Vous devez lancer l'utilitaire "mysqlbinlog" sur la liste de vos fichiers "binary log" afin d'extraire tout ce qui a été fait après votre restauration.
Le résultat est un fichier de nom "recovery.log" qui va contenir toutes les modifications faites sur votre base de données.

Vous vérifiez dans ce fichier que vous n'allez pas reproduire les mêmes bêtises qui ont provoqué votre plantage.
Sinon, tout ce que vous avez fait depuis le début, ne vous servira à rien. Vous devez recommencer toute la procédure.

Ensuite, vous effectuez une restauration de ce fichier "recovery.sql" en utilisant la commande "mysql --database=votre_base < recovery.sql".

Avez-vous besoin d'un exemple un peu plus détaillé ?

@+
07/12/2017, 14h51
macben

Citation:

Envoyé par CinePhil

Ça veut dire que MySQL enregistre dans son binlog les requêtes DML ou DDL fausses ?
C'est normal, ça ? 8O

Je me suis posé la même question. Quelle est l'utilité d'enregistrer une requête fausse ?

Citation:

Envoyé par Artemus24

[...]

Merci pour ce post... détaillé ! Il ne répond pas à ma question ni à ma problématique mais il peut être utile.

Je suis conscient qu'il y a trop de binlog, il faut que j'analyse pourquoi, mais sur une base mysql (l'outil qui s'appuie dessus est Centreon) très sollicitée, une sauvegarde full hebdomadaire et des binlog au nombre de 10 quotidiennement ne me parait pas être anormal. Du fait en a avoir 69 à restaurer dans le "pire" des cas cela peut arriver.

Je comprends ton point de vu sur la maîtrise des temps start-position ; stop-position, mais tu ne dois pas être sans savoir que lorsque l'on remonte une sauvegarde si cela ne doit pas se faire dans la précipitation, cela doit se faire efficacement, dans un délai le plus court possible, et avec le minimum d'intervention manuelle source d'erreur.

Il me reste la problématique première : pourquoi les binlog enregistrent des ordres erronés, et pourquoi l'outil ne peut-il pas les ignorer.

Dans mon cas si Centreon a une sonde qui déconne et loggue toutes les minutes un ordre DDL faux, autant dire que peut importe le nombre de binlog, la restauration va se faire dans un délai déraisonnable.

Salut macben.

Citation:

Envoyé par macben

Il me reste la problématique première : pourquoi les binlog enregistrent des ordres erronés, et pourquoi l'outil ne peut-il pas les ignorer.

Je peux me tromper, mais je pense que c'est la première fois qu vous faites une réinstallation d'une base de données avec "recovery" à partir des fichiers "binary log".

Le fichier n'enregistre pas les erreurs ! Vous vous êtes trompé dans les dates, au moment de la restauration. Et les erreurs sont du genre :
--> duplication de la clef primaire car vous désirez faire une insertion qui existe déjà.
--> supprimer une ligne qui a déjà été supprimée auparavant.

Autrement dit, c'est comme si vous faisiez deux fois la même chose.

Citation:

Envoyé par macben

Merci pour ce post... détaillé ! Il ne répond pas à ma question ni à ma problématique mais il peut être utile.

Je suppose aussi que vous n'avez rien compris de mes explications car vous avez un manque de pratique concernant la gestion des fichiers "binary log".

Citation:

Envoyé par macben

Je suis conscient qu'il y a trop de binlog, il faut que j'analyse pourquoi,

Tout dépend comment ont été configurés ces fichiers "binary log".

Citation:

Envoyé par macben

mais sur une base mysql (l'outil qui s'appuie dessus est Centreon) très sollicitée, une sauvegarde full hebdomadaire et des binlog au nombre de 10 quotidiennement ne me parait pas être anormal

Dans votre premier message, vous parliez de 480 fichiers "binary log". Une dizaine me parait tout à fait correcte.
Je préconise plutôt un fichier "binary log" par jour.

Je ne connais pas l'outil centreon, mais je suppose que le principe doit rester le même pour restaurer une base de données sous MySql.

Une sauvegarde complète disons le vendredi soir !
Vos fichiers "binary log" vont servir pour effectuer des sauvegardes incrémentielles à partir de la dernière sauvegarde complète.

Il est important que les fichiers "binary log" soient synchronisés sur la dernière sauvegarde complète.
C'est pourquoi, on remet tous ces fichiers à zéro afin de ne pas trop se poser de question quand on fait une restauration.
Il semble que cela ne soit pas le cas dans votre environnement.
Des fichiers "binary log" anciens qui n'ont aucune raison d'être encore présent.
Peut-être des fichiers "binary log" trop petit donc mal taillé, d'où le nombre trop importants.
Ou encore, vous redémarrez votre serveur mysql quinze fois par jours, ce qui provoque une rotation de ces fichiers.

Citation:

Envoyé par macben

Je comprends ton point de vu sur la maîtrise des temps start-position ; stop-position, mais tu ne dois pas être sans savoir que lorsque l'on remonte une sauvegarde si cela ne doit pas se faire dans la précipitation, cela doit se faire efficacement, dans un délai le plus court possible, et avec le minimum d'intervention manuelle source d'erreur.

C'est pourquoi, il faut préparer l'environnement avant de faire quoi que ce soit d'autre.

1) bien tailler les fichiers "binary log" afin d'en avoir un et un seul par jour.
Peu importe la volumétrie, il faut mieux en avoir qu'un seul par jour, c'est plus facile à gérer.

2) faire une rotation de ces fichiers "binary log" afin d'en avoir un seul par jour, le soir de préférence quand personne ne travaille plus sur la base.

3) on fait une sauvegarde complète de la base de données, 1 fois par semaine.
On note bien sûr la date, histoire de connaitre le moment de la sauvegarde.

4) on fait une sauvegarde du fichier "binary log" 1 fois par jour.
Et comme précédemment, on note la date du jour de la sauvegarde.

5) on remet ce fichier "binary log" à zéro, en faisant une rotation, car il va débuter la journée suivante.

6) en gros, la sauvegarde complète représente le point stable.
Chaque fichier journalier de vos "binary log" correspondent aux fichiers incrémentiels.

7) et avec cette façon de procéder, on s'en fout de la période à restaurer.
On restaure d'abord la sauvegarde complète, qui en principe est la dernière sauvegarde.
Puis ensuite chaque "binary log" qui a été sauvegardé, en commençant par le plus ancien au plus récent.

8) Que devez-vous faire ensuite ?
Soit deux choses l'une :
--> Soit on perd la journée en cours,
--> Soit on restaure les dernières modifications de la journée en cours à partir du fichier "binary log" courant, c'est-à-dire celle non sauvegardé.

9) comment créer le fichier "recovery" ?
Deux cas, soit on prend tout, soit on prend les modifications dans une période données.
Dans le premier cas, il suffit de ne pas préciser la période, mais le principe reste le même.

Voici un exemple d'extraction du fichier "recovery" à partir d'une date de début et d'une date de fin.
Comme je travaille sous Windows et sous WampServer, il faudra adapté ce script à votre environnement.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @echo off setlocal enableDelayedExpansion chcp 1252 > nul set PATH=.;F:\Wamp\bin\mysql\mysql5.7.20\bin\;%PATH% IF EXIST recovery.sql (DEL recovery.sql) mysqlbinlog --database=base --result-file=recovery.sql --start-datetime="2017-12-03 01:45:00" --stop-datetime="2017-12-05 13:00:00" F:/Wamp/logs/mysql_binary.000001 F:/Wamp/logs/mysql_binary.000002 F:/Wamp/logs/mysql_binary.000003 @echo. pause exit
Dans mon exemple, j'ai mis trois fichiers "binary log" car mon serveur mysql, durant cette période a redémarré trois fois.
La période va donc du "2017-12-03 01:45:00", heure à laquelle j'ai fait ma sauvegarde, jusqu'au "2017-12-05 13:00:00", heure estimé où juste après, j'ai eu mon plantage.
Durant cette période, ma base de données est correcte !
Le fichier d'extraction se nomme ici "recovery.log". Il est au même format que celui servant à la restauration de votre base de données.

Comme vous avez effectué la restauration de votre base de données, vous devez appliquer le fichier "recovery" sur votre restauration.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 @echo off setlocal enableDelayedExpansion chcp 1252 > nul set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH% mysql --database=base < recovery.sql @echo. pause exit
Aucune difficulté pour cette partie !

Par elle-même, l'extraction du fichier "recovery" et son exécution sur la restauration ne sont pas très longues.
Le plus difficile est d'identifier la période de restauration donc de la dernière sauvegarde, juste avant votre plantage.

@+