The article explains that MySQL redo log operates at the InnoDB engine layer to ensure transaction durability and crash recovery, while binlog works at the server layer to record logical changes for replication, archiving, and point‑in‑time recovery, highlighting their distinct layers, purposes, content, and write mechanisms.
This article explains how MySQL master‑slave replication works, covering its role in high‑availability and read/write separation, the responsibilities of the master and slave servers, the IO and SQL threads, common replication topologies, and the step‑by‑step data flow from binlog to relay log.
This article explains the design of a zero‑code, real‑time data quality alert platform that leverages Binlog‑based ingestion, configurable metrics, automated attribution, and LLM‑driven decision making to provide fine‑grained monitoring, rapid response, and measurable operational benefits across marketing workflows.
This article provides a comprehensive interview guide on MySQL’s three core logs—redo log, undo log, and binlog—covering their architecture, roles in crash recovery, MVCC, replication, configuration commands, two‑phase commit workflow, and frequent follow‑up questions to help candidates ace database interview questions.
This article explains MySQL's redo log and binlog, why their write order can cause master‑slave inconsistencies, and how the two‑phase commit protocol atomically coordinates both logs to ensure reliable data consistency in distributed transactions.
This article examines the common data‑consistency challenges when writing to both a database and a Redis cache, evaluates four write‑order strategies, and presents the most reliable approach—writing to the database first then deleting the cache—along with retry mechanisms using scheduled jobs, message queues, and binlog listeners.
This article explains why a naïve full‑table UPDATE on massive MySQL tables can cripple replication, explores deep‑pagination and IN‑clause inefficiencies, and presents a batch‑processing strategy using NO_CACHE, FORCE INDEX, and rate‑controlled scripts to perform safe, high‑performance updates.
This guide explains how to restore lost MySQL data after accidental DELETE operations by using binary log replay, logical and physical backups, third‑party recovery utilities, and preventive measures such as proper binlog configuration, backup strategies, and permission controls.
This article examines the need for comprehensive business operation logging in a central system, outlines the benefits of audit, security, monitoring, and analysis, and walks through three progressive solutions—AOP with annotations, AOP with SpEL, and a Binlog‑based time‑window approach—detailing their implementations, trade‑offs, and architectural considerations.
An in‑depth guide to MySQL binary logs explains their role in replication, point‑in‑time recovery, auditing, and real‑time data pipelines, covering binlog formats, GTID, encryption, multi‑threaded replication, cleanup strategies, and practical mysqlbinlog commands for monitoring and troubleshooting.
This article provides a comprehensive guide to MySQL’s three essential logs—redo, undo, and binlog—detailing their hierarchy, purposes, write mechanisms, configuration parameters, and how they cooperate during transaction processing and replication, while also offering troubleshooting tips for common issues.
This guide walks you through a full disaster‑recovery workflow for MySQL, covering emergency read‑only switching, pinpointing the deletion time via binlog, preparing a recovery instance, restoring the latest full backup with Xtrabackup or mysqldump, applying incremental binlog changes, verifying data integrity, and safely switching traffic back to the restored database.
This article explains the differences between MySQL's RR and RC isolation levels, details master‑slave replication and binlog formats, shows why RR is the default, and why many internet companies now prefer RC for higher concurrency despite its phantom‑read trade‑offs.
This article explains why the binlog Position may not advance after executing SQL on an OceanBase MySQL tenant, outlines two main causes, and provides step‑by‑step commands to verify binlog task health and identify unsupported statements.
This article explains the distinct yet collaborative roles of MySQL's Redo Log and Binlog, covering two‑phase commit, write‑ahead logging, flushing mechanisms, performance trade‑offs, and their use cases in crash recovery, replication, and point‑in‑time restoration.
This article explores why MySQL alone struggles with large‑scale queries, introduces Elasticsearch as a complementary search engine, and compares several synchronization methods—including synchronous and asynchronous dual‑write, Logstash, binlog‑based, Canal, and Alibaba Cloud DTS—detailing their advantages, drawbacks, and typical application scenarios.
This article walks through a real‑world OceanBase ODP binlog connection failure, explains the error message, analyzes possible causes, shows how to trace logs and configuration, and provides a concrete fix by correcting the binlog_service_ip setting and restarting ODP.
This article walks through two enterprise MySQL disaster‑recovery scenarios, detailing backup policies, binlog extraction, temporary database restoration, and step‑by‑step commands to safely recover accidentally dropped tables and ensure data integrity.
This article explains the differences between MySQL’s default REPEATABLE READ (RR) and READ COMMITTED (RC) isolation levels, why large internet firms switch to RC, the mechanics of master‑slave replication, the three binlog formats, and how gap locks affect consistency and performance.
This article explores a step‑by‑step evolution of business operation logging—from a simple AOP‑annotation approach, through enriched SpEL expressions, to a robust Binlog‑based solution with time‑window aggregation—highlighting benefits, challenges, code examples, and architectural diagrams for backend developers.
This guide explains how to restore mistakenly deleted MySQL rows by leveraging binary logs, covering binlog activation, mode configuration, locating relevant log files, extracting the original INSERT statements or generating reverse DELETE statements, and using open‑source tools to automate the recovery process.
This article explains how the REPLACE INTO statement can cause mismatched AUTO_INCREMENT values between MySQL master and replica after a failover, leading to primary‑key conflicts, and provides reproduction steps, underlying mechanics, and practical mitigation strategies.
This article provides a comprehensive overview of MySQL master‑slave replication, explaining its core mechanisms, the push‑pull hybrid model, replication modes (asynchronous, full‑sync, semi‑sync), binlog formats, and practical strategies to reduce replication lag caused by large transactions, server load, and network constraints.
Learn how to reduce MySQL 8 master‑slave replication delay from seconds to sub‑second levels by tuning five key parameters and applying three layers of parallel binlog replication, with detailed configuration examples, performance test results, troubleshooting tips, and ready‑to‑use scripts.
This article explains why MySQL alone struggles with large‑scale, complex queries, introduces Elasticsearch as a complementary search engine, and compares five practical synchronization approaches—synchronous double‑write, asynchronous double‑write, Logstash, Binlog, and Canal/DTS—detailing their mechanisms, advantages, disadvantages, and typical use cases.
Learn how MySQL’s three essential logs—Redo Log for durability, Undo Log for transaction rollback, and Binlog for replication—work together, understand their configurations, performance trade‑offs, and practical monitoring and recovery techniques to ensure data integrity in production environments.
This article explains how MySQL’s three essential logs—redo, undo, and binlog—work together to guarantee data durability, enable crash recovery, support transaction rollback, and power master‑slave replication, while providing practical configuration tips, monitoring queries, and recovery procedures for production environments.
This article breaks down MySQL's three core log types—undo log, redo log, and binlog—explaining how they support transaction atomicity, durability, MVCC, crash‑safe recovery, write‑ahead logging, and replication, while also covering buffer pool behavior and flush policies.
This article explains why MySQL‑Elasticsearch synchronization is needed in large‑scale projects, compares several sync methods—including synchronous and asynchronous double‑write, Logstash, Binlog, Canal, and Alibaba Cloud DTS—detailing their implementation steps, advantages, disadvantages, and typical application scenarios.
This article explains the sequential write process of Undo Log, Redo Log, and Binlog during MySQL transaction execution and commit phases, describes the involved caches, two‑phase commit rationale, and outlines key configuration parameters for each log type.
This article analyzes the pain points of inconsistent online‑offline configurations, proposes three synchronization schemes—including timed batch sync, automatic real‑time binlog‑MQ sync, and manual selective sync—compares their applicability, risks and performance, and presents practical deployment results across multiple projects using MySQL and TiDB.
MySQL employs a two‑phase commit to synchronize InnoDB’s redo log with the server’s binlog, ensuring atomicity during transaction commits and preventing data inconsistencies during crash recovery or replication, while also introducing lock contention, extra I/O, and potential blocking.
This article explains how to use MySQL flashback utilities such as binlog2sql, my2sql, and MyFlash to recover from accidental DML operations, covering supported SQL types, required binlog settings, tool features, command‑line usage examples, and best‑practice recommendations for preventing future data loss.
This article examines a failure case of OceanBase's OBLogProxy service in Binlog mode, detailing background conditions, status checks, log analysis, the root cause of timestamp mismatches, and provides step‑by‑step remediation procedures to restore normal binlog processing.
This article explains how MySQL redo logs and binlog logs work together to ensure data durability, consistency, and high availability, covering InnoDB crash‑recovery, flush policies, log file groups, binlog formats, two‑phase commit, and the complete recovery workflow.
This article explores comprehensive strategies for capturing business operation logs in a Spring‑based system, comparing three solutions—from a simple AOP‑annotation approach, through an enhanced AOP + SpEL method, to a robust Binlog‑plus‑time‑window architecture—while weighing their advantages, drawbacks, and implementation details.
This article breaks down MySQL's three binlog formats—Statement, Row, and Mixed—detailing how each records changes, their advantages and drawbacks, and when to choose the most appropriate mode for reliable replication.
This article details the background, configuration, and step‑by‑step troubleshooting process for a data pipeline that replaces a MySQL source with OceanBase using OBLogProxy in binlog mode, explains why downstream reads missed incremental data, and provides conclusions and optimization measures.
This article presents two real‑world MySQL recovery scenarios—full backup restoration after an accidental table deletion and incremental recovery using XtraBackup—detailing backup strategies, binlog extraction, step‑by‑step commands, and validation procedures to safely restore production databases.
This article provides a comprehensive analysis of MySQL’s internal architecture, detailing the four-layer design, the server service layer’s parsing, optimization and execution components, and the critical roles of Undo Log, Redo Log, and Binlog in ensuring ACID transaction properties.
To recover accidentally deleted MySQL rows, ensure binary logging is enabled and set to ROW mode, locate the appropriate binlog files, use mysqlbinlog (or tools like binlog2sql) to extract the original INSERT events within a time window, and replay or reconstruct them to restore the data.
This article explains why MySQL alone struggles with large‑scale queries, introduces Elasticsearch as a complementary search engine, and compares several synchronization approaches—including synchronous and asynchronous dual‑write, Logstash, Binlog, Canal, and Alibaba Cloud DTS—detailing their implementations, advantages, disadvantages, and suitable use cases.
This article explains how MySQL stores and manages data by introducing the InnoDB storage engine, its buffer pool, data pages, B+Tree and adaptive hash indexes, change buffer, undo/redo logs, binlog, and the server layer that parses, optimizes, and executes SQL statements.
This article examines various methods for synchronizing MySQL data with Elasticsearch, including synchronous and asynchronous dual writes, Logstash pipelines, binlog streaming, and Alibaba Cloud DTS, outlining implementation approaches, advantages, disadvantages, and suitable application scenarios for each solution.
This guide explains how to prevent data loss in MySQL by enabling backups and binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements, and using flashback tools like binlog2sql and MyFlash.
This article provides a comprehensive overview of MySQL's layered architecture, the functions and core components of the server layer, and detailed explanations of Undo Log, Redo Log, and Binlog mechanisms that together ensure the ACID properties of transactions, including practical execution flows for queries and updates.
This article demonstrates how to achieve real‑time synchronization of MySQL data to Redis using Spring Boot 3, the open‑source mysql‑binlog‑connector‑java library, and optional JMX exposure, providing step‑by‑step setup, code examples for parsing binlog events, listening to changes, and configuring Maven dependencies.
This article explains why MySQL alone may struggle with large‑scale, complex queries, introduces Elasticsearch as a high‑performance search engine, and compares several synchronization approaches—including synchronous and asynchronous dual‑write, Logstash, Binlog, Canal, and Alibaba Cloud DTS—detailing their advantages, disadvantages, and suitable scenarios.
This article walks through the design and implementation of a decoupled, high‑performance business operation log solution that captures MySQL binlog changes via Canal, streams them through Kafka, and stores and queries them in Elasticsearch, addressing challenges such as batch operations, multi‑table transactions, and non‑business data filtering.
Learn how to schedule automated MySQL backups for a Docker container using Linux crontab, create optimized backup scripts, compress and retain backups, and recover lost data with binlog files, including detailed command examples and best practices for reliable database maintenance.
Updating billions of rows in a MySQL table can overwhelm binlog replication and cause deep‑pagination inefficiencies, so this article explains the pitfalls of direct UPDATE, explores limit‑based and IN‑based approaches, and presents a production‑ready batch update strategy using NO_CACHE and forced primary‑key indexing.
The article explains why executing a full‑table UPDATE on large MySQL tables using row‑based binlog can overload replication, discusses the inefficiencies of deep pagination and IN clauses, and presents a batch‑processing strategy that leverages SQL_NO_CACHE and FORCE INDEX to safely and efficiently migrate data.
This article explains why a simple full‑table UPDATE on massive MySQL tables can cripple master‑slave replication, analyzes deep‑pagination inefficiencies, and presents a step‑by‑step batch‑update strategy using NO_CACHE and FORCE INDEX to keep binlog size and buffer‑pool impact under control.
This article explains the core components of MySQL's InnoDB storage engine—including undo log, redo log, buffer pool, change buffer, and binlog—detailing their roles in data writing, crash recovery, MVCC, and master‑slave synchronization, and describing related flushing strategies and LRU management.
Explore 15 classic MySQL log interview questions covering redo log, WAL, binlog, undo log, two‑phase commit, write‑ahead logging, crash‑safe mechanisms, log flushing settings, and recovery procedures, with detailed explanations, execution flow diagrams, and configuration parameters to help you ace database engineering interviews.
This article introduces Pika 3.5.x’s major enhancements—including a new sync mechanism, expanded Redis command support, RocksDB upgrades, BlobDB, containerization, observability tools, ACL and transaction support—while providing a step‑by‑step upgrade procedure, optional data synchronization, and two rollback strategies for safe migration.
The article explains how to prepare for interview "if" questions about Binlog loss by describing Flink CDC's binlog extraction principles, possible recovery mechanisms, and practical strategies such as resetting offsets, extending log retention, and building offline‑online reconciliation pipelines.
This article compiles 15 classic MySQL log interview questions and provides detailed explanations of redo log, WAL, binlog, undo log, two‑phase commit, crash‑safe mechanisms, and related configuration parameters, helping readers understand database recovery and replication fundamentals.
This article explains the challenges of executing full‑table UPDATE statements on large MySQL tables using row‑based binlog replication, analyzes deep pagination issues, and presents a batch‑processing strategy with FORCE INDEX, SQL_NO_CACHE and controlled rate limiting to safely migrate data at scale.
This article explains why a direct full‑table UPDATE on a massive MySQL table can overwhelm binlog replication, explores the pitfalls of deep pagination and IN‑clauses, and presents a batch‑processing strategy using NO_CACHE and FORCE INDEX to achieve efficient, low‑impact updates.
This article explains the challenges of performing full‑table updates on large MySQL tables using row‑based binlog replication and presents a step‑by‑step batch‑update strategy that leverages SQL_NO_CACHE, FORCE INDEX, and controlled pagination to minimize binlog volume and maintain performance.
This article explains the four major differences between MySQL’s redo log and binlog—including their purposes, recorded content, data types, and lifecycle—highlighting how redo logs ensure data durability while binlogs support replication, recovery, auditing, and high‑availability scenarios.
This article explains various methods for synchronizing MySQL data to Elasticsearch, including synchronous and asynchronous double‑write, Logstash pipelines, binlog‑based real‑time sync, Canal, and Alibaba Cloud DTS, comparing their advantages, disadvantages, and suitable scenarios.
This article explains why MySQL‑Elasticsearch synchronization is needed, compares six practical approaches—including sync write, async write, Logstash, binlog, Canal, and Alibaba Cloud DTS—detailing their implementation methods, advantages, disadvantages, and suitable use cases for large‑scale data environments.
This article examines whether a MySQL replica can safely omit setting sync_binlog=1, explains the underlying InnoDB and GTID mechanisms, presents an experiment reproducing power‑loss scenarios, and analyzes how different master_auto_position settings affect data consistency and GTID continuity during recovery.
This guide compares four MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous MQ‑based dual‑write, timer‑driven SQL extraction, and real‑time Binlog replication—and reviews popular CDC tools such as Canal, Alibaba Cloud DTS, Databus, and others to help you choose the right solution.
The article analyzes a MySQL replication error where a MEMORY table is cleared after a master restart, explains why the binlog records only a statement despite row format, and provides step‑by‑step troubleshooting and remediation measures to ensure data consistency.
This article presents 15 classic MySQL log interview questions and provides detailed explanations of redo log, WAL, binlog, undo log, two‑phase commit, log flushing mechanisms, and recovery strategies, helping readers deepen their database knowledge and ace technical interviews.
This article reviews four common MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous dual‑write via MQ, timer‑based SQL extraction, and real‑time Binlog replication—evaluates their pros and cons, and compares popular migration tools such as Canal, Alibaba DTS, Databus and others.
This article compares four MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous dual‑write, SQL extraction, and Binlog‑based real‑time sync—evaluates their pros and cons, and reviews popular migration tools such as Canal, Alibaba DTS, Databus, Flink, CloudCanal, Maxwell, and DRDS.
The article explains how to respond to accidental MySQL data deletion by analyzing loss causes, choosing recovery methods such as mysqldump backups, binary‑log point‑in‑time restores, or Percona’s InnoDB tool, provides step‑by‑step commands, and recommends preventive practices like regular backups, transactions, and strict permissions.
This guide explains how to answer MySQL accidental deletion interview questions and outlines practical recovery methods—including backup restoration, binlog usage, third‑party tools—and essential preventive practices such as regular backups, transaction handling, and permission management.
This article reviews the 14-part series on MySQL's transaction module, covering initialization, transaction types, two‑phase commit, binlog handling, savepoints, and full rollback, and provides links to each detailed post, including discussions on BEGIN statements, read‑only transactions, and the internal mechanisms of InnoDB.
This article explains how MySQL 8.0.32 rolls back a transaction by first attempting a binlog rollback (which does nothing for a plain ROLLBACK), then performing InnoDB undo‑log processing, committing the internal InnoDB changes, and finally clearing temporary binlog data from the transaction cache.
This article explains what MySQL binlog is, how it enables master‑slave replication, and explores practical business applications such as data heterogeneity, cache synchronization, and task distribution, while highlighting performance considerations and the benefits of using binlog‑based middleware.
This article explains how InnoDB implements savepoints in MySQL 8.0.32, covering undo log sequencing, the savepoint structures in the server, binlog, and InnoDB layers, the process of locating and deleting duplicate savepoints, and the three-step procedure for creating a new savepoint.
The article explains why data inconsistency occurs between MySQL and Redis under high concurrency, analyzes cache‑delete timing issues, and presents two solutions—delayed double‑delete and asynchronous cache updates via MySQL binlog—detailing implementation steps, advantages, drawbacks, and practical considerations.
This article explains how MySQL 8.0.32 processes the flush, sync, and commit subphases during a two‑phase commit, detailing the roles of leader and follower threads, log handling, system variables, and the conditions that trigger binlog rotation and persistence.
This article explores the motivations, benefits, and step‑by‑step implementations of business operation logging, starting with a simple AOP‑annotation approach, advancing to AOP combined with Spring Expression Language for richer context, and finally leveraging MySQL binlog and time‑window techniques for low‑level, reliable change capture.
This article examines the progressive design of business operation logging—from a basic AOP‑annotation approach, through an AOP‑SpEL enhancement, to a binlog‑based time‑window solution—detailing each method's implementation steps, trade‑offs, and practical considerations for robust audit and monitoring in enterprise systems.
This article explores the evolution of business operation logging in a Java backend, starting from a simple AOP‑annotation approach, advancing to AOP combined with Spring Expression Language for richer context, and finally leveraging MySQL binlog with time‑window processing to achieve comprehensive, low‑intrusion audit trails.
This article explains how to use a MyBatis interceptor to cheaply improve database stability for high‑growth tables, compares common archiving and sharding solutions, and provides a complete, code‑driven implementation of date‑based horizontal sharding with binlog‑driven double‑write to handle critical‑point data continuity.
This article explains MySQL's binary log (binlog), a binary format file that records SQL statements for database updates, its role in master-slave replication, crash recovery, and detailed implementation mechanisms including cache management and event writing processes.
This article explains how to deploy and use Alibaba Canal to capture MySQL binlog changes, configure MySQL and Canal, and write Java code that listens to row‑level events for synchronizing data to other systems such as Redis or Elasticsearch.
This article explains how Apache Pulsar is used within Apache InLong to collect, sort, and reliably deliver massive MySQL binlog incremental data, covering component architecture, job isolation, client and producer management, consumption strategies, common pitfalls, performance tuning, and practical code examples.
This article explains why master‑slave lag occurs in MySQL, describes the evolution of parallel replication schemes—including group‑commit, Commit‑Parent‑Based, Lock‑Based, and WRITESET approaches—shows benchmark results, and provides practical configuration steps to enable high‑performance parallel replication.
This tutorial walks through configuring Canal in cluster mode to capture MySQL row‑based binlog events, publish them to a RocketMQ topic, and consume the ordered messages to update an Elasticsearch index, covering MySQL, RocketMQ, Elasticsearch mappings, and the required Canal properties.
This tutorial demonstrates how to use Alibaba's Canal in cluster mode with Zookeeper to capture MySQL row‑based binlog events, publish them via RocketMQ, and consume them with a custom listener that transforms inserts, updates, and deletes into synchronized documents in an Elasticsearch index.
This guide walks through setting up a test environment, extracting binlog positions, generating rollback SQL with the my2sql tool, and safely re‑importing the data to restore rows mistakenly removed by a DELETE statement.
This article thoroughly examines MySQL replication, detailing binlog formats, event types, replication workflows, semi‑synchronous and parallel replication techniques, performance benchmarks, and practical implementation steps such as fake‑slave registration and connection‑pool enhancements, while illustrating each concept with concrete examples and code snippets.
The article compares four MySQL‑to‑Elasticsearch synchronization approaches—synchronous dual‑write, asynchronous dual‑write via MQ, timer‑based SQL extraction, and binlog‑based real‑time sync—evaluates their trade‑offs, and recommends practical tools such as Canal, DTS, and Databus for implementation.
This article explains why a single MySQL instance is insufficient for production, introduces binlog concepts and formats, details the master‑slave replication threads and workflow, explores parallel replication mechanisms, analyzes causes of replication lag, and provides practical strategies to minimize that delay.
This article introduces MySQL’s three primary logs—redo log, binlog, and undo log—explaining their purposes, how they ensure durability and recovery, the mechanisms for flushing them to disk, and the key differences among them, along with practical configuration recommendations.
reverse_sql is an open‑source utility that parses MySQL binary logs in ROW format, generates both original and reverse SQL statements, and helps recover data from accidental DML operations using multithreaded processing and flexible filtering options.
This article walks through MySQL's three‑layer logical architecture, explains the roles of redo, undo, bin, and relay logs, shows how they ensure crash‑safety and point‑in‑time recovery, and provides practical commands for inspecting and configuring these logs.
This article explains MySQL master‑slave architecture, why it’s used, the replication process, consistency challenges, causes of replication lag, and practical strategies—including binlog formats, mixed mode, monitoring, caching, and failover setups—to optimize performance and ensure high availability.
The article explains a real‑world MySQL replication failure caused by a 4 GB binlog, walks through the replication architecture, binlog write mechanics, diagnostic commands, mode differences, and shows how a large row‑based transaction with a BLOB field inflated the log, concluding with a practical fix.
This guide explains how to use the open‑source binlog_parse_sql tool to parse MySQL 8.0 binary logs, generate SQL statements, and seamlessly migrate data to MariaDB or ClickHouse, covering installation, execution modes, troubleshooting, and practical ETL scenarios.
This article explains MySQL's binlog, its role in master‑slave replication, and how businesses can harness it for data heterogeneity, cache synchronization, and task dispatch, illustrating practical middleware designs that transform raw changes into valuable services.
This article explains MySQL replication, covering its background, binlog fundamentals, replication formats, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world implementation details for high availability and data synchronization.
