This article examines MySQL's recommendation to use auto_increment primary keys instead of UUIDs or random keys by creating three comparable tables, inserting large data sets with Spring JdbcTemplate, and analyzing insertion speed, index structure impact, and the trade‑offs of each key strategy.
This article explains how a plain SELECT does not lock data, while SELECT … FOR UPDATE adds a pessimistic lock whose scope—row or table—depends on whether the query uses an indexed or primary‑key column, and demonstrates the behavior with SQL examples and transaction scenarios.
This article walks through setting up a high‑availability MySQL environment using master‑master replication and Keepalived on two Ubuntu servers, covering architecture design, Docker container preparation, MySQL configuration, Keepalived installation, failover testing, and troubleshooting common issues.
This article explains how the SELECT ... FOR UPDATE statement in MySQL adds a pessimistic lock, describes when it results in a row lock versus a table lock based on index usage, provides verification SQL, example scenarios, and practical tips for transaction handling.
This article walks through a real‑world case where a MySQL instance showed an abnormal 13 GB data_free value, explains how the ibtmp1 temporary tablespace caused the growth, demonstrates verification steps with sysbench and SQL queries, and provides configuration and operational solutions to prevent and resolve the issue.
An in‑depth investigation reveals that Spring’s @Transactional rollback can silently fail due to a bug in mysql‑connector‑java 8.0.28 when useLocalSessionState=true, causing auto‑commit to stay enabled; the article explains the root cause, debugging steps, and how upgrading to 8.0.29 resolves the issue.
This article recounts a MySQL high‑availability incident in a dual‑master environment, explains how missing binary‑log index files caused replication failures, and details step‑by‑step troubleshooting, directory recreation, binlog position correction, and configuration improvements to restore reliable database operation.
This article explains why the MySQL 8.0 client may report the caching_sha2_password authentication error requiring a secure connection, describes the plugin’s design, RSA and SSL mechanisms, cache behavior, and provides practical commands and replication settings to resolve the issue.
This article explains in detail what happens inside MySQL when an UPDATE statement is executed, covering the execution flow, the roles of undo log, redo log, and binlog, buffer pool mechanics, two‑phase commit, group commit, and practical optimization tips.
This article demonstrates how to calculate a running total for each code and date in a MySQL table by using user-defined variables, addresses the need to reset totals per code, and shows an optimized version with the WITH clause and indexing recommendations.
This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.
This article explains why MySQL upgrades can cause syntax, semantic and performance incompatibilities, introduces the Percona Toolkit's pt‑upgrade tool, details its detection logic and common usage patterns, provides a full demo with real query results, and lists essential parameters and precautions for safely migrating from MySQL 5.7 to 8.0.
This article demonstrates two approaches for efficiently importing massive CSV data into MySQL using Python: a direct pymysql method with chunked inserts and a concise pandas‑SQLAlchemy method, comparing performance, code complexity, and offering tips for further speed improvements.
This tutorial walks developers through installing Docker on Ubuntu, explains core concepts such as images, containers and repositories, and demonstrates practical tasks including pulling a hello‑world image, running nginx and MySQL containers, configuring network bridges, handling common errors, and building a custom Spring Boot image with a Dockerfile.
The preview release SQLE 1.2206.0-pre1 introduces TiDB audit plugin support, TiDB‑specific rules, enhanced SQL syntax checks, and recommendations on primary‑key usage, while providing full release details, documentation links, and references to prior versions for database administrators and developers.
The 2022 Gdevops Global Agile Operations Summit in Guangzhou featured a technical session by Zheng Zengquan of Aikesheng on MySQL high‑availability architecture, covering data consistency, production‑ready solutions, and a comparison of MGR versus traditional master‑slave setups, with the full PPT available for download.
This article explains MySQL index fundamentals, covering clustered and secondary indexes, best‑practice design rules such as using auto‑increment primary keys, avoiding large or low‑cardinality columns, respecting the left‑most prefix, and common scenarios that cause index loss, with practical SQL examples.
This tutorial walks through setting up a highly available MySQL master‑master cluster using Docker containers, configuring MySQL replication, and employing Keepalived for automatic failover and virtual IP management, complete with step‑by‑step commands, configuration files, and troubleshooting tips.
This article explains several methods—ranging from insecure shortcuts to recommended practices—for hiding the MySQL password‑exposure warning that appears when invoking MySQL tools from a Linux shell, providing code examples and security considerations for each approach.
The LNMP one‑click installation package V1.9 adds support for Rocky Linux, AlmaLinux, CentOS Stream 9 and several Chinese Linux distributions, introduces PHP 8.1, new PHP extensions, MySQL 5.7/8.0 binaries, IPv6 and ZeroSSL options, and provides detailed management commands for deploying and maintaining Nginx, MySQL, PHP and related services.
The article recounts a production‑like MySQL dual‑master HA setup using Keepalived, describes how a missing binary‑log index caused replication failure, and details step‑by‑step troubleshooting commands, configuration fixes, and preventive measures to restore reliable database synchronization.
This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.
This article explains MySQL's internal execution process—including the connector, cache, parser, optimizer, and executor—describes privilege tables and verification, discusses query caching removal, outlines logical SQL execution order, and provides practical tips for ordering WHERE conditions to improve performance.
This article explains how to properly set file and process permissions for Nginx, PHP‑FPM, and MySQL on Linux servers, recommending the use of a dedicated www user group, showing configuration snippets, and illustrating how to verify running processes to enhance security and avoid permission‑related errors.
This article provides a detailed guide on optimizing MySQL performance, covering SQL statement tuning, index usage, table design normalization, system configuration, hardware choices, and the internal architecture of MySQL to help developers write faster and more efficient queries.
This article enumerates twelve typical reasons why MySQL queries become slow—ranging from missing or ineffective indexes, type conversions, OR/LIKE patterns, composite‑index misuse, built‑in functions, large tables, deep pagination, join overload, oversized IN clauses, dirty‑page flushing, order‑by filesort, lock contention, delete‑in pitfalls, and group‑by temp tables—and provides concrete SQL examples, execution‑plan explanations, and optimization techniques for each case.
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.
