This article explains the three core MySQL logs—binlog, redo log, and undo log—their structures, formats, configuration parameters, and how they work together to provide replication, crash recovery, and transactional atomicity while balancing performance and consistency.
This article investigates MySQL binlog numbering, discovers that the maximum sequence number is 2^31‑1 (2147483647), shows through three experiments how MySQL behaves when approaching or hitting this limit, and explains the resulting warnings and server shutdown.
This article explains how to recover MySQL data by preparing backups with binlog enabled, performing full and point‑in‑time restores using mysqldump, xtrabackup, or binlog, restoring individual tables, skipping unwanted SQL statements, and using flashback tools such as binlog2sql and MyFlash.
This article explains how ClickHouse can act as a MySQL replica, covering full and incremental synchronization, supported MySQL versions, DDL compatibility, handling of DELETE/UPDATE events, the underlying binlog mechanism, and provides complete code examples to set up both MySQL master and ClickHouse slave.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This article introduces several MySQL binlog-based change data capture solutions—including Canal, Maxwell, Databus, and Alibaba Cloud's Data Transmission Service—explaining their principles, architecture, features, and usage considerations for incremental data subscription and processing.
This article explains MySQL's two‑layer architecture, the step‑by‑step execution flow of an SQL statement—including connection, query cache, parser, optimizer, executor, and storage engine—and the roles of redo log and binlog in ensuring data consistency.
After upgrading a MySQL 5.6 master to 5.7, inserts on several InnoDB tables began failing with duplicate‑key errors because the auto_increment value became inconsistent, and the article walks through kernel‑level investigation, reproduction steps, bug analysis, and practical fixes.
This article investigates whether MySQL re‑executes UPDATE statements that do not change any data, comparing the behavior under binlog_format = ROW and binlog_format = STATEMENT with a full row image setting, and presents test results from two sessions.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This guide demonstrates how to recover MySQL 5.7 data after an accidental UPDATE by extracting row‑based binlog entries, converting them into executable SQL statements, and applying those statements to restore the original table contents.
This article experimentally investigates whether MySQL re‑executes UPDATE statements that set a column to its current value, comparing behavior under ROW and STATEMENT binlog formats and revealing how InnoDB handles redundant updates.
This article investigates how MySQL handles UPDATE statements that set columns to their existing values, comparing the behavior under ROW and STATEMENT binlog formats with binlog_row_image set to FULL, and shows that duplicate updates are skipped in ROW mode but executed in STATEMENT mode.
This article examines common operation‑log types and compares two implementation approaches—traditional AOP with annotations and a database binlog‑based solution—detailing their advantages, drawbacks, code examples, database design, future enhancements, and practical deployment considerations.
The article explains how iQIYI’s order system leverages MySQL binlog and redo log with a two‑phase commit to drive event‑driven processing, reveals hidden consistency gaps where binlog events can precede database state, and proposes retry or direct binlog consumption strategies to ensure reliable order fulfillment.
MySQL achieves crash‑safe durability by employing a write‑ahead log architecture that records changes in redo, undo, and binary logs, coordinates them through two‑phase and group commit to maintain order, and uses a structured recovery process to replay or roll back transactions after any failure.
The article explains MySQL 8.0.20's binlog transaction compression feature using the ZSTD algorithm, describes its internal implementation, outlines usage steps, presents performance test results, and lists important considerations for deploying compression in production environments.
This guide explains how to achieve MySQL high availability and scalability through full and incremental backups, binlog-based point‑in‑time recovery, and both asynchronous and synchronous replication, while highlighting trade‑offs, best practices, and practical command examples.
This article recounts a February 2020 MySQL data‑loss incident, detailing how the author identified the offending binlog, used open‑source tools like binlog2sql and MyFlash to reconstruct and restore the missing records, and reflects on operational lessons learned.
The article explains how to approximate the progress of mysqlbinlog when parsing large MySQL binary logs by observing the file handle's read offset, since the tool lacks a built‑in progress indicator.
This article explains the complete disk‑write journey of a MySQL INSERT statement, detailing the roles and order of redo log, binlog, double‑write, insert buffer, and data files, and clarifying InnoDB’s buffer‑pool and flushing mechanisms.
This article analyses why the GTID information recorded by Xtrabackup’s xtrabackup_binlog_info file can differ from the GTID shown by SHOW MASTER STATUS after a restore, comparing the behavior of Xtrabackup 2.4 on MySQL 5.7 and Xtrabackup 8.0 on MySQL 8.0, and explains the underlying backup processes and MySQL internals that cause the discrepancy.
An experiment using pt‑tools shows that a single MySQL INSERT triggers three fsync operations on the redo log and one fdatasync on the binlog, while highlighting that flush behavior can vary due to multiple internal mechanisms.
This article explains how a large e‑commerce platform replaced a MySQL‑centric intermediate table with a binlog‑driven pipeline that streams changes through Kafka into Elasticsearch, ensuring ordered, complete, and low‑latency data synchronization while addressing schema evolution and operational monitoring.
The article investigates a MySQL crash caused by binlog errors due to a full '/' partition, explains why the error leads to server abort, demonstrates reproducing the issue with large transactions, traces the problem to the my_write function in the source code, and offers mitigation strategies such as reducing transaction size or expanding the temporary directory space.
This article details a step‑by‑step guide for deploying Canal to capture MySQL binlog events, configure HA with ZooKeeper, design a client that parses binlog into JSON, asynchronously acknowledges messages, archive data to local files for batch upload to HDFS, and monitor latency for alerts.
This article explains how to use Alibaba's Canal to capture MySQL binlog changes in real time, covering its underlying protocol, component architecture, HA design with ZooKeeper, configuration steps, deployment examples, and a detailed comparison with alternative tools such as Maxwell and mysql_streamer.
Facing MySQL performance bottlenecks at massive scale, Xiaomi built the LCSBinlog service that captures MySQL binlog events, streams them through the Talos platform, and writes to Kudu for real‑time BI, detailing architecture, job scheduling, consistency guarantees, use cases, and troubleshooting lessons.
This article presents three common MySQL interview questions—whether query fields are case‑sensitive, how InnoDB implements transactions and logs, and the different binlog formats—along with detailed explanations, configuration examples, and SQL code snippets for each scenario.
The DTLE 2.19.11.0 release notes detail the project's purpose as a MySQL‑focused data transfer component, outline new features such as binlog relay and MySQL 8.0 support, list numerous bug fixes, and provide links to documentation and download resources for developers.
A detailed case study shows how a DELETE statement executed inside a MySQL stored procedure can switch binlog format to ROW, causing billions of bitmap operations on a partitioned table and resulting in severe master‑slave replication delay, and presents three practical solutions.
This guide explains how to download, install, and configure Alibaba Canal—including extracting the package, setting up canal.properties, instance.properties, and instance.xml files, and tuning key parameters—to enable reliable MySQL binlog capture for big‑data pipelines.
This article introduces Alibaba's Canal project, explaining its background, architecture, and how it parses MySQL binlog for real‑time data subscription, and provides a complete Java client example with code snippets to demonstrate connecting, fetching, and processing binlog events.
This article describes a complete solution that uses Alibaba's Canal for real‑time MySQL binlog collection, Kafka for transport, and a customized Camus pipeline to load and merge binlog data into Hive, addressing performance, consistency, and delete‑event challenges in large‑scale data warehousing.
This article explains how MySQL's slave_rows_search_algorithms setting and the presence of primary or unique indexes affect the way a replica locates rows for DELETE operations, compares index‑scan, table‑scan, and hash‑scan methods, and provides practical guidance to reduce replication lag.
Canal, an open‑source Alibaba project, mimics MySQL slave behavior to subscribe to binlog events, parses them, and supports both standalone and ZooKeeper‑coordinated cluster deployments, offering flexible state storage, message processing pipelines, and integration options such as TCP, Kafka, and RocketMQ for real‑time data synchronization.
To meet growing e‑commerce search demands, the team replaced a MySQL‑based intermediate table with a real‑time binlog‑driven pipeline that streams changes through Kafka into Elasticsearch, detailing design choices, ordering and completeness guarantees, custom modules, and monitoring for sub‑second sync latency.
This article explains MySQL's write‑set based parallel replication, detailing how writeset hashes are generated, stored in a historical map, and used to adjust the last‑commit value for improved parallel replay, including configuration parameters, code examples, and the impact of different dependency‑tracking modes.
This article provides a comprehensive, step‑by‑step analysis of MySQL’s binlog commit workflow, covering key parameters such as binlog_group_commit_sync_delay, sync_binlog, and binlog_transaction_dependency_tracking, and explains how last_commit and seq_number are generated and used in parallel replication.
This article explains why traditional MySQL queries no longer meet the growing e‑commerce data needs, describes the limitations of a MySQL‑to‑Elasticsearch intermediate table, and details a binlog‑driven, Kafka‑based pipeline with custom modules, upsert handling, filtering, and monitoring to ensure fast, reliable data synchronization.
This article explains the concepts of unitization and multi‑active data centers, analyzes the drawbacks of single‑IDC deployments, and provides a detailed guide on implementing MySQL binlog synchronization while addressing common challenges such as data loops, duplicate inserts, unique‑index conflicts, and DDL handling, including practical solutions like GTID and auxiliary tables.
The article investigates why a DELETE without a WHERE clause on a 500k‑row MySQL table takes over ten hours to replay on a slave, analyzes row‑mode binlog replay mechanisms, compares different slave_rows_search_algorithms, presents test results, and offers practical recommendations for improving replication performance.
MySQL Flashback leverages the ROW‑format binary log to generate inverse DML statements—turning deletes into inserts, updates into reverse updates, and inserts into deletes—while tools parse WriteRows, UpdateRows, and DeleteRows events, using injected Pseudo GTID markers to delimit change ranges and enable automated rollback despite DDL limitations.
This article explains MySQL binlog’s role, surveys existing flashback implementations, and details NetEase’s custom patch that adds flashback support by parsing and reversing binlog events, including event types, code logic, and comparisons with MariaDB, Oracle, and TiDB solutions.
The article explains how data heterogeneity is achieved using JD's BinLake to capture MySQL binlogs, with Flink handling sequential and parallel consumption for order, subscription, and product data, discussing challenges such as ordering guarantees, idempotency, IO overhead, and the shift toward stream‑processing architectures.
YouZan’s order data synchronization moves changes from MySQL through Canal‑parsed binlogs into a message queue, then uses sequential SeqNo‑based optimistic locking and HBase’s column‑version timestamps to guarantee ordering for both single‑ and multi‑table updates, while a Logstash‑style configurable pipeline feeds ES for search and HBase for detail queries, eliminating ordered‑queue bottlenecks and ensuring high‑throughput consistency.
The article examines MySQL 5.6’s crash‑safe slave replication, explaining how earlier versions’ unsafe relay‑log handling could corrupt position data, describing the atomic update of mysql.slave_relay_log_info via table‑based relay‑log info and transaction coordination, and covering configuration options, recovery behavior, GTID implications, performance trade‑offs, and implementation guidance.
This article tests whether MySQL re‑executes an UPDATE that sets a row to its existing values, comparing the behavior under binlog_format=ROW with binlog_row_image=FULL versus binlog_format=STATEMENT, and explains why redundant updates are ignored in ROW mode.
This article examines MySQL bug #89370, detailing how configuring semi‑synchronous replication with multiple slaves can cause a slave to stop receiving data for minutes despite normal replication status, and explains the underlying mechanisms, reproduction steps, and root cause of the issue.
This article explains why the native streaming engine Apache Flink can offer a SQL API by showing that MySQL binlog events form a time‑stamped data stream that can be replayed as a dynamic table, establishing a lossless stream‑table duality useful for both batch and streaming queries.
This article introduces the concept of data heterogeneity in large-scale systems, explains how sharding creates query challenges, describes query‑dimension and aggregation heterogeneity, and details implementation techniques such as subscribing to MySQL binlog and using Alibaba’s Canal for reliable data synchronization and storage.
This guide explains why a single MySQL server is a bottleneck, how to form a clustered master‑slave architecture, the binlog and GTID replication mechanisms, step‑by‑step configuration commands, and the trade‑offs of each approach.
This article explains Meituan's architecture for accurately and efficiently moving MySQL data into a Hive data warehouse by capturing binlog streams with Canal, transporting them via Kafka, and restoring them offline with Camus and a merge process that handles inserts, updates, and deletes.
The article presents a CDC solution that combines Alibaba’s Canal for real‑time MySQL binlog capture into Kafka with LinkedIn’s Camus for hourly Kafka‑to‑Hive loading, then merges snapshots and incremental binlog data to accurately and efficiently rebuild ODS tables, supporting sharding and delete events.
After an unexpected crash, InnoDB restores data using a multi‑stage process that first replays redo logs based on checkpoints, then leverages binlog and undo logs to resolve uncommitted transactions, with detailed steps, optimizations, and checkpoint handling explained.
The article explains the architecture and core mechanisms of JD's Virtual Order Center, describing how the Hamal service leverages MySQL binlog listening, Zookeeper coordination, fast TCP‑based consumption, read‑write separation, and multi‑level search to reliably process billions of virtual orders.
This article reviews various approaches for keeping MySQL data in sync with Elasticsearch, including direct business‑layer hooks, independent synchronization via plugins or custom scripts, and real‑time binlog subscription using tools like zongji, while discussing their advantages, drawbacks, and implementation details.
This article analyzes a MySQL master‑crash scenario where mismatched Read_Master_Log_Pos and Exec_Master_Log_Pos prevent HA failover, explains binlog event structures, and proposes updating Read_Master_Log_Pos only after a full transaction is received to ensure reliable asynchronous replication.
This guide walks through enabling MySQL binlog, installing Logtail, configuring data collection, indexing, previewing logs, writing custom SQL queries for user login analysis, constructing real‑time dashboards, setting abnormal‑login alerts, and backing up data to OSS for long‑term storage.
MyFlash is an open‑source MySQL binlog flashback tool that directly parses binlogs, filters events, reverses row operations, and generates a rollback binlog, offering faster, version‑compatible recovery than mysqlbinlog, binlog2sql, or patched MySQL, and is available on GitHub.
This article explains how MySQL 5.7 master‑slave replication works, covering the binlog formats, replication threads, event types, common causes of replication lag, and practical strategies to reduce latency and improve database availability.
After a disastrous ERP data loss caused by a careless drop, this article walks through the importance of backups and provides a detailed, practical MySQL recovery process using full backups, binlog extraction, and incremental restoration to retrieve lost tables.
After a mistaken rm -rf command wiped an entire production server—including MySQL data—the author chronicles a step‑by‑step recovery using ext3grep, custom scripts, and binlog restoration, highlighting lessons learned and best practices for future incident handling.
This article explains Pika's master‑slave replication architecture, detailing its specialized threads, full and incremental replication workflows, key API functions, and the slaveof and trysync processes that enable persistent, large‑scale data synchronization beyond Redis limitations.
This article provides a detailed walkthrough of Pika’s write2file binlog mechanism, covering manifest and binlog file structures, record formats, core implementation classes, and step‑by‑step procedures for constructing, updating, producing, and consuming logs in a large‑capacity Redis‑compatible storage system.
This guide outlines nine practical MySQL recovery methods—including full backup with binlog, business‑logic rollback, flashback tools, innodb_force_recovery, tablespace discard/import, manual ibd editing, extundelete, and Percona's recovery utility—to help DBAs restore data after accidental deletions, corruption, or hardware failures.
The article explains why a MySQL UPDATE statement took five seconds by examining binlog entries, distinguishing slow‑query scans from lock‑waits, and using a Bash script to identify long‑running transactions that reveal hidden lock contention.
This article explains how MySQL binlog timestamps are derived from the THD start_time, why all events in a transaction share the same timestamp, how exec_time is calculated, and demonstrates the effect of autocommit and manual commit on binlog timing through concrete examples and source code analysis.
This tutorial walks you through locating MySQL binlog files, extracting the faulty statements, converting the logs into SQL scripts, and safely restoring the database while handling duplicates, emphasizing the importance of backups before any operation.
This guide explains how to restore a MySQL database that was mistakenly dropped by using a nightly full backup together with binlog files, extracting only the needed incremental statements, filtering out DROP commands, and applying the recovered data in a safe, step‑by‑step process.
The article clarifies that nontransactional statements are SQL commands operating on non‑transactional tables such as MyISAM, explains how MySQL parameters max_binlog_stmt_cache_size and max_binlog_cache_size control memory for these statements, and demonstrates the difference with transactional InnoDB tables through concrete command‑line examples.
This guide shows how to restore a MySQL database that was accidentally dropped by combining a nightly full mysqldump backup (with master‑log info) and the subsequent binlog files, extracting only the needed statements and replaying them in order.
An experienced Tencent Cloud engineer shares a detailed case study of moving over 130 MySQL instances (20 TB) from on‑premise IDC to CDB for MySQL, covering test cases, migration tool mechanics, multi‑instance concurrency, binlog handling, encountered issues, and practical solutions that ensured a smooth, high‑availability transition.
This guide explains how to set up MySQL master‑slave replication, configure binlog, create users, synchronize data, troubleshoot common issues, and apply query‑performance optimizations such as limiting result sets, using covering indexes, and refactoring complex joins.
A MySQL master crashed while its sync_binlog was set to 0, causing the binlog cache to lose data and making the slave’s binary log longer than the master’s, highlighting the importance of configuring sync_binlog for durability versus performance.
This article explains how to simulate a flashback feature for MySQL and MariaDB by parsing binlogs, allowing you to restore tables after accidental DELETE or UPDATE operations without relying on full backups, and provides step‑by‑step commands and precautions.
A MySQL replica was consuming an entire CPU core due to single‑process replication, and analysis of binlog and InnoDB status revealed heavy row locks on a table without a primary key, which was resolved by adding an auto‑increment ID, dropping CPU usage to 3% and eliminating replication lag.
The article explains how MySQL replication parameters such as expire_logs_days, relay-log-recovery, and relay-log-info-repository affect binlog cleanup, SQL and I/O thread consistency, and why crashes can produce duplicate‑key errors, then offers configuration fixes including the critical relay‑log cleaning option and the super_read_only setting.
This article explains the design, configuration parameters, core data structures, initialization, coordinator distribution, worker execution, checkpointing, and shutdown procedures of MySQL 5.6's Multi‑Threaded Slave (MTS) parallel replication, providing a code‑level walkthrough for developers and DBAs.
Understanding MySQL master‑slave replication hinges on optimizing binlog size; by configuring master parameters like Binlog_Do_DB and Binlog_Ignore_DB and slave filters such as Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table, and wildcard options, you can reduce I/O, network load, and replication errors.
This article examines the evolution of MySQL parallel replication across versions 5.5, 5.6, MariaDB, and 5.7, comparing their threading strategies, binlog handling, performance trade‑offs, and practical deployment considerations for reducing replication lag.
This article explains how MySQL replication works by copying the master’s binlog to the slave and shows eight configuration parameters that let you filter databases and tables to reduce binlog volume, network load, and replication latency.
