2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
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éesDML
opé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
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.
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.
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 |
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)
Pour activer Bin Log, vous devez configurer le fichier de configuration MySQL (généralementmy.cnf
oumy.ini
) et effectuez la configuration suivante :
[mysqld]
log-bin=mysql-bin
server-id=1
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)
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.Commandes courantes pour la gestion des fichiers Bin Log :
Afficher la liste des fichiers journaux du bac:
SHOW BINARY LOGS;
Afficher la taille du fichier journal du bac:
SHOW MASTER STATUS;
Supprimer les anciens fichiers journaux Bin:
PURGE BINARY LOGS TO 'mysql-bin.000010';
ou:
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');
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)
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
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
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
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 :
Configurer la bibliothèque principale:
[mysqld]
log-bin=mysql-bin
server-id=1
Configurer la bibliothèque esclave:
[mysqld]
server-id=2
relay-log=relay-bin
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'@'%';
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;
Vérifier l'état de la réplication:
SHOW SLAVE STATUSG
L'état de la réplication peut être surveillé avec la commande suivante :
SHOW SLAVE STATUSG
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.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
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;
Journal du bac de sauvegarde:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
Restaurer la sauvegarde complète:
mysql < full_backup.sql
Appliquer une sauvegarde incrémentielle:
mysql < incremental_backup.sql
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.