Technologieaustausch

한어Русский язык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

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:

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.