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 provides a comprehensive overview of MySQL's built‑in functions—including numeric, string, date/time, and aggregation utilities—explaining their usage, parameters, and output with clear examples to help developers write more efficient SQL queries.
The article shares a comprehensive methodology for MySQL database inspection, classifying checks by method, time and depth, and detailing availability, reliability, and performance best‑practice items along with concrete command examples and configuration recommendations.
The article details a MySQL memory‑usage incident at Qunar where abnormal slab memory, especially dentry allocation, caused a rapid increase in used memory after a mis‑configured yum makecache cron job, and explains the investigation steps, Linux memory concepts, diagnostic commands, and the corrective actions taken.
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 how MySQL allocates auto‑increment IDs, the differences between MySQL versions, the three InnoDB lock modes, scenarios that break ID continuity, what to do when IDs run out, and strategies to hide sequential IDs from malicious crawlers.
This article explains how InnoDB stores data in 16 KB pages, calculates how many rows a B+Tree index can hold, shows how to determine the tree height from the page level, and answers why MySQL uses B+Tree rather than other tree structures.
This article explains MySQL's experimental EXPLAIN FORMAT=TREE feature and the later EXPLAIN ANALYZE extension, showing how they present execution plans, estimated costs, actual runtime metrics, and how to interpret the tree output for query optimization.
The article investigates a MySQL 8.0.26 crash triggered by setting the terminology_use_previous parameter to BEFORE_8_0_26, analyzes stack traces and core dumps, compares processlist access methods, references related bugs, and offers mitigation recommendations for monitoring tools.
This article details Industrial and Commercial Bank of China's comprehensive MySQL governance framework, covering risk identification, prevention strategies, a four‑step methodology, automated quality gates, production‑level monitoring, SRE management, and future visions for rapid incident detection and self‑healing.
This article evaluates MySQL insertion performance of three primary key strategies—auto‑increment, UUID, and random Snowflake‑like keys—by building identical tables, running Spring‑Boot/JdbcTemplate benchmarks, analyzing index structures, and discussing the advantages and drawbacks of each approach.
This article explains why MySQL queries become slow, how to enable and analyze slow‑query logs, and provides practical steps for index, SQL, schema, optimizer, and architecture optimizations to keep performance strong even with millions of rows.
Learn how MySQL’s powerful join operations transform simple table queries into meaningful results, covering Cartesian products, inner, left, right joins, aggregation with group functions, and practical filtering techniques, complete with sample data, SQL syntax, and execution examples.
This article introduces several MySQL performance‑diagnostic utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run them, and shows how to interpret their reports for optimizing database configuration and query performance.
This article explains ProxySQL's built‑in databases, key configuration tables such as mysql_servers, mysql_users, mysql_replication_hostgroups, mysql_group_replication_hostgroups, and mysql_query_rules, and demonstrates how to set up read/write splitting and automatic failover for MySQL primary‑replica and group replication environments.
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 reproduces a prolonged MySQL 5.7 binlog replay issue, analyzes the underlying client memory‑allocation bug, and demonstrates how MySQL 8.0 resolves the problem, resulting in significantly faster binlog restoration for large delete operations.
This tutorial walks through creating a maintainable GraphQL application using Go, demonstrating how to set up a Docker‑based MySQL database, configure Viper and Echo, define ent schemas, run migrations, integrate gqlgen with ent, and implement queries and mutations following Clean Architecture principles.
This article examines the architecture differences between traditional MySQL and the cloud‑native TDSQL‑C database, outlines MySQL disaster‑recovery deployment models, and details TDSQL‑C’s multi‑dimensional disaster‑recovery system, including its agent‑scheduler design, cross‑AZ switching challenges, and mitigation strategies.
A developer noticed an UPDATE statement that appeared to run without effect, discovered that using AND between column assignments is invalid in MySQL, learned the correct comma‑separated syntax, and saw how logical AND expressions can produce unexpected zero values.
This article examines the architecture differences between cloud‑native TDSQL‑C and traditional MySQL, outlines TDSQL‑C’s elastic, serverless, low‑latency features, compares MySQL disaster‑recovery models, and details the multi‑dimensional disaster‑recovery system and its cross‑AZ/Region challenges and solutions.
This article explains how MySQL handles massive full‑table scans without blowing up server memory, detailing the role of net_buffer, socket send buffers, the "edge‑compute‑and‑send" model, and InnoDB's optimized LRU algorithm that protects the buffer pool during large scans.
The article explores MySQL's Hash Join algorithms, comparing Basic, On-disk, Grace, and Hybrid variants, while detailing rule-based optimizer decisions, memory management, disk spilling mechanisms, and outer join execution strategies through source code analysis.
This article explains the principles, advantages, and pitfalls of MySQL composite indexes, covering the left‑most matching rule, field order effects, when single columns can use an index, and whether a composite index can replace single‑column indexes, with practical EXPLAIN examples.
The article investigates a MySQL 8.0.18 crash that occurs when importing a large create.sql containing longblob data under semi‑synchronous replication, reveals that enabling slave_compressed_protocol together with high‑availability latency checks triggers the failure, and provides a concrete fix by disabling the compressed protocol.
This article explains how MySQL's JSON data type bridges relational and non‑relational storage, shows practical table designs for user login and profiling, demonstrates querying with JSON functions and operators, and covers function and multi‑valued indexes to efficiently retrieve JSON‑based data.
This guide explains MySQL GROUP BY syntax, the role of aggregate functions such as AVG, COUNT, MAX, MIN, and SUM, and demonstrates practical queries with single‑field and multi‑field grouping, filtering with WHERE and HAVING, ordering, limiting results, and the correct execution order of keywords.
The article introduces a lightweight Go ORM for MySQL, explains native connection setup, details the SmallormEngine design with chainable methods for Table, Where, Insert, Update, Delete, and BatchInsert, and demonstrates how reflection builds SQL fragments while handling errors and supporting fluent query composition.
This article explains how to use MySQL native partitioning to split tables by time fields—year, month, or day—detailing the SQL syntax, partition creation steps, performance impacts, and best‑practice tips for each granularity.
This article explains the MySQL JSON data type, demonstrates how to store structured and semi‑structured data with JSON objects and arrays, shows practical table designs for user login and profiling, and details virtual columns, function indexes, and multi‑valued indexes for fast querying.
A practical MySQL InnoDB test shows that count(1) and count(*) have almost identical performance, explains the underlying index usage, and clarifies why MyISAM can count rows faster due to its storage design.
This guide details comprehensive naming conventions and design best‑practices for MySQL database objects—including schemas, tables, indexes, views, stored procedures, functions, triggers, constraints, and users—along with recommended storage engine, character set, field types, index strategies, and SQL usage rules to ensure consistency, performance, and maintainability.
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.
Learn how to effectively sort query results using ORDER BY, apply ASC/DESC rules, combine multiple fields, use aliases, integrate functions, and limit result sets with LIMIT for pagination, while avoiding common pitfalls such as duplicate sorting values and expression misuse in MySQL.
This article introduces MySQL indexes, explains why they are needed, compares B+Tree and hash index structures, describes how indexes improve query performance, outlines their limitations, shows how to create single‑ and multi‑column indexes, and discusses the trade‑offs of using them.
This article explains Change Data Capture (CDC), compares query‑based and log‑based approaches, introduces Debezium and ClickHouse, and provides step‑by‑step Flink CDC and Flink SQL CDC examples—including Java source, deserialization, sink code and required Maven dependencies—to stream MySQL binlog changes into ClickHouse for real‑time analytics.
This article presents practical MySQL optimization methods—including proper column definitions, NOT NULL usage, JOIN over sub‑queries, UNION, transaction handling, foreign keys, table locking, indexing strategies, and a collection of query‑tuning tips—each illustrated with clear examples and code snippets to improve database speed and reliability.
This article explains why a MySQL UPDATE statement can appear to have no effect when using AND between assignments, shows the correct comma‑separated syntax, and provides visual verification of the fix.
The article explains that using the logical operator AND between column assignments in a MySQL UPDATE statement causes unexpected results, demonstrates the issue with sample data, and shows that multiple columns must be separated by commas to update them correctly.
The article shares a senior DBA's journey from early database engine development to modern MySQL operations, outlining practical methods for improving efficiency, automating monitoring, building data‑driven processes, and redefining the DBA role for proactive, high‑impact service delivery.
The article explains how MySQL 8.0 improves deadlock logging by showing lock information for the holding transaction, discusses potential misunderstandings, and provides a step‑by‑step reproduction of a deadlock case with SQL statements and detailed lock analysis.
The article shares the author’s journey from early database development at DM6/DM7 through MySQL operations at Qunar, offering practical advice on demand‑driven implementation, data‑driven management, intelligent alerting, full‑log analysis, slow‑query risk modeling, high‑availability, and automation to transform traditional DBA work into a proactive, efficient New DBA role.
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 explains the architecture of Alibaba Cloud RDS MySQL three‑node enterprise edition, detailing the X‑Paxos consensus protocol, node roles, election mechanisms, weight‑based leader selection, state‑machine diagnostics, disk health probing, and reverse‑heartbeat design that together provide a robust high‑availability database solution.
This article explains MySQL 8.0's dual‑password mechanism for smooth password rotation and its built‑in random password generation feature, providing step‑by‑step commands, usage scenarios, and important cautions for database administrators.
A former Oracle MySQL lead engineer explains why, after five years of work, he finds PostgreSQL a superior open‑source RDBMS, criticizing MySQL’s design flaws, comparing MariaDB, and highlighting real‑world migration experiences and industry perspectives.
This comprehensive guide walks through MySQL performance fundamentals, real‑world testing results, slow‑query detection, index impact, pagination pitfalls, count optimizations, sharding techniques, and step‑by‑step migration plans for large tables, equipping engineers with actionable tuning insights.
Using LIMIT with a large offset in MySQL can cause severe performance degradation due to excessive random I/O, but by rewriting the query to first fetch primary keys in a subquery and then joining, execution time can drop from seconds to milliseconds, as demonstrated with real data.
This article examines workload analysis and query performance differences between PostgreSQL and MySQL, focusing on JSON handling, indexing strategies, concurrency control, benchmark results, and replication features, while offering configuration tips to improve each database’s speed and efficiency.
This guide explains how to install the DTLE RPM, prepare MySQL source and target instances, create job JSON files to rename databases or tables during transfer, submit jobs via Nomad, verify synchronization, and clean up jobs, providing a complete end‑to‑end data migration workflow.
This article examines why MySQL benchmark results often show low transactions per second on seemingly powerful servers, exploring hidden factors such as network bottlenecks, SSL overhead, and inappropriate sort_buffer_size settings, and provides practical troubleshooting steps and configuration tweaks to achieve optimal performance.
This article examines seven typical scenarios where MySQL indexes unexpectedly fail—such as OR conditions, leading wildcards in LIKE, missing quotes, functions on indexed columns, NULL checks, arithmetic operations, and composite index ordering—providing SQL examples, execution plans, and practical tips to avoid costly full‑table scans.
This comprehensive guide covers MySQL fundamentals such as ACID transaction properties, isolation levels, index types and design principles, storage engines, MVCC mechanics, query execution flow, replication, locking strategies, and performance‑tuning techniques for large tables and complex queries.
The DTLE 3.21.11.0 release introduces MySQL‑specific enhancements such as partitioned table creation, geometry type support, multi‑threaded replay with unique columns, and large‑transaction handling, while also fixing an alter‑table rename issue, and provides download links and related reading resources.
This article demonstrates how to use the FunTester framework to benchmark MySQL SELECT and UPDATE statements, providing sample scripts, execution results, and insights into performance tuning for typical workloads and discusses observed QPS values.
This article explains why SQL queries can become slow, outlines MySQL's internal architecture, demonstrates how to use the EXPLAIN statement to view execution plans, and interprets key fields such as type, key, and Extra to help developers identify and optimize performance bottlenecks.
This article demonstrates how MySQL 8.0 can enforce password history count and reuse‑interval policies for development and operations users by configuring global parameters and per‑user settings, eliminating the need for external password‑tracking scripts.
This article walks through using the FunTester framework to benchmark MySQL insert and delete operations, showing Java code examples, test configurations, and JSON results that reveal a high QPS for inserts and a noticeable slowdown when deletions are performed.
This article explains why MySQL queries become slow, how index design and lock contention affect performance, and when to complement MySQL with Elasticsearch for full‑text search or HBase for write‑intensive workloads, providing practical optimization strategies and architectural guidance.
A former Oracle MySQL lead engineer denounces MySQL as a poor open‑source database, praises PostgreSQL, critiques MariaDB, and discusses the historical evolution and corporate politics behind these relational database systems.
The article investigates a bug introduced in MySQL 5.7.36 where mysqldump writes the SET @@GLOBAL.GTID_PURGED statement at the end of the dump file, causing GTID and data inconsistencies that break replication, and recommends using an older mysqldump version until the issue is fixed.
NetEase’s DBA team presents a comprehensive analysis comparing TiDB‑based innovative architecture with the traditional MySQL + DDB stack, detailing business fit, cost reduction, technical innovation, scalability, high availability, HTAP capabilities, migration strategies, and practical deployment experiences to justify adopting TiDB as a next‑generation distributed database solution.
This article examines how MySQL Connector/J determines the character set during connection initialization, explains the transition from UTF8MB3 to UTF8MB4, analyzes source code of versions 5.1.46 and 5.1.47, and provides practical methods to enable UTF8MB4 without upgrading the driver.
When a table grows beyond ten million rows, sharding it across multiple databases improves performance, but the usual LIMIT offset, pagesize pagination breaks, leading to missing or incorrect records; this article examines why simple merge approaches fail and evaluates global, secondary query, and no‑skip paging strategies, highlighting their trade‑offs.
The article details Facebook’s multi‑year effort to migrate its heavily customized MySQL 5.6 deployment—including the MyRocks storage engine—to MySQL 8.0, describing the technical challenges, patch‑porting strategy, replication changes, automated verification, and application validation performed during the upgrade.
This article reviews MySQL horizontal partitioning, explains five partitioning strategies (HASH, RANGE, KEY, LIST, COMPOSITE), demonstrates how to create and test partitioned tables, compares performance with and without indexes, and covers partition management operations such as adding, dropping, and rebuilding partitions.
This article explains how to set the correct time zone for MySQL during installation, describes the relevant startup parameters and system variables, demonstrates their effect on functions like NOW() and TIMESTAMP columns, and provides practical solutions for Java applications, data migration, and dump utilities.
This guide walks through the step‑by‑step deployment of MHA on a MySQL cluster, covering package installation on all nodes, copying and installing the MHA RPMs, creating the required MySQL user, configuring MHA, testing SSH connectivity, and reviewing the failover script.
This article explains why deleting rows in MySQL does not shrink the underlying table file, describes the InnoDB storage mechanics that cause this behavior, and provides practical solutions such as OPTIMIZE TABLE, ALTER TABLE, and Online DDL to reclaim space and avoid table‑locking issues.
This weekly community newsletter curates technical articles and updates, covering MySQL index length limitations, new password policies, backup‑induced replication issues, open‑source middleware progress, bug fixes, and upcoming development plans, while inviting reader feedback for further improvements.
This article explains why SQL queries become slow in MySQL, describes the server architecture, details how to use the EXPLAIN statement to interpret execution plans, and outlines how to read and optimize key fields such as type, key, and extra for better performance.
This article examines MySQL 5.7 replication issues caused by GTID settings during database migration, detailing environment setup, problem description, reproduction steps, analysis, conclusions on --set-gtid-purged usage, and practical solutions to ensure consistent in master‑slave synchronization.
The article explains how MHA handles a crashed MySQL master by preserving binary logs, selecting the most up‑to‑date slave, applying relay logs, promoting a slave to master, and provides detailed commands for creating replication users and configuring master‑slave replication.
This article walks through a practical approach to safely split a 50 million‑row MySQL history table, add missing indexes, and migrate data in batches using backup, table‑copy, and incremental INSERT operations while avoiding long locks and performance degradation.
Learn the essential MySQL Data Manipulation Language commands—INSERT, UPDATE, DELETE, and TRUNCATE—including syntax variations, best practices, and step‑by‑step command‑line examples that illustrate single‑row, batch, and conditional operations, plus key differences between delete and truncate.
This article explains how to use mysqldump for various MySQL backup scenarios, provides command‑line examples for full, structure‑only, and selective backups, shows how to restore dumps, and demonstrates a Bash script combined with crontab to automate daily backups with retention management.
This article explains MySQL 8.0's new current‑password validation strategies, showing how to enforce old‑password checks per user or globally, with detailed command‑line examples and options to enable, disable, or customize the behavior for enhanced security.
The article explains MySQL 5.7's engine‑dependent prefix index length limits, demonstrates how utf8mb4 reduces the maximum indexable column size for MyISAM tables, provides test cases with SQL code, and offers practical solutions such as prefix indexes, composite indexes, and CTAS for large datasets.
This article provides a comprehensive overview of MySQL, detailing its configuration file search order, component architecture, various storage engines such as MyISAM, NDB, Memory, and an in‑depth examination of InnoDB’s internal structures, memory management, background threads, LRU handling, redo log buffering, and checkpoint mechanisms.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This article details a MySQL InnoDB startup failure caused by ibdata1 lock errors, explains the underlying resource‑unavailable issue, and walks through removing stale socket lock files and restarting the server to achieve a successful launch.
The article investigates a DBCP 1.4 connection‑pool bug that allows the pool to exceed its configured limits, explains how removeAbandoned and socketTimeout interact to produce excess MySQL connections, reproduces the issue with a multithreaded test, and proposes configuration changes to avoid it.
This article explains the storage units of InnoDB, calculates how many rows a B+ tree can hold at different heights, shows how to determine the tree height from the page level, and answers why MySQL uses B+ trees for indexing.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
This article compares popular Kubernetes Operator scaffolding tools, explains why Kubebuilder 3.0 was selected for the radondb‑mysql‑kubernetes project, and provides step‑by‑step commands to initialize the operator and generate its API and controller code.
This article introduces the RadonDB MySQL Operator, explaining its design goals, deployment topology on Kubernetes, detailed architecture—including RBAC, manager, custom resources, and services—and provides a visual overview to help engineers simplify MySQL high‑availability deployments in cloud‑native environments.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
A colleague reported that a MyBatis insert stored a timestamp from the previous day, leading to a deep investigation that revealed mismatched timezone handling between MySQL, the JDBC driver, and Java, and presented two practical fixes to align all components.
MySQL’s default settings target a 512 MB virtual machine and work for development or testing, but production deployments require a series of performance optimizations, including understanding why database tuning matters, how to optimize MySQL instances, and which parameters to adjust.
This article explains how font‑based anti‑scraping works, shows how to locate and download custom font files, decode their glyph mappings into a dictionary, and use the mappings to extract real data from a recruitment site with Python, Scrapy and MySQL.
This article explains how MySQL InnoDB implements locking, the two‑phase lock protocol, the four transaction isolation levels, the differences between pessimistic and optimistic locks, MVCC implementation, and how next‑key and gap locks prevent phantom reads in high‑concurrency environments.
This article explains how MySQL InnoDB partition tables can dramatically boost query speed, simplify routine operations, enable parallel writes, and provide efficient data exchange and cleanup compared to non‑partitioned tables, illustrated with concrete SQL examples and performance metrics.
This article explains MySQL's architecture, the server and storage‑engine layers, query execution phases, status states, query cache behavior, the optimizer's parsing and planning steps, EXPLAIN output fields, the cost model, and why the optimizer selects index_author_id instead of index_title for a given query.
This article explains the concepts of driving (outer) and inner tables in MySQL join algorithms, covering Nested‑Loop, Block Nested‑Loop, and Hash Join implementations, how the optimizer selects the outer table, and comparisons with other database systems.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This comprehensive tutorial walks through configuring a Hadoop‑based environment (Flink 1.13.1, Scala 2.11, CDH 6.2.0, Hive 2.1.1, Hudi 0.10), compiling Hudi, setting up Flink and MySQL binlog, creating CDC source and Hudi sink tables, running Flink jobs, and synchronizing the results to Hive partitions for query via Hive and Presto.
A colleague encountered an "Incorrect string value" error when inserting UTF‑8 text into a GBK‑encoded MySQL table, and the article explains how charset mismatches and the use of the CHAR() function cause the failure, then provides reproducible tests and concrete solutions.
This article explains why inserting emoji into MySQL fails with the default utf8 charset, demonstrates how switching to utf8mb4 resolves the issue, and provides a historical overview of MySQL's limited utf8 implementation and the proper use of true UTF‑8 encoding.
Qunar’s DBA team overhauled their MySQL monitoring and alert system—originally built on Nagios and NRPE—by integrating a Kafka‑based pipeline, a custom alarm service, and MySQL‑stored alert templates, achieving flexible thresholds, granular silencing, high‑availability processing, and early‑stage intelligent management of alerts, slow queries, and disk space.
