Explication détaillée de MySQL Binlog : technologie de base pour améliorer la fiabilité de la base de données

2024-07-12

1. Introduction

1.1 Qu'est-ce que MySQL Poubelle Enregistrer?

MySQL Bin Log(Binaire Enregistrer, journal binaire) est un fichier journal de la base de données MySQL qui enregistre toutes les modifications apportées à la base de donnéesDMLopérations (telles que INSERT, UPDATE, DELETE, etc.), maisExclut SELECTattendezDQL Opération en lecture seule. Bin Log est un outil important pour MySQL pour mettre en œuvre la réplication, la récupération et l'audit. Pour plus de détails, voir :DDL, DML, DQL et DCL dans MySQL

1.2 Fonctionnement et scénarios d'application de Bin Log

Les principales fonctions de Bin Log incluent : la réplication maître-esclave, la récupération de données, la sauvegarde des données et l'abonnement aux données.

Scénarios d'application	concept	Cas
réplication maître-esclave	La bibliothèque maître envoie les enregistrements d'opération de mise à jour dans le journal binaire à la bibliothèque esclave, et la bibliothèque esclave lit le journal binaire et exécute les instructions SQL.	Plateforme de commerce électronique : la base de données principale enregistre les opérations de commande dans binlog et synchronise les données de commande de la base de données pour maintenir la cohérence de tous les nœuds.
Récupération de données	Utilisez les enregistrements binlog pour exécuter à l'envers des instructions SQL afin de récupérer des données	Système financier : les administrateurs récupèrent les enregistrements de transactions supprimés accidentellement en analysant le journal binaire pour garantir l'intégrité des données et éviter les pertes financières.
sauvegarde de données	Binlog est utilisé pour la sauvegarde incrémentielle, économisant ainsi du temps de sauvegarde et des coûts d'espace	Plateforme de médias sociaux : sauvegardez régulièrement les fichiers binlog chaque jour pour restaurer rapidement le dernier état sans avoir à sauvegarder complètement l'intégralité de la base de données.
Abonnement de données	Utilisez binlog pour surveiller les opérations de mise à jour de la base de données en temps réel	Analyse des données en temps réel des entreprises de vente au détail : en analysant le binlog, l'opération d'insertion des enregistrements de ventes est capturée et transférée à la plateforme d'analyse de données en temps réel pour l'analyse des tendances des ventes.

A travers ces cas précis, on voit plus clairement l'importance et la diversité du binlog MySQL dans des applications pratiques. Une utilisation appropriée de la fonction binlog peut améliorer considérablement la fiabilité du système, la capacité de récupération et la vitesse de réponse de l'entreprise.

2. Concepts de base de Bin Log

2.1 Principe de fonctionnement de Bin Log

Lorsque le serveur MySQL active la fonction Bin Log, toutes les opérations de modification de la base de données serontenregistrement d'événement au fichier Bin Log. Ces événements sont stockés par ordre d'exécution, formant une séquence de journal d'opération continue. Ces événements peuvent être rejoués pour recréer l'état de la base de données lorsque les données doivent être restaurées ou copiées.

2.2 Trois formats de Bin Log

MySQL Bin Log a trois formats d'enregistrement :
Voici les trois formats d'enregistrement de MySQL Bin Log organisés sous forme de tableau :

format d'enregistrement	décrire	avantage	défaut
Journalisation basée sur les instructions (SBL)	Enregistrez l'instruction SQL exécutée elle-même	Le volume du journal est petit et adapté aux opérations SQL simples.	La cohérence des données peut ne pas être garantie dans certains cas, comme dans le cas de fonctions non déterministes (telles que NOW()).
Journalisation par ligne (RBL)	Enregistrez les modifications spécifiques de chaque ligne de données	Plus précis, adapté aux opérations complexes et à la copie	Volume de journaux important, surcharge du disque et du réseau importante
Exploitation forestière mixte (ML)	Basculez entre les modes Statement et Row en fonction de circonstances spécifiques.	Compte tenu des avantages des deux	Relativement complexe à mettre en œuvre et à gérer

3. Configurer et gérer le journal du bac

3.1 Activer le journal du bac

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

Pour activer Bin Log, vous devez configurer le fichier de configuration MySQL (généralementmy.cnfoumy.ini) et effectuez la configuration suivante :

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

Après avoir modifié la configuration, redémarrez MySQL.mettre en œuvreSHOW VARIABLES LIKE 'log_bin'; La valeur Valeur est ON.

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 Configurer les paramètres du journal du bac

Les paramètres de configuration courants du journal de bac incluent :

log_bin: Activer le journal du bac.
server_id: Identifiant unique du serveur, utilisé pour la réplication.
binlog_format: Définissez le format du journal de bac (STATEMENT, ROW, MIXED).
expire_logs_days: définissez le nombre de jours d'expiration et de suppression automatiques des fichiers Bin Log.
max_binlog_size: Définissez la taille maximale d'un seul fichier journal Bin.

3.3 Gérer les fichiers journaux de bac

Commandes courantes pour la gestion des fichiers Bin Log :

Afficher la liste des fichiers journaux du bac：
```
SHOW BINARY LOGS;
1
```
Afficher la taille du fichier journal du bac：
```
SHOW MASTER STATUS;
1
```

Supprimer les anciens fichiers journaux Bin：

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

ou:

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

3.4 Afficher le contenu du journal du bac


-- 创建一个名为 '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

Utilisez l'outil mysqlbinlog pour afficher le contenu du Bin Log :

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

Si vous l'ouvrez directement, il sera tronqué.

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 Utilisation de l'outil mysqlbinlog

mysqlbinlog est un outil de ligne de commande utilisé pour analyser les fichiers Bin Log. Les options courantes incluent :

--start-datetime: Spécifiez l’heure à laquelle l’analyse commence.
--stop-datetime: Spécifiez l’heure à laquelle l’analyse se termine.
--start-position: Spécifiez la position où l'analyse commence.
--stop-position: Spécifiez la position où l'analyse se termine.

Par exemple, pour afficher le journal du bac pour une période spécifique :

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3.6 Analyser et relire le journal du bac

Les événements Bin Log peuvent être relus sur le serveur MySQL pour la récupération des données :

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

4. Application de la réplication Bin Log

4.1 Principe de réplication maître-esclave

Le principe de base de la réplication maître-esclave MySQL est que la base de données maître enregistre le journal Bin et que la base de données esclave lit et relit ces journaux pour réaliser la synchronisation des données. Les étapes spécifiques sont les suivantes :

La bibliothèque principale effectue des opérations d'écriture et enregistre ces opérations dans le journal Bin.
La bibliothèque esclave se connecte à la bibliothèque principale, lit le journal Bin et l'applique à ses propres données.

4.2 Configurer la réplication maître-esclave

Configurer la bibliothèque principale：

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

Configurer la bibliothèque esclave：

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

Créer un utilisateur de réplication sur la base de données principale：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
1
2

Configurer la réplication sur la base de données esclave：

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

Vérifier l'état de la réplication：
```
SHOW SLAVE STATUSG
1
```

4.3 Surveillance et gestion de la réplication

L'état de la réplication peut être surveillé avec la commande suivante :

SHOW SLAVE STATUSG
1

Explications des champs d'état courants :

Slave_IO_Running: État du thread IO.
Slave_SQL_Running:État du fil SQL.
Seconds_Behind_Master: Le temps pendant lequel la bibliothèque esclave est en retard par rapport à la bibliothèque maître.

4.4 Sauvegarde incrémentielle

La sauvegarde incrémentielle signifie sauvegarder toutes les modifications depuis la dernière sauvegarde complète ou la dernière sauvegarde incrémentielle. Une sauvegarde incrémentielle peut être réalisée à l'aide de Bin Log.

Effectuer une sauvegarde complète：

mysqldump --all-databases --master-data=2 > full_backup.sql
1

Enregistrer l'emplacement actuel du journal du bac：

Recherchez les lignes suivantes dans le fichier de sauvegarde complète :
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Journal du bac de sauvegarde：

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

4.5 Récupération basée sur le journal Bin

Restaurer la sauvegarde complète：
```
mysql < full_backup.sql
1
```
Appliquer une sauvegarde incrémentielle：
```
mysql < incremental_backup.sql
1
```

5. Écrivez à la fin

MySQL Bin Log Il s'agit d'un outil puissant largement utilisé dans des scénarios tels que la récupération, la réplication et l'audit de données.En configurant et en utilisant correctementBin Log , ce qui peut grandement améliorer la fiabilité et la disponibilité de la base de données MySQL. Dans les applications pratiques, il est crucial pour les administrateurs de bases de données et les développeurs de maîtriser les compétences d'utilisation et les méthodes d'optimisation de Bin Log.

Partage de technologie