This article presents ten practical techniques—ranging from avoiding chat‑tool SQL transmission to using logical deletes, backups, and temporary tables—to help developers and DBAs safely execute and modify data in production environments while minimizing accidental data loss.
This article examines MySQL's ON DUPLICATE KEY UPDATE clause, explains its official behavior, runs experiments with primary and multiple unique indexes, compares InnoDB row‑level locking to MyISAM table‑level locking, and highlights performance and deadlock risks that developers should avoid.
This article describes how to create a Python‑based face‑matching tool that crawls celebrity photos, stores them in a MySQL database, and uses Baidu's AI face‑compare API to identify a person from an uploaded image, presenting the matching results with similarity scores.
This article explains Meituan's non‑intrusive, Go‑based packet‑capture solution for full‑SQL database auditing, details the performance challenges encountered at high QPS, and describes a series of systematic analyses and optimizations—including probe redesign, CPU profiling, data‑masking, scheduling, GC pressure reduction, and packet filtering—that dramatically lowered loss rates and CPU usage.
A recent Shadowserver scan of the MySQL port 3306 revealed that over 3.6 million servers worldwide—67% of the total discovered—are directly reachable from the Internet, highlighting a massive exposure risk across IPv4 and IPv6 networks.
This article details how a fast‑growing supply‑chain platform migrated from MySQL and Hive to Apache Doris for real‑time analytics, describing the architectural evolution, the advantages of the new design, practical implementation steps, encountered challenges, and the performance and cost benefits achieved.
This tutorial walks through creating a headless‑browser crawler using Puppeteer, extracting data from Juejin, storing it in a MySQL database, and exposing the results through an Express web service with user authentication, subscription, and a simple front‑end UI.
This guide walks through troubleshooting DBLE container deployment by adjusting Docker network subnets, correcting DBLE configuration IPs, and granting the necessary database permissions to ensure the logical schema is created without errors.
This article investigates why S‑type GAP locks appear in deadlocks under the READ COMMITTED isolation level in MySQL InnoDB, detailing the transaction workflow, lock acquisition during INSERT operations, lock inheritance after rollbacks, and reproducing the deadlock scenario with code examples and diagrams.
This article explains three practical methods for adding audit capabilities to MySQL 5.7—using the init_connect parameter to log user connections, installing the MariaDB server_audit plugin for detailed event logging, and deploying the McAfee audit plugin—while providing step‑by‑step commands, configuration examples, code snippets, and a comparison of advantages and drawbacks.
This article explains the concept of MySQL auto‑increment locks, describes different insertion methods and lock modes, analyzes how InnoDB allocates and manages auto‑increment IDs—including simple, bulk, and mixed inserts—and provides source‑code insights, practical experiments, and mitigation strategies for ID continuity issues.
This article explores the various reasons MySQL queries become slow—including index misuse, insufficient connection pools, small InnoDB buffer pools, and query cache limitations—while providing practical solutions such as optimizing indexes, adjusting max_connections, enlarging buffer pools, and configuring connection pools for better performance.
After upgrading a MySQL instance from 5.6 to 5.7, long‑running INSERT statements stalled due to MDL (metadata lock) contention and the default derived_merge optimizer setting, and the issue was resolved by disabling derived_merge or rewriting the SQL.
This article walks through the complete lifecycle of a MySQL SELECT statement, from establishing the TCP connection and authentication, through query cache, parsing, optimization, and execution, detailing how the server and storage engine collaborate and explaining concepts like index usage, connection handling, and memory management.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves indexing and range queries, outlines the conversion functions and their performance, discusses readability drawbacks, and provides Java code for bidirectional conversion between string and integer representations.
The article explains why MySQL’s high‑performance guide recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of VARCHAR, detailing space savings, faster range queries, conversion functions (INET_ATON/INET_NTOA), associated drawbacks, and provides Java code to convert between string and integer representations.
This article explains MySQL’s core architecture, detailing components such as connection pools, SQL interface, optimizer, and storage engine plugins, then compares MyISAM and InnoDB, and dives deep into InnoDB’s memory structures, buffer pool, adaptive hash index, tablespaces, and redo log mechanisms.
This article explains three MySQL pagination techniques in Java—using LIMIT clauses, MyBatis RowBounds, and the MyBatis‑PageHelper plugin—providing syntax, XML and Java code examples, execution results, and practical recommendations for efficient data retrieval in backend development.
This article provides a comprehensive overview of MySQL indexes, explaining their underlying data structures, classification methods, practical usage scenarios, and optimization strategies, while illustrating concepts with diagrams and SQL examples to help developers design efficient query performance.
This weekly community newsletter summarizes recent MySQL licensing research, bug fixes, open-source middleware updates for DBLE, DTLE, and SQLE, highlights new features, community Q&A, and upcoming plans, providing developers with concise technical insights and resource links.
This article explains MySQL's redo log, binlog, and undo log, covering their roles in crash recovery, durability, performance optimization, log formats, two‑phase commit handling, and how they together ensure data consistency and atomicity in InnoDB.
This article explains why a MySQL root account may bypass password authentication, walks through common causes, demonstrates reproducing the issue, identifies the auth_socket plugin as the culprit, and shows how to switch to a native password plugin to restore proper password checks.
This article explains MySQL index fundamentals, covering B+Tree structures, various index types, creation syntax, and practical optimization techniques such as prefix, covering, and composite indexes, while illustrating how to avoid common pitfalls like index misuse and performance‑degrading scenarios.
This article walks you through the complete hands‑on lab for migrating a local MySQL database to Huawei Cloud RDS using the DRS service, covering environment setup, connection testing, full and incremental migration phases, and final execution tips.
This comprehensive guide details naming conventions for databases, tables, columns, indexes, views, procedures, functions, triggers, users, and constraints, along with design principles for objects, storage engines, character sets, table structures, field types, index strategies, and SQL usage to ensure consistency, performance, and maintainability in MySQL environments.
This article introduces and compares three MySQL online schema‑change solutions—pt‑online‑schema‑change, gh‑ost, and native MySQL ONLINE DDL—detailing their workflows, limitations, risks, monitoring features, and practical considerations for safely altering large tables in production environments.
This article walks through the background of a production Java service that received many performance complaints, enumerates common causes such as slow MySQL queries, complex business logic, thread‑pool misconfiguration, lock contention and machine issues, and provides concrete diagnostic steps and code‑level solutions including pagination fixes, indexing strategies, async processing, thread‑pool tuning, lock refinement and caching techniques.
The article explains why Oracle can release MySQL under a dual GPLv2/commercial licensing model, how the GPL works, the impossibility of changing licenses for existing versions, and proposes creating a domestically controlled GPL‑v2 MySQL fork to avoid future licensing risks.
Ctrip’s hotel order platform evolved from a single SQL Server instance to a multi‑IDC, sharded architecture, and this article details the middleware‑driven migration, unified caching, data‑chain redesign, consistency safeguards, and operational practices that enabled seamless, high‑performance order processing at millions of transactions per day.
This article explains why backup is the last line of data security, outlines multi‑location and multi‑media deployment requirements, compares physical and logical backup methods, reviews MySQL backup tools, evaluates their speed, and provides practical guidance for testing backup validity and minimizing RTO and RPO.
The SQLE 1.2205.0 release introduces three major features, enhancements to the SQL audit workflow, Oauth2 and LDAPS integrations, and a comprehensive list of bug fixes and optimizations, providing a more secure and efficient experience for database users and administrators.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication with Docker, explains redo log and binlog fundamentals, demonstrates two‑phase commit, and shows how to implement read‑write splitting and sharding using ShardingSphere‑Jdbc and Sharding‑Jdbc with detailed code examples.
This article explains how MySQL's EXPLAIN command reveals execution plans through twelve key fields, demonstrates practical analysis of a slow query on a large table, and provides actionable tips such as adding or forcing indexes to dramatically improve query performance.
This article analyses why physical backups using Xtrabackup fail when MySQL 5.7 performs online DDL operations that skip redo logging, demonstrates the issue with real‑world logs, and presents four practical solutions—including lock‑DDL parameters, per‑table MDL locks, and disabling online DDL—to ensure consistent backups.
This article explains the two kinds of MySQL temporary tables—external and internal—covers the scenarios that trigger their creation, details how MySQL chooses MEMORY, MyISAM or InnoDB storage engines based on system variables and data size, and describes indexing, hash‑field fallbacks, and optimization hints such as SQL_SMALL_RESULT and SQL_BIG_RESULT.
This article explains why concurrency issues like overselling occur in e‑commerce, introduces distributed lock concepts and mainstream implementations (MySQL, Redis, Zookeeper/etcd), and provides a three‑stage quality‑assurance framework with code‑review checklists, testing methods, and data‑reconciliation techniques.
This article provides a comprehensive overview of MySQL's internal execution flow—from client connection handling, permission verification, caching, parsing, optimization, and execution—to the detailed SQL execution order, helping readers grasp how queries are processed and optimized within the database engine.
This tutorial explains why read/write splitting is useful, shows step‑by‑step how to build a MySQL master‑slave cluster with Docker, configure the master and slave instances, and then use Sharding‑JDBC to transparently route writes to the master and reads to the slave.
The article investigates why read queries are sent to the master node when Dble is configured with rwSplitMode=3, analyzes reproductions on standard replication and MySQL Group Replication, identifies heartbeat and replication status issues, and provides troubleshooting steps and recommendations.
This article provides a comprehensive, step‑by‑step explanation of MySQL InnoDB's MVCC mechanism, covering its fundamentals, the role of Undo Log and Read View, and how it enables repeatable‑read and read‑committed isolation levels while preventing dirty reads and other concurrency anomalies.
The article explains why a newly created read‑only MySQL 5.7 user receives a “MySQL server has gone away” error, analyzes the role of the init_connect variable and SUPER privilege, and provides a step‑by‑step fix by correcting the init_connect setting.
This article shares a detailed, step‑by‑step account of three interview rounds at ByteDance, covering the background, technical and algorithm questions on Java, Redis, MySQL, Kafka, and more, along with personal reflections and practical tips for future candidates.
The article introduces the SQLE 1.2205.0-pre3 preview release, detailing the project's purpose, new enterprise‑edition SQL query features, complete release notes, and upcoming plans, while providing download links, documentation, and demo environments for users and administrators.
This article explains MySQL replication fundamentals, binary log mechanics, master‑slave roles, configuration steps, and the various replication types—including asynchronous, semi‑synchronous, multi‑source, and delayed replication—while providing practical commands and code examples for reliable high‑availability setups.
This article chronicles the architectural evolution of JD Baitiao’s backend—from early MySQL monoliths through Solr‑HBase, MongoDB, and DBRep—to the adoption of Apache ShardingSphere, highlighting the motivations, technical trade‑offs, decoupling strategies, and performance outcomes for a high‑throughput financial service.
The article investigates how loss of TCP SYN, SYN‑ACK, or ACK packets during the three‑way handshake affects MySQL client‑server connections, demonstrating retransmission behavior, kernel parameter tuning, and keep‑alive mechanisms through controlled iptables and tcpdump experiments.
This guide demonstrates how to deploy a MySQL cluster on Kubernetes using NFS persistent storage, ConfigMap for configuration, Service for access, and a StatefulSet to manage multiple replicas with init containers and volume claims.
This article explains the complete MySQL query processing pipeline, covering the connector, authentication, privilege checks, caching, parser, optimizer, executor, and the detailed SQL execution order with examples of each stage, helping readers understand and optimize their SQL statements.
The article investigates why two MySQL 5.7.32 installations on RedHat 7 show different client character sets—utf8 in the first batch and latin1 in the second—by reviewing MySQL charset settings, OS locale configuration, and providing corrective commands.
This article explains InnoDB’s role as a MySQL storage engine, how it reads and writes data using 16 KB pages, the mechanics of row formats—especially the dynamic format—and why varchar columns are limited to 16383 characters, illustrated with SQL examples and diagrams.
This article details how a Java Spring Boot microservice's response time was reduced from 5‑6 seconds to under one second by applying gzip compression, parallel data fetching, short‑lived caching, MySQL index tuning, and JVM G1 garbage‑collector adjustments.
This guide walks beginners through downloading, installing, and configuring MySQL 8.0.28 on Windows and Linux, covering environment variables, service management, password recovery, user and role creation, and JDBC connection setup with Maven, providing detailed commands, code snippets, and troubleshooting tips for a complete setup.
This article explains the complete MySQL execution pipeline—from client connection, authentication, and permission checks through caching, parsing, optimization, and execution—plus the logical order of SQL clauses, illustrated with diagrams to help developers optimize and comprehend database queries.
This article explains how to replace raw PyMySQL usage with SQLAlchemy ORM in Python, covering environment setup, database connection initialization, MVC‑style project structure, model class definitions, field attributes, session handling with context managers, and practical code examples for saving and querying data.
This article walks through MySQL's InnoDB storage engine, explaining how pages, locality, skip‑list concepts and B+‑tree structures work together to make indexes efficient, and shows how clustered, non‑clustered and covering indexes affect query performance and I/O behavior.
This article investigates a MySQL 5.7.30 replica crash caused by an InnoDB assertion in btr0btr.cc when handling temporary tables, reproduces the issue using the MySQL Test Run framework, analyzes logs and parameters, and proposes configuration and SQL‑optimisation fixes.
This article explains the concepts, motivations, strategies, and challenges of database and table sharding, covering vertical and horizontal partitioning, range and hash algorithms, when to adopt them, and popular middleware solutions for scalable MySQL architectures.
This article provides a comprehensive examination of InnoDB's physical file layout, detailing page components such as the file header, page header, record storage formats, variable‑length and NULL handling, free space, page directory, file trailer, external storage pages, and the differences among row formats and compression techniques, illustrated with concrete hexdump examples and SQL statements.
This article explains MySQL hash partition tables, illustrating their suitable use cases such as equality queries on IDs or dates, demonstrating creation, data loading, and query plans, and comparing standard hash partitions with linear hash partitions to highlight performance and data distribution considerations.
This article explains how Meituan tackles MySQL slow‑query problems by leveraging the database cost‑based optimizer to generate index suggestions, evaluate their quality, and operate a full‑stack governance workflow that includes data collection, statistical modeling, validation, and continuous improvement.
This article demonstrates how to package Ansible in a Docker container, use the mysql_db module to create MySQL backups, and run a simple playbook, highlighting the benefits of containerized deployment for clean, flexible operations automation.
This article analyzes a MySQL replication failure where the slave reports error 1236 "Could not open log file", explains the root cause involving duplicate entries in the binlog index after manual deletion of active binlog files, and provides step‑by‑step reproduction procedures and preventive recommendations.
The article explains how a pagination query on a 9.5‑million‑row MySQL table using a large OFFSET can take seconds, demonstrates an optimization that moves the condition into a subquery to fetch only primary keys before joining, and validates the speed gain with buffer‑pool measurements.
This article explains MySQL 8.0.17 InnoDB locking mechanisms—including latches, row and table locks, lock modes, intention locks, their compatibility matrix, and detailed source‑code examples that illustrate how IX and X locks are acquired during record updates.
This article details the end‑to‑end development of a supplier management system using SpringBoot, Thymeleaf, Mybatis‑Plus, MySQL and Redis, covering project planning, code implementation, file upload handling, caching optimization, and deployment considerations.
This weekly community newsletter curates the latest technical news and open‑source progress, covering MySQL and Mycat issues, a ClickHouse distributed DDL block case, a MySQL MDL lock analysis, the new SQLE release, and updates on DBLE, DTLE, and SQLE middleware projects.
The article introduces the SQLE 1.2205.0-pre1 release, detailing the project's purpose, new capabilities such as OAuth2 login integration and improved MySQL syntax‑error handling, and provides complete release notes, bug fixes, links to documentation, previous versions, and community resources.
This article shares a curated selection of interview questions and answers covering concurrency programming, MySQL, and Redis, extracted from the author's extensive interview notes, with links to the full content on a personal website and instructions for obtaining the complete material.
This article analyzes MySQL batch insertion by examining field size, max_allowed_packet limits, transaction overhead, buffer pool usage, and empirical tests with varying batch sizes, ultimately recommending a batch size around half of the max_allowed_packet for optimal throughput.
The article investigates a production MySQL outage caused by FLUSH NO_WRITE_TO_BINLOG TABLES blocking during backups, presents three experimental scenarios to identify the root cause, and offers practical recommendations such as setting query timeouts, adding indexes, and using Percona XtraBackup options to avoid backup stalls.
MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.
This article provides a comprehensive tutorial on using ShardingSphere‑JDBC, configuring MySQL master‑slave replication with Docker, explaining binlog and redo‑log mechanisms, demonstrating two‑phase commit, and detailing Sharding‑JDBC read/write splitting and sharding configurations with code examples.
In MySQL, an UPDATE that modifies no actual data returns an affected‑rows count of zero, making it unreliable for business logic that distinguishes between unchanged rows and non‑existent records, so developers should avoid using this count for critical decisions.
This article explains that MySQL imposes no fixed row limit, discusses the common 5‑million‑row myth and Alibaba's 2 GB recommendation, shows a Java insertion test that slows after millions of rows, and highlights how InnoDB buffer size and memory constraints affect table performance.
This article provides a step‑by‑step tutorial on setting up Prometheus and Grafana for 24/7 monitoring of Linux servers and MySQL databases, covering installation, configuration, data visualization, alerting with onealert, and common troubleshooting tips for reliable operations.
Facing a sudden MySQL table lock? This guide walks through real‑world troubleshooting steps—from checking table usage and process lists to inspecting InnoDB transaction and lock tables, identifying lock‑wait relationships, and safely killing offending sessions, while also explaining lock types and common metadata‑lock scenarios.
After deploying a project, a 500 Server Error appears the next day due to MySQL closing idle connections after the default 8‑hour wait_timeout, and the article explains how to verify the issue, adjust wait_timeout, use autoReconnect, and properly configure the HikariCP connection pool to prevent the failure.
This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
A recent poll revealed that MySQL with the InnoDB engine can support up to four billion tables, a limit dictated by the underlying file system rather than MySQL itself, and the official documentation confirms this massive capacity.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article introduces a full‑stack food‑delivery project built with Spring Boot, Vue.js, MySQL and MongoDB, detailing its architecture, module breakdown, technology choices, database setup, and step‑by‑step instructions for launching the backend API, admin portal, and mobile client, along with download links for data and images.
This article compiles eight classic MySQL error scenarios—including forgotten passwords, case‑sensitivity mismatches, Windows service startup failures, export/import restrictions, excessive connections, full binary logs, and primary‑key replication conflicts—along with detailed troubleshooting steps, configuration tweaks, and command‑line solutions to help database practitioners resolve them efficiently.
MySQL 8.0.29, released on April 26 2022, brings 160 bug fixes, several feature refinements—including string handling, stricter time formats, replication defaults, instant‑algorithm support for InnoDB, and new clone variables—while signaling the final phase of the 8.0 lifecycle and urging users of older versions to upgrade for security.
This article walks through generating a heavy‑load MySQL workload, using top, iostat, and iotop to pinpoint high disk I/O, mapping OS thread IDs back to MySQL sessions, and safely killing the offending query to relieve pressure.
The article asks how many tables can be created in a single MySQL database using the InnoDB storage engine, prompting readers to provide the correct limit without consulting external sources and discuss the theoretical maximum based on MySQL specifications.
The article describes how a large‑scale membership system achieves high performance and fault tolerance by deploying a dual‑center Elasticsearch cluster, isolating traffic with multiple ES clusters, adding a Redis cache with distributed locks, migrating the primary relational store from SQL Server to a partitioned MySQL cluster, and implementing fine‑grained flow‑control and degradation strategies.
The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
This article explains MySQL's lack of a hard row limit, the industry rule of thumb around 5 million rows, how InnoDB buffer size and hardware affect performance, and demonstrates a simple insertion test that reveals slowdown when a table grows too large.
This article analyzes a MySQL InnoDB lock wait timeout error, explains how gap locks cause blocked inserts and phantom reads under repeatable‑read isolation, and offers optimization strategies such as primary‑key deletions, while concluding with a recruitment invitation for the Zero technology team.
Yearning is a lightweight, self‑contained MySQL SQL audit platform that offers query auditing, SQL review, permission management, and automated rollback, with simple installation steps and no reliance on third‑party tools, making it ideal for small and medium enterprises.
This article analyzes MySQL batch insertion performance, explains how row size, max_allowed_packet, buffer pool, transaction handling, and index design affect throughput, and presents empirical tests that suggest using roughly half of the max_allowed_packet size as the optimal batch size for large data loads.
This weekly roundup from the DBLE open‑source community shares the latest MySQL‑related news, feature releases, bug fixes, and upcoming plans for DBLE, DTLE, and SQLE, providing developers with concise technical updates and links to detailed articles.
The article explains how Meituan uses MySQL's cost‑based optimizer to analyze slow queries, generate virtual index candidates, evaluate their costs with detailed statistics, and deploy a recommendation system that validates, tracks, and governs index suggestions to reduce CPU/IO waste and prevent database failures.
This article outlines several frequent MySQL query pitfalls—including missing table aliases in multi‑table operations, unsupported FULL JOIN syntax, unexpected REPLACE INTO side effects, auto‑increment edge cases, data‑type mismatches, nondeterministic functions, and warning‑driven query behavior—providing examples and best‑practice recommendations to prevent data loss and performance issues.
The article analyzes why a MySQL 5.7.32 instance’s monitoring thread remains in the Opening table state when querying information_schema.tables, discovers the involvement of the Federated storage engine, reproduces the issue with a non‑existent remote server, and confirms the root cause through controlled experiments.
This article provides a comprehensive guide to relational database design with MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine selection, character set and data type choices, and index design principles, illustrated through a student‑course selection example.
This article explains the components of MySQL InnoDB Cluster, introduces the InnoDB Cluster Set (MICS) for disaster‑recovery, outlines its limitations, and provides a step‑by‑step demonstration with code on how to create, monitor, and fail over a MICS deployment.
This article presents eight common MySQL error scenarios—including forgotten passwords, password policy violations, case‑sensitivity issues, service startup failures, export/import restrictions, connection limits, binary log overflow, and primary‑key replication errors—along with detailed troubleshooting steps and configuration commands to resolve each problem.
This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.
