This comprehensive guide explains MySQL's binary log, undo log, redo log, and ChangeBuffer, covering their concepts, purposes, recording formats, flush timing, recovery processes, and how they interact during transactions and crash‑safe recovery.
The article describes an experiment that shows when a floating IP (SIP) moves between MySQL servers, existing client connections experience long delays until TCP keepalive detects the failure, highlighting the need to configure shorter keepalive intervals for high‑availability database deployments.
This article explains the fundamentals of MySQL InnoDB page size, how it interacts with operating system pages, and the performance implications for rows, indexes, and tablespaces, providing practical guidance for choosing optimal page sizes in production environments.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, pre‑filtering, and intermediate result handling, and provides rewritten SQL examples that dramatically reduce execution time.
This guide outlines comprehensive MySQL best‑practice standards covering naming conventions, storage engine selection, charset usage, table and column comments, size limits, partitioning, hot‑cold data separation, index design, SQL coding rules, and operational safeguards to ensure performant, maintainable, and secure database systems.
This article explains how using MySQL's INSERT INTO … SELECT without proper indexing can cause full‑table scans and locks, illustrates the problem with a real‑world order‑migration incident, and provides a concrete solution by adding an index to the source column.
This article explains MySQL primary key design principles, compares auto‑increment, UUID and uuid_short approaches, provides SQL examples and performance benchmarks, and offers guidance on choosing business‑related versus unrelated key columns for optimal data integrity and query efficiency.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
When MySQL reports 100% disk usage with only a few megabytes left, typical fixes like dropping tables or restarting may not release space, so this article explains how to diagnose data_free bloat, apply proper fragmentation cleanup, and restore the database safely.
This guide explains how to manually back up a MySQL database using mysqldump, import the backup file, and set up a Bash script with crontab for daily automated backups, including handling of MySQL event tables and path configuration.
This article outlines five practical methods for synchronizing MySQL data to external systems such as Elasticsearch, Redis, and message queues, covering business‑layer hooks, middleware integration, scheduled tasks using updated_at, binlog parsing with ROW format, and handling mixed or statement binlog formats, plus open‑source tools.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, predicate push‑down, early row reduction, and intermediate result set handling, providing concrete rewrites and execution‑plan comparisons that dramatically improve query speed.
This article provides a comprehensive technical analysis of ShardingSphere's XA distributed transaction capabilities, covering the CAP theory, X/Open DTP model, MySQL XA support, JTA mapping, Atomikos initialization, transaction lifecycle (begin, getConnection, commit, rollback), and the recovery mechanism with detailed code snippets and diagrams.
This guide explains how to install MySQL 8.0.16 on two CentOS7 servers, configure master‑slave replication with binlog, set up read/write splitting, and integrate the setup into a Spring Boot application using dynamic‑datasource, covering all necessary commands, configuration files, and code examples.
This article outlines MySQL Group Replication’s key features, fault-detection mechanism, limitations, required environment, and step-by-step deployment scripts, providing a practical guide for setting up a high-availability MySQL cluster on CentOS using the provided shell scripts and configuration files.
This guide explains how to filter data in MySQL using various query conditions such as WHERE, comparison operators, BETWEEN, IN, NULL checks, logical operators, short‑circuit evaluation, and operator precedence, with clear examples and visual illustrations.
This article examines a slow MySQL 5.7 query, uses the optimizer trace tool to reveal why the optimizer chose an inefficient plan, explains the role of fan‑out estimation on information_schema tables, and shows how hints can be applied to correct the plan.
This article examines MySQL pagination strategies for large tables, detailing preparation, general LIMIT‑based pagination, subquery and ID‑range optimizations, performance test results, and practical recommendations for improving query speed on millions of rows.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article explains how Vivo uses Alibaba's open‑source Canal to capture MySQL binlog changes, achieve non‑stop sharding and cross‑region data migrations, ensure high availability with Zookeeper, and share practical lessons on serialization, consistency, and monitoring in large‑scale backend systems.
The article explains why short URLs are popular, outlines their basic workflow, and details a backend service design that uses an incremental ID generator, Redis caching, MySQL storage, batch allocation, and distributed strategies to efficiently create and manage short links.
This article examines how differences between Galera GTID and MySQL GTID caused replication interruptions during a Percona XtraDB Cluster (PXC) split at Qunar, details the migration procedure, reproduces the problem with step‑by‑step commands, explains GTID concepts, and proposes operational improvements to avoid data inconsistency.
This article provides an extensive overview of MySQL, covering database creation, renaming, and deletion, table creation and alteration, column and index management, data queries, inserts, updates, deletes, operator usage, view handling, functions, stored procedures, triggers, sequence generation, user management, and permission configuration, with practical examples and code snippets.
This article explains the fundamental concepts and practical design of MySQL indexes, covering implementation-level index types, application-level index categories, the differences between clustered and non‑clustered indexes, B‑Tree and hash structures, covering indexes, and best‑practice strategies for optimal query performance.
The article describes a puzzling MySQL 5.7 situation where any password works, investigates the cause as the auth_socket authentication plugin, provides exact SQL commands to fix the issue, and explains the plugin's security characteristics and appropriate deployment scenarios.
This article explains Change Data Capture (CDC) and how Flink CDC can be used for incremental data synchronization, real‑time materialized views, audit logging, and CDC‑based joins, providing code examples, Maven dependencies, and SQL/Java snippets for MySQL and Kafka integrations.
The article details a MySQL server startup failure caused by insufficient memory, shows command outputs and error logs, and explains how reducing the innodb_buffer_pool_size to 64 MB in the my.cnf configuration resolves the issue on a 2 GB Alibaba Cloud instance.
This article explains why indexes dramatically improve MySQL query speed on large tables, outlines common reasons for slow SQL, shows how to use EXPLAIN and SHOW PROFILE to diagnose problems, and provides step‑by‑step examples of creating and evaluating indexes for optimal performance.
This article examines why a MySQL 5.7 query that finds tables lacking primary or unique keys runs extremely slowly, demonstrates an experiment reproducing the issue, analyzes the optimizer's subquery rewrite, and shows how adding hints and materialization reduces execution time from over 16 seconds to under one second.
This article explains four methods to determine MySQL server startup time, such as checking service status, querying uptime, using ps, and inspecting logs, and also outlines common crash causes like bugs and resource allocation failures, providing troubleshooting steps and diagnostic commands.
This article presents a comprehensive set of mandatory and recommended MySQL database standards—including table creation rules, SQL writing conventions, and index design practices—aimed at preventing improper schemas, reducing erroneous queries, and improving overall performance for backend development teams.
The article evaluates how turning on binlog transaction compression in MySQL 8.0 (both 8.0.19 and 8.0.20) affects throughput and resource usage under a sysbench workload of 120 tables with 240 concurrent threads, showing roughly a 1% performance drop and a similar increase in CPU consumption.
This guide explains how to build a private npm registry on Ubuntu using cnpmjs, covering MySQL installation, server configuration, repository setup, package publishing, and synchronization options, providing step‑by‑step commands and troubleshooting tips for a stable internal package management solution.
This article explains MySQL 8's persisted system variables feature, showing how to use SET PERSIST and SET PERSIST_ONLY to make variable changes survive restarts, how to view and clear persisted settings, how to disable persistence, required privileges, and how to monitor persisted variables via performance_schema.
The article examines MySQL pagination performance on a large order_history table, presenting various pagination methods—including simple LIMIT, subquery, ID range, and temporary table techniques—along with detailed test results that show how query speed varies with record count and offset.
This article explains the MySQL EXPLAIN command, its extended forms, the meaning of each output column, common performance pitfalls such as full table scans, using temporary tables or filesorts, and provides practical optimization recommendations with illustrative examples.
This article details the design and implementation of a Java chat system that uses Netty for server‑side networking, Swing for the client UI, and MySQL with C3P0 for data persistence, covering features such as user authentication, friend management, single‑chat messaging, and online status detection.
This article explains MySQL 8’s newly supported CHECK constraints, outlines usage restrictions, and provides step‑by‑step examples—including simple age validation and complex gender‑age logic—showing how to create, test, alter, and drop constraints while highlighting practical considerations.
This article explains how to diagnose MySQL client connection failures by checking whether the mysqld process is running, verifying network communication settings such as skip‑networking and bind‑address, and examining authentication or password issues via the error log.
This tutorial walks through adding Dataway to a Spring Boot project by importing Hasor and Dataway dependencies, configuring Maven, setting Dataway and datasource properties, creating a Hasor module to inject the Spring DataSource, enabling Hasor annotations, launching the application, and finally using the Dataway UI to define and publish SQL or DataQL‑based APIs.
Using sysbench, this study benchmarks MySQL 5.7.22 and MySQL 8.0.15 under read‑write, read‑only, and write‑only workloads with various sync_binlog and innodb_flush_log_at_trx_commit settings, revealing performance trends across different concurrency levels and highlighting that MySQL 5.7 often outperforms 8.0 in these tests.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
This article explains the distinct purposes of MySQL's InnoDB redo log and server‑level binlog, how they work together during large update operations, the concepts of write position, checkpoint, and two‑phase commit, and why both logs are essential for durability and replication.
This article explains MyBatis-Plus's key features, shows how to add Maven dependencies, demonstrates the built‑in batch‑insert API, and provides step‑by‑step code to extend MyBatis‑Plus with a custom mapper and service for fast MySQL batch inserts.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
This page continuously gathers and organizes over 200 Java backend interview Q&A articles, covering topics such as Spring, Redis, MySQL, concurrency, design patterns, and system design, providing a valuable resource for candidates to review before interviews.
This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.
This article investigates why MySQL 5.7 crash recovery can take hours on a development instance with thousands of tables, detailing stack‑frame analysis, GDB debugging, InnoDB source inspection of validation logic, performance testing, and methods to bypass the table‑space verification step.
This guide explains why MySQL reports 100% disk usage despite minimal data, shows how to identify excessive data_free, details step‑by‑step commands to defragment tables, free space, and safely backup and restore the database.
This article details how Vivo's cloud service tackled explosive data growth by applying horizontal and vertical sharding, routing‑table based dynamic expansion, and MySQL InnoDB compression, providing step‑by‑step guidance, performance results, and practical recommendations for large‑scale database deployments.
The article investigates the MySQL 5.7 sys schema ERROR 1356 caused by mysqldump --all-databases dropping mysql.proc, explains why the bug occurs, cites official documentation and bug reports, and provides four practical solutions including mysql_upgrade, separate sys backups, using --databases, and rebuilding from mysql‑sys.
This article provides a comprehensive overview of common MySQL attack techniques—including client‑side arbitrary file reads, SSRF‑based data extraction, server‑side file read/write, remote code execution vulnerabilities (CVE‑2016‑6662), and authentication bypass (CVE‑2012‑2122)—and supplies practical command examples and mitigation insights.
This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
This article compares MySQL and Oracle databases, covering their fundamental nature, security mechanisms, permission models, schema migration considerations, storage concepts, syntax variations, and practical differences such as primary keys, quoting rules, pagination, and data type handling.
This article explains how to determine whether a PHP string contains emoji characters, provides functions to detect and strip emojis using multibyte string functions, and discusses storing emoji‑containing strings in MySQL with utf8mb4 or base64 encoding.
The article explains how MySQL 8.0.22 enhances the util.import_table function with the decodeColumns option, allowing users to preprocess each row during import, replace load data statements, and customize column values directly within MySQL Shell.
This article examines common techniques for implementing a user check‑in function in web and mobile applications, comparing a straightforward MySQL table solution with a Redis bitmap approach, detailing the data structures, code examples, workflow, and the advantages and disadvantages of each method.
This article explains MySQL's lock levels, describes various lock algorithms, presents multiple deadlock scenarios with SQL examples, analyzes the root causes, and outlines InnoDB's deadlock prevention mechanisms and detection methods for developers dealing with concurrency issues.
This article provides a comprehensive overview of MySQL fundamentals, covering transaction properties and isolation levels, MVCC mechanics, differences between InnoDB and MyISAM, query execution flow, redo and binlog, index structures, common pitfalls, and practical optimization and scaling strategies.
Lufax completed a two‑year, zero‑downtime, 100% Oracle‑to‑MySQL migration across its entire financial platform, detailing the dual‑write strategy, fine‑grained batch switching, tool‑driven automation, and the resulting cost and operational benefits for over 45 million users and supporting critical transaction services.
This article introduces ClickHouse, compares column‑store and row‑store databases, shows how migrating a 50‑million‑row MySQL table to ClickHouse reduced query time from minutes to under one second, and shares practical installation, migration, performance testing, and synchronization tips.
MySQL startup failures are typically caused by either insufficient system resource access (such as file permissions or security policies) or incorrect configuration parameters, and can be diagnosed by checking permissions, security policies, and the order and content of MySQL option files.
This article explains the severe impact of MySQL slow queries, shows how to enable and configure the slow query log, demonstrates using EXPLAIN to analyze query execution, and provides concrete optimization techniques for indexes, pagination, and common pitfalls.
This guide walks you through removing old MySQL versions, installing MySQL 8.0 via YUM or binary packages, configuring a secure initial password, setting up multiple instances on different ports, and addressing typical MySQL 8.0 pitfalls such as user creation, remote root access, authentication plugins, and character set settings.
This article provides a detailed walkthrough of MySQL protocol analysis using Wireshark, covering SSL disabling, packet capture commands, step‑by‑step examination of handshake, authentication, database selection, query execution packets, and an in‑depth look at related source‑code functions and command enums.
This comprehensive guide explains what NoSQL is, contrasts non‑relational and relational databases, outlines the main representatives of each, dives deep into MongoDB architecture, features, use‑cases and limitations, and also covers MySQL fundamentals, replication, backup, and common monitoring tools like Prometheus and Zabbix.
This article explains how to investigate a MySQL crash when core dumps are disabled by extracting the crash address from the error log, obtaining the matching binary, using GDB to map the address to source code, and decoding encoded function signatures to pinpoint the underlying bug.
This article explains the functional and performance differences among MySQL's count(1), count(*), and count(column) functions, illustrates how NULL handling varies, and provides a concrete example demonstrating their results and execution efficiency under different table schemas.
This article introduces ClickHouse, compares column‑ and row‑oriented storage, explains a real‑world migration from MySQL to ClickHouse that reduced a 3‑minute query to under one second, details installation, migration methods, performance results, synchronization options, and common pitfalls.
After upgrading a critical MySQL 5.6 table to 5.7, the system began reporting duplicate‑key errors on master, replica and read‑only instances due to unexpected changes in the AUTO_INCREMENT value, prompting a deep kernel investigation, bug analysis, on‑site reproduction, and practical mitigation steps.
The author recounts his 2012 MySQL interview experience, describes the global InnoDB team structure, outlines the bug‑fix and feature‑development workflow, lists key contributions such as spatial indexes and transparent encryption, and reflects on leaving Oracle for Tencent Cloud.
In the eighth episode of the MySQL冲冲冲 live series, experts discuss the rise of Chinese domestic databases such as TiDB, OceanBase, and Jusheng, comparing their architectures, ecosystems, and market prospects while reflecting on the broader implications for MySQL‑based distributed systems.
Vivo Cloud Service scaled from millions to tens of millions of users by applying four sharding strategies—horizontal table partitioning, horizontal database splitting, vertical partitioning, and dynamic routing—while enabling InnoDB compression, which halved storage size, kept CPU impact modest, and preserved ample free capacity for future growth.
The article describes how the Wukong Activity Platform’s data‑persistence layer uses Node.js as a BFF with MySQL, combines raw drivers, optional ORMs, and a custom lightweight Node‑MyBatis framework that offers dynamic SQL templating, built‑in injection protection, declarative transaction decorators, and automatic TypeScript type generation for full‑stack JavaScript development.
This article explains why multithreaded inserts into a single MySQL table can be faster than single-threaded inserts, analyzes the time distribution of insertion operations, and presents techniques such as prepared statements, multi-value inserts, batch execution, and transaction batching to significantly improve insert throughput.
The article explains how MySQL processes SELECT and UPDATE statements, detailing each execution stage—from client authentication and cache lookup to parsing, optimization, and execution—while highlighting the additional redo log and binlog handling required for UPDATE operations and offering practical code examples.
Allen Lai recounts his unexpected recruitment by Oracle, four interview rounds with MySQL's InnoDB leaders, the global and flat team structure, the disciplined bug‑fix workflow, his major feature contributions, and why he later moved to Tencent Cloud's CDB engine.
The author recounts his 2012 MySQL interview experience, describes the flat, globally distributed InnoDB team structure, details the typical bug‑fix workflow, lists major InnoDB features he contributed to, and reflects on the lessons learned before moving to Tencent Cloud's database division.
This article explains why MySQL indexes dramatically improve query speed, outlines common reasons for slow SQL, shows how to use EXPLAIN to analyze execution plans, and provides step‑by‑step optimization examples with code and visual illustrations.
This article explains why GTID‑based replication is preferred for synchronizing data from four regional factories to an IDC, and provides a step‑by‑step guide—including data export, GTID configuration, master‑slave channel setup, adding a new slave, and important precautions—for successful multi‑source MySQL replication.
This article explains why a MySQL query that uses an index can still become a slow query, explores the difference between index usage and execution time, and presents practical optimization techniques such as improving selectivity, reducing back‑table lookups, using index condition push‑down, and leveraging virtual columns.
This article details how to improve performance of massive MySQL tables on Alibaba Cloud by redesigning schemas and indexes, migrating to PolarDB with read‑write separation, partitioning historical data using the X‑Engine storage engine, and leveraging PolarDB's parallel query capabilities for faster analytics.
This article explains how MySQL's CASE WHEN, IF, and IFNULL functions can be used to implement conditional logic in SQL queries, providing syntax, examples, and combinations with aggregate functions for common data‑analysis scenarios.
This article compiles 100 common MySQL interview questions covering index structures, hash vs B‑tree trade‑offs, clustering, transaction isolation levels, lock types, primary key choices, storage engines, data types, binlog formats, pagination strategies, and best practices for schema design and query optimization.
This article examines common MySQL pitfalls—including signed vs. unsigned integers, auto‑increment key requirements, varchar length versus byte count, case‑insensitive collations, and storing emoji characters—providing concrete SQL examples, error screenshots, and configuration tips to help developers avoid unexpected errors.
This article explains how MySQL indexes work, comparing binary search trees, red‑black trees, hash tables, B‑trees, B+‑trees, and the differences between MyISAM and InnoDB implementations, and it details primary, secondary, covering, and composite index usage with practical examples.
This article explores four practical methods—batch inserts, transaction wrapping, ordered primary‑key insertion, and their combined use—to dramatically improve MySQL InnoDB insert performance, presents test results across various data volumes, and offers key configuration cautions.
This article explains how MySQL Group Replication (MGR) can provide zero‑RPO high‑availability within a city‑scale data center, why it needs asynchronous replication for WAN‑scale disaster recovery, and walks through a step‑by‑step setup—including code examples—for automatic failover of asynchronous replication channels.
This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.
A production MySQL query that omitted its leftmost indexed column caused a full‑table scan, CPU overload, and a read‑only alert, prompting a rapid investigation, temporary index addition, and a deeper discussion of the left‑most matching principle, Java validation pitfalls, and best‑practice lessons.
The DTLE 3.20.10.0 release introduces full and incremental job status output, a reschedule option to prevent unlimited retries, code refactoring, and provides repository, documentation, and download links for the open‑source MySQL data‑transfer component, followed by a light‑hearted personal note.
When a MySQL table grows large, CRUD performance degrades, so this article presents comprehensive optimization techniques—including field design, indexing, query rewriting, engine selection, system parameters, hardware upgrades, read‑write separation, caching, partitioning, vertical and horizontal splitting, sharding architectures, and recommendations for compatible scalable databases—to help maintain high performance and scalability.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article compiles essential MySQL interview questions covering data types, storage engines, locks, transactions, triggers, index creation and usage, join types, and practical SQL optimization techniques with concrete examples and code snippets for developers to master database fundamentals.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This guide explains how to identify bottlenecks and boost MySQL performance through both soft (query, index, schema) and hard (hardware, configuration, sharding, caching) optimizations, offering practical commands, best‑practice tips, and visual illustrations for each step.
This article explains the structure of MySQL ROW‑mode binlogs, demonstrates how to modify binlog events with Python to recover deleted rows, locate oversized transactions, and split large transactions into smaller ones, providing practical scripts and examples for advanced database manipulation.
This article explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, shows several SELECT queries to list duplicates, and provides multiple DELETE strategies—including sub‑queries and multi‑column handling—to safely remove excess records while keeping one copy.
Database auditing records user actions to detect illegal operations, with approaches ranging from application‑layer and transport‑layer monitoring to kernel‑level and plugin solutions; TXSQL’s MySQL‑compatible audit plugin offers both synchronous and asynchronous modes, delivering flexible rule configuration and only 3‑6 % performance overhead, making it a low‑impact, feature‑rich choice for compliance and forensics.
This article provides a comprehensive overview of MySQL’s logging mechanisms—including Binlog, Undo log, Redo log, and Change Buffer—explaining their concepts, roles in replication and crash‑recovery, recording formats, flush timing, two‑phase commit, checkpoint handling, and how they interact during data modifications.
