A comprehensive engineering guide to zero-downtime database migration covering cursor-based stock migration, incremental sync via binlog or dual-write, CRC-based chunked validation, field-level diffing, gray read cutover with dual-read verification, write-switch patterns, and rollback procedures — all implemented in Spring Boot with production-grade patterns and pitfalls.
This article explains why MySQL COUNT(*) is slow on large tables due to MVCC, and presents a six-layer optimization strategy from emergency indexing to bucket counting tables, Redis caching, and binlog-based async counting, with code examples and 12 pitfalls to avoid.
A step-by-step guide for DBAs to handle database disk space emergencies in the final 10 minutes, covering confirmation, growth identification, safe buffering, write throttling, online expansion, and verification for PostgreSQL and MySQL, emphasizing evidence-driven actions over risky shortcuts.
This article distills production-hardened Elasticsearch patterns from a billion-item e-commerce search system, covering index mapping pitfalls, IK analyzer tuning, search_after pagination, split-brain prevention, hot-warm-cold ILM tiering, dual-path MySQL-ES sync via Canal and MQ, and a 7-point architecture review checklist.
This article deep-dives into Apache Fluss virtual tables, explaining how $changelog and $binlog suffixes expose change logs as read-only queryable tables, detailing their schemas, use cases, differences between PrimaryKey and Log tables, startup modes, limitations, and the design philosophy enabling native CDC consumption via SQL without external tools.
A production-grade MySQL backup guide analyzing a 3 AM replica crash, covering RPO/RTO design, logical vs physical backup trade-offs, XtraBackup streaming, PITR with binlog, automated restore drills, and a 3-2-1-1-0 resilience architecture.
MySQL-DTS unifies structure sync, full migration, and incremental replication in a single task for MySQL sources and MySQL/TiDB targets, featuring consistent snapshots, chunked parallelism, GTID/file+pos checkpoints, and built-in pt-osc/gh-ost compatibility for seamless online schema changes.
The article examines why the common update‑database, delete‑cache, add‑TTL workflow can still produce permanent stale data under high concurrency, explains the underlying race conditions, and compares several alternative strategies—including delete‑first, binlog‑driven invalidation, and lease‑based approaches—to help engineers choose the most reliable and low‑complexity solution for cache consistency.
This comprehensive guide analyzes four production-ready strategies for MySQL-Redis dual-write consistency, from basic cache-aside with delayed double-delete to binlog-based CDC, including Spring Boot code examples, concurrency pitfalls, and interview-focused scenario breakdowns.
This guide details a production-ready MySQL accidental deletion recovery process: immediate damage control via read locks and replication break, then binlog flashback using position-based extraction or binlog2sql for reverse SQL generation, plus delayed replica failover, with prevention strategies including sql_safe_updates, delayed replicas, and backup drills.
This article explains what MySQL's binary log (binlog) is, how it differs from the redo log, the three binlog formats, the full execution flow of an UPDATE statement, why MySQL uses a two‑phase commit, and how crash recovery ensures data consistency across primary and replica servers.
The article explains how MySQL determines whether a statement should be recorded in the binary log under ROW format, detailing the check_table_binlog_row_based function, the cached_row_logging_check flag, OPTION_BIN_LOG, and various scenarios—such as temporary tables, replication filters, no‑replicate tables, session settings, and internal operations—where binlog entries are omitted.
When MySQL runs out of disk space, the safest approach is to identify the full filesystem, examine data, binlog, temporary and log directories, and use SQL‑based cleanup commands like PURGE BINARY LOGS while never manually removing critical files such as ibdata1, ib_logfile* or active binlogs.
The article details how an e‑commerce platform re‑architected its order database by separating recent data into a hot store and historical data into a cold store, describing the motivations, design principles, write and read routing, downstream impacts, consistency safeguards, risks, and observed performance improvements.
This article analyzes common MySQL backup pitfalls, explains the three backup dimensions and five types, compares major tools such as mysqldump, mysqlpump, mydumper, XtraBackup and cloud snapshots, and provides practical scripts, sizing recommendations, scheduling, monitoring, recovery procedures, and failure case studies for reliable production backup engineering.
This article explains MySQL master‑slave replication in detail, covering its role in high‑availability and read/write separation, the core binlog mechanism, typical one‑master‑multiple‑slaves architecture, and the step‑by‑step data flow from write operations on the master to replay on the replicas.
This guide walks through why MySQL binlog can fill disks, explains its structure and formats, and provides a step‑by‑step, risk‑aware process—including preparation, safe PURGE commands, automatic expiration settings, verification, and monitoring—to clean binlog without breaking replication or losing data.
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.
Data synchronization problems often cause user complaints, inventory mismatches, and search gaps, but the root cause lies in application‑level design; this article explains the fundamentals, challenges, common scenarios, and evaluates five synchronization patterns—including cache‑aside, read‑through, write‑behind, binlog‑based replication, and transactional messaging—plus monitoring, reconciliation, and best‑practice guidelines.
This article provides a comprehensive MySQL data‑loss recovery guide, covering logical and physical backup methods, their advantages and drawbacks, detailed command‑line examples, XtraBackup installation, full and incremental backups, compression and streaming, and point‑in‑time recovery using binary logs, along with core backup best‑practice principles.
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 provides detailed explanations of ten core MySQL topics—including InnoDB lock upgrades, redo log mechanics, MVCC version chains, tablespace types, page splitting, optimizer plan selection, crash recovery, binlog vs redo log, adaptive hash index, and memory management—along with practical tips for avoiding pitfalls and tuning performance.
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 explains the fundamentals of MySQL master‑slave replication, detailing how binlog‑based data syncing works and providing a complete Docker‑based deployment guide—including configuration files, user creation, replication commands, and troubleshooting tips for a one‑master‑two‑slave cluster.
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.
Full Table UpdatePerformance OptimizationSQL_NO_CACHE
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.
Deep PaginationFull Table UpdatePerformance Optimization
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.
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 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.
Full Table UpdatePaginationPerformance Optimization
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 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.