This article recounts a production server data loss caused by an erroneous rm -rf command, describes how ext3grep and extundelete were used to attempt file restoration, and explains how MySQL binlog replay finally recovered critical data while sharing lessons learned for future incident handling.
The DB‑Engines March 2021 ranking evaluates 364 databases, highlighting shifts among the top ten—including Microsoft SQL Server’s steep decline, MySQL’s growth surge, PostgreSQL’s continued rise, and Snowflake’s dramatic climb—while offering trend graphs and insights for professionals choosing the right database technology.
This tutorial explains how to perform asynchronous MySQL operations in Python using aiomysql, covering basic connections, CRUD actions, safe parameter handling to prevent SQL injection, connection pooling, transaction management, integration with SQLAlchemy for ORM queries, and a custom reconnection wrapper for resilient database access.
This article experimentally demonstrates how increasing MySQL's innodb_log_buffer_size reduces redo‑log write I/O, showing fewer write operations but similar total data volume, and explains why performance gains may be subtle in typical virtual‑machine storage environments.
This article explains how to diagnose and resolve MySQL slave replication failures that occur when XtraBackup’s --kill-long-queries-timeout and --kill-long-query-type options interrupt long‑running queries, detailing steps such as checking performance_schema, reviewing error logs, restarting replication, and adjusting backup parameters.
This article reviews the history of MySQL, introduces MariaDB as its open‑source fork, and presents a series of benchmark tests—including single‑row inserts, batch inserts, and various query scenarios—showing that MariaDB generally outperforms MySQL in speed while using more memory.
This article walks through a step‑by‑step optimization of a Java‑based Excel import pipeline, covering the transition from raw POI to EasyPOI and EasyExcel, caching database lookups, batch inserts, optimal batch sizes, parallel streams, and logging reductions to achieve sub‑minute import times for hundreds of thousands of rows.
This article provides a detailed shell-script tutorial for installing MySQL 5.7 on a Linux node, initializing the database, setting the root password, and automatically creating multiple user accounts with random passwords, suitable for operations and database administrators. The guide includes full script listings, explains repository configuration, service management, and error handling, making it a practical reference for DevOps engineers.
An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.
This article explains why caching MySQL with Redis is essential, outlines the consistency challenges that arise, and presents four practical solutions—expiration, delete‑then‑repopulate, message‑queue updates, and binlog subscription—along with their pros, cons, and guidance for choosing the right approach in interviews.
This article explains the technical details of data cleaning, dirty‑data handling, and three methods—database read‑only, application termination, and network ACL isolation—to ensure data consistency during the data‑regulation and cutover phases of cross‑cloud migration, illustrated with real‑world case studies.
This article explains how to use MySQL's EXPLAIN FORMAT=JSON to view detailed execution plan costs, illustrates table structures, walks through two query examples with cost breakdowns, and clarifies the calculation of read_cost and eval_cost for both simple and join queries.
This article explains how enterprises can evaluate their needs and compare four popular open‑source relational databases—MySQL, PostgreSQL, MariaDB, and SQLite—covering licensing, performance, language support, strengths, and limitations to guide an informed selection.
This article outlines practical techniques for multi‑cloud data migration, covering challenges, resource categories, migration phases, and detailed synchronization and verification methods for MySQL databases, file systems, and object storage using tools such as UDTS, pt‑table‑checksum, sync_diff_inspector and US3SYNC.
During a holiday‑time alert, a seemingly harmless pair of UPDATE statements on the same InnoDB table caused a deadlock, and the article walks through log analysis, reproduction steps, lock mechanics, and practical ways to avoid such deadlocks in MySQL.
This guide outlines a step‑by‑step learning roadmap for PHP beginners, covering HTML/CSS/JS fundamentals, core PHP syntax, database integration with MySQL, object‑oriented programming, and popular frameworks, while emphasizing practice, project building, and continuous skill refinement.
This article explains practical strategies for adding a new column to a MySQL table with tens of millions of rows, including creating a temporary table, copying data, renaming tables, and alternative replica‑based or third‑party methods to avoid long locks and potential crashes.
This article introduces Python's DB-API for interacting with various databases, explains how to use PyMySQL and MySQLdb for MySQL operations, demonstrates basic CRUD examples, shows techniques to prevent SQL injection, and presents connection pooling solutions with DBUtils for multi‑threaded applications.
This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.
This article explains practical techniques for handling dirty data, guaranteeing data consistency, and performing validation during the data cleaning and cut‑over phases of a cross‑cloud migration, including read‑only database accounts, process termination, and network ACL isolation.
This article explains how incorrectly placed double‑quotation marks in MySQL UPDATE and SELECT statements cause implicit type conversion, turning intended string assignments into zero values and making WHERE clauses always true, leading to massive data corruption and unexpected query results.
This article examines several MySQL pagination techniques—including basic LIMIT, subquery‑based, ID‑range, and temporary‑table methods—provides detailed performance measurements on a multi‑million‑row table, and offers practical recommendations for fast and scalable data retrieval.
This tutorial walks through creating a Spring Boot project, configuring Maven dependencies, setting up MySQL with MyBatis, defining entity, mapper, service, and controller layers, and building simple Thymeleaf login and registration pages, culminating in a runnable dynamic authentication demo.
This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.
This article explains MySQL transaction fundamentals, the ACID properties, common concurrency problems, isolation level differences, and how InnoDB implements MVCC using version chains and consistency views, illustrated with practical examples and diagrams.
This guide explains how to use MySQL’s built‑in mysqlslap tool and the third‑party sysbench utility to perform comprehensive database stress testing, covering configuration changes, command examples, result interpretation, and additional CPU and I/O benchmarks for accurate performance evaluation.
This article explains how MySQL treats NULL values, why columns defined with NULL can invalidate indexes, demonstrates the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, COUNT, DISTINCT, GROUP BY and ORDER BY with NULLs, and offers practical recommendations for schema design.
This article analyzes common causes of slow MySQL queries—especially index misuse—offers practical indexing techniques, explains MDL locks and large‑table bottlenecks, and then compares ElasticSearch and HBase as complementary solutions for high‑performance search and storage.
This article reviews MySQL's evolution of high‑availability solutions—from classic master‑slave replication to InnoDB Cluster and InnoDB Replica Set—explaining each component, their features, deployment steps, and how to select the appropriate architecture based on business requirements such as RTO, RPO, and regional scope.
This tutorial demonstrates how to create a MySQL master‑slave replication cluster on Kubernetes using a StatefulSet, local persistent volumes, ConfigMaps, Secrets, and Services, covering storage class configuration, pod initialization, data cloning, replication setup, verification, and horizontal scaling.
This article compiles 52 practical SQL tuning strategies—including index best practices, query rewriting, use of temporary tables, proper data types, and server configuration tips—to dramatically improve MySQL query speed and overall database efficiency.
This article demonstrates how to create a test table, build indexes, and analyze their effectiveness across multiple query scenarios using MySQL's EXPLAIN output, highlighting the best‑left‑prefix rule, range condition impacts, ORDER BY considerations, and common pitfalls such as Using filesort and temporary tables.
This article traces MySQL’s evolution, introduces MariaDB’s origins, and presents a comprehensive benchmark comparing insert and query performance of MySQL 8.0.19 and MariaDB 10.4.12 on identical hardware, highlighting speed differences with and without indexes.
This article presents a systematic benchmark of MySQL 5.7.22 and MySQL 8.0.15 using sysbench to measure TPS and QPS across read‑write, read‑only, and write‑only modes under two persistence configurations, analyzes the results, and provides a Bash script for reproducing the tests.
This case study explains how a client with a 2‑billion‑row MySQL table avoided disk exhaustion and poor query performance by streaming data through Kafka to Google BigQuery, partitioning the data, and cleaning it to reclaim storage—all without downtime.
This article analyzes how many TCP connections MySQL will open when writing data at 100 rows per second for five seconds, examining MySQL processing capacity and connection‑pool configuration to illustrate the key factors that affect connection count.
This article explains the challenges of cross‑cloud migration, outlines three resource categories, details MySQL, file and object storage synchronization methods, presents practical tools and validation techniques, and shares real‑world case studies to ensure data integrity and efficiency.
This article explains the meaning of the Extra column in MySQL EXPLAIN output, covering how "Using index", "Using index condition", and "Using where" appear, when index coverage and index condition push‑down occur, and provides multiple practical examples with code.
This article investigates why the Alibaba Java Development Manual advises against joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, indexing effects, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article traces MySQL’s evolution, introduces its fork MariaDB, and presents a comprehensive performance benchmark—covering single and batch inserts, indexed and non‑indexed queries—on identical hardware, revealing that MariaDB generally delivers faster execution times albeit with higher memory usage.
This article explains MySQL's Partial Revokes feature introduced in version 8.0.16, showing how to enable the partial_revokes system variable, grant and selectively revoke SELECT privileges at both coarse and fine granularity, and verify the resulting restrictions using SHOW GRANTS and the mysql.user table.
This article explains why using ORDER BY with LIMIT for pagination in MySQL can yield inconsistent results when the ordering column contains duplicate values, describes MySQL's limit optimization behavior, and provides a reliable solution by adding a secondary unique sort key.
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.
This article explains why, in MySQL row‑based replication, a master table having an extra column does not disrupt replication to a slave table lacking that column, provided column order, placement, and default values meet specific conditions, and demonstrates the behavior with a MySQL 5.7.25 test.
This article explains the fundamentals of MySQL table lookup (回表操作), compares low‑version and high‑version handling, and demonstrates how index pushdown reduces unnecessary lookups, using clear diagrams and a practical user‑table example.
This article explains the difference between logical and physical pagination, demonstrates how MySQL's LIMIT clause works, compares two query methods on a large MEMORY table, analyzes their performance with indexes and EXPLAIN, and provides practical pagination examples and Java code for efficient data retrieval.
The article analyzes a production incident where dozens of MySQL UPDATE statements unintentionally set a column to zero because misplaced quotation marks caused MySQL to interpret the expression as a boolean comparison with implicit type conversion, and it explains how to detect and prevent such errors.
This article explores why MySQL queries become slow, delves into index pitfalls and optimization techniques, then compares ElasticSearch and HBase architectures, offering practical guidance on when to use each technology and how to combine them for high‑performance data retrieval.
This guide explains how to configure MySQL for high‑concurrency testing, use the built‑in mysqlslap tool and the third‑party sysbench utility to benchmark read/write performance, CPU, I/O, and memory, and interpret the resulting metrics for database optimization.
This article explains the meaning of each column in MySQL's EXPLAIN output, including id, select_type, type, possible_keys, key, key_len, ref, rows, filtered, and Extra, with examples of various query patterns such as simple selects, joins, subqueries, UNIONs, and derived tables.
The article investigates the Alibaba Java Development Manual's rule against joining more than three tables by designing and executing extensive MySQL and Oracle experiments, generating massive test data, measuring query performance, and concluding that the restriction stems from join scalability limits on large datasets.
This article breaks down nine core MySQL index concepts—including index types, B+Tree advantages, search steps, covering indexes, index invalidation, left‑most prefix rules, index push‑down, and safe index creation on large tables—to help candidates answer interview questions about tree‑search operations and performance trade‑offs.
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 provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
This article explains the root causes of MySQL master‑slave replication lag—including random replay, high master concurrency, and lock waits—and presents practical solutions such as parallel replication, reducing master load, and reading from the master to ensure data consistency.
This article demonstrates a step‑by‑step method for diagnosing MySQL hangs by collecting callstack information, identifying mutex conflicts, and mapping function calls to code locations to pinpoint the root cause of deadlocks.
This article explains how MySQL's index‑merge optimization can cause row‑level deadlocks during inventory updates, analyzes the lock sequence and deadlock logs, and presents practical solutions such as forcing a specific index, disabling index‑merge, and creating a composite index.
The article explains MySQL transaction ACID properties, details the four isolation levels and their associated anomalies, describes InnoDB's record, gap, and next‑key locks, and provides concrete SQL examples illustrating how these mechanisms prevent phantom reads and cause lock‑wait timeouts.
The DTLE 3.21.01.0 release introduces Kafka batch‑send parameters, support for rename‑table DDL, empty transactions for skipped DDL/DCL, and several bug fixes, while providing repository links, documentation, and installation tips for this MySQL‑focused data‑transfer component.
This tutorial explains how to set up MySQL master‑slave replication, configure MyCat as a database middleware, install and configure MyCat, and integrate it with a SpringBoot application using dynamic data sources and AOP to achieve transparent read‑write separation.
This article explains the three essential log files involved in MySQL write operations—redo log, binary log, and undo log—detailing the two‑phase commit process, their roles, write timing, storage mechanisms, and how they enable data durability, recovery, and replication.
This guide walks through MySQL's advanced features, covering lock types, transaction control commands, autocommit handling, savepoints, SQL mode configuration, regular expression usage, common SQL tricks, and a comprehensive overview of built‑in functions with practical code examples.
This article provides a comprehensive guide to advanced MySQL concepts, covering transaction control, various lock levels, lock and unlock statements, autocommit settings, savepoints, SQL security issues, SQL mode configurations, regular expression usage, and a collection of useful string, numeric, date‑time, and flow functions with practical examples.
The article explains why appending LIMIT to MySQL DELETE statements improves performance, reduces lock contention, and safeguards data by limiting rows affected, provides syntax details, compares deletion strategies for removing large numbers of rows, and offers practical recommendations for safe and efficient data removal.
This article walks through a systematic performance investigation of a Python‑Flask backend, using Chrome Network, flame‑graph profiling, and MySQL query refactoring to shrink a 37‑second endpoint down to 1.5 seconds, while sharing practical code snippets and optimization lessons.
This article explains the core concepts of MySQL transactions, the ACID properties, transaction states, isolation levels, multi‑version concurrency control (MVCC), and the various lock types and granularity used to ensure data consistency and concurrency control.
This article explains MySQL 8.0 window function FRAME clause syntax, differentiates ROWS and RANGE units, and demonstrates various frame boundaries such as CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, and expression‑based preceding/following with concrete query examples.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to execute, explores several ineffective optimization attempts, uncovers a client‑side LIMIT issue, and finally resolves the performance problem by forcing the use of the idx_end_time index, cutting the runtime to under a second.
This article explains InnoDB's record storage, primary and secondary index mechanics, composite index rules, and provides practical e‑commerce sharding and table‑design guidelines with real‑world case studies to help developers optimize MySQL performance and storage efficiency.
A production MySQL database suffered CPU overload due to a high‑volume query that bypassed an index because a leftmost column was missing, leading to full‑table scans, and the issue was resolved by adding a new composite index and correcting validation annotations in the Java code.
This weekly community newsletter summarizes recent MySQL issues, a recommended MySQL book, DBA team insights, MySQL 8.0.23 updates, index design tips, data recovery guidance, as well as DBLE and DTLE open‑source middleware progress, new features, bug fixes, community Q&A, and upcoming plans.
This article details a real‑world MySQL production data loss incident, explains how to identify the relevant binlog range, use binlog2sql and MyFlash to generate reverse SQL or binary logs, and outlines the step‑by‑step recovery process and post‑mortem reflections for operations teams.
This article examines the root causes of MySQL deadlocks in online services, explains transaction and lock mechanisms such as UndoLog and RedoLog, analyzes real‑world cases, and presents practical strategies—including isolation level adjustments and pre‑delete checks—to avoid and resolve deadlock issues.
This article explains how to achieve database read‑write separation in high‑concurrency scenarios using Spring Boot, Druid connection pool, and MySQL, covering master‑slave data source configuration, routing via AbstractRoutingDataSource, context holder management, custom annotations, AOP switching, and usage examples with code snippets.
This article explains how MySQL InnoDB generates AUTO_INCREMENT values, compares the full‑table locking mechanism before version 5.1 with the lightweight mutex introduced in 5.1, and describes the further improvements in 8.0 that rely on row‑based replication and lock‑mode settings.
The article documents a MySQL 5.6.35 deadlock occurring during inventory deduction, explains how Index Merge caused inconsistent lock ordering across multiple indexes, and offers solutions such as forcing specific indexes, disabling Index Merge, adding composite indexes, or restructuring the SQL to prevent the deadlock.
This article explains the concept, creation, and performance benefits of MySQL prefix indexes, demonstrates how to choose an optimal prefix length using selectivity calculations, and presents practical techniques for handling both prefix and suffix search patterns.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
This article explains why MySQL queries that use indexes can still become slow, examines full‑index scans, index selectivity, back‑table costs, and shows how features like index condition pushdown and virtual columns can dramatically improve performance.
The article reviews MySQL 8.0.23's maintenance release, detailing new features such as invisible columns, query attributes, security enhancements, InnoDB auto‑extend size, replication terminology changes, X protocol improvements, and various deprecations, providing code examples and links to official documentation.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
This tutorial walks through configuring a Django project to use MySQL instead of the default SQLite, covering database creation, settings adjustments, required package installation, migrations, and a complete example with URLs, views, templates, and server startup.
This article details how JD.com designed, scaled, and refined its mask reservation backend during the COVID‑19 pandemic, addressing extreme traffic spikes, hot‑key issues, MySQL bottlenecks, and introducing a circuit‑breaker mechanism to protect the overall transaction system.
This article provides an in‑depth guide to MySQL internals, covering the SQL execution process, server and storage engine layers, binlog/redo/undo logs, index structures, transaction isolation levels, lock types, MVCC implementation, buffer pool management, and practical optimization techniques.
During a production outage, a high‑volume SQL query without a required leftmost index field caused full table scans that saturated CPU, prompting a temporary index fix and revealing a deeper bug involving missing @NotNull validation and improper parameter handling, highlighting MySQL’s left‑most index rule.
When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.
This article provides a step‑by‑step guide to preparing the operating system, installing and configuring MySQL, tuning system parameters, and using a Bash one‑click deployment script to automate the setup and initialization of a MySQL 5.7.32 instance on Linux.
This article explains the data structures behind MySQL indexes, demonstrates how B+Tree indexes are built, and shows why the optimizer may choose full‑table scans over index scans in various scenarios such as pattern matching, OR conditions, type conversion, and NULL handling.
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.
This article explains how to list MySQL databases, perform full, multiple‑database, single‑database, and table‑specific backups using mysqldump, and restore them correctly, highlighting the differences when using the --databases option and common pitfalls.
This article explains what MySQL's slow query log is, how to configure and read it, and provides step‑by‑step procedures for safely clearing the log in both FILE and TABLE output modes to free disk space and maintain database performance.
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.
This article walks through building a Java Swing supermarket management desktop application, covering database table creation, user registration and login, product category and product CRUD operations, and the main menu UI, with complete source code snippets for each feature.
This article explains several efficient methods—including shell scripts, generated series tables, JSON functions, and MySQL 8.0 recursive CTEs—to quickly locate missing auto‑increment primary key values in a MySQL table, comparing their performance and suitability.
This article introduces MyBatis-Plus, explains its non‑intrusive features, demonstrates how to add Maven dependencies, examines the built‑in batch insert API, and provides step‑by‑step code to extend BaseMapper with a custom EasyBaseMapper for efficient MySQL batch inserts.
This tutorial walks you through using Flyway in a Spring Boot application to manage MySQL schema versions, from initial table creation and CRUD implementation to incremental migrations like adding new columns, complete with Maven dependencies, SQL scripts, Java code, and automated tests.
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.
This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.
When selecting an open‑source database, enterprises must assess their specific data needs, understand licensing differences, and weigh the strengths and weaknesses of popular options such as MySQL, PostgreSQL, MariaDB, and SQLite, as this guide details.
This article details how to dramatically speed up the import of massive Excel files in a Java backend by switching to EasyExcel, caching database lookups, using batch inserts, and applying parallel streams while eliminating excessive logging.
