Technologieaustausch

Ausführliche Erklärung von MySQL Binlog: Kerntechnologie zur Verbesserung der Datenbankzuverlässigkeit

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. Einleitung

1.1 Was ist MySQL? Behälter Protokoll?

MySQL Bin Log(Binär Protokoll, Binärprotokoll) ist eine Protokolldatei der MySQL-Datenbank, die alle an der Datenbank vorgenommenen Änderungen aufzeichnetDMLOperationen (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

1.2 Die Funktion und Anwendungsszenarien von Bin Log

Zu den Hauptfunktionen von Bin Log gehören: Master-Slave-Replikation, Datenwiederherstellung, Datensicherung und Datenabonnement

AnwendungsszenarienKonzeptFall
Master-Slave-ReplikationDie 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.
DatenwiederherstellungVerwenden Sie Binlog-Datensätze, um SQL-Anweisungen umgekehrt auszuführen und Daten wiederherzustellenFinanzsystem: Administratoren stellen versehentlich gelöschte Transaktionsdatensätze durch Parsen von Binlog wieder her, um die Datenintegrität sicherzustellen und finanzielle Verluste zu vermeiden.
DatensicherungBinlog wird für inkrementelle Sicherungen verwendet, wodurch Sicherungszeit und Speicherplatzkosten gespart werdenSocial-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.
DatenabonnementVerwenden Sie binlog, um Datenbankaktualisierungsvorgänge in Echtzeit zu überwachenEchtzeit-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.

2. Grundkonzepte von Bin Log

2.1 Funktionsprinzip von Bin Log

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.

2.2 Drei Formate von Bin Log

MySQL Bin Log verfügt über drei Datensatzformate:
Im Folgenden sind die drei Datensatzformate von MySQL Bin Log in Tabellenform organisiert:

AufnahmeformatbeschreibenVorteilMangel
Anweisungsbasiertes Protokollieren (SBL)Zeichnen Sie die ausgeführte SQL-Anweisung selbst aufDas 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 DatenzeileGenauer, geeignet für komplexe Vorgänge und KopiervorgängeGroß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 beiderRelativ komplex in der Implementierung und Verwaltung

3. Konfigurieren und verwalten Sie das Bin-Protokoll

3.1 Bin-Protokoll aktivieren

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)


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

Um Bin Log zu aktivieren, müssen Sie die MySQL-Konfigurationsdatei konfigurieren (normalerweisemy.cnfodermy.ini) und führen Sie die folgende Konfiguration durch:

[mysqld]
log-bin=mysql-bin
server-id=1
  • 1
  • 2
  • 3

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)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

3.2 Konfigurieren Sie die Bin-Log-Parameter

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.

3.3 Bin-Protokolldateien verwalten

Allgemeine Befehle zum Verwalten von Bin-Protokolldateien:

  • Liste der Bin-Protokolldateien anzeigen

    SHOW BINARY LOGS;
    
    • 1
  • Größe der Bin-Protokolldatei anzeigen

    SHOW MASTER STATUS;
    
    • 1
  • Löschen Sie alte Bin-Log-Dateien

    PURGE BINARY LOGS TO 'mysql-bin.000010';
    
    • 1

    oder:

    PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
    
    • 1

3.4 Inhalt des Bin-Protokolls anzeigen


-- 创建一个名为 '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');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

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)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

3.5 Verwendung des mysqlbinlog-Tools

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
  • 1

3.6 Analysieren und Wiedergeben des Bin-Protokolls

Bin-Log-Ereignisse können zur Datenwiederherstellung auf dem MySQL-Server wiedergegeben werden:

mysqlbin logbinlog.000001 | mysql -u root -p
  • 1

4. Anwendung der Bin-Login-Replikation

4.1 Prinzip der Master-Slave-Replikation

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:

  1. Die Hauptbibliothek führt Schreibvorgänge aus und zeichnet diese Vorgänge im Bin-Protokoll auf.
  2. Die Slave-Bibliothek stellt eine Verbindung zur Hauptbibliothek her, liest das Bin-Protokoll und wendet es auf ihre eigenen Daten an.

4.2 Konfigurieren Sie die Master-Slave-Replikation

  1. Konfigurieren Sie die Hauptbibliothek

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    
    • 1
    • 2
    • 3
  2. Slave-Bibliothek konfigurieren

    [mysqld]
    server-id=2
    relay-log=relay-bin
    
    • 1
    • 2
    • 3
  3. Erstellen Sie einen Replikationsbenutzer in der Primärdatenbank

    CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
    
    • 1
    • 2
  4. 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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
  5. Überprüfen Sie den Replikationsstatus

    SHOW SLAVE STATUSG
    
    • 1

4.3 Überwachen und Verwalten der Replikation

Der Replikationsstatus kann mit dem folgenden Befehl überwacht werden:

SHOW SLAVE STATUSG
  • 1

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.

4.4 Inkrementelles Backup

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.

  1. Führen Sie eine vollständige Sicherung durch

    mysqldump --all-databases --master-data=2 > full_backup.sql
    
    • 1
  2. 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;
    
    • 1
  3. Backup-Bin-Protokoll

    mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
    
    • 1

4.5 Bin-Protokollbasierte Wiederherstellung

  1. Vollständiges Backup wiederherstellen

    mysql < full_backup.sql
    
    • 1
  2. Wenden Sie eine inkrementelle Sicherung an

    mysql < incremental_backup.sql
    
    • 1

5. Schreiben Sie am Ende

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.