Partage de technologie

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

2024-07-12

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

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'applicationconceptCas
réplication maître-esclaveLa 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éesUtilisez les enregistrements binlog pour exécuter à l'envers des instructions SQL afin de récupérer des donnéesSystè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éesBinlog est utilisé pour la sauvegarde incrémentielle, économisant ainsi du temps de sauvegarde et des coûts d'espacePlateforme 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éesUtilisez binlog pour surveiller les opérations de mise à jour de la base de données en temps réelAnalyse 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'enregistrementdécrireavantagedéfaut
Journalisation basée sur les instructions (SBL)Enregistrez l'instruction SQL exécutée elle-mêmeLe 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éesPlus précis, adapté aux opérations complexes et à la copieVolume 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 deuxRelativement 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 :

  1. La bibliothèque principale effectue des opérations d'écriture et enregistre ces opérations dans le journal Bin.
  2. 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

  1. Configurer la bibliothèque principale

    [mysqld]
    log-bin=mysql-bin
    server-id=1
    
    • 1
    • 2
    • 3
  2. Configurer la bibliothèque esclave

    [mysqld]
    server-id=2
    relay-log=relay-bin
    
    • 1
    • 2
    • 3
  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
  4. 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
  5. 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.

  1. Effectuer une sauvegarde complète

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

  1. Restaurer la sauvegarde complète

    mysql < full_backup.sql
    
    • 1
  2. 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.