2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
MySQL Bin Log
(Binär Protokoll, Binärprotokoll) ist eine Protokolldatei der MySQL-Datenbank, die alle an der Datenbank vorgenommenen Änderungen aufzeichnetDML
Operationen (wie INSERT, UPDATE, DELETE usw.), aberSchließt SELECT ausWartenDQL
Nur-Lese-Vorgang. Bin Log ist ein wichtiges Tool für MySQL, um Replikation, Wiederherstellung und Überwachung zu erreichen. Einzelheiten finden Sie unter:DDL, DML, DQL und DCL in MySQL
Zu den Hauptfunktionen von Bin Log gehören: Master-Slave-Replikation, Datenwiederherstellung, Datensicherung und Datenabonnement
Anwendungsszenarien | Konzept | Fall |
---|---|---|
Master-Slave-Replikation | Die Master-Bibliothek sendet die Aktualisierungsvorgangsdatensätze im Binlog an die Slave-Bibliothek, und die Slave-Bibliothek liest das Binlog und führt SQL-Anweisungen aus. | E-Commerce-Plattform: Die Hauptdatenbank zeichnet Bestellvorgänge in Binlog auf und synchronisiert Bestelldaten aus der Datenbank, um die Konsistenz aller Knoten aufrechtzuerhalten. |
Datenwiederherstellung | Verwenden Sie Binlog-Datensätze, um SQL-Anweisungen umgekehrt auszuführen und Daten wiederherzustellen | Finanzsystem: Administratoren stellen versehentlich gelöschte Transaktionsdatensätze durch Parsen von Binlog wieder her, um die Datenintegrität sicherzustellen und finanzielle Verluste zu vermeiden. |
Datensicherung | Binlog wird für inkrementelle Sicherungen verwendet, wodurch Sicherungszeit und Speicherplatzkosten gespart werden | Social-Media-Plattform: Sichern Sie Binlog-Dateien regelmäßig jeden Tag, um schnell den neuesten Stand wiederherzustellen, ohne die gesamte Datenbank vollständig sichern zu müssen. |
Datenabonnement | Verwenden Sie binlog, um Datenbankaktualisierungsvorgänge in Echtzeit zu überwachen | Echtzeit-Datenanalyse von Einzelhandelsunternehmen: Durch das Parsen von Binlog wird der Vorgang zum Einfügen von Verkaufsdatensätzen erfasst und zur Analyse von Verkaufstrends in Echtzeit an die Datenanalyseplattform übertragen. |
Anhand dieser spezifischen Fälle können wir die Bedeutung und Vielfalt von MySQL-Binlog in praktischen Anwendungen deutlicher erkennen. Durch die ordnungsgemäße Verwendung der Binlog-Funktion können die Zuverlässigkeit, die Wiederherstellungsfähigkeit und die Reaktionsgeschwindigkeit des Unternehmens erheblich verbessert werden.
Wenn der MySQL-Server die Bin-Log-Funktion aktiviert, werden alle Änderungsvorgänge an der Datenbank ausgeführtEreignisaufzeichnung in die Bin-Log-Datei hinzufügen. Diese Ereignisse werden in der Reihenfolge ihrer Ausführung gespeichert und bilden eine kontinuierliche Betriebsprotokollsequenz. Diese Ereignisse können wiederholt werden, um den Zustand der Datenbank wiederherzustellen, wenn die Daten wiederhergestellt oder kopiert werden müssen.
MySQL Bin Log verfügt über drei Datensatzformate:
Im Folgenden sind die drei Datensatzformate von MySQL Bin Log in Tabellenform organisiert:
Aufnahmeformat | beschreiben | Vorteil | Mangel |
---|---|---|---|
Anweisungsbasiertes Protokollieren (SBL) | Zeichnen Sie die ausgeführte SQL-Anweisung selbst auf | Das Protokollvolumen ist klein und für einfache SQL-Operationen geeignet. | In einigen Fällen kann die Datenkonsistenz möglicherweise nicht garantiert werden, z. B. bei nicht deterministischen Funktionen (z. B. NOW()). |
Zeilenbasierte Protokollierung (RBL) | Notieren Sie die spezifischen Änderungen jeder Datenzeile | Genauer, geeignet für komplexe Vorgänge und Kopiervorgänge | Großes Protokollvolumen, großer Festplatten- und Netzwerkaufwand |
Gemischte Protokollierung (ML) | Wechseln Sie je nach den jeweiligen Umständen zwischen dem Anweisungs- und dem Zeilenmodus. | Unter Berücksichtigung der Vorteile beider | Relativ komplex in der Implementierung und Verwaltung |
mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)
Um Bin Log zu aktivieren, müssen Sie die MySQL-Konfigurationsdatei konfigurieren (normalerweisemy.cnf
odermy.ini
) und führen Sie die folgende Konfiguration durch:
[mysqld]
log-bin=mysql-bin
server-id=1
Nachdem Sie die Konfiguration geändert haben, starten Sie MySQL neu.implementierenSHOW VARIABLES LIKE 'log_bin';
Der Wertwert ist EIN.
mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
+---------------------------------+--------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:UsershiszmMySQL5.7.26databinlog |
| log_bin_index | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
Zu den allgemeinen Bin-Log-Konfigurationsparametern gehören:
log_bin
: Bin-Protokoll aktivieren.server_id
: Eindeutige Server-ID, die für die Replikation verwendet wird.binlog_format
: Legen Sie das Format des Bin-Protokolls fest (STATEMENT, ROW, MIXED).expire_logs_days
: Legen Sie die Anzahl der Tage für den automatischen Ablauf und das Löschen von Bin-Protokolldateien fest.max_binlog_size
: Legen Sie die maximale Größe einer einzelnen Bin-Protokolldatei fest.Allgemeine Befehle zum Verwalten von Bin-Protokolldateien:
Liste der Bin-Protokolldateien anzeigen:
SHOW BINARY LOGS;
Größe der Bin-Protokolldatei anzeigen:
SHOW MASTER STATUS;
Löschen Sie alte Bin-Log-Dateien:
PURGE BINARY LOGS TO 'mysql-bin.000010';
oder:
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
`item_id` int(11) NOT NULL, -- 项目编号
`value` int(11) DEFAULT NULL, -- 值
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 最后更新时间
PRIMARY KEY (`item_id`), -- 设置项目编号为主键
KEY `value_index` (`value`), -- 为值字段创建索引
KEY `update_time_index` (`last_updated`) -- 为最后更新时间字段创建索引
) ENGINE=InnoDB; -- 使用InnoDB存储引擎
-- 插入数据,将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
Verwenden Sie das Tool mysqlbinlog, um den Inhalt des Bin-Protokolls anzuzeigen:
mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 2411 |
+---------------+-----------+
1 row in set (0.00 sec)
Wenn Sie es direkt öffnen, wird es verstümmelt.
mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: *** NONE ***
*************************** 1. row ***************************
Log_name: binlog.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: binlog.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: binlog.000001
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: binlog.000001
Pos: 219
Event_type: Query
Server_id: 1
End_log_pos: 765
Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
`item_id` int(11) NOT NULL, -- 项目编号
`value` int(11) DEFAULT NULL, -- 值
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 最后更新时间
PRIMARY KEY (`item_id`), -- 设置项目编号为主键
KEY `value_index` (`value`), -- 为值字段创建索引
KEY `update_time_index` (`last_updated`) -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)
ERROR:
No query specified
mysqlbinlog ist ein Befehlszeilentool zum Parsen von Bin-Log-Dateien. Zu den gängigen Optionen gehören:
--start-datetime
: Geben Sie den Zeitpunkt an, zu dem die Analyse beginnt.--stop-datetime
: Geben Sie den Zeitpunkt an, zu dem die Analyse endet.--start-position
: Geben Sie die Position an, an der die Analyse beginnt.--stop-position
: Geben Sie die Position an, an der die Analyse endet.Um beispielsweise das Bin-Protokoll für einen bestimmten Zeitraum anzuzeigen:
mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
Bin-Log-Ereignisse können zur Datenwiederherstellung auf dem MySQL-Server wiedergegeben werden:
mysqlbin logbinlog.000001 | mysql -u root -p
Das Grundprinzip der MySQL-Master-Slave-Replikation besteht darin, dass die Master-Datenbank das Bin-Protokoll aufzeichnet und die Slave-Datenbank diese Protokolle liest und wiedergibt, um eine Datensynchronisierung zu erreichen. Konkrete Schritte sind wie folgt:
Konfigurieren Sie die Hauptbibliothek:
[mysqld]
log-bin=mysql-bin
server-id=1
Slave-Bibliothek konfigurieren:
[mysqld]
server-id=2
relay-log=relay-bin
Erstellen Sie einen Replikationsbenutzer in der Primärdatenbank:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Konfigurieren Sie die Replikation auf der Slave-Datenbank:
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=0;
START SLAVE;
Überprüfen Sie den Replikationsstatus:
SHOW SLAVE STATUSG
Der Replikationsstatus kann mit dem folgenden Befehl überwacht werden:
SHOW SLAVE STATUSG
Allgemeine Erklärungen zu Statusfeldern:
Slave_IO_Running
: IO-Thread-Status.Slave_SQL_Running
:SQL-Thread-Status.Seconds_Behind_Master
: Die Zeit, die die Slave-Bibliothek hinter der Master-Bibliothek zurückbleibt.Bei der inkrementellen Sicherung handelt es sich um die Sicherung aller Änderungen seit der letzten Vollsicherung oder der letzten inkrementellen Sicherung. Eine inkrementelle Sicherung kann mithilfe von Bin Log erreicht werden.
Führen Sie eine vollständige Sicherung durch:
mysqldump --all-databases --master-data=2 > full_backup.sql
Notieren Sie die aktuelle Position des Bin-Protokolls:
Suchen Sie in der vollständigen Sicherungsdatei nach den folgenden Zeilen:
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
Backup-Bin-Protokoll:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
Vollständiges Backup wiederherstellen:
mysql < full_backup.sql
Wenden Sie eine inkrementelle Sicherung an:
mysql < incremental_backup.sql
MySQL Bin Log
Es handelt sich um ein leistungsstarkes Tool, das häufig in Szenarien wie Datenwiederherstellung, Replikation und Überwachung eingesetzt wird.Durch die richtige Konfiguration und VerwendungBin Log
, was die Zuverlässigkeit und Verfügbarkeit der MySQL-Datenbank erheblich verbessern kann. In praktischen Anwendungen ist es für Datenbankadministratoren und Entwickler von entscheidender Bedeutung, die Verwendungsfähigkeiten und Optimierungsmethoden von Bin Log zu beherrschen.