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.
This article provides practical guidance on server hardware selection, Linux kernel parameter tuning, and MySQL configuration adjustments to improve overall system performance, reliability, and efficiency for production environments.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication using Docker, explains redo log and binlog mechanisms, and demonstrates how to implement Sharding‑JDBC read/write splitting and database sharding with Spring Boot, including code examples and configuration details.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This tutorial demonstrates how to analyze an e‑commerce search page, use Selenium to automate browsing, extract product details across multiple pages, and store the collected data into a MySQL database, providing complete Python code and step‑by‑step explanations.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download, install, and run them, and shows how to interpret their diagnostic reports to identify configuration issues and optimize database performance.
After repeatedly encountering account balance discrepancies in a financial system, I traced the issue to improper use of pessimistic locks and Hibernate caching, then resolved it by correcting lock placement, optimizing SQL queries, and switching to native SQL, sharing the detailed debugging steps and lessons learned.
The article explains why MySQL’s DELETE command does not shrink the physical table file, describes InnoDB’s storage mechanics, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains why MySQL fails to start on Ubuntu due to AppArmor restrictions and provides two practical solutions: editing the AppArmor profile to grant MySQL directory access or switching AppArmor to complain mode, with full command examples and configuration snippets.
This article explains MySQL disaster‑recovery techniques, covering cold and hot backups, same‑city versus remote setups, master‑slave topologies, async, semi‑sync and full‑sync replication, the MAR strong‑sync approach, and practical recommendations for building resilient two‑city three‑center architectures.
This article provides an in‑depth explanation of MySQL InnoDB’s transaction system, covering ACID properties, isolation levels, MVCC implementation, various lock types, key data structures, lock acquisition flows, semi‑consistent reads, insert locking, implicit‑to‑explicit lock conversion, and rollback mechanisms.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article provides a comprehensive overview of MySQL fundamentals—including logical architecture, locks, transactions, and storage engines—and offers practical optimization strategies for schema design, data types, indexing, and query execution, supplemented with code examples and performance‑analysis tips.
The DTLE 3.21.10.0 release introduces foreign‑key support, a new job parameter for foreign key checks, cascade operations, handling of large‑memory transactions, and various bug fixes and refactorings, providing a more robust MySQL data‑transfer solution.
This article presents a complete Java backend project for community‑based COVID‑19 management, detailing its architecture, modules such as login, daily epidemic tracking, prevention management, system administration and user management, and provides extensive SpringBoot service‑layer code examples with RBAC permission control.
After a colleague accidentally dropped a production database, we revoked DROP privileges for a MySQL user and systematically tested how this restriction impacts common operations such as renaming tables, using TRUNCATE, deleting rows, dropping objects, and recovering data via binlog, revealing surprising behaviors and practical workarounds.
This article explains what an execution plan is, how to use the EXPLAIN keyword in MySQL 5.7, interprets each column of the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, rows, and Extra—provides detailed examples of different query scenarios, and shows how optimizer settings like derived_merge affect the plan.
This article explains MySQL's character encoding workflow, illustrates why queries containing Chinese characters fail without proper settings, and shows how to configure JDBC URLs, server variables, and Docker‑based MySQL instances to ensure lossless UTF‑8 handling.
This article explains why duplicate rows appear in MySQL tables, demonstrates how to identify them with SELECT queries, and provides step‑by‑step SQL solutions—including safe deletion of all duplicates or retaining a single record per group—using subqueries and temporary tables for efficient cleanup.
