This tutorial explains how to set up the environment, configure Elasticsearch and Logstash, create the necessary MySQL tables, and use a Logstash pipeline to continuously sync MySQL records into an Elasticsearch index, while also covering common pitfalls and troubleshooting steps.
This article explains how to safely run MySQL's CLONE INSTANCE command with the DATA DIRECTORY option on a replica that lacks enough disk space for both source and target data sets, offering step‑by‑step strategies, SQL examples, and a final recommendation.
This article reproduces MySQL's official LIMIT‑optimization example, shows how adding rows changes the ORDER BY + LIMIT result, explains the hidden priority‑queue mechanism revealed by optimizer_trace, and derives the exact row‑count threshold where the behavior switches.
This article details a practical approach to handling massive billing data by dynamically creating monthly tables in MySQL via stored procedures, integrating them with a SpringBoot‑MyBatis backend, and providing APIs for insertion, serial‑number lookup, and time‑range queries, complete with code examples and operational tips.
This article examines why combining ORDER BY with LIMIT in MySQL pagination can produce duplicate rows on subsequent pages, analyzes the optimizer’s priority‑queue behavior introduced in version 5.6, and presents practical solutions such as using unique sort keys, adding ID ordering, and understanding deep‑pagination limitations.
This article explores multiple strategies for enabling fuzzy search on encrypted phone numbers, including in‑memory caching, database decryption functions, segment‑wise storage, and dedicated fuzzy‑search fields, comparing their trade‑offs in performance, memory usage, and data consistency.
This article explains database sharding concepts, compares vertical and horizontal partitioning, details ID‑range and modulo sharding methods, discusses the read‑amplification issue caused by non‑shard queries, and presents solutions such as auxiliary index tables, Elasticsearch integration, and TiDB as alternatives.
This tutorial walks you through creating a Node backend using NestJS, configuring Sequelize with MySQL, setting up the project structure, writing DTOs, controllers, services, and modules, and implementing basic CRUD operations with code examples and deployment tips.
This article explains how to import 1 billion 1 KB log records stored in HDFS or S3 into MySQL by analyzing table capacity limits, choosing storage engines, designing batch inserts, coordinating file reading and writing, and handling task reliability with Redis, Redisson, and Zookeeper.
This article walks through setting up a Node.js backend using NestJS, integrating Sequelize with MySQL, configuring the database, defining entities, services, controllers, and demonstrating CRUD operations, along with optional features like GraphQL, Swagger, and deployment considerations.
This article explains why a MySQL 5.7.30 upgrade with GTID enabled causes a GTID‑consistency error for CREATE TABLE ... SELECT statements, describes the underlying mechanism, provides a safe two‑step workaround, and notes that MySQL 8.0.21+ now supports atomic DDL for this operation.
This article explains the fundamentals and types of MySQL indexes, provides guidelines for selecting appropriate indexes based on query frequency, uniqueness and complexity, and presents PHP code examples for adding and using indexes, along with optimization strategies such as proper schema design, data types, and index maintenance.
This article explains how to assess MySQL table size, understand performance degradation caused by large B+‑tree indexes, and apply three practical solutions—table partitioning, database sharding, and cold‑data archiving—to improve query speed and maintainability.
This article investigates the nondeterministic behavior of MySQL when combining ORDER BY with LIMIT, reproduces the official example, identifies a critical row count threshold where the optimizer switches to a priority‑queue (heap) sort, and explains the underlying source‑code logic and its impact on query results.
The mysqlstat command‑line tool provides real‑time monitoring and analysis of MySQL servers, offering metrics such as QPS, TPS, network bandwidth, top‑executed SQL statements, hot tables, lock and deadlock information, index analysis, connection counts, table size statistics, binlog insights, and replication status, with usage examples and download instructions.
This article explains why the dbops tool does not support MySQL master‑master architecture, describes the two common dual‑master patterns, outlines their drawbacks such as role‑confusion, dirty‑data risk, split‑brain, and update loss, and provides practical workarounds and step‑by‑step deployment instructions to safely implement or avoid dual‑master setups.
This article presents practical methods—including indexing, query consolidation, field selection, PHP code refinements, and caching strategies with Redis and page caching—to significantly improve the performance of PHP‑MySQL applications, accompanied by concrete code examples.
This article explains how MySQL's optimizer calculates execution costs for different indexes, why it may select a full table scan instead of an apparently better index, and shows how to use EXPLAIN and optimizer trace to reveal and improve the chosen execution plan.
This article presents a comprehensive guide to planning and executing MySQL version upgrades—including industry version statistics, performance benchmarks, migration challenges, stability guarantees, detailed upgrade strategies, compatibility testing, and post‑upgrade validation—to ensure zero‑downtime and minimal operational impact.
This article explains the reasons for read/write splitting, compares direct‑client and proxy‑based master‑slave setups, and presents five practical strategies—including forcing reads to the master, sleep delays, lag checks, master‑position waiting, and GTID waiting—to mitigate stale reads caused by MySQL replication lag.
This article compares various MySQL data import/export methods—including mysqldump, mydumper, SELECT OUTFILE, and MySQL Shell utilities—explaining their commands, configuration options, performance results, and best‑practice recommendations based on testing with a 10‑million‑row table.
This article explores practical methods for performing fuzzy searches on encrypted phone numbers, covering in‑memory caching, database decryption functions, segment‑based storage, and adding dedicated fuzzy‑search fields to balance security, performance, and scalability.
This article explains how to identify whether MySQL replication delay originates from the I/O thread or SQL thread, demonstrates diagnostic commands, and provides practical tips, configuration tweaks, and monitoring tools such as pt‑stalk to reduce lag and improve replication performance.
This technical report details the background, hardware configuration, database setup, tuning results, and step‑by‑step optimization procedures—including BIOS, OS, network, container NUMA binding, MySQL CRC32 patching, and Go PGO tuning—performed to improve JED performance on ARM compared with Intel.
This article demonstrates two practical approaches for handling MySQL auto‑increment columns during migration to OB Oracle—creating custom sequences with DBCAT and using the GENERATED BY DEFAULT AS IDENTITY attribute—provides step‑by‑step commands, scripts, and validation results to help DBA engineers achieve seamless primary‑key migration.
This guide explains MySQL's lock mechanisms—including shared and exclusive locks, row‑level locking requirements, pessimistic versus optimistic strategies, and practical implementations with InnoDB, transaction handling, and JMeter concurrency testing—to prevent overselling in high‑traffic scenarios.
The article examines why using application‑layer association instead of MySQL JOIN can improve cache efficiency, reduce lock contention, simplify scaling, and enhance query performance, while also outlining scenarios where JOIN is still beneficial and offering practical alternatives for large‑scale data retrieval.
This article examines why MySQL can still handle massive tables and presents practical techniques—including proper primary keys, eliminating redundant indexes, choosing optimal data types, compression, archiving, partitioning, sharding, query optimization, and safe schema changes—to keep large‑table performance high.
This article explains how MySQL's offline_mode variable and the pt‑kill tool can be used by DBAs to place a server in maintenance mode, instantly dropping non‑privileged client connections, preventing new ones, and providing a safe way to perform maintenance or backups.
When a single MySQL table grows to millions of rows, offset‑based pagination becomes painfully slow, so this article explains why, measures the performance impact, and presents three practical sharding‑aware pagination strategies—including global query, jump‑page prohibition, and a two‑step query method—along with concrete SQL rewrites and optimization tips.
Starting with MySQL 8.0.30, the Generated Invisible Primary Keys (GIPKs) feature automatically adds an invisible bigint auto‑increment column named my_row_id when a table lacks an explicit primary key, simplifying schema design, easing DBA‑developer conflicts, and offering flexible handling in MGR and replication environments, while noting its limitations and usage commands.
This article investigates why inserting the foreign_key_checks system variable into MySQL's my.cnf prevents the server from starting, explains the underlying NO_CMD_LINE flag mechanism, and provides a simple command‑line method to distinguish variables that can or cannot be set via configuration files.
This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
This guide walks through installing Apache Ignite via Docker, configuring it alongside MySQL in a Spring Boot project, setting up data sources and MyBatis, and performing benchmark tests to illustrate Ignite’s superior in‑memory SQL performance compared to traditional MySQL setups.
This article explains how to enable fuzzy searches on encrypted sensitive fields such as names, phone numbers, and ID numbers in a Spring Boot backend by using a tokenized ciphertext mapping table, outlining four possible solutions, recommending the tokenized approach, and providing complete configuration and code examples.
This article details the architecture, technical requirements, and implementation techniques—including hybrid push‑pull, heterogeneous storage, distributed scheduling, task chunking, and at‑least‑once delivery—used to build a high‑concurrency, reliable reminder notification service for QQ.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
This article explains how to use MySQL's EXPLAIN statement to analyze slow queries, detailing each output column, various select types, access methods, and common pitfalls, then provides practical optimization techniques such as proper indexing, query rewriting, index merging, handling COUNT, UNION, OFFSET, and table maintenance.
This guide presents a comprehensive set of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting specific columns, avoiding costly ORDER BY RAND(), choosing UNION ALL over UNION, distinguishing IN from EXISTS, employing full‑text indexes, respecting left‑most prefix rules, and leveraging FORCE INDEX or STRAIGHT_JOIN—to help developers write faster, more efficient queries.
Learn how MySQL's index merge optimization works, explore its three algorithms—intersect, union, and sort_union—understand when each applies, see practical setup and sample queries, and discover configuration flags and best‑practice tips to improve query efficiency.
This guide reviews popular free MySQL graphical clients—including MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, and DBeaver—detailing their key features, platform support, and download links, offering a cost‑free alternative to paid tools like Navicat.
Learn how to capture MySQL data changes using Debezium's CDC capabilities within a Spring Boot application, avoiding heavyweight message brokers by leveraging binlog monitoring, configuring connectors, handling snapshots, and processing change events for use cases like cache invalidation, data integration, and simplifying monolithic architectures.
This article examines how executing an UPDATE before a slow SELECT within a @Transactional method in Spring can prolong MySQL write locks, and demonstrates code refactoring techniques—reordering operations and separating transactional boundaries—to improve overall system performance.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
This article explains how to create optimal MySQL indexes for a service_log table based on various search conditions, covering single‑column, composite, full‑text, prefix, and covering indexes, along with best practices, index size considerations, and practical SQL examples.
This article analyzes a large MySQL table that caused SLA alerts due to master‑slave lag, examines slow‑query logs with pt‑query‑digest, identifies index and delete inefficiencies, proposes index redesign and batch‑delete strategies, and validates the improvements with testing and DDL tools.
This article examines how InnoDB processes UPDATE statements on a table with primary and secondary indexes, analyzes three scenarios to determine which index blocks are modified, explains the internal filtering steps, and validates the behavior by inspecting LSN changes of the affected blocks.
This article explains how MySQL permission modifications behave at table, database, and global levels, demonstrates verification steps with Uproxy's connection pool, and clarifies why query cache settings and USE statements affect the visibility of privilege changes.
This guide outlines a comprehensive MySQL backup solution, covering backup strategy planning, choosing physical versus logical backups, using tools like mysqldump and Percona XtraBackup, automating backups with scripts and cron, storage and retention considerations, and regular recovery testing to ensure data integrity.
This article explains how to create a basic PHP-based question‑and‑answer web application, covering functional requirements, environment setup, MySQL schema design, and providing complete code snippets for user registration, login, posting, answering, listing, and searching questions.
This article explains how to securely expose a MySQL server behind an Nginx stream proxy, covering required modules, configuration directives, IP‑based access control, and complete example setups for both single‑instance and clustered environments.
This guide walks through installing MySQL in Docker, configuring master‑slave replication, verifying synchronization, deploying a Canal server, and integrating it with a Spring Boot application to automatically push database changes into Redis.
This article explains the principle behind MySQL's index‑merge optimization, demonstrates how to set up test data, walks through the three merge algorithms (intersect, union, sort‑union), provides practical SQL examples, and shares tips for tuning and avoiding common pitfalls.
This article explains how to use the mysqldump command to back up MySQL databases, demonstrates various backup and restore options, provides a Bash script that maintains a limited number of daily backups, and shows how to schedule the script with crontab for automated execution.
This article explains how MySQL 8 improves the auto‑increment counter by persisting its maximum value across server restarts, contrasting the behavior with MySQL 5.7 through detailed examples and SQL commands that demonstrate the differences in primary‑key generation.
This article demonstrates how to implement a data merging feature by creating MySQL tables, building a PHP API that retrieves and combines records from two tables, and developing a Vue.js front‑end that fetches and displays the merged data, complete with full code examples.
Learn step‑by‑step how to configure Alibaba’s open‑source Canal to capture MySQL binlog changes, route them through RocketMQ, and index the data into Elasticsearch, covering cluster mode, MySQL and Elasticsearch setup, Canal properties, and consumer implementation details.
This article examines why PostgreSQL appears to outrank MySQL in recent popularity surveys, explains the DB‑Engines ranking methodology, compares architectural choices such as MVCC, multi‑process vs multi‑thread models, transaction rollback behavior, licensing, and real‑world performance benchmarks like TPC‑C, ultimately revealing that MySQL still dominates many high‑throughput OLTP scenarios.
The SQLE 2.2309.0-pre4 release introduces an enterprise‑level SQL lifecycle management panel, a rule knowledge base, new TDSQL audit rules, and intelligent metadata scanning tasks, while providing links to the repository, documentation, and demo environments for both community and enterprise editions.
Learn how to protect MySQL servers by using Nginx as a stream proxy, configure the required modules, set up listening ports, and restrict access to specific IP addresses, enabling safe remote connections for developers.
The article examines a MySQL InnoDB deadlock that occurs when concurrent inventory‑write transactions in a pre‑release environment contend for row and gap locks, explains InnoDB lock types, isolation‑level effects, and proposes solutions such as distributed locks and isolation level adjustments.
This tutorial walks through configuring Canal in cluster mode to capture MySQL row‑based binlog events, publish them to a RocketMQ topic, and consume the ordered messages to update an Elasticsearch index, covering MySQL, RocketMQ, Elasticsearch mappings, and the required Canal properties.
The article explains why master‑master MySQL architectures are discouraged, outlines the pitfalls of dual‑write setups, and provides step‑by‑step instructions using dbops to deploy a master‑slave topology and manually create a reversible master‑master configuration for testing.
This article compares UUID and auto‑increment primary keys in MySQL, explaining how each is generated, their advantages and drawbacks, performance implications, and practical guidance on selecting the right key type for different business scenarios.
This tutorial walks through using Spring Batch with Spring Boot 2.6.12 to create a batch job that reads CSV data, processes each record, and writes the results into a MySQL database, covering architecture, configuration, code components, and execution steps.
This article examines a MySQL deadlock case involving DELETE and UPDATE statements, explains why the primary key index is locked despite non‑key predicates, analyzes execution plans and optimizer trace, and proposes index restructuring and query hints to resolve the issue.
This article summarizes a performance comparison between Spring Boot applications using virtual threads and those built with Spring WebFlux, detailing the test scenario of JWT verification and MySQL queries, the environment, code implementations, benchmark results across various concurrency levels, and concluding that WebFlux outperforms virtual‑threaded Spring Boot.
The article examines the challenges of running MySQL in Docker containers, highlighting data‑security, performance, state, and resource‑isolation concerns, while also presenting scenarios and strategies where containerizing MySQL can be viable.
This article explains why a single MySQL table should stay below about twenty million rows by examining primary‑key limits, InnoDB data‑page structure, B+‑tree storage calculations, and provides a practical recommendation based on row size and fragmentation.
The article explains how large‑offset (deep) pagination in MySQL and distributed databases degrades performance and increases memory and bandwidth usage, and describes VtDriver's streaming and SQL‑rewrite techniques along with practical recommendations such as range queries and sub‑queries to mitigate these issues.
The article evaluates database success by comparing PostgreSQL and MySQL across popularity, features, licensing, transaction behavior, MVCC, concurrency models, and use cases, showing PostgreSQL leads in complex queries and feature richness while MySQL remains the most widely used OLTP system, especially in China.
This article explains how MySQL calculates execution costs for different indexes using IO and CPU metrics, demonstrates with a sample table and queries, compares the optimizer's choices with actual performance, and shows how to use EXPLAIN and optimizer trace to fine‑tune query plans.
The SQLE 2.2309.0-pre3 release introduces a fast SQL audit feature, adds numerous MySQL audit rules, provides detailed community and enterprise version updates, and includes links to the repository, documentation, and previous versions for comprehensive usage and evaluation.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors with genuine four‑byte UTF‑8 symbols, and advises all MySQL/MariaDB users to migrate to the proper “utf8mb4” charset using available conversion guides.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—covers lock compatibility, deadlock causes and prevention, compares optimistic and pessimistic locking, and shows how SELECT FOR UPDATE behaves under different isolation levels.
The article explains that, although MySQL’s theoretical limits are far higher, practical B+‑tree page and index calculations suggest keeping a single table under about twenty million one‑kilobyte rows to avoid performance degradation, illustrated with interview examples and estimation formulas.
This article explains how to configure Nginx as a stream proxy to securely connect to MySQL servers, covering required modules, stream, server, listen directives, IP access restrictions, and provides complete configuration examples for both single‑instance and clustered environments.
This article walks through the creation and step‑by‑step optimization of a MySQL service_log table, covering proper data‑type selection, index usage, NULL handling, DATETIME vs TIMESTAMP choices, and floating‑point considerations to achieve a lean, high‑performance schema.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This tutorial demonstrates how to use Alibaba's Canal in cluster mode with Zookeeper to capture MySQL row‑based binlog events, publish them via RocketMQ, and consume them with a custom listener that transforms inserts, updates, and deletes into synchronized documents in an Elasticsearch index.
The article analyzes the performance problems of deep pagination on massive MySQL tables storing billions of fan records and presents three progressive solutions—simple LIMIT, tag‑record (maxId) pagination, and range‑limited pagination with async and offline minId caching—along with general indexing best‑practices for high‑throughput queries.
This article demonstrates how to insert 300,000 rows into a MySQL table efficiently by defining entity, mapper and configuration files, comparing direct batch insertion, row‑by‑row insertion, and optimized batch strategies with MyBatis and JDBC, and summarizing performance results and best‑practice tips.
MySQL 5.7 reaches its official End‑of‑Life in October 2023, exposing users to security, compliance, performance, and support risks, and the article outlines four migration paths—continuing with 5.7, upgrading to MySQL 8.0, switching to domestic open‑source forks like GreatSQL, or adopting commercial Chinese databases—while providing a detailed case study of GreatSQL’s suitability for Chinese enterprises.
This article analyzes MySQL 8.0 password‑related parameters, explains the meaning of NULL values in the mysql.user table, demonstrates how global settings interact with per‑user attributes, and clarifies the priority rules between password_history and password_reuse_interval through practical test scenarios.
This article presents a systematic performance test of inserting massive data into MySQL, comparing three strategies—MyBatis lightweight insertion, direct JDBC (with and without transactions), and JDBC batch processing—showing how transaction handling and batch execution dramatically affect insertion speed.
This article explains how to configure Nginx's stream module to proxy MySQL connections, including building Nginx with the required module, setting up stream, server, and listen directives, and using allow/deny rules to restrict access by IP, followed by a complete configuration example.
The Aikexing Open Source Community weekly newsletter shares curated database articles, technical insights on MySQL, OceanBase, and Redis, announces SQLE 2.2309.0‑pre2 release with Feishu integration, recaps recent events like QCon Beijing, and promotes upcoming meetups and open‑source projects.
This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.
This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), detailing how transaction isolation levels such as read‑committed and repeatable‑read create snapshots, how undo logs and hidden columns form version chains, and walks through concrete examples to show which row values are visible to concurrent transactions.
This article demonstrates how to use MySQL to create employee and performance tables, populate them with sample data, and write SQL statements that first select employees whose salaries exceed their department's average and then filter out those with non‑A performance ratings, providing a complete layoff‑target list.
This step‑by‑step tutorial shows how to configure Alibaba's Canal to capture MySQL binlog changes, route them through RocketMQ, and index the data into Elasticsearch, covering cluster mode, MySQL and Elasticsearch setup, RocketMQ topic creation, Canal properties, and consumer implementation.
This article explains nine practical MySQL optimization methods—including smarter LIMIT usage, implicit conversion pitfalls, JOIN‑based updates, mixed ordering tricks, EXISTS rewrites, predicate pushdown, early range reduction, intermediate result pushdown, and a concise summary—showing how to transform slow queries into sub‑millisecond operations.
With MySQL 5.7 reaching end‑of‑life in October 2023 and over half of installations still on that version, this guide explains the new features of MySQL 8.0, how to assess upgrade readiness, and the three main migration paths—including staying put, moving to a DBaaS, or switching to PostgreSQL.
This article explains how ordering issues in message queues like RabbitMQ and Kafka can break MySQL binlog synchronization, illustrates common pitfalls, and offers practical solutions to guarantee correct processing order in high‑throughput backend systems.
This article provides a collection of ready-to-use Bash scripts for common MySQL administration tasks on Linux, including database backup, table optimization, cleanup of old backups, process monitoring, status checks, size reporting, schema export/import, and lock/unlock operations.
This article analyzes the MySQL ERROR 1499 caused by exceeding partition limits in OceanBase, examines server parameters, recycle‑bin settings, and tenant memory usage, and provides calculations and recommendations to expand memory or limit partition counts to prevent the error.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
The article explains how MySQL 8 enhances replication observability by exposing detailed metrics through Performance Schema tables and sys schema views, providing DBAs with richer information such as per‑channel lag, worker thread states, and full replication status beyond the traditional SHOW REPLICA STATUS output.
This comprehensive guide consolidates 43 essential MySQL interview topics—from normalization, storage engines, and indexing to transaction handling, logging, replication, and performance tuning—providing clear explanations, code examples, and diagrams to help candidates quickly master the core concepts.
