This article explains how to install, configure, and test MySQL 8.x query‑rewriter and DDL‑rewriter plugins, demonstrating environment setup, sample SQL scripts, rule creation, and the impact on query performance and CREATE TABLE statements.
This article analyzes MySQL data‑update loss caused by writes on master‑slave pairs, explains BEFORE and AFTER IMAGE handling in row‑based binlogs, examines the binlog_row_image and slave_rows_search_algorithms parameters, and details the implementation of hash, index, and table scans during slave replay.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
This article explains database concurrency control mechanisms, detailing transaction fundamentals, isolation levels, associated anomalies, and how DB2, MySQL, and Oracle implement locks and isolation to ensure data consistency while balancing performance.
This article explains the principles of MySQL performance optimization, distinguishes active and passive approaches, and provides concrete solutions for slow single queries, partially slow queries, and overall slow queries through proper indexing, data partitioning, slow‑query‑log configuration, and read‑write splitting.
This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.
This article explains MySQL's implicit and explicit locking mechanisms—including table, row, and intention locks—covers lock modes, MVCC, transaction isolation levels, optimistic and pessimistic locking, gap locks, and deadlock causes and solutions, providing practical SQL examples for each concept.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This comprehensive guide covers MySQL index creation syntax, various index types—including primary, unique, full‑text, and composite—explains their advantages and drawbacks, details underlying B‑Tree and B+Tree structures, and provides practical strategies and optimization tips for effective index usage.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains how to locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
This article explains the three types of MySQL InnoDB tablespaces—system, single, and general—detailing their structures, configuration options, advantages, disadvantages, and practical procedures for creation, migration, optimization, and removal, with concrete command‑line examples.
Learn step‑by‑step how to set up a binlog‑based MySQL master‑slave replication using Docker‑Compose and Dockerfiles, covering directory structure, configuration files, container creation, verification, and troubleshooting, so you can quickly deploy a reliable MySQL replication environment.
This article details a comprehensive investigation of MySQL out‑of‑memory (OOM) alerts on a Tencent Cloud CDB instance during a high‑traffic marketing event, covering background, observed symptoms, step‑by‑step debugging, analysis of server and engine memory usage, relevant MySQL bugs, fetch_cache behavior, and the final configuration recommendations to prevent OOM.
This article details a step-by-step optimization of a Java-based Excel import process, covering migration from POI to EasyExcel, caching database lookups, using batch inserts with VALUES, and leveraging parallel streams to dramatically reduce import time for massive datasets.
This article explains how Transparent Data Encryption (TDE) protects sensitive data in databases by encrypting pages with keys stored in a secure vault, outlines its advantages and drawbacks, and compares page-level encryption with column-level approaches.
This article explains the MySQL binary log (binlog), its structure and contents, the three main uses—point‑in‑time recovery, master‑slave replication, and audit logging—followed by a detailed walkthrough of the replication process, an overview of Alibaba's Canal mechanism, and extensive code examples illustrating binlog parsing and event handling.
This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.
The article evaluates MySQL Shell 8.0.21's new logical backup and restore method—featuring ZSTD compression, chunked parallel export, and parallel LOAD DATA import—against mysqldump, mysqlpump, and mydumper, presenting benchmark results, analysis, and practical command examples.
This document outlines comprehensive MySQL database design standards and SQL writing guidelines, covering naming conventions, character sets, table and column specifications, index design, partitioning, data types, transaction handling, and prohibited statements to ensure stable, performant, and maintainable production databases.
This article breaks down MySQL’s architecture into its five key parts—connector, parser, optimizer, executor, and storage engine—explaining each component’s role from establishing connections and handling permissions to query parsing, execution planning, and data storage, with example commands and code snippets.
The article explains that MySQL’s legacy "utf8" charset only supports three‑byte sequences, causing errors with true four‑byte Unicode characters, and shows how the later "utf8mb4" charset provides proper UTF‑8 support along with historical context and migration guidance.
This article walks through a practical MySQL troubleshooting experiment that shows how to identify a CPU‑intensive query using the processlist, locate the offending thread with top‑H, interpret performance_schema IDs, and safely terminate the problematic SQL statement.
This article provides a comprehensive guide to gh-ost, the GitHub‑released online MySQL schema migration tool, covering its design principles, three migration modes, download and RPM installation, common command‑line options, interactive socket commands, and a step‑by‑step example of altering a column type while monitoring progress and performing a safe cut‑over.
Understanding CPU usage in MySQL involves recognizing that user processes, IO waits, and system interrupts dominate consumption, and by reducing IO, optimizing queries, indexing, and minimizing costly calculations, you can lower CPU load, improve throughput, and avoid performance bottlenecks.
This article provides a comprehensive guide to using Sqoop for importing data from relational databases into HDFS, Hive, and HBase, as well as exporting data back to databases, covering command syntax, options, and practical examples for big‑data workflows.
This article explains MySQL index structures—including B‑Tree, B+Tree, and hash indexes—their underlying principles, performance characteristics, and practical usage guidelines such as left‑most prefix, full‑text indexing, and common pitfalls, providing a comprehensive guide for database developers.
This article explains MySQL Binlog’s role in event‑driven order processing, analyzes hidden pitfalls such as premature Binlog reads and two‑phase commit issues, and offers practical solutions like retry strategies and direct Binlog consumption to ensure data consistency.
This guide explains how to restore MySQL databases when the server cannot start by generating table definitions with mysqlfrm, discarding damaged InnoDB tablespaces, copying old .ibd files, and importing them back, followed by verification using mysqlcheck.
This article explores the requirements and database design patterns for implementing likes, comments, and favorites in apps, comparing traditional MySQL schemas with Redis‑based storage and caching solutions, and discusses their trade‑offs, scalability, and data consistency considerations.
This article explains how to use MySQL's EXPLAIN statement to view execution plans, describes the meaning of each of the twelve output fields, provides practical examples with sample tables and queries, and offers optimization tips such as index creation and the left‑most prefix rule.
The article evaluates MySQL 8.0.21's new ability to disable the InnoDB redo log, comparing load‑data performance with redo log enabled and disabled, and discusses practical implications, limitations, and safety considerations for bulk data import scenarios.
This article presents practical MySQL performance tricks, covering bulk data insertion with DISABLE/ENABLE KEYS, loading data in primary‑key order, multi‑value INSERTs, INSERT DELAYED, separating index and data files, GROUP BY sorting elimination, index‑driven ORDER BY, OR‑condition indexing, replacing sub‑queries with JOINs, and using SQL hints such as USE, IGNORE, and FORCE INDEX.
The article provides a comprehensive analysis of major relational databases—Oracle, DB2, SAP HANA, MySQL, PostgreSQL—and emerging NoSQL solutions, examining their underlying technologies, recent developments, market adoption, and the overall industry landscape shaping the future of data storage.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
This article introduces several MySQL binlog-based change data capture solutions—including Canal, Maxwell, Databus, and Alibaba Cloud's Data Transmission Service—explaining their principles, architecture, features, and usage considerations for incremental data subscription and processing.
This article presents a thorough overview of MySQL optimization, covering the query execution process, philosophical principles, risk assessment, required participants, optimization dimensions, commonly used diagnostic tools, emergency and regular tuning procedures, hardware and system level adjustments, as well as detailed configuration parameters for both the server and the database engine.
This article explains the differences between normal and unique indexes in MySQL, examines how they affect query and update performance, details InnoDB's change‑buffer mechanism, and provides practical guidance on when to prefer ordinary indexes for better write efficiency.
This article explains InnoDB's 16KB page structure, row formats, record metadata, primary key strategies, and how B+Tree indexes—including clustered, secondary, and covering indexes—organize and retrieve data, with practical SQL examples and performance considerations.
This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
This article explains how to achieve high‑performance bulk inserts in .NET Core applications by converting entities to CSV or DataTable formats and using MySQL's LOAD DATA and SQL Server's SqlBulkCopy, providing reusable extension methods, interface abstractions, and performance benchmarks.
This article explains how a friend loaded over 2 billion rows into MySQL using XeLabs TokuDB, detailing configuration, bulk‑load commands, benchmark results of roughly 574 k rows per second, and comparisons with InnoDB performance on a cloud server.
This article details a real‑world test that loads 200 million rows into a MySQL TokuDB table in under six minutes, achieving roughly 570 k writes per second, and compares the results with InnoDB while providing configuration tips and bulk‑loader considerations.
This guide walks through downloading, installing, and configuring MySQL Enterprise Monitor, explains the agent and agentless client architectures, and demonstrates how to use its overview, event, metric, and query‑analysis features for comprehensive MySQL performance monitoring.
This article explains MySQL's two‑layer architecture, the step‑by‑step execution flow of an SQL statement—including connection, query cache, parser, optimizer, executor, and storage engine—and the roles of redo log and binlog in ensuring data consistency.
This article provides an in‑depth overview of MySQL, covering storage engine concepts, selecting appropriate data types, index design and usage, view creation, stored procedure syntax, variable handling, and trigger implementation, with practical examples and SQL code snippets to illustrate each topic.
This article explains how to load over 20 billion rows into MySQL using the XeLabs‑compiled TokuDB engine, detailing configuration tweaks, table schema, bulk‑loader commands, benchmark results that reach 574 k rows per second, and practical tips for handling auto‑increment keys.
This article walks through common Linux memory inspection commands, explains the meaning of the buff/cache field, and then dives into MySQL join types, join algorithms, buffer settings, and performance‑tuning tips, all presented as a simulated interview with promotional side notes.
This article explains why MySQL data can become garbled due to mismatched character sets during insertion or retrieval, explores various scenarios such as client‑side encoding errors, mixed encodings within tables, and LATIN1 storage, and provides detailed solutions including setting proper client encodings, using strict SQL_MODE, and exporting/importing data to unify character sets.
This comprehensive guide walks you through setting up a pseudo‑distributed Hadoop environment, loading massive MySQL data with LOAD DATA, Python scripts, and multithreading, and then synchronizing the data to HBase using three approaches—Sqoop, a Kafka‑Thrift pipeline, and a real‑time Kafka‑Flink pipeline—while also comparing query performance of HBase and Phoenix.
This article explains the three most important MySQL log types—redo log, undo log, and binary log—detailing their purposes, contents, creation and release timing, related configuration parameters, physical files, and how they interact with transaction processing and replication.
This article explains how MySQL implements row‑level locking, the two‑phase locking protocol, common deadlock scenarios with online ticket sales, and practical strategies—including lock ordering, deadlock detection settings and sharding tricks—to improve concurrency and reduce CPU overhead.
The article explains that MySQL’s legacy “utf8” character set only supports three‑byte sequences and therefore cannot store true UTF‑8 characters, describes the historical reasons behind this limitation, and advises all MySQL/MariaDB users to migrate to the proper utf8mb4 charset.
This article demonstrates how to audit MySQL user actions by configuring init_connect, creating an audit log table, enabling binlog, and analyzing binlog entries to identify the user and IP responsible for accidental table deletions.
This article examines why full‑table scans on massive MySQL tables are slow, explains standard LIMIT‑based pagination, and presents several optimization strategies—including sub‑queries, ID‑range filtering, and temporary tables—backed by concrete performance measurements.
This tutorial walks through the test environment and demonstrates how InnoDB Cluster's built‑in functions—such as get_cluster, describe, list_routers, set_option, options, set_instance_option, set_primary_instance, switch_to_multi_primary_mode, switch_to_single_primary_mode, status, and dissolve—simplify the deployment, monitoring, and administration of MySQL group replication, complete with code snippets and visual results.
This article explains MySQL master‑slave replication, covering its concepts, asynchronous replication mechanisms, various synchronization strategies, step‑by‑step installation and configuration on CentOS 7, and includes common interview questions with answers; it also provides practical commands, troubleshooting tips, and performance considerations for read‑write separation in production environments.
This article explains common MySQL performance problems such as missing indexes and lack of pagination, and provides practical guidance on data size limits, concurrency settings, query time targets, table design, index types, and a variety of SQL optimizations with concrete code examples.
This guide details recommended MySQL versions for Zabbix, proper grant statements for read/write and read‑only users, essential MySQL configuration tweaks, SQL techniques for item and function updates, read‑write splitting, upgrade procedures, backup strategies, and comprehensive partition maintenance scripts to improve performance and manage data growth.
This article explains how MySQL implements transaction ACID properties by using redo logs for durability, undo logs for atomicity, lock mechanisms and MVCC for isolation, and discusses isolation levels, consistency, and practical examples with SQL code and diagrams.
This article explains various MySQL pagination methods—including simple LIMIT queries, sub‑query optimizations, ID‑range filtering, and temporary‑table approaches—provides detailed performance measurements on a large order_history table, and concludes with practical recommendations while also promoting a comprehensive interview‑question PDF collection.
The article describes a bug where a user's VIP expiration date cannot exceed a specific day in 2038 because the MySQL INT column storing the timestamp reaches its maximum signed value, explaining the cause, impact, and the pragmatic decision to ignore the issue.
After upgrading a MySQL 5.6 master to 5.7, inserts on several InnoDB tables began failing with duplicate‑key errors because the auto_increment value became inconsistent, and the article walks through kernel‑level investigation, reproduction steps, bug analysis, and practical fixes.
This article investigates whether MySQL re‑executes UPDATE statements that do not change any data, comparing the behavior under binlog_format = ROW and binlog_format = STATEMENT with a full row image setting, and presents test results from two sessions.
This article provides a thorough, step‑by‑step guide to MySQL, covering how to install and start the server, create databases and tables, use DDL/DML/DQL statements, understand numeric, date/time and string data types, apply arithmetic, comparison, logical and bitwise operators, and leverage common built‑in functions for efficient data manipulation.
This article demonstrates how to use MySQL's DBUG debugging tool to observe the creation of a table, track changes in internal statistics tables such as innodb_index_stats, and reveal the underlying operations and stored procedures that update these tables.
This article explains how to modify systemd unit files or use systemctl commands to set resource isolation parameters such as CPUQuota and MemoryLimit, demonstrates the changes with a MySQL CPU‑usage experiment, and describes the underlying cgroup and systemd mechanisms on Linux 7.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
This article explains MySQL transaction isolation concepts, the four standard isolation levels, their effects on dirty, non‑repeatable, and phantom reads, practical configuration steps, when to use repeatable read, how InnoDB implements isolation, and tips for avoiding long transactions and planning backups.
This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
This article explains how to implement a host cache within the JVM and a MySQL‑based distributed lock in a Java backend service, including detailed code examples for the service layer, a static cache class, lock acquisition, and release logic.
This guide explains how to upgrade MySQL 5.6 instances to 5.7 on Windows by using the ZIP distribution and an in‑place upgrade, covering preparation, installation, required Visual C++ libraries, common pitfalls, troubleshooting steps, and a complete checklist of post‑upgrade actions.
An in‑depth investigation shows that counting rows with COUNT(*) on a MySQL table using the PRIMARY key index can be slower than a full table scan, revealing differences in execution plans, optimizer behavior, logical reads, and profiling data that explain the 0.02‑second performance gap.
This guide walks through installing MySQL nodes, Java, and MyCat 1.6.5, configuring schemas, data nodes, sharding rules, adjusting ports, adding users, and verifying the MyCat service, providing complete code snippets and troubleshooting tips for a functional distributed database middleware setup.
MySQL 8.0.21 introduces numerous enhancements such as dynamic redo log control, optional tablespace path validation, refined lock system, JSON_VALUE function, atomic CREATE TABLE…AS SELECT, optimizer improvements, reduced group replication cache, multi‑address X Plugin support, and various security and deprecation updates.
This guide walks through preparing the environment, configuring the master and slave MySQL servers, and using Python's Paramiko library to automate SSH commands, edit configuration files, restart services, and verify replication, all while handling common pitfalls.
This article explains why MySQL connections can fail even when the concurrent client count is below max_connections, by analyzing the max_connect_errors parameter, its interaction with the host cache, and how to resolve the issue using flush hosts and proper configuration.
The article explains why adding @Transactional in Spring may fail to roll back when MySQL uses the MyISAM engine, shows the required Hibernate dialect configuration for InnoDB, and offers tips for diagnosing similar transaction issues.
This tutorial introduces Deno.js, explains its security‑first runtime features, compares it with Node.js, and walks through building a complete MySQL‑backed CRUD REST API using TypeScript, covering installation, code structure, and execution steps.
This article tests whether MySQL re‑executes an UPDATE statement that does not change any column values, comparing the behavior under binlog_format=ROW and binlog_format=STATEMENT on MySQL 5.7.25 running on CentOS 7.4.
After upgrading a critical MySQL client from 5.6 to 5.7, duplicate‑key errors appeared during inserts on master, replica and read‑only instances, prompting a deep dive into InnoDB auto‑increment mechanics, related kernel bugs, on‑site analysis, and both application‑side and engine‑side remediation strategies.
This article explains how to identify and measure large MySQL transactions by extracting GTID events from binlog files using Linux command-line tools such as grep, awk, and sort, and demonstrates the process with example commands and visual results.
This article explains the database design for likes, comments, and favorites in apps, comparing MySQL table structures and Redis storage solutions, discussing query patterns, scalability challenges, and data consistency measures for moderate to massive traffic scenarios.
This article walks through diagnosing severe SLA alerts caused by slow queries on a massive MySQL table, analyzes query patterns with pt‑query‑digest, redesigns indexes, applies online DDL and pt‑osc for index changes, and demonstrates batch‑delete techniques that dramatically reduce execution time and replication lag.
This tutorial explains two approaches to achieve MySQL read/write splitting—first by manually implementing routing logic in Spring Boot with MyBatis interceptors and dynamic data sources, and second by configuring the MyCat middleware for automatic load balancing, fault‑tolerance, and multi‑node support, complete with code samples and configuration files.
The article shares practical experiences on running test cases concurrently using Java multithreading and a custom thread pool, and outlines MySQL database naming, null handling, and schema design guidelines, including full code examples for the case runner and thread pool implementation.
After upgrading a critical MySQL 5.6 database to 5.7, inserts on the master began failing with duplicate‑key errors due to unexpected auto‑increment values, prompting a deep dive into InnoDB internals, related bugs, and practical mitigation steps.
This article explains how MySQL 8.0 introduces atomic DDL by moving the data dictionary into InnoDB system tables, describes the pre‑8.0 non‑atomic behavior, outlines the DDL execution phases, shows practical comparison results, and details how to view and configure DDL logs.
This article explains MySQL's storage engines, their features and selection criteria, guides on choosing appropriate data types such as CHAR, VARCHAR, TEXT, BLOB, numeric and date types, and discusses character sets and related SQL commands.
This article walks through the evolution of MySQL index implementations—from hash tables and binary search trees to AVL, red‑black, B‑trees and finally B+ trees—explaining their performance trade‑offs, disk‑I/O considerations, and how InnoDB and MyISAM store data and indexes differently.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This article explains how to specify and manage character sets and collations when creating or altering MySQL databases, tables, views, triggers, and columns, illustrating the impact on stored procedures and data retrieval with practical SQL examples.
This article explains Linux CPU states, identifies typical MySQL queries and configurations that cause high CPU usage, and provides practical optimization techniques—including indexing, buffer tuning, and proper use of system tools—to diagnose and reduce MySQL-related CPU bottlenecks.
This article introduces the Screw plugin, a lightweight tool that automates database table structure documentation across multiple databases, supports various output formats, and can be configured via custom Java code or a Maven plugin to streamline enterprise development workflows.
The article explains why a SELECT COUNT(*) on a 5‑million‑row InnoDB table was extremely slow, analyzes the execution plan, demonstrates that the query used the primary (clustered) index, and shows how creating a small secondary index reduces the runtime from dozens of seconds to under a second.
This article analyzes the various system‑level and database‑level reasons why SELECT, UPDATE, INSERT, or DELETE statements become slow, covering network issues, OS and hardware constraints, MySQL configuration, indexing, execution plans, and query design, and offers practical mitigation strategies.
This article walks through preparing a four‑node MySQL environment, configuring password‑less SSH, setting up master‑slave replication with GTID, installing and configuring MHA, testing connectivity, running the manager, verifying automatic failover, and highlighting the key advantages of using MHA for high availability.
This article explains the MySQL Memcached API, its evolution from MySQL 5.6 to 8.0, demonstrates how to install the plugin, configure tables, and perform batch and range key retrievals with code examples, highlighting suitable use cases and performance benefits.
This article examines why MySQL auto‑increment primary keys can jump unexpectedly when using REPLACE INTO or INSERT … ON DUPLICATE KEY UPDATE, explains the role of innodb_autoinc_lock_mode, demonstrates examples, and offers practical solutions to prevent uncontrolled ID growth.
