|
Wie wir alle wissen, sind Binlog-Protokolle für MySQL-Datenbanken sehr wichtig. Im Notfall eines Datenverlusts denken wir oft daran, die Binlog-Protokollfunktion zur Datenwiederherstellung zu verwenden (geplante vollständige Sicherung + Binlog-Protokollwiederherstellung inkrementeller Daten), um Gefahren zu vermeiden! Ohne weitere Umschweife hier nun die Erklärung der Binlog-Protokollvorgänge: 1. Vorläufiges Verständnis von Binlog
Das Binärprotokoll von MySQL, binlog, kann als das wichtigste Protokoll von MySQL bezeichnet werden. Es zeichnet alle DDL- und DML-Anweisungen (außer Datenabfrageanweisungen select) in Form von Ereignissen auf und enthält auch die für die Ausführung der Anweisungen benötigte Zeit. Das Binärprotokoll von MySQL ist transaktionssicher.
------------------------------------------------------------------------------------------------------------------------------------------------------------
DDL
----Datendefinitionssprache Die wichtigsten Befehle der Datenbankdefinitionssprache sind CREATE, ALTER, DROP usw. DDL wird hauptsächlich verwendet, um die Struktur der Tabelle (TABLE), den Datentyp, Links und Einschränkungen zwischen Tabellen und andere Initialisierungsarbeiten zu definieren oder zu ändern. Sie werden hauptsächlich beim Erstellen von Tabellen verwendet. DML
----Datenmanipulationssprache Die wichtigsten Befehle der Datenmanipulationssprache sind SELECT, UPDATE, INSERT und DELETE. Wie der Name schon sagt, werden diese vier Befehle verwendet, um die Daten in der Datenbank zu bearbeiten.
------------------------------------------------------------------------------------------------------------------------------------------------------------ Die allgemeinen Optionen für mysqlbinlog sind wie folgt:
--start-datetime: Liest aus dem Binärprotokoll eine Zeit, die gleich oder später als der Zeitstempel des lokalen Computers ist.
--stop-datetime: Liest aus dem Binärlog einen Zeitwert, der kleiner als der Zeitstempel oder gleich der Zeit des lokalen Computers ist. Der Wert ist derselbe wie oben.
--start-position: Liest die angegebene Position des Positionsereignisses aus dem Binärprotokoll als Start.
--stop-position: Liest als Ereignisende die angegebene Ereignisposition aus dem Binärlog ********************************************************************* Im Allgemeinen führt das Aktivieren von Binlog zu einem Leistungsverlust von etwa 1 %.
Für Binlog-Protokolle gibt es zwei wichtige Verwendungsszenarien:
1) MySQL Master-Slave-Replikation : MySQL Replication startet das Binärprotokoll auf der Masterseite und der Master übergibt sein Binärprotokoll an die Slaves, um
Der Zweck der Master-Slave-Datenkonsistenz.
2) Natürlich werden die Daten mithilfe des Tools mysqlbinlog wiederhergestellt .
Das Binlog-Protokoll umfasst zwei Dateitypen:
1) Die Binärprotokoll-Indexdatei ( Dateinamenserweiterung ist .index ) wird zum Aufzeichnen aller Binärdateien verwendet
2) Binäre Protokolldateien ( Dateinamenserweiterung .00000* ) zeichnen alle DDL- und DML-Anweisungsereignisse (außer Select-Anweisungen zur Datenabfrage) in der Datenbank auf. 2. Öffnen Sie das Binlog-Protokoll:
1) Bearbeiten und öffnen Sie die MySQL-Konfigurationsdatei /etc/mys.cnf
[root@vm-002 ~]# vim /etc/my.cnf
Fügen Sie im Abschnitt [mysqld] hinzu
log-bin=mysql-bin bestätigt, dass es eingeschaltet ist (mysql-bin ist der Basisname oder Präfixname des Protokolls); 2) Starten Sie den mysqld-Dienst neu, damit die Konfiguration wirksam wird
[root@vm-002 ~]# /etc/init.d/mysqld stoppen
[root@vm-002 ~]# /etc/init.d/mysqld neu starten
Stoppen von mysqld: [ OK ]
mysqld wird gestartet: [ OK ] 3) Überprüfen Sie, ob Binlog aktiviert ist
mysql> Variablen wie „log_%“ anzeigen;
+---------------------------------+---------------------+
| Variablenname | Wert |
+---------------------------------+---------------------+
| log_bin | EIN |
| log_bin_trust_function_creators | AUS |
| log_bin_trust_routine_creators | AUS |
| log_fehler | /var/log/mysqld.log |
| log_ausgabe | DATEI |
| log_queries_not_using_indexes | AUS |
| log_slave_updates | AUS |
| log_slow_queries | AUS |
| log_warnungen | 1 |
+---------------------------------+---------------------+
9 Zeilen im Satz (0,00 Sek.) 3. Häufig verwendete Binlog-Protokolloperationsbefehle
1) Alle Binlog-Loglisten anzeigen
mysql> Master-Protokolle anzeigen;
+------------------+------------+
| Protokollname | Dateigröße |
+------------------+------------+
|mysql-bin.000001 | 149 |
|mysql-bin.000002 | 4102 |
+------------------+------------+
2 Zeilen im Satz (0,00 Sek.) 2) Überprüfen Sie den Masterstatus, d. h. den Nummernnamen des letzten (neuesten) Binlog-Protokolls und den POS-Endpunktwert seines letzten Operationsereignisses
mysql> Masterstatus anzeigen;
+------------------+----------+--------------+------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 4102 | | |
+------------------+----------+--------------+------------------+
1 Zeile im Satz (0,00 Sek.) 3) Flush aktualisiert das Protokoll und generiert ab diesem Zeitpunkt eine neue nummerierte Binlog-Protokolldatei
mysql> Protokolle leeren;
Abfrage OK, 0 Zeilen betroffen (0,13 Sek.) mysql> Master-Protokolle anzeigen;
+------------------+------------+
| Protokollname | Dateigröße |
+------------------+------------+
|mysql-bin.000001 | 149 |
|mysql-bin.000002 | 4145 |
|mysql-bin.000003 | 106 |
+------------------+------------+
3 Zeilen im Satz (0,00 Sek.) Beachten:
Immer wenn der mysqld-Dienst neu gestartet wird, wird dieser Befehl automatisch ausgeführt, um das Binlog-Protokoll zu aktualisieren. Wenn Sie beim Sichern von Daten mit mysqldump die Option -F hinzufügen, wird das Binlog-Protokoll ebenfalls aktualisiert. 4) Alle Binlog-Protokolle zurücksetzen (löschen)
mysql> Master zurücksetzen;
Abfrage OK, 0 Zeilen betroffen (0,12 Sek.) mysql> Master-Protokolle anzeigen;
+------------------+------------+
| Protokollname | Dateigröße |
+------------------+------------+
|mysql-bin.000001 | 106 |
+------------------+------------+
1 Zeile im Satz (0,00 Sek.) 4. Es gibt zwei gängige Möglichkeiten, den Inhalt des Binlog-Protokolls anzuzeigen:
1) Verwenden Sie die in mysqlbinlog integrierte Anzeigebefehlsmethode:
Beachten:
-->binlog ist eine Binärdatei. Gewöhnliche Dateibetrachter wie cat, more und vim können sie nicht öffnen. Sie müssen den integrierten Befehl mysqlbinlog verwenden, um sie anzuzeigen.
-->binlog-Log- und Datenbankdateien liegen im selben Verzeichnis
-->Wenn bei Verwendung des Befehls mysqlbinlog in MySQL-Versionen unter 5.5 ein Fehler auftritt, fügen Sie die Option „--no-defaults“ hinzu Überprüfen Sie das MySQL-Datenspeicherverzeichnis. Aus den folgenden Ergebnissen können wir ersehen, dass es /var/lib//mysql ist
[root@vm-002 ~]# ps -ef|grep mysql
root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/ lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 9896 9791 0 21:18 Punkte/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx
root 9919 9715 0 21:23 Punkte/1 00:00:00 grep --color mysql [root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock Ops-Test Verwenden Sie den Befehl mysqlbinlog, um den Inhalt des Binlog-Protokolls anzuzeigen. Nachfolgend sehen Sie eine Ausschnittanalyse:
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002
..............
# bei 624
#160925 21:29:53 Server-ID 1 End-Log-Pos 796 Abfrage Thread-ID = 3 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1474810193/*!*/;
insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #Ausgeführte SQL-Anweisung
/*!*/;
# bei 796
#160925 21:29:53 Server-ID 1 end_log_pos 823 Xid = 17 #Ausführungszeit
............. erklären:
Server-ID 1: die Servicenummer des Datenbank-Hosts;
end_log_pos 796: POS-Knoten am Ende von SQL
thread_id=11: Threadnummer 2) Die obige Methode liest mehr Volltext der Binlog-Protokolle und es ist nicht einfach, die POS-Punktinformationen zu unterscheiden und anzuzeigen
Hier ist ein bequemerer Abfragebefehl:
Befehlsformat:
mysql> Binlog-Ereignisse anzeigen [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; Parametererklärung:
IN 'log_name': Geben Sie den Namen der abzufragenden Binlog-Datei an (wenn nicht angegeben, ist es die erste Binlog-Datei).
FROM pos: Geben Sie den Startpunkt der Suche an (wenn nicht angegeben, wird am ersten Pos-Punkt der gesamten Datei begonnen)
LIMIT [Offset,]: Offset (0, wenn nicht angegeben)
row_count: Abfrage der Gesamtzahl der Zeilen (alle Zeilen, wenn nicht angegeben) mysql> Master-Protokolle anzeigen;
+------------------+------------+
| Protokollname | Dateigröße |
+------------------+------------+
|mysql-bin.000001 | 125 |
|mysql-bin.000002 | 823 |
+------------------+------------+
2 Zeilen im Satz (0,00 Sek.) mysql> Binlog-Ereignisse in „mysql-bin.000002“ anzeigen\G;
*************************** 1. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 4
Event_type: Format_desc
Server-ID: 1
End_log_pos: 106
Info: Serverversion: 5.1.73-log, Binlogversion: 4
*************************** 2. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 106
Event_type: Abfrage
Server-ID: 1
End_log_pos: 188
Info: Verwenden Sie „ops“; löschen Sie die Tabelle „Kunden“
*************************** 3. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 188
Event_type: Abfrage
Server-ID: 1
End_log_pos: 529
Info: verwenden Sie `ops`; CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NICHT NULL,
`Geschlecht` enum('m','w') NICHT NULL STANDARD 'm',
`Alter` tinyint(3) unsigned NOT NULL,
`classid` char(6) DEFAULT NULL,
PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
*************************** 4. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 529
Event_type: Abfrage
Server-ID: 1
End_log_pos: 596
Info: BEGIN
*************************** 5. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 596
Ereignistyp: Intvar
Server-ID: 1
End_log_pos: 624
Info: INSERT_ID=1
*************************** 6. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 624
Event_type: Abfrage
Server-ID: 1
End_log_pos: 796
Info: Verwenden Sie „ops“. Fügen Sie in das Mitglied („Name“, „Geschlecht“, „Alter“, „Klassen-ID“) Werte ein („wangshibo“, „m“, 27, „cls1“), („guohuihui“, „w“, 27, „cls2“).
*************************** 7. Reihe ***************************
Protokollname: mysql-bin.000002
Position: 796
Ereignistyp: Xid
Server-ID: 1
End_log_pos: 823
Informationen: COMMIT /* xid=17 */
7 Zeilen im Satz (0,00 Sek.) FEHLER:
Keine Abfrage angegeben MySQL> Die obige Anweisung kann die angegebene Binlog-Protokolldatei in Form gültiger Ereigniszeilen zurückgeben und mithilfe von „Limit“ den Startversatz des POS-Punkts angeben, um die Anzahl der Einträge abzufragen!
Nachfolgend sehen Sie ein Beispiel für die Funktionsweise:
a) Fragen Sie das erste (früheste) Binlog-Protokoll ab:
mysql> Binlog-Ereignisse anzeigen\G; b) Abfrage der Datei mysql-bin.000002:
mysql> Binlog-Ereignisse in „mysql-bin.000002“ anzeigen\G; c) Geben Sie die Abfragedatei mysql-bin.000002 an, beginnend bei POS-Punkt: 624:
mysql> zeige Binlog-Ereignisse in „mysql-bin.000002“ von 624\G; d) Geben Sie an, dass die Datei mysql-bin.000002 abgefragt werden soll, beginnend bei Positionspunkt 624, und fragen Sie 10 Datensätze ab (also 10 Anweisungen).
mysql> zeige Binlog-Ereignisse in „mysql-bin.000002“ ab 624 Limit 10\G; e) Geben Sie die Abfragedatei mysql-bin.000002 an, starten Sie die Abfrage ab Positionspunkt: 624, verschieben Sie 2 Zeilen (d. h. überspringen Sie 2 in der Mitte) und fragen Sie 10 Datensätze ab
mysql> zeige Binlog-Ereignisse in „mysql-bin.000002“ ab 624 Limit 2,10\G; 5. Verwenden Sie das Binlog-Protokoll, um MySQL-Daten wiederherzustellen Die folgenden Operationen werden an der Member-Tabelle der Ops-Bibliothek durchgeführt
mysql> Ops verwenden;
mysql> CREATE TABLE IF NOT EXISTS `member` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(16) NICHT NULL,
-> `Geschlecht` enum('m','w') NICHT NULL STANDARD 'm',
-> `Alter` tinyint(3) unsigned NOT NULL,
-> `classid` char(6) DEFAULT NULL,
-> PRIMÄRSCHLÜSSEL (`id`)
->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Abfrage OK, 0 Zeilen betroffen (0,10 Sek.) mysql> Tabellen anzeigen;
+-----------------+
| Tabellen_in_Ops |
+-----------------+
| Mitglied |
+-----------------+
1 Zeile im Satz (0,00 Sek.) mysql> desc-Mitglied;
+---------+---------------------+------+-----+---------+----------------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NEIN | PRI | NULL | auto_increment |
| Name | varchar(16) | NEIN | | NULL | |
| Geschlecht | Aufzählung('m','w') | NEIN | | m | |
| Alter | tinyint(3) unsigniert | NEIN | | NULL | |
| Klassen-ID | char(6) | JA | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 Zeilen im Satz (0,00 Sek.) Geben Sie vorab zwei Daten ein
mysql> einfügen in Mitglied(`Name`,`Geschlecht`,`Alter`,`Klassen-ID`) Werte('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
Abfrage OK, 2 Zeilen betroffen (0,08 Sek.)
Datensätze: 2 Duplikate: 0 Warnungen: 0
mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+----------+-----+-----+---------+
2 Zeilen im Satz (0,00 Sek.) Beginnen wir mit der Szenensimulation:
1)
Die Ops-Bibliothek führt täglich um 4 Uhr morgens eine geplante vollständige Sicherungsaufgabe wie folgt aus:
[root@vm-002 ~]# crontab -l
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz Hier ist eine manuelle Ausführung zum Sichern der Ops-Datenbank in der Datei /opt/backup/ops_$(date +%F).sql.gz:
[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Passwort eingeben:
[root@vm-002 ~]# ls /opt/backup/
ops_2016-09-25.sql.gz
-----------------
Parameterbeschreibung:
-B: Datenbank angeben
-F: Protokoll aktualisieren
-R: Sicherungsspeichervorgang usw.
-x: Tabelle sperren
--master-data: Fügen Sie der Sicherungsanweisung die Anweisung CHANGE MASTER sowie die Binärprotokolldatei und Standortinformationen hinzu
-----------------
Sobald die Datenbanksicherung abgeschlossen ist, müssen Sie sich keine Sorgen mehr über Datenverlust machen, da vollständige Sicherungsdaten vorliegen! ! Da die Option -F in der obigen vollständigen Sicherung verwendet wird, aktualisiert das System das Protokoll automatisch, wenn der Datensicherungsvorgang beginnt, sodass automatisch ein neues Binlog-Protokoll generiert wird. Dieses neue Binlog-Protokoll wird verwendet, um die Datenbankvorgänge „Hinzufügen, Löschen und Ändern“ nach der Sicherung aufzuzeichnen.
Hör zu:
mysql> Masterstatus anzeigen;
+------------------+----------+--------------+------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | | |
+------------------+----------+--------------+------------------+
1 Zeile im Satz (0,00 Sek.) Das heißt, mysql-bin.000003 wird verwendet, um alle „Hinzufügen-, Löschen- und Ändern“-Vorgänge in der Datenbank nach 4:00 Uhr aufzuzeichnen. 2)
Ich gehe morgens um 9 Uhr zur Arbeit. Aus geschäftlichen Gründen werden verschiedene „Hinzufügen-, Löschen- und Ändern“-Vorgänge an der Datenbank ausgeführt.
Beispielsweise werden Daten in die Mitgliedertabelle unter der Ops-Datenbank eingefügt oder geändert: Geben Sie zunächst morgens die Daten ein:
mysql> einfügen in ops.member(`Name`,`Geschlecht`,`Alter`,`Klassen-ID`) Werte('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
Abfrage OK, 5 Zeilen betroffen (0,08 Sek.)
Datensätze: 5 Duplikate: 0 Warnungen: 0 mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.) 3) Mittags wurde die Datenänderungsoperation erneut durchgeführt:
mysql> aktualisiere ops.member set name='Element', wobei id=4 ist;
Abfrage OK, 1 Zeile betroffen (0,07 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0 mysql> aktualisiere ops.member set name='Element', wobei id=2 ist;
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)
Übereinstimmende Zeilen: 1 Geändert: 1 Warnungen: 0 mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | Xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.) 4)
Um 18:00 Uhr kam es auf unerklärliche Weise zu einer Tragödie!
Ich habe versehentlich die Drop-Anweisung ausgeführt und die Ops-Bibliothek direkt gelöscht! Zu Tode erschrocken!
mysql> Datenbankoperationen löschen;
Abfrage OK, 1 Zeile betroffen (0,02 Sek.) 5)
Geraten Sie in solchen Momenten nicht in Panik! ! !
Überprüfen Sie zunächst sorgfältig das letzte Binlog-Protokoll und notieren Sie die wichtigsten POS-Punkte. Welcher POS-Punktvorgang hat die Datenbank beschädigt (normalerweise in den letzten Schritten)? Sichern Sie zuerst die letzte Binlog-Protokolldatei:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/
`mysql-bin.000003' -> `/opt/backup/mysql-bin.000003'
[root@vm-002 mysql]# ls /opt/backup/
mysql-bin.000003 ops_2016-09-25.sql.gz Führen Sie dann einen Vorgang zum Aktualisieren des Protokollindex aus und starten Sie eine neue Binlog-Protokolldatei neu. Es liegt auf der Hand, dass mysql-bin.000003
In diese Datei wird nicht erneut geschrieben, da es für uns praktisch ist, die Ursache zu analysieren und den Ops-Knoten zu finden. Alle Datenbankvorgänge werden in Zukunft in die nächste Protokolldatei geschrieben.
mysql> Protokolle leeren;
Abfrage OK, 0 Zeilen betroffen (0,13 Sek.) mysql> Masterstatus anzeigen;
+------------------+----------+--------------+------------------+
| Datei | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | | |
+------------------+----------+--------------+------------------+
1 Zeile im Satz (0,00 Sek.) 6)
Lesen Sie das Binlog-Protokoll und analysieren Sie das Problem.
Die Methode zum Lesen von Binlog-Protokollen wurde oben erwähnt.
Methode 1: Verwenden Sie mysqlbinlog, um Binlog-Protokolle zu lesen:
[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003 Methode 2: Beim Server anmelden und anzeigen (empfohlen)
mysql> Binlog-Ereignisse in „mysql-bin.000003“ anzeigen; +------------------+-----+----------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| Logname | Pos | Ereignistyp | Server-ID | End_log_pos | Info |
+------------------+-----+----------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Serverversion: 5.1.73-log, Binlogversion: 4 |
| mysql-bin.000003 | 106 | Abfrage | 1 | 173 | BEGIN |
| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID=3 |
| mysql-bin.000003 | 201 | Abfrage | 1 | 444 | verwende „ops“; füge ein in ops.member(„Name“, „Geschlecht“, „Alter“, „gsan“, „w“, 21, „cls5“, („lisi“, „m“, 20, „cls4“), („wangwu“, „w“, 26, „cls6“) |
| mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ |
| mysql-bin.000003 | 471 | Abfrage | 1 | 538 | BEGINNEN |
| mysql-bin.000003 | 538 | Abfrage | 1 | 646 | verwende „ops“; aktualisiere ops.member set name='Objekt', wobei id= |
| mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ |
| mysql-bin.000003 | 673 | Abfrage | 1 | 740 | BEGIN |
| mysql-bin.000003 | 740 | Abfrage | 1 | 848 | verwende „ops“; aktualisiere ops.member set name='Benutzername', wobei id= |
| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid=69 */ |
| mysql-bin.000003 | 875 | Abfrage | 1 | 954 | Datenbankoperationen löschen |
| mysql-bin.000003 | 954 | Rotieren | 1 | 997 | mysql-bin.000004;pos=4 |
+------------------+-----+----------+-----------+-------------+-------------------------+--------------------------------------------------------------------------------------------------------------------------+
13 Zeilen im Satz (0,00 Sek.) oder: mysql> Binlog-Ereignisse in „mysql-bin.000003“ anzeigen\G;
.........
.........
*************************** 12. Reihe ***************************
Protokollname: mysql-bin.000003
Position: 875
Event_type: Abfrage
Server-ID: 1
End_log_pos: 954
Info: Datenbankoperationen löschen
*************************** 13. Reihe ***************************
Protokollname: mysql-bin.000003
Position: 954
Event_type: Drehen
Server-ID: 1
End_log_pos: 997
Info: mysql-bin.000004;pos=4
13 Zeilen im Satz (0,00 Sek.) Durch Analyse wurde festgestellt, dass das POS-Punktintervall, das den Datenbankschaden verursacht hat, zwischen 875 und 954 lag (berechnet anhand der POS-Knoten im Protokollintervall) und nur auf einen Wert vor 875 wiederhergestellt werden muss. 7)
Stellen Sie zunächst um 4 Uhr morgens die vollständigen Sicherungsdaten wieder her:
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 Sicherung]# ls
mysql-bin.000003 ops_2016-09-25.sql.gz
[root@vm-002 Sicherung]# gzip -d ops_2016-09-25.sql.gz
[root@vm-002 Sicherung]# mysql -uroot -p -v < ops_2016-09-25.sql
Passwort eingeben:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
-------------- --------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
-------------- .............
............. --------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
-------------- Auf diese Weise werden alle Sicherungsdaten vor dem frühen Morgen des gleichen Tages (4:00 Uhr) wiederhergestellt. mysql> show databases; #Es wurde festgestellt, dass die Ops-Datenbank wiederhergestellt wurde
mysql> Ops verwenden;
Einlesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten Datenbank geändert
mysql> Tabellen anzeigen;
+-----------------+
| Tabellen_in_Ops |
+-----------------+
| Mitglied |
+-----------------+
1 Zeile im Satz (0,00 Sek.) mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+----------+-----+-----+---------+
2 Zeilen im Satz (0,00 Sek.) MySQL>
Allerdings wurden dadurch nur die Daten vor 4 Uhr morgens an diesem Tag wiederhergestellt, die Daten zwischen 4:00 und 18:00 Uhr wurden nicht wiederhergestellt! !
Was sollte ich tun?
Keine Panik! Dies kann anhand des zuvor erwähnten neuen Binlog-Protokolls von mysql-bin.000003 wiederhergestellt werden.
8)
Wiederherstellen von Daten aus Binlog
Das Syntaxformat des Wiederherstellungsbefehls lautet:
mysqlbinlog mysql-bin.0000xx | mysql -u Benutzername -p Passwort Datenbankname --------------------------------------------------------
Erklärung gängiger Parameteroptionen:
--start-position=875 Startpositionspunkt
--stop-position=954 Endpunkt
--start-datetime="2016-9-25 22:01:08" Startzeit
--stop-datetime="2019-9-25 22:09:46" Endzeit
--database=zyyshop gibt an, dass nur die zyyshop-Datenbank wiederhergestellt werden soll (auf einem Host befinden sich häufig mehrere Datenbanken, nur lokale Protokolle sind beschränkt)
--------------------------------------------------------
Weniger gebräuchliche Optionen:
-u --user=name Benutzername für die Verbindung mit dem Remote-Host
-p --password[=name] Passwort für die Verbindung mit dem Remote-Host
-h --host=name Ruft Binlog-Protokolle vom Remote-Host ab
--read-from-remote-server Binlog-Protokolle von einem MySQL-Server lesen
--------------------------------------------------------
Zusammenfassung: Tatsächlich wird der gelesene Binlog-Protokollinhalt über das Pipe-Zeichen an den MySQL-Befehl übergeben. Diese Befehle und Dateien sollten möglichst als absolute Pfade geschrieben werden. a) Vollständige Wiederherstellung (Sie müssen mysql-bin.000003 manuell mit vim bearbeiten und die Drop-Anweisung entfernen)
[root@vm-002 Backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops b) Geben Sie den Endpunkt der Wiederherstellung an (teilweise Wiederherstellung):
--stop-position=471 pos Endknoten (471 entsprechend dem Transaktionsintervall) Beachten: Dieser POS-Endknoten befindet sich zwischen den „Originaldaten der Mitgliedstabelle“ und den Daten vor der Aktualisierung von „name=‘李四‘“, sodass die Daten vor der Änderung von „name=‘李四‘“ wiederhergestellt werden können.
Die Funktionsweise ist wie folgt:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.) Stellen Sie die Daten bis zu dem Zeitpunkt wieder her, als „name='李四'“ geändert wurde (673 im Hinblick auf das Transaktionsintervall).
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.) c) Geben Sie die Wiederherstellung des PSO-Punktintervalls an (teilweise Wiederherstellung):
Aktualisieren Sie die Daten von name='李四'. Das Protokollintervall ist: Pos[538] --> End_log_pos[646]. Das Transaktionsintervall ist: Pos[471] --> End_log_pos[673] Aktualisieren Sie die Daten von name='小等'. Das Protokollintervall ist: Pos[740] --> End_log_pos[848], und das Transaktionsintervall ist: Pos[673] --> End_log_pos[875] c1)
Um name='李四' separat wiederherzustellen, können Sie Folgendes tun:
Separate Wiederherstellung gemäß dem Binlog-Protokollintervall:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops Separate Wiederherstellung nach Transaktionsintervall
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c2)
Um name='小二' separat wiederherzustellen, können Sie Folgendes tun:
Separate Wiederherstellung gemäß dem Binlog-Protokollintervall:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=740 --stop-position=848 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops Separate Wiederherstellung nach Transaktionsintervall
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=673 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops c3)
Um die mehrstufigen Vorgänge name='李四' und name='小二' gemeinsam wiederherzustellen, müssen Sie das Transaktionsintervall einhalten. Dies geht folgendermaßen:
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops Sehen Sie sich die Datenbank an:
mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | Xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.) Dadurch wird der Datenzustand vor der Löschung wiederhergestellt! ! Zusätzlich: Sie können auch ein Zeitknotenintervall für die Wiederherstellung (teilweise Wiederherstellung) angeben:
Zusätzlich zur Verwendung der POS-Knotenmethode zur Wiederherstellung können Sie auch eine Wiederherstellung durchführen, indem Sie ein Zeitknotenintervall angeben. Um nach Zeit wiederherzustellen, müssen Sie den Befehl mysqlbinlog verwenden, um den Binlog-Protokollinhalt zu lesen und den Zeitknoten zu finden. Wie oben, nach versehentlichem Löschen der Ops-Bibliothek:
Führen Sie zuerst eine vollständige Sicherungswiederherstellung durch
[root@vm-002 Sicherung]# mysql -uroot -p -v < ops_2016-09-25.sql Anzeigen der Ops-Datenbank
mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
+----+----------+-----+-----+---------+
2 Zeilen im Satz (0,00 Sek.) MySQL>
Überprüfen Sie das mysq-bin00003-Protokoll, um den Zeitknoten zu finden
[root@vm-002 ~]# cd /var/lib/mysql
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003
.............
.............
BEGINNEN
/*!*/;
# bei 173
#160925 21:57:19 Server-ID 1 end_log_pos 201 Intvar
SETZEN SIE INSERT_ID=3/*!*/;
# bei 201
#160925 21:57:19 Server-ID 1 end_log_pos 444 Abfrage Thread-ID=3 exec_time=0 error_code=0
verwenden Sie „ops“/*!*/;
ZEITSTEMPEL EINSTELLEN=1474811839/*!*/;
einfügen in ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') #Ausgeführte SQL-Anweisung
/*!*/;
# bei 444
#160925 21:57:19 Server-ID 1 end_log_pos 471 Xid = 66 #Ausführungszeit starten
BEGEHEN /*!*/;
# bei 471
#160925 21:58:41 Server-ID 1 end_log_pos 538 Abfrage thread_id=3 exec_time=0 error_code=0 #Endzeit
ZEITSTEMPEL FESTLEGEN=1474811921/*!*/;
BEGINNEN
/*!*/;
# bei 538
#160925 21:58:41 Server-ID 1 End-Log-Pos 646 Abfrage Thread-ID = 3 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1474811921/*!*/;
update ops.member set name='李四' where id=4 # Ausgeführte SQL-Anweisung
/*!*/;
# bei 646
#160925 21:58:41 Server-ID 1 end_log_pos 673 Xid = 68 #Ausführungszeit starten
BEGEHEN /*!*/;
# bei 673
#160925 21:58:56 Server-ID 1 end_log_pos 740 Abfrage thread_id=3 exec_time=0 error_code=0 #Endzeit
ZEITSTEMPEL FESTLEGEN=1474811936/*!*/;
BEGINNEN
/*!*/;
# bei 740
#160925 21:58:56 Server-ID 1 End-Log-Pos 848 Abfrage Thread-ID = 3 Exec-Zeit = 0 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1474811936/*!*/;
update ops.member set name='Element' where id=2 # Ausgeführte SQL-Anweisung
/*!*/;
# bei 848
#160925 21:58:56 Server-ID 1 end_log_pos 875 Xid = 69 #Ausführungszeit starten
BEGEHEN /*!*/;
# bei 875
#160925 22:01:08 Server-ID 1 end_log_pos 954 Abfrage thread_id=3 exec_time=0 error_code=0 #Endzeit
ZEITSTEMPEL EINSTELLEN=1474812068/*!*/;
Datenbankoperationen löschen
/*!*/;
# bei 954
#160925 22:09:46 Server-ID 1 end_log_pos 997 Rotieren zu mysql-bin.000004 pos: 4
TRENNUNGSZEICHEN ;
# Ende der Protokolldatei
ROLLBACK /* hinzugefügt von mysqlbinlog */;
/*!50003 SETZEN SIE COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; Stellen Sie die Daten vor dem Ändern von „name='李四'“ wieder her.
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:57:19" --stop-datetime="2016-09-25 21:58:41" -- Datenbank=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | xiaoer | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.)
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:41" --stop-datetime="2016-09-25 21:58:56" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | guohuihui | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.)
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:56" --stop-datetime="2016-09-25 22:01:08" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
mysql> wähle * aus Mitglied;
+----+----------+-----+-----+---------+
| ID | Name | Geschlecht | Alter | Klassen-ID |
+----+----------+-----+-----+---------+
| 1 | wangshibo | m | 27 | cls1 |
| 2 | Xiaoer | w | 27 | cls2 |
| 3 | yiyi | w | 20 | cls1 |
| 4 | Li Si | m | 22 | cls3 |
| 5 | Zhangsan | W | 21 | cls5 |
| 6 | lisi | m | 20 | cls4 |
| 7 | wangwu | w | 26 | cls6 |
+----+----------+-----+-----+---------+
7 Zeilen im Satz (0,00 Sek.)
Dadurch wird der Zustand vor der Löschung wiederhergestellt!
Zusammenfassen:
Die sogenannte Wiederherstellung besteht darin, dass MySQL die im angegebenen Absatzintervall im Binlog-Protokoll gespeicherten SQL-Anweisungen nacheinander erneut ausführt. Die obige Erklärung des MySQL-Binlog-Protokolls und der Methode zur Verwendung des Binlog-Protokolls zur Wiederherstellung von Daten ist der gesamte Inhalt, den der Editor mit Ihnen teilt. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden. Das könnte Sie auch interessieren:- MySQL-Binärprotokolldatei - Datenbank wiederherstellen
- Bringen Sie Ihnen bei, die Protokolldatei (binlog) der MySQL-Datenbank automatisch wiederherzustellen
- Detaillierte Erklärung zum Wiederherstellen von Datenbankdaten über das MySQL-Binärprotokoll
- MySQL verwendet Binlog-Protokolle zur Implementierung der Datenwiederherstellung
- Erfahren Sie, wie Sie MySQL-Daten über Protokolldateien wiederherstellen
| 
