Binärprotokoll für MySQL-Betrieb und -Wartung

Das MySQL-Binärprotokoll speichert SQL-Anweisungen, die Datenänderungen verursachen oder verursachen können. Funktionen wie Echtzeit-Remote-Disaster-Recovery-Backup, Lese-/Schreibtrennung und Datenwiederherstellung können über Binärprotokolle durchgeführt werden. Schauen wir uns als Nächstes das MySQL-Binärprotokoll an.

Bin-Log-Protokoll aktivieren

Mysql aktiviert das Bin-Log-Protokoll standardmäßig nicht, wir müssen die Konfiguration selbst hinzufügen.

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

log-bin Nach der Konfiguration dieses Elements ist die Binärprotokollfunktion aktiviert. mysql-bin ist der Name der bin-log-Protokolldatei.

expire_logs_days = 10 gibt an, dass nur die Bin-Log-Protokolle der letzten 10 Tage gespeichert werden.

Im Allgemeinen werden Bin-Log-Protokolle unter dem MySQL-Installationspfad /var/ gespeichert.

Betriebs- und Wartungstipps: Es ist am besten, keine binären Protokolldateien und Datenbankdatendateien abzulegen Wenn die Festplatte, auf der die Datendateien gespeichert sind, defekt ist, können Sie das Binärprotokoll einer anderen Festplatte verwenden, um die Daten wiederherzustellen

Mehrere nützliche Befehle

• Flush-Protokolle: Neue Bin-Log-Protokolle erstellen

• Master-Status anzeigen: Den letzten Bin-Log-Protokollstatus anzeigen.

• Master zurücksetzen: Alle Bin-Log-Dateien löschen

MySQL > Master-Status anzeigen

Mysql-Protokoll anzeigen

Da es sich bei dem Protokoll um ein Binärprotokoll handelt, führt die Anzeige mit dem allgemeinen Befehl cat oder vim zu verstümmeltem Code . . MySQL stellt uns das Tool mysqlbinlog zur Verfügung. Verwenden Sie es, um es anzuzeigen.

./mysqlbinlog ../var/mysql-bin.000015
……
# at 123
#200601  8:35:19 server id 1  end_log_pos 154 CRC32 0xd25b404e  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
……

• at: POS-Knoten, wenn SQL startet

• server_id: die Dienstnummer des Datenbankhosts

• end_log_pos 154: POS-Knoten am Ende von SQL

Die allgemeinen Optionen von MySQL sind wie folgt:

• --start-datetime : Lies die angegebene Zeit aus dem Binärprotokoll, die dem Zeitstempel des lokalen Computers entspricht oder später liegt

• --stop-datetime: Lies die angegebene Zeit aus dem Binärprotokoll, die kleiner ist als der Zeitstempel oder gleich dem lokalen Computer. Der Zeitwert ist derselbe wie oben

• --start-position: Lesen Sie die angegebene Positionsereignisposition aus dem Binärprotokoll als Start.

• --stop-position: Lesen Sie die angegebene Positionsereignisposition aus dem Binärprotokoll als Ereignis ab

• -d,-- Datenbank = Name: Nur Protokollvorgänge der angegebenen Datenbank anzeigen

Bin-Log-Protokolle verwenden, um Daten wiederherzustellen

• SQL-Datei exportieren Befehl: mysqldump-Datenbankname [Datentabellenname 1 [Datentabellenname 2...]] > Externes Dateiverzeichnis (empfohlen die Verwendung von .sql)

• SQL-Dateiimport Datenbank: mysql - u** -p** Datenbankname < Verzeichnis der Sicherungsdatei

Simulieren Sie nun ein Szenario: Eine Datenbank wird regelmäßig jede Nacht um 15 Uhr gesichert und Am nächsten Tag läuft die Website einen halben Tag lang normal. Plötzlich um 17 Uhr hat Programmierer A beim DELETE versehentlich keine WHERE-Bedingung hinzugefügt, und dann gingen alle Daten in einer der Tabellen verloren. Dann suchte Xiao A den technischen Direktor Dasheng auf und bat ihn, bei der Wiederherstellung der Daten zu helfen.

Die binlog_test-Datenbank hat nur eine Benutzertabelle

Die Daten vor der Sicherung um drei Uhr morgens lauten wie folgt:

+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+

Um 3 Uhr in am Morgen, die Sicherungsdaten

mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql
ll /root/sql_backup/
总用量 4
-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql
=======数据备份完成=========

Die Website läuft seit einiger Zeit normal und viele Benutzer haben sich registriert

INSERT INTO `user` (username) values('user1'),('user2'),('user3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============新增了3个用户user1 user2 及user3==============

Um 17 Uhr nachmittags begann Little A, sich dumm zu benehmen

DELETE FROM user;
Query OK, 6 rows affected (0.00 sec)
=========没where条件，数据全没了===========

Der kleine A fand den Großen Weisen, um bei der Wiederherstellung der Daten zu helfen, und der Große Weise stellte zuerst die Daten von gestern wieder her. Die Daten wurden um 3 Uhr morgens wiederhergestellt.

service nginx stop;  # 大圣先关闭了nginx，使网站用户暂时访问不了数据库
Stoping nginx...  done 
MariaDB [binlog_test]> flush logs;  #生成新的binlog日志
MariaDB [binlog_test]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1536 |              |                  |
+------------------+----------+--------------+------------------+
mysql -v -f binlog_test < /root/sql_backup/20180706.sql

Zu diesem Zeitpunkt war der Große Weise wiederhergestellt die Daten um 3 Uhr morgens letzte Nacht

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
+---------+----------+---------------------+
=============昨晚凌晨三点数据恢复完成===============

Als nächstes wurden die Daten zwischen 3 Uhr morgens und DELETE wiederhergestellt

Suchen Sie zuerst den POS-Punkt zum Löschen. Nach der Sicherung lautet das Protokoll 000002. Nach dem Löschen. Flush-Protokolle sind ebenfalls 000003, also suchen Sie einfach den POS-Punkt vor 000002 und löschen Sie ihn.

# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >
'mysql-bin.000002' >
| mysql binlog_test;

MariaDB [binlog_test]> select * from user;
+---------+----------+---------------------+
| user_id | username | add_time            |
+---------+----------+---------------------+
|       1 | gwx      | 2018-07-05 13:00:31 |
|       2 | snn      | 2018-07-05 14:00:00 |
|       3 | zy       | 2018-07-05 15:00:00 |
|       4 | user1    | 2018-07-06 15:01:18 |
|       5 | user2    | 2018-07-06 15:01:18 |
|       6 | user3    | 2018-07-06 15:01:18 |
+---------+----------+---------------------+
==============数据都回来了========================

Das obige ist der detaillierte Inhalt vonBinärprotokoll für MySQL-Betrieb und -Wartung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!