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.
This article explains a MySQL master‑slave synchronization failure caused by a corrupted relay log, walks through the replication principles, analyzes slave status and GTID differences, and provides step‑by‑step commands to skip the problematic GTID and restore replication.
This article explains MySQL replication’s background, binlog formats, event types, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world deployment strategies such as HA components, middleware, remote binlog copying, and data‑transfer services, providing a comprehensive guide for building highly available and scalable MySQL infrastructures.
MySQL replication copies data changes from a primary server to one or more replicas using binlog events—supporting statement, row, or mixed formats and GTID positioning—to provide real‑time backup, read‑write separation, high‑availability failover, and integration pipelines via asynchronous, semi‑synchronous, or centralized binlog services.
This article compares two methods for synchronizing MySQL data with Redis cache—using MySQL triggers combined with a UDF function, and parsing MySQL binlog via tools like Canal—detailing their workflows, advantages, limitations, and implementation considerations.
This article introduces Alibaba's open‑source Canal middleware, explains its architecture and high‑availability design, walks through MySQL binlog configuration, Canal setup, and provides a complete Java client example for real‑time data synchronization, cache refresh, and task dispatch scenarios.
This article explains two technical solutions for keeping MySQL data in sync with Redis cache—using a MySQL trigger with a UDF function and parsing MySQL binlog events—while also reviewing the Canal open‑source tool and discussing their trade‑offs and implementation details.
This article explains MySQL’s internal architecture, walks through how a SELECT query and an UPDATE statement are processed—including the connector, parser, optimizer, executor, redo log, binlog, and two‑phase commit—and details transaction properties, isolation levels, MVCC, and related configuration parameters.
The article explains how executing FLUSH commands on MySQL replicas that are in read_only mode can still write to the binary log, generate new GTIDs, and lead to GTID mismatches between primary and replica, especially when tools like ClickHouse issue such statements.
This article examines MySQL’s transition from dual‑master to master‑slave replication, highlights the complexities of position‑based syncing, introduces GTID as a more reliable alternative, and provides practical steps to enable GTID and accurately monitor replication lag using seconds_behind_master, binlog positions, and GTID sets.
This article explores the challenges of cache‑database consistency under the cache‑aside pattern, compares four cache‑update strategies, and provides practical techniques—including delayed double delete, MQ‑driven invalidation, and binlog subscription—to achieve near‑real‑time consistency while maintaining high read performance.
This article walks through the complete lifecycle of a MySQL UPDATE statement, covering InnoDB page structure, tablespaces, the buffer pool, redo and undo logs, binlog, and the two‑phase commit process, illustrating each step with examples and command outputs.
This article walks through a step‑by‑step investigation of why incremental backups failed on a DBaaS platform, revealing that the PERCONA_SCHEMA.xtrabackup_history table’s binlog_pos column was too short, and shows how altering its length resolves the problem.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
The e‑commerce platform’s order‑query alerts were caused by MySQL master‑slave replication lag—specifically, delayed redo‑log flushing let the slave receive binlog events before the master committed—so queries returned empty, a condition mitigated by retry logic, delayed MQ delivery, or emitting post‑commit business events.
The article explains query separation as a technique to improve performance of massive database tables by storing write‑optimized data and read‑optimized copies in separate systems, discusses when to apply it, implementation methods, storage choices, consistency handling, and practical considerations.
This article explains MySQL’s binlog, how it enables master‑slave replication, and explores three practical business uses—data heterogeneity, cache synchronization, and task dispatch—showing how a middleware that pretends to be a slave can leverage binlog events to improve system architecture and performance.
This article compares the MySQL binlog parsing tools my2sql and binlog2sql, detailing their parameters, performance benchmarks on 2 GB binlog files with varying thread counts, feature differences, usage constraints, and provides practical guidance for selecting the appropriate tool.
This article explains why timestamps in MySQL binary logs can appear out of order, detailing how event times are derived from the thread start time, how long‑running transactions and binlog rotation affect ordering, and provides code examples and an experiment to illustrate the behavior.
This guide explains TiDB Binlog fundamentals, its Pump‑Drainer architecture, how to scale Drainer nodes, use the Reparo tool to parse binlog files, and combine full backups with binlog for reliable data recovery after accidental deletions.
This article compares two approaches for keeping MySQL and Redis in sync—using MySQL triggers combined with a UDF to write directly to Redis, and parsing MySQL binlogs (e.g., with Alibaba's Canal) to propagate changes—detailing their workflows, advantages, limitations, and implementation details.
This guide explains how to restore a mistakenly dropped MySQL table by enabling binlog, creating backups, extracting relevant statements from the binlog, editing out DROP commands, and re‑importing the data to recover the table to its state before deletion.
The article examines how a large transaction in MySQL 5.7 can block the SHOW MASTER STATUS command by holding the lock_log during the commit's flush stage, analyzes the commit and SHOW MASTER STATUS code paths, and proposes mitigation strategies such as avoiding big transactions and using SELECT @@global.gtid_executed.
This article explains the MySQL max_allowed_packet limit, how packets are structured and split, what operations count as a packet, and demonstrates through tests how the setting affects SQL imports, binlog replication, query results, and LOAD DATA operations.
This article explains MySQL's layered design, detailing how the Server and InnoDB storage engine interact, the role of the Buffer Pool in caching data, and the functions of redo log, undo log, and binlog in ensuring durability, recovery, and replication.
This article explains in detail what happens inside MySQL when an UPDATE statement is executed, covering the execution flow, the roles of undo log, redo log, and binlog, buffer pool mechanics, two‑phase commit, group commit, and practical optimization tips.
This article explains MySQL's redo log, binlog, and undo log, covering their roles in crash recovery, durability, performance optimization, log formats, two‑phase commit handling, and how they together ensure data consistency and atomicity in InnoDB.
This article analyzes a MySQL replication failure where the slave reports error 1236 "Could not open log file", explains the root cause involving duplicate entries in the binlog index after manual deletion of active binlog files, and provides step‑by‑step reproduction procedures and preventive recommendations.
This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
This article details the background, architecture, core features, and operational practices of a custom high‑reliability, high‑throughput key‑value storage system that combines Raft replication, flexible partitioning, binlog support, bulk loading, and multi‑active deployment for B‑Station's diverse data workloads.
This article presents a systematic method for parsing massive MySQL binlog files, identifying which fields are truly updated in each SQL statement, quantifying effective versus ineffective updates, and applying the results to optimize database design and reduce unnecessary binlog generation.
This guide explains how to prevent data loss in MySQL by enabling row‑based binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements using binlog or GTID, and leveraging flashback tools such as binlog2sql and MyFlash.
This article explains MySQL high availability concepts, how master‑slave replication works, how to measure and mitigate replication lag, and compares reliable‑first and availability‑first failover strategies with practical experiments on binlog formats.
This article delves into MySQL’s internal logging mechanisms—redo log, undo log, and binlog—explaining their structures, purposes, and interactions, and then explores transaction concepts such as dirty reads/writes, non‑repeatable reads, phantom reads, isolation levels, and the MVCC implementation that ensures data consistency.
This article explains why caching is essential for high‑concurrency systems, analyzes the challenges of keeping database and cache data consistent, and compares five practical cache‑invalidation strategies—including write‑after‑DB, delete‑before‑write, delayed double delete, queue‑based deletion, and binlog subscription—highlighting their trade‑offs and suitable scenarios.
This article explains how to prevent data loss in MySQL by enabling binlog in ROW format, performing full and incremental restores using mysqldump or XtraBackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements, and using flashback tools such as binlog2sql and MyFlash.
The article explains why data inconsistency occurs when MySQL and Redis are used together under heavy load, analyzes cache‑delete ordering issues, and presents two practical solutions—delayed double‑delete and asynchronous cache updates via MySQL binlog—along with their trade‑offs and implementation steps.
This article demonstrates how to use Go to parse MySQL binlog events and synchronize data by implementing a lightweight replication client, covering configuration, server module, packet handling, handshake, registration, dump command, and providing complete source code examples with explanations.
This article describes practical improvements to the my2sql binlog‑parsing tool—adding query support and automatic binlog range detection—and demonstrates how to integrate it into DBeaver to provide developers with easy data‑rollback and original‑SQL tracing without requiring DBA intervention.
This article explains how InnoDB’s two‑phase commit mechanism keeps the redo log and binlog logically consistent, illustrating failure scenarios, the impact on data recovery, and how the prepare‑commit split prevents inconsistencies in MySQL’s storage engine.
This article walks through MySQL’s internal query processing pipeline, the roles of the connector, parser, optimizer and executor, explains InnoDB’s redo log and binlog mechanisms with a WAL analogy, and details transaction ACID properties, isolation levels, and concurrency anomalies.
This article explains the importance of MySQL backups, compares common logical and physical backup tools such as mysqldump, Mydumper and Percona XtraBackup, details XtraBackup's step‑by‑step workflow, lock handling options, and provides practical Q&A for reliable database backup strategies.
This article explains how to use Alibaba's open‑source Canal middleware to capture MySQL binlog changes and forward them via RabbitMQ for real‑time data synchronization, covering server installation, configuration, testing, and a Java client implementation.
This article provides a practical guide to using mysqlbinlog for parsing and replaying MySQL binlog files, covering options for time, position, and GTID ranges, as well as important parameters such as --no-defaults, -v, --base64-output, and --skip-gtids.
This article presents a step‑by‑step investigation of a data‑loss incident in a MySQL table by examining binlog files, identifying unintended TRUNCATE operations, and offering practical binlog‑parsing techniques and configuration tips for reliable troubleshooting.
This article summarizes the design, working principle, deployment steps, testing results, and future outlook of a TiDB cross-data-center high‑availability solution based on Binlog bidirectional replication, aiming to ensure rapid failover and continuous service between two data‑center clusters.
The article explains that in MySQL 8.0 the binlog_expire_logs_seconds variable can fail to purge old binary logs when a backup lock is held, demonstrates the problem with step‑by‑step reproduction, analyzes the cause, and offers practical work‑arounds and references to the official bug report.
This article explains the purpose and mechanics of MySQL's binlog, redo log, and undo log, covering their structures, use cases such as replication and recovery, flush timing parameters, format options, and how they together ensure data durability and consistency in InnoDB.
After a client mistakenly imported 60,000 useless rows and a developer’s faulty delete command erased over 100,000 critical records, this guide explains how to use MySQL binlog to locate, extract, and replay the missing SQL statements, restore the data, and prevent future disasters.
This article recounts a real-world incident where a client mistakenly imported 60,000 rows of erroneous Excel data into a MySQL production database, leading to an accidental bulk delete, and explains how the team used MySQL binlog to recover the lost data while offering practical safeguards to prevent similar disasters.
This article explains MySQL's layered architecture, the roles and mechanisms of redo log and binlog, their write and flush strategies, crash‑safe guarantees, and how two‑phase commit ensures consistency, providing practical commands and configuration tips for reliable database operation.
This article explains MySQL’s essential logging mechanisms—binary log, redo log, and undo log—detailing their structures, purposes, configuration parameters, and how they work together to support replication, crash recovery, and transaction durability while balancing performance and consistency.
This article explains how MySQL’s logical‑clock replication can be enhanced with writeset‑based dependency tracking, detailing the underlying theory, code implementation, configuration options, performance testing, and practical considerations for improving parallel applier concurrency.
The article explains the MySQL BlackHole storage engine, its black‑hole behavior, practical scenarios such as acting as a pseudo‑master to reduce master load and as a binlog collector, and provides detailed configuration steps including engine selection and binlog settings.
This article explains the design and implementation of MySync, a component that leverages MySQL binlog to synchronously replicate data to heterogeneous storage systems such as Redis, ElasticSearch, and HBase, detailing its architecture, reader and writer modules, Kafka integration, and strategies for handling master‑slave failover.
The article explains that Xtrabackup only needs to ensure consistency between data and the binlog position, not full binlog data, because its backup process captures only committed or not‑started transactions, eliminating the need to back up the binlog itself.
This article explains MySQL's logging system—including binlog, redo log, and undo log—their purposes, formats, flush timing, usage in replication and recovery, and how they work together to ensure durability and atomicity in InnoDB transactions.
This article describes a step‑by‑step investigation of a MySQL 8.0.18 startup failure caused by a missing binlog index file after using Percona XtraBackup on CentOS 8, analyzes the root cause, and provides practical fixes and diagnostic techniques such as strace and source‑code inspection.
This article explains how PolarDB‑X classifies different data‑deletion scenarios, compares traditional recovery methods like PITR, Flashback Query, and binlog‑based tools, and introduces its precise SQL‑level flashback feature that uses TraceID to quickly and accurately restore mistakenly deleted rows.
This article explains how Canal, Maxwell, Databus, and Alibaba's Data Transmission Service (DTS) enable incremental data subscription and consumption by parsing MySQL binlog streams, describing their architectures, processing steps, and comparative advantages for building reliable change‑data‑capture pipelines.
This guide explains Tencent Cloud MySQL's three instance types, detailed replication modes (asynchronous, semi‑synchronous, strong synchronous), high‑availability failover mechanisms, upgrade procedures, binlog management, restore options, slow‑query tuning, and space fragmentation, providing practical insights for reliable cloud database operations.
The article explains why large‑scale e‑commerce systems need sharding and data heterogeneity, compares dual‑write, asynchronous MQ, and Canal‑based binlog solutions, and discusses how to achieve real‑time, ordered data synchronization using MySQL replication, Canal, and message queues.
This guide explains how to safely recover MySQL data using full backups, incremental binlog recovery, point‑in‑time restores, single‑table extraction, and methods to skip erroneous SQL statements via GTID or delayed replication, complete with practical command examples.
This article explains how to prevent data loss in MySQL by enabling backups and row‑based binlog, then details full‑volume recovery with mysqldump or xtrabackup, point‑in‑time restoration using binlog, table‑level recovery, skipping erroneous SQL statements, and introduces open‑source tools such as binlog2sql and MyFlash.
This article details Ctrip Finance's end‑to‑end data‑foundation architecture that uses MySQL binlog collection via Canal, Kafka streaming, Spark‑Streaming persistence to HDFS, and a merge process to produce timely MySQL‑Hive snapshots, addressing performance, consistency, and delete‑handling challenges.
DiDi’s real‑time MySQL‑to‑Hive pipeline captures row‑mode binlog with a custom Canal component, converts it to JSON, streams it via Kafka to HDFS, restores it into Hive tables, and uses Dquality for integrity, achieving millisecond latency for over 19,000 daily sync tasks handling roughly 50 TB of data.
This article explains why MySQL chooses Repeatable Read as its default transaction isolation level, contrasting it with Oracle’s default Read Committed, and discusses how binlog formats, GAP locks, and historical bugs influence this design decision.
This article explains why MySQL binlog files can become large, demonstrates how to enable the slave_compressed_protocol parameter for binlog compression, presents benchmark results showing reduced network traffic and CPU impact, and outlines compatibility considerations and known bugs.
This article explains two methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers combined with a UDF function and parsing MySQL binlog via Alibaba's Canal—detailing their principles, implementation steps, advantages, limitations, and practical deployment considerations.
This article analyzes a puzzling MySQL update where the operation reports success yet the data remains unchanged, explains the WAL mechanism and two‑phase commit process, and provides a step‑by‑step binlog investigation script to uncover the root cause.
This article investigates the meaning of exec_time in MySQL binary logs, demonstrates how row and statement binlog formats record execution times through systematic tests, analyzes the underlying principles, and shows how exec_time can be used to diagnose replication lag caused by CPU, disk, or network bottlenecks.
This article describes how Ctrip Finance built a unified financial data center by collecting MySQL binlog streams with Canal, transporting them via Kafka, persisting to HDFS with Spark‑Streaming, and merging into Hive tables, while addressing performance, idempotency, delete handling, and data‑quality checks.
This article demonstrates how a MySQL DBA can use the open‑source analysis_binlog tool to efficiently parse large binlog files, count DML operations, convert binlog to SQL, and perform parallel analysis after setting up a CentOS test environment and running sysbench stress tests.
This guide explains how to verify that MySQL binlog is enabled, locate binlog files, use the mysqlbinlog utility to extract change logs for specific time ranges or tables, and apply the extracted SQL to recover or roll back erroneous data modifications.
This article explains how the creator red‑packet feature was engineered to guarantee idempotent, fault‑tolerant, and high‑throughput red‑packet claims by using multi‑level caching, empty placeholders, binlog‑driven synchronization, active cache invalidation, ordered Kafka consumption, and fallback strategies to resolve cache‑DB consistency issues.
The article explains two technical methods for keeping MySQL and Redis in sync—using MySQL triggers with a custom UDF to write directly to Redis, and parsing MySQL binlog streams (or using Alibaba's Canal) to propagate changes, while discussing their suitable scenarios, challenges, and implementation details.
This article explains two practical methods for keeping MySQL and Redis cache in sync—using MySQL triggers with UDF functions or parsing binlog events—and introduces the open‑source Canal project for incremental log parsing, while also discussing the pitfalls of reverse synchronization.
This article explains two technical methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers combined with a UDF function and parsing MySQL binary logs (with Canal)—detailing their workflows, advantages, limitations, and implementation considerations.
This article explains how a growing e‑commerce platform replaced a heavyweight MySQL intermediate table with a binlog‑driven pipeline that streams changes to Elasticsearch via Kafka, detailing the architecture, modules, customizations, monitoring, and performance results.
The article investigates the discrepancy between MySQL binlog execution time and slow query log duration for an INSERT statement, conducts an experiment with artificial I/O latency, and explains why the slow log provides a more complete measurement of query execution.
The article analyzes a real‑world incident where a series of MySQL UPDATE statements with incorrectly placed quotation marks caused the entire source_name column to be set to zero, explains MySQL's implicit type conversion behavior, and shows how to recover the data using binlog analysis and flashback SQL.
This article examines how MySQL binlog timestamps and the SET TIMESTAMP command affect the exec_time values of statements, especially the BEGIN event, through step‑by‑step experiments that reveal timing discrepancies and their impact on log accuracy.
The article explains how incorrectly placed quotation marks in MySQL UPDATE statements can cause expressions like "str_col=\"xxx\" = \"yyy\"" to evaluate to zero, leading to unintended data overwrites and highlights the importance of testing SQL in safe environments.
The article introduces go-mysql-transfer, a Go-implemented MySQL binlog incremental synchronization solution that avoids extra components, supports multiple downstreams like Redis, offers Lua scripting, Prometheus monitoring, high‑availability clustering, retry mechanisms, full data initialization, and provides installation, deployment, and performance testing details.
The article introduces my2sql, a Go‑written MySQL binlog parser that supports flashback, forward SQL generation, and transaction analysis, compares it with binlog2sql and MyFlash, details its command‑line parameters, demonstrates three practical scenarios, lists limitations, and summarizes its performance and usage advantages.
This article provides a step‑by‑step guide on configuring MySQL binlog, installing and deploying Oracle GoldenGate, extracting changes, converting them to JSON, and streaming the data into Kafka for real‑time processing, complete with code snippets and verification procedures.
This guide explains how to enable and configure MySQL binary logging, examine its parameters, understand event formats, view and filter binlog contents, and extract logs using positions, timestamps, or GTIDs for backup, replication, and troubleshooting purposes.
This article explains the MySQL client‑server communication protocol, detailing the connection phase—including handshake, authentication, and packet structure—as well as the replication process, covering registration as a slave, binlog streaming, and the various binlog event types and their formats.
This article explores common data migration scenarios such as sharding, storage engine changes, and system switches, and outlines practical approaches for bulk and incremental migration, validation techniques, cut‑over strategies, and ID management to ensure reliable, low‑risk database transitions.
