MySQL silently resolves missing column names in subqueries to outer scope columns, turning filters into correlated subqueries that always evaluate true, causing full-table updates or deletes without errors or warnings; no sql_mode prevents this behavior.
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.
This comprehensive guide details nine essential MySQL DBA commands — SHOW DATABASES, SHOW TABLE STATUS, SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS, SHOW VARIABLES, SHOW STATUS, EXPLAIN, SHOW INDEX, and SHOW CREATE TABLE — with syntax examples, key output fields, and practical troubleshooting scenarios for performance tuning and database administration.
This article explains the 20-million-row rule of thumb for MySQL InnoDB tables by analyzing B+ tree index structure, 16KB page size, and fan-out calculations, showing how row size and tree depth affect query performance.
This article categorizes Elasticsearch data types into five groups, maps each MySQL type to its ES equivalent with practical notes, explains the critical text vs keyword distinction, covers dynamic mapping traps, and provides interview-ready mnemonics for type selection and concept translation.
This article details the engineering implementation and performance optimization of TCC distributed transactions in Go microservices, using an order placement scenario with inventory locking and wallet freezing, covering data models, idempotent state machines, recovery workers, hotspot optimization, and observability.
This comprehensive guide covers MySQL read-write separation from fundamentals to production implementation, including master-slave replication principles, replication lag causes and three enterprise-grade solutions, one-master-multi-slave architecture patterns, MHA automatic failover, Sharding-JDBC middleware configuration, and five critical production pitfalls to avoid.
The article examines a MySQL experiment where three transactions (A, B, C) interact on the same row, showing why transaction B’s UPDATE on a primary‑key field proceeds without waiting while transaction C’s UPDATE of the primary key blocks, due to the interplay of row‑level locks, gap locks, and the internal delete‑plus‑insert transformation of indexed updates.
This article demonstrates how to store four independent notification channel switches (SMS, email, in-app, WeChat) in a single MySQL TINYINT column using bitwise operations, covering schema design, SQL queries, Java enum and utility classes, service and controller layers, MyBatis mapper, extensibility to N channels, and key considerations like indexing, concurrency, and readability.
The article walks through a complete Spring Boot implementation of WeChat‑like message recall, explaining why a simple DELETE is insufficient and detailing how to use a status flag, conditional UPDATE, WebSocket, Redis/MQ, and multi‑device session management to synchronize recall across all clients, even under concurrency and offline scenarios.
This article introduces a production-ready Go Gin DDD scaffold featuring four-layer architecture, RocketMQ event-driven messaging, dual MySQL/PostgreSQL databases, unified responses, and step-by-step guides for setup and new module development.
This article explains what MySQL sharding and partitioning are, when they are necessary, outlines four splitting patterns, details four sharding key strategies, shows a production‑ready Sharding-JDBC configuration, and discusses five common distributed challenges with practical solutions.
The article examines a common duplicate‑order bug caused by client retries after network timeouts, explains why @Transactional alone cannot guarantee idempotency, critiques simple Redis SETNX solutions, and presents a robust Spring Boot design that stores idempotency records together with order data in a single MySQL transaction, uses request fingerprints, unique keys, and owner tokens to safely replay or reject retries while handling concurrency and cleanup.
This article details the architecture of an enterprise governance platform for Codex AI coding assistants, covering local encrypted collection with 90ms hooks, server-side parsing with tenant isolation, tiered content access (L0–L3) requiring multi-party approval, and operational use cases from deployment health monitoring to employee offboarding with zero-residue verification.
This article explains how MySQL's Repeatable Read isolation level prevents phantom reads by combining MVCC snapshot reads for consistent views and Next-Key Locks (record + gap locks) to block inserts during current reads like UPDATE, detailing the ReadView mechanism, version chains, and a concrete phantom read scenario.
This guide explains what the MySQL slow query log records, why it is essential for production, when and how to enable it temporarily or permanently, key parameters to tune, how to simulate slow queries, interpret log fields, use mysqldumpslow for aggregation, follow a step‑by‑step optimization pipeline, and avoid common pitfalls.
The article explains why ElasticSearch excels at full‑text search and analytics while MySQL remains the reliable storage engine, compares their underlying data structures, outlines real‑time write behavior, lists scenarios where ES should not be used, and describes a production double‑store architecture with asynchronous sync.
The article explains why a successful database transaction can leave an order incomplete when the accompanying Kafka message fails, examines the limitations of @Transactional, and demonstrates how Spring Modulith's Event Publication Registry and the Outbox pattern provide reliable, idempotent messaging solutions.
This article analyzes the performance bottleneck of LIMIT OFFSET deep pagination in MySQL with millions of rows, demonstrates EXPLAIN plan analysis, compares traditional pagination, subquery, covering index, and keyset pagination approaches, provides Spring Boot implementation examples for MyBatis-Plus and JPA, and benchmarks showing keyset pagination maintains constant response time regardless of page depth.
The article explains that MySQL’s EXPLAIN command acts as a SQL health check, detailing each of its 12 fields—especially type, key, rows, and Extra—while providing real‑world examples, a step‑by‑step diagnostic workflow, and common pitfalls to help developers reliably tune and troubleshoot queries.
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.
This article compares PostgreSQL and MySQL across complex query optimization, data types, MVCC implementation, transaction rollback capabilities, and cloud ecosystem support, explaining why PostgreSQL is increasingly preferred for complex analytics, vector search, and geospatial applications.
The article explains why MySQL back‑table lookups (回表) double I/O and degrade performance, defines covering indexes that retrieve all needed columns from a secondary index, shows how to identify back‑table cases via EXPLAIN, and provides step‑by‑step commands to create, test, and verify covering indexes for high‑frequency queries.
This article explains MySQL InnoDB's MVCC mechanism, detailing how snapshot reads and current reads work, the hidden fields that drive versioning, the Read View rules for RC and RR isolation levels, and provides hands‑on SQL demos plus common pitfalls to avoid.
When a query suddenly slows down tenfold, adding an index often fails to help; the real solution is to read and understand MySQL's execution plan, which reveals how the optimizer works, which indexes are used, and where performance bottlenecks lie, enabling systematic query tuning.
The article chronicles the twelve‑year evolution of China’s Database Technology Conference (DTCC), showing how it transformed isolated DBA communities into a collaborative ecosystem, introduced new topics such as cloud and NoSQL, and enabled figures like 那海蓝蓝 to shape the nation’s database engineering landscape.
The article explains why high‑traffic MySQL deployments hit performance limits, introduces the concepts of vertical and horizontal sharding, and provides a step‑by‑step guide—including necessity assessment, shard key selection, schema design, middleware integration, and data migration—using an e‑commerce order system as a concrete example.
MySQL master‑slave replication separates writes to the master and reads to one or more replicas, using binary logs to record changes; the article explains its core concepts, typical scenarios like read‑write splitting and high‑availability, and provides a step‑by‑step configuration guide covering server IDs, binlog settings, replication accounts, data initialization, GTID options, and status verification.
Understanding MySQL’s lock types—table, row, and gap locks—reveals how they act as resource tokens to ensure data consistency under concurrency, while the article details their characteristics, appropriate and prohibited use cases, common pitfalls like lock degradation and deadlocks, and provides hands‑on SQL examples to reproduce and avoid these issues.
This article presents a complete design for a 12‑state payment order state machine that handles high‑concurrency scenarios such as payment callbacks arriving after an order has been cancelled, using explicit state transitions, database CAS updates, Redis + DB idempotency, Outbox pattern and Kafka‑driven asynchronous processing to achieve reliable, auditable and compensatable order lifecycle management.
This article walks through a comprehensive case study of a provincial medical appointment platform, diagnosing slow‑query bottlenecks, proposing patient‑id + create_time composite indexes, designing read‑write separation with replication, selecting patient_id for horizontal sharding, and implementing cache‑aside strategies to ensure consistency while handling cache avalanche, penetration and thundering‑herd scenarios.
This guide walks through why MySQL master‑slave replication lag occurs, the key metrics to monitor, a step‑by‑step troubleshooting flow, ten typical root causes, concrete remediation actions, verification methods, rollback plans, and production‑grade best practices for keeping replication latency near zero.
This article explains why indexes speed up queries by reducing disk I/O, dives into MySQL's page structure and B+Tree evolution, compares clustered and secondary indexes, clarifies composite index rules, lists common index‑misuse scenarios, and provides seven practical guidelines for designing efficient MySQL indexes.
This article explains why MySQL deadlocks occur in production, distinguishes them from simple lock waits, and provides a step‑by‑step guide—including enabling deadlock logging, analyzing InnoDB lock types, and applying four practical solutions such as distributed locks, unique constraints, isolation‑level changes, and SQL reordering—to reliably troubleshoot and prevent deadlocks.
The article explains what a database transaction is, breaks down the ACID properties, details MySQL’s four isolation levels with their appropriate use‑cases and pitfalls, and provides step‑by‑step SQL and SpringBoot code to reproduce and resolve dirty reads, non‑repeatable reads, and phantom reads.
The article analyses lock contention caused by per‑request ID generation, derives a segment‑based solution that batches IDs using a configurable step, defines a left‑closed/right‑open interval schema in MySQL, and builds a double‑buffer Java issuer with asynchronous pre‑loading, thorough concurrency handling, testing, and configuration guidelines.
The article explains that whether adding an index locks a MySQL table depends on the version and DDL algorithm—MySQL 5.5 and earlier lock the whole table, 5.6‑5.7 use Online DDL with brief metadata locks, and 8.0+ can create secondary indexes instantly without locking, while primary, unique, and full‑text indexes always require locking.
This article analyzes why cache and database can become inconsistent, compares four basic cache‑update patterns, explains the delayed double‑delete technique, shows how to use MQ for retrying cache deletions, and evaluates Canal binlog subscription as a zero‑intrusion solution for strong eventual consistency.
This article investigates whether SELECT COUNT(*) forces a full table scan, explains MySQL's cost‑based index selection (IO and CPU components), demonstrates the optimizer's choices with real‑world examples, and shows how EXPLAIN and optimizer trace can reveal and improve query performance.
The article explains how to generate strictly sequential invoice numbers required by auditors by using a MySQL InnoDB watermark (water level) table combined with row locking and UPSERT within a single transaction, covering the workflow, concurrency handling, rollback versus void semantics, performance trade‑offs, and applicability limits.
Oracle’s 2026 layoffs hit the MySQL team, community contributions fell 44% since 2017, while PostgreSQL climbs, and SQLite—once dismissed as a toy—now powers billions of devices and edge‑computing platforms, boosted by projects like LibSQL, Turso, Cloudflare D1 and Litestream, offering latency advantages over traditional servers.
The article walks through a real‑world MySQL deadlock incident, shows how to extract the LATEST DETECTED DEADLOCK section from SHOW ENGINE INNODB STATUS, explains gap and next‑key locks, and presents a three‑step method plus practical fixes to eliminate the deadlock.
This article explains why MySQL discourages using NULL as a default column value, demonstrates how NULL affects indexing, comparisons, and aggregate functions, and shows through concrete examples that improper NULL handling can lead to unexpected query results and performance degradation.
The article explains what MySQL slow queries are, why they must be detected, when to enable slow‑query logging, step‑by‑step commands to configure the log, how to simulate and analyze problematic SQL with EXPLAIN, and practical optimization techniques—including index creation and Spring Boot integration—to eliminate performance bottlenecks.
This article demystifies databases by contrasting them with Excel, explains core concepts such as tables, rows, columns, primary and foreign keys, compares relational and NoSQL systems, introduces SQL CRUD operations, ACID properties, and provides a quick MySQL hands‑on guide, helping readers decide when to adopt a database.
Adding a column to a MySQL table with tens of millions of rows can lock the table for minutes or even hours, disrupting services, so this article evaluates six practical approaches—including native online DDL, offline maintenance, PT‑OSC, logical migration with dual‑write, gh‑ost, and partition sliding‑window—detailing their mechanisms, trade‑offs, and suitable scenarios.
This article explains MySQL EXPLAIN output fields (id, select_type, type, key, key_len, rows, Extra), demonstrates optimizing a 200M-row query from 8 seconds to 15 milliseconds via composite index, and provides a four-point checklist (type, key, rows, Extra) for daily SQL troubleshooting.
The article explains that a shopping cart can tolerate loss of recent writes but not accumulated items, outlines the data model with seven fields and a unique key, compares client‑side storage options, details merge strategies for guest and logged‑in carts, and evaluates Redis, MySQL, and hybrid solutions with concrete trade‑offs.
This article explains MySQL indexes with a simple book‑catalog analogy, shows why missing or mis‑used indexes cause severe latency and CPU issues, outlines when to create or avoid indexes, and provides a step‑by‑step SpringBoot demo that measures query time dropping from 80‑200 ms to under 5 ms.
This guide walks MySQL operators through a complete slow‑query troubleshooting workflow—starting with enabling and analyzing the slow‑query log, using pt‑query‑digest and EXPLAIN to pinpoint index, SQL, schema, configuration or hardware bottlenecks, and then applying concrete optimizations such as proper indexing, cursor pagination, JOIN tuning, and server‑level parameter tweaks.
This article presents a five-step practical guide for optimizing MySQL queries on tables exceeding 100 million rows, covering index optimization, SQL rewriting, cursor-based pagination, table structure improvements including partitioning, and architectural upgrades like read-write separation and sharding, with concrete code examples and performance comparisons.
The article examines how to implement read/unread status for single and group chats at scale, comparing a simple last‑read‑ID approach for one‑on‑one conversations with database, Redis hash, and bitmap‑watermark solutions for groups, and discusses their performance and memory trade‑offs.
This article explains how MySQL deadlocks arise, details the four necessary conditions, compares lock types, shows how to enable detailed deadlock logging, query lock metadata, interpret logs, and provides practical code‑level and configuration strategies to prevent and resolve common deadlock scenarios in production environments.
A logistics company struggled with mismatched label fees, manual reconciliation errors, and monthly profit loss, so the team deployed an automated recharge, label import, one‑click deduction, and reconciliation system that eliminated leakage, cut reconciliation time, and provided real‑time balance visibility.
The article walks through a systematic MySQL 8.0 index‑optimization workflow—starting with diagnosing slow queries and lock waits, validating execution plans with EXPLAIN ANALYZE, safely adding or dropping indexes using online DDL, handling pagination patterns, and verifying improvements via comprehensive metrics before and after.
This article walks MySQL developers through the essential architectural, schema, data‑type, SQL‑dialect, transaction, vacuum, indexing, and extension differences when moving to PostgreSQL, providing concrete examples, code snippets, and best‑practice recommendations to avoid common pitfalls.
The article explains why placing column C immediately after the equality column A in a composite index (A, C, B) avoids filesort and keeps performance stable regardless of how many values appear in the B IN list, outperforming other index orders such as (A, B, C).
This guide walks through a systematic approach to uncovering and fixing MySQL slow queries, covering slow‑query‑log configuration, log analysis with mysqldumpslow and pt‑query‑digest, EXPLAIN‑based execution‑plan inspection, index design principles, SQL rewrites, configuration tuning, and ongoing monitoring to prevent performance regressions.
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.
To address MySQL master‑slave replication lag, the article explains enabling parallel replication (setting slave_parallel_workers), routing reads to the master for latency‑sensitive operations, and using a Redis short‑term marker to direct recent writes to the master, while outlining configuration steps and trade‑offs.
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 article breaks down MySQL’s locking mechanisms—table, row, and gap locks—explaining their principles, performance trade‑offs, when they are triggered, how they relate to index usage, and provides practical deadlock avoidance techniques and a concise cheat‑sheet for common concurrency problems.
This article walks through a complete production‑grade view of MySQL 8.4 LTS, explaining how a chain of traffic spikes, connection‑pool exhaustion, long transactions and replication lag can cause an avalanche, and then detailing the five core modules, seven production mechanisms, architectural evolution steps, incident post‑mortems, and concrete configuration and code examples to build a resilient MySQL service.
This comprehensive guide walks MySQL administrators through the fundamentals of backup and recovery, covering RPO/RTO concepts, tool comparisons (mysqldump, mydumper, xtrabackup), step‑by‑step scripts for full, incremental, and binlog backups, encryption, compression, troubleshooting, and best‑practice monitoring to ensure data safety and rapid restoration.
When MySQL replication latency jumps from milliseconds to 10 seconds, blindly raising replica_parallel_workers won’t help; the article walks through diagnosing the delay, then applies three concrete parallel‑replication optimizations—enabling WRITESET on the source, configuring LOGICAL_CLOCK with appropriate applier workers, and optionally preserving commit order—while showing the required SQL commands, monitoring queries, and rollback steps.
This article explains why database transactions are a core backend skill, breaks down the four ACID properties, clarifies dirty read, non‑repeatable read and phantom read, compares the four isolation levels, and offers practical guidelines for choosing the right level in production.
This article analyzes the risks of running MySQL in Docker—data loss, I/O performance degradation, networking overhead, and HA gaps—and provides production-ready solutions including Docker Compose configs, custom my.cnf, backup scripts, Kubernetes StatefulSet examples, and a troubleshooting matrix for interview preparation.
A MySQL query on a varchar column with an unquoted numeric literal triggered implicit type conversion, forcing a full scan of 2 million rows instead of an index lookup; adding quotes cut latency from 19 seconds to 50 ms. The article explains the mechanism, lists four other common index-invalidation patterns, and provides a quick EXPLAIN-based diagnosis checklist.
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.
MySQL servers can trigger alarming disk‑space warnings even when the data directory is small, because unchecked binary logs rapidly consume storage; this article explains the log’s purpose, why it grows, how to diagnose the issue, and provides step‑by‑step cleanup, configuration, replication, monitoring, and recovery best practices.
This comprehensive guide covers MySQL table creation best practices including naming conventions, field type selection, index design principles, constraint strategies, and solutions for large-table performance, JSON querying, and logical deletion conflicts, with production-ready SQL templates and interview-focused insights.
The article explains the difference between character sets and encodings, shows that MySQL’s utf8 is actually an alias for utf8mb3, demonstrates how using the wrong charset or collation causes garbled data, and provides practical steps to consistently use utf8mb4 across versions and connections.
This guide walks you through a complete emergency response, root‑cause analysis, and long‑term mitigation for MySQL connection‑limit exhaustion, covering Linux diagnostics, SQL commands, quick‑kill scripts, monitoring with Prometheus/Grafana, and best‑practice configuration of connection pools and max_connections.
The article explains why a single MySQL server eventually hits read, write, or availability limits, outlines a step‑by‑step evolution—from SQL tuning and caching to read‑write splitting, high‑availability setups, and finally vertical or horizontal sharding—while warning against premature distribution.
This article walks through every stage a MySQL statement undergoes—from client connection, authentication, and (now‑removed) query cache, through parsing, optimization, execution, and storage‑engine access—highlighting the roles of InnoDB vs MyISAM, logging mechanisms, and best‑practice table design tips.
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 to make MySQL do less work by reducing scanned rows, avoiding back‑and‑forth lookups with covering indexes, and minimizing sorting or temporary tables, while detailing common index pitfalls, deep pagination tricks, COUNT usage, ORDER BY/filesort handling, JOIN strategies, and step‑by‑step methods for finding and fixing slow SQL statements.
The article analyzes why MySQL and Redis cannot guarantee strong consistency with simple cache‑aside patterns, explains the pitfalls of delayed double delete, and presents a tiered Java design—including transaction‑after‑commit deletion, retryable invalidation, CDC/Outbox pipelines, TTL safeguards, and multi‑level cache considerations—to achieve reliable cache consistency.
This white‑paper dissects why a monolithic MySQL‑based order service collapses under peak traffic and presents a layered, asynchronous architecture—using Redis for stock pre‑allocation, RocketMQ for transactional messaging, sharding, idempotency, and comprehensive observability—to reliably handle tens of millions of queries per second.
The article analyzes the requirements of a massive‑scale like feature, compares small‑scale MySQL implementations with the graph‑plus‑KV architectures used by ByteDance, Kuaishou and Xiaohongshu, and presents a practical hybrid solution based on Redis, MySQL and batch processing to handle hot content and super‑node challenges.
The article explains the differences between database sharding, table sharding, and combined sharding, outlines interview expectations, details vertical and horizontal splitting strategies, discusses when to apply each technique, and reviews practical tools such as ShardingSphere and MyCat for Java projects.
This article walks through MySQL InnoDB's five‑level storage hierarchy—record, page, extent, segment, tablespace—explaining page internals, record chaining, page directories, headers, trailers, extent and segment management, and the system tablespace and data dictionary across MySQL 5.7 and 8.0.
The article explains why deep pagination with large offsets becomes increasingly slow, why adding indexes often does not help, examines common “optimizations” like delayed joins, introduces cursor (keyset) pagination as a high‑performance alternative, and provides practical guidelines for designing pagination APIs in Java back‑ends at scale.
This article explains why MySQL needs locks beyond MVCC snapshot reads, categorizes lock granularity, mode and design, details global, table and row‑level locks—including intention, gap, next‑key, and implicit locks—then compares pessimistic and optimistic locking, shows how to choose the right lock, and covers deadlock causes, detection, and best‑practice avoidance.
To survive a million concurrent flash‑sale clicks, the article breaks down a production‑grade, five‑layer architecture—CDN, load balancer, API gateway, Redis/Lua stock control, message‑queue order processing, and a state‑machine fallback—that filters traffic early, uses atomic operations, and decouples writes to keep database load to just a few QPS.
The article explains why InnoDB must cache disk pages in a Buffer Pool, details the pool's internal structure, eviction policies, dirty‑page flushing mechanisms, multi‑instance and chunk sizing for large workloads, and how to monitor its health with SHOW ENGINE INNODB STATUS.
Databasus is an open‑source backup management platform that unifies PostgreSQL, MySQL, MariaDB and MongoDB backups with a web UI, offering logical, physical and incremental backups, automated scheduling, AES‑256‑GCM encryption, multi‑cloud storage, RBAC collaboration, and optional agents for secure, out‑bound connections.
This article walks through MySQL's EXPLAIN output column by column, explaining id, table, select_type, type rankings, possible_keys, key, key_len, ref, rows, filtered, and Extra, while demonstrating each concept with concrete SQL examples, JSON‑formatted plans, and SHOW WARNINGS insights.
The article explains why MySQL may still report Using filesort despite a composite index that appears to satisfy both WHERE and ORDER BY clauses, covering index ordering rules, the impact of range conditions, mismatched ORDER BY field order, ASC/DESC mixing, and practical optimization techniques.
The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.
The article walks through a real‑world order system that spikes to 300,000 QPS, explaining why the original single‑node MySQL design fails, and detailing a step‑by‑step evolution—index tuning, transaction fixes, read‑write splitting, vertical and horizontal sharding, plus data‑pipeline integration—to achieve stable low latency at massive scale.
The article examines the drawbacks of using Snowflake for generating short numeric user IDs, details a MySQL auto‑increment based approach, reveals deadlock problems with REPLACE INTO, evaluates alternative schemes, and presents a final sharding‑friendly short‑ID design that meets performance and usability requirements.
Adding a column to a tens‑of‑millions‑row MySQL table may look trivial, but the real difficulty lies in the metadata‑lock timing and online traffic impact, requiring careful DDL type selection, pre‑flight checks, and often an online migration tool such as gh‑ost to avoid business‑level outages.
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.
This article provides a comprehensive, hands‑on guide to MySQL master‑slave replication, covering the underlying architecture, binlog formats, GTID and semi‑synchronous modes, detailed configuration steps, thread workflows, common failure scenarios with step‑by‑step diagnostics, and practical monitoring and failover scripts.
The article identifies eight easy-to-overlook SQL habits—such as using SELECT *, applying functions on indexed columns, careless LIKE patterns, improper IN/OR logic, deep offset pagination, unconditional UPDATE/DELETE, excessive indexing, and ignoring execution plans—that can cause slow queries, lock tables, index failures, and even data loss, and provides concrete examples and safer alternatives.
An in‑depth MySQL benchmark compares auto‑increment, UUID, and Snowflake‑style random long keys, showing how index structure, insert latency, and page fragmentation differ, and explains why auto‑increment keys usually outperform the others while also highlighting the security and lock‑contention drawbacks of sequential IDs.
When migrating tens of billions of rows, the article explains how a simple progress‑tracking table and failure‑log table enable automatic checkpoint‑based resumption, stateless execution, and dynamic batch tuning without restarting or rewriting code.
The article walks through setting up a MySQL fake server, crafting a malicious JDBC URL with autoDeserialize and query interceptors, demonstrating how the MySQL JDBC driver automatically deserializes BLOB data via ObjectInputStream, and traces the call chain to show how arbitrary code can be executed during connection initialization.