This article explains how to use mysqldump for various MySQL backup scenarios, provides command‑line examples for full, structure‑only, and selective backups, shows how to restore dumps, and demonstrates a Bash script combined with crontab to automate daily backups with retention management.
This article explains MySQL 8.0's new current‑password validation strategies, showing how to enforce old‑password checks per user or globally, with detailed command‑line examples and options to enable, disable, or customize the behavior for enhanced security.
The article explains MySQL 5.7's engine‑dependent prefix index length limits, demonstrates how utf8mb4 reduces the maximum indexable column size for MyISAM tables, provides test cases with SQL code, and offers practical solutions such as prefix indexes, composite indexes, and CTAS for large datasets.
This article provides a comprehensive overview of MySQL, detailing its configuration file search order, component architecture, various storage engines such as MyISAM, NDB, Memory, and an in‑depth examination of InnoDB’s internal structures, memory management, background threads, LRU handling, redo log buffering, and checkpoint mechanisms.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This article details a MySQL InnoDB startup failure caused by ibdata1 lock errors, explains the underlying resource‑unavailable issue, and walks through removing stale socket lock files and restarting the server to achieve a successful launch.
The article investigates a DBCP 1.4 connection‑pool bug that allows the pool to exceed its configured limits, explains how removeAbandoned and socketTimeout interact to produce excess MySQL connections, reproduces the issue with a multithreaded test, and proposes configuration changes to avoid it.
This article explains the storage units of InnoDB, calculates how many rows a B+ tree can hold at different heights, shows how to determine the tree height from the page level, and answers why MySQL uses B+ trees for indexing.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
This article compares popular Kubernetes Operator scaffolding tools, explains why Kubebuilder 3.0 was selected for the radondb‑mysql‑kubernetes project, and provides step‑by‑step commands to initialize the operator and generate its API and controller code.
This article introduces the RadonDB MySQL Operator, explaining its design goals, deployment topology on Kubernetes, detailed architecture—including RBAC, manager, custom resources, and services—and provides a visual overview to help engineers simplify MySQL high‑availability deployments in cloud‑native environments.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
A colleague reported that a MyBatis insert stored a timestamp from the previous day, leading to a deep investigation that revealed mismatched timezone handling between MySQL, the JDBC driver, and Java, and presented two practical fixes to align all components.
MySQL’s default settings target a 512 MB virtual machine and work for development or testing, but production deployments require a series of performance optimizations, including understanding why database tuning matters, how to optimize MySQL instances, and which parameters to adjust.
This article explains how font‑based anti‑scraping works, shows how to locate and download custom font files, decode their glyph mappings into a dictionary, and use the mappings to extract real data from a recruitment site with Python, Scrapy and MySQL.
This article explains how MySQL InnoDB implements locking, the two‑phase lock protocol, the four transaction isolation levels, the differences between pessimistic and optimistic locks, MVCC implementation, and how next‑key and gap locks prevent phantom reads in high‑concurrency environments.
This article explains how MySQL InnoDB partition tables can dramatically boost query speed, simplify routine operations, enable parallel writes, and provide efficient data exchange and cleanup compared to non‑partitioned tables, illustrated with concrete SQL examples and performance metrics.
This article explains MySQL's architecture, the server and storage‑engine layers, query execution phases, status states, query cache behavior, the optimizer's parsing and planning steps, EXPLAIN output fields, the cost model, and why the optimizer selects index_author_id instead of index_title for a given query.
This article explains the concepts of driving (outer) and inner tables in MySQL join algorithms, covering Nested‑Loop, Block Nested‑Loop, and Hash Join implementations, how the optimizer selects the outer table, and comparisons with other database systems.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This comprehensive tutorial walks through configuring a Hadoop‑based environment (Flink 1.13.1, Scala 2.11, CDH 6.2.0, Hive 2.1.1, Hudi 0.10), compiling Hudi, setting up Flink and MySQL binlog, creating CDC source and Hudi sink tables, running Flink jobs, and synchronizing the results to Hive partitions for query via Hive and Presto.
A colleague encountered an "Incorrect string value" error when inserting UTF‑8 text into a GBK‑encoded MySQL table, and the article explains how charset mismatches and the use of the CHAR() function cause the failure, then provides reproducible tests and concrete solutions.
This article explains why inserting emoji into MySQL fails with the default utf8 charset, demonstrates how switching to utf8mb4 resolves the issue, and provides a historical overview of MySQL's limited utf8 implementation and the proper use of true UTF‑8 encoding.
Qunar’s DBA team overhauled their MySQL monitoring and alert system—originally built on Nagios and NRPE—by integrating a Kafka‑based pipeline, a custom alarm service, and MySQL‑stored alert templates, achieving flexible thresholds, granular silencing, high‑availability processing, and early‑stage intelligent management of alerts, slow queries, and disk space.
This article provides practical guidance on server hardware selection, Linux kernel parameter tuning, and MySQL configuration adjustments to improve overall system performance, reliability, and efficiency for production environments.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication using Docker, explains redo log and binlog mechanisms, and demonstrates how to implement Sharding‑JDBC read/write splitting and database sharding with Spring Boot, including code examples and configuration details.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This tutorial demonstrates how to analyze an e‑commerce search page, use Selenium to automate browsing, extract product details across multiple pages, and store the collected data into a MySQL database, providing complete Python code and step‑by‑step explanations.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download, install, and run them, and shows how to interpret their diagnostic reports to identify configuration issues and optimize database performance.
After repeatedly encountering account balance discrepancies in a financial system, I traced the issue to improper use of pessimistic locks and Hibernate caching, then resolved it by correcting lock placement, optimizing SQL queries, and switching to native SQL, sharing the detailed debugging steps and lessons learned.
The article explains why MySQL’s DELETE command does not shrink the physical table file, describes InnoDB’s storage mechanics, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains why MySQL fails to start on Ubuntu due to AppArmor restrictions and provides two practical solutions: editing the AppArmor profile to grant MySQL directory access or switching AppArmor to complain mode, with full command examples and configuration snippets.
This article explains MySQL disaster‑recovery techniques, covering cold and hot backups, same‑city versus remote setups, master‑slave topologies, async, semi‑sync and full‑sync replication, the MAR strong‑sync approach, and practical recommendations for building resilient two‑city three‑center architectures.
This article provides an in‑depth explanation of MySQL InnoDB’s transaction system, covering ACID properties, isolation levels, MVCC implementation, various lock types, key data structures, lock acquisition flows, semi‑consistent reads, insert locking, implicit‑to‑explicit lock conversion, and rollback mechanisms.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article provides a comprehensive overview of MySQL fundamentals—including logical architecture, locks, transactions, and storage engines—and offers practical optimization strategies for schema design, data types, indexing, and query execution, supplemented with code examples and performance‑analysis tips.
The DTLE 3.21.10.0 release introduces foreign‑key support, a new job parameter for foreign key checks, cascade operations, handling of large‑memory transactions, and various bug fixes and refactorings, providing a more robust MySQL data‑transfer solution.
This article presents a complete Java backend project for community‑based COVID‑19 management, detailing its architecture, modules such as login, daily epidemic tracking, prevention management, system administration and user management, and provides extensive SpringBoot service‑layer code examples with RBAC permission control.
After a colleague accidentally dropped a production database, we revoked DROP privileges for a MySQL user and systematically tested how this restriction impacts common operations such as renaming tables, using TRUNCATE, deleting rows, dropping objects, and recovering data via binlog, revealing surprising behaviors and practical workarounds.
This article explains what an execution plan is, how to use the EXPLAIN keyword in MySQL 5.7, interprets each column of the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, rows, and Extra—provides detailed examples of different query scenarios, and shows how optimizer settings like derived_merge affect the plan.
This article explains MySQL's character encoding workflow, illustrates why queries containing Chinese characters fail without proper settings, and shows how to configure JDBC URLs, server variables, and Docker‑based MySQL instances to ensure lossless UTF‑8 handling.
This article explains why duplicate rows appear in MySQL tables, demonstrates how to identify them with SELECT queries, and provides step‑by‑step SQL solutions—including safe deletion of all duplicates or retaining a single record per group—using subqueries and temporary tables for efficient cleanup.
This article explains the different types of auto‑increment identifiers used by MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, and thread_id—describes how they reach their maximum values, the consequences of overflow, and compares external solutions such as Redis‑based keys.
This article outlines HuoLaLa's journey from a fragmented multi‑cloud environment to a unified, platform‑driven database governance system, detailing the technical challenges, architectural decisions, key components for MySQL, Redis, Kafka, and other middleware, and the measurable stability and cost improvements achieved after a year of operation.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, using UNION ALL, driving large tables with small ones, batch operations, LIMIT usage, index control, proper data types, and efficient GROUP BY—to help developers improve query speed, reduce resource consumption, and avoid common performance pitfalls.
This article demonstrates how to design and implement a high‑frequency like/unlike feature using Spring Cloud, Redis for caching, MySQL for persistence, and Quartz for scheduled data synchronization, covering cache design, data structures, service interfaces, database schema, and deployment scripts.
This article documents a reproducible test that shows gh-ost can lose rows when used on a MySQL 5.7 AFTER_SYNC semi‑synchronous replica, explains the underlying cause, and presents a source‑code modification that prevents the loss.
This guide presents practical MySQL performance tricks—including choosing minimal field types, using NOT NULL, preferring JOIN over sub‑queries, leveraging UNION, applying proper transaction control, foreign keys, table locking, indexing strategies, and query‑writing best practices—to dramatically speed up data retrieval and reduce resource consumption.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
This article explains how to use the mysqldump command to back up MySQL databases, provides various command examples for full or partial backups, demonstrates a Bash script for automated daily backups with log management, and shows how to schedule these tasks using crontab.
The article analyzes why xtrabackup full backups fail on MySQL 8.0 when undo log truncation occurs, identifies a bug triggered by the super_read_only setting, and provides reproducible steps and configuration tweaks to prevent the issue.
This article introduces Flink CDC 2.0, explains its distributed full‑load and incremental reading mechanisms, details the slice partitioning, snapshot correction, and binlog handling logic, and provides a complete Java example that demonstrates how to configure Flink SQL, MySQL source, and Kafka sink.
This article explains the fundamentals of MySQL indexes, focusing on B+Tree structures, differences between MyISAM and InnoDB implementations, practical indexing strategies, configuration and SQL tuning tips, and provides a detailed case study with EXPLAIN analysis to help developers design efficient indexes.
This article explains the concepts, characteristics, and practical scenarios of database sharding—including vertical and horizontal table partitioning as well as vertical and horizontal database partitioning—helping developers decide when and how to apply each strategy to improve scalability and performance.
This article explains how PolarDB MySQL introduces an In‑Memory Column Index (IMCI) to combine row‑store OLTP strength with column‑store analytical speed, detailing the architectural innovations, optimizer decisions, data organization, resource isolation strategies, and benchmark results that show IMCI achieving tens to hundreds of times faster query execution than native MySQL and matching ClickHouse performance.
This article presents a full‑stack Java SpringBoot project that manages community users and tracks epidemic data, employing RBAC for role‑based permissions, MySQL for storage, and various Spring modules such as SpringMVC, AOP, interceptors, and global exception handling.
The article explains a common MySQL UPDATE mistake where developers use AND between column assignments, shows the unexpected result of owner_code becoming 0, analyzes the logical expression behind it, and provides the correct comma‑separated syntax to reliably update multiple fields.
This guide walks through SQL’s language categories—DDL, DML, DQL, DCL, and TCL—then demonstrates practical database and table management commands such as creating and dropping databases, defining tables with various constraints (not‑null, default, primary/foreign keys, unique, auto‑increment), and modifying or copying fields and tables.
This article explains the purpose and mechanics of MySQL's binlog, redo log, and undo log, covering their structures, use cases such as replication and recovery, flush timing parameters, format options, and how they together ensure data durability and consistency in InnoDB.
This tutorial explains how MySQL implements full‑text search, covering the creation of full‑text indexes (including Chinese ngram support), the three query modes (natural language, boolean, and query expansion), relevance ranking, underlying inverted‑index structures, cache handling, and common DML operations.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds a pessimistic lock, detailing when it results in row-level locking versus table-level locking based on the presence of indexes or primary keys, and demonstrates the behavior with multiple practical examples.
The article explains how MySQL's explicit_defaults_for_timestamp setting and strict SQL mode cause TIMESTAMP columns with default CURRENT_TIMESTAMP to produce "Invalid default value" errors during migration from 5.7.28 to 5.7.20, especially when synchronization tools omit NULL and DEFAULT attributes.
The article describes how Qunar.com upgraded its Nagios/NRPE‑based database monitoring by inserting a Kafka‑driven alarm component, centralizing alert configuration in MySQL, adding flexible shielding and multi‑channel notifications, and exploring intelligent features such as slow‑query and disk‑space management.
This article compares MySQL and HBase across architecture, storage engine, indexing structures (B+ tree vs LSM tree), data access features, and ecosystem integration, highlighting each system's strengths, limitations, and the scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
Vitess 12, the latest major release of the MySQL clustering solution, introduces the experimental Gen4 query planner, enhanced VTAdmin multi‑cluster management, role‑based access control, updated benchmarking tools, and more inclusive naming conventions to improve scalability and cloud deployment.
This article explains the fundamentals, architecture, deployment patterns, recovery procedures, performance tuning, and common troubleshooting of MySQL Group Replication (MGR) as a high‑availability solution for private deployments, illustrated with real‑world examples and practical SQL commands.
The article investigates how changing the operating system clock while MySQL is running influences functions such as SLEEP, metadata lock (MDL) wait calculations, and other timing‑related mechanisms, demonstrating that forward or backward adjustments can cause premature exits or inaccurate wait measurements.
This comprehensive guide covers MySQL fundamentals such as the three normal forms, differences between InnoDB and MyISAM, auto‑increment primary key behavior, index types and design principles, transaction isolation levels, MVCC, logging mechanisms, two‑phase commit, query execution, replication, high‑availability architectures, and practical performance tuning techniques.
In a production environment, a pagination export feature caused duplicate and missing records due to MySQL’s nondeterministic ordering when multiple rows share the same ORDER BY value, and the article explains the root cause, official documentation, and a reliable solution using additional sorting columns.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication with Docker, explains redo log and binlog mechanisms, demonstrates two‑phase commit, and shows how to use ShardingSphere‑Jdbc for read/write splitting and sharding with detailed Spring Boot examples and code snippets.
This article examines a real‑world Percona XtraDB Cluster (PXC) fault where simultaneous write‑node connection spikes were caused by flow‑control triggered by 100% disk I/O on a shared read node, ultimately traced to slow sorting queries involving a mediumtext column, and offers diagnostic steps and mitigation strategies.
This article outlines the principles, recommended practices, and detailed steps for upgrading MySQL from 5.7 to 8.0, covering version compatibility, major 8.0 feature changes, pre‑upgrade checks, in‑place and logical migration procedures, and essential configuration adjustments with example commands.
This article explains MySQL InnoDB lock types, analyzes common deadlock scenarios with detailed SQL examples, describes the underlying locking mechanisms such as next‑key and gap locks, and provides practical prevention and resolution techniques for developers working with transactional databases.
This article explains why MySQL 8.0 no longer requires the legacy proxy user feature, shows how to replace it with roles, and provides a step‑by‑step demonstration—including plugin installation, user creation, privilege granting, and the two main limitations of proxy users—using concrete MySQL commands and code snippets.
This article explains the background, design criteria, and practical implementations of horizontal database sharding—including range, hash, gene, routing‑table, and consistent‑hash methods—while discussing common pitfalls, data‑skew mitigation, and both doubling and consistent‑hash expansion techniques for MySQL clusters.
This article explains how the MySQL system variable explicit_defaults_for_timestamp controls the default and NULL handling of TIMESTAMP columns, demonstrates the differences between OFF and ON settings with practical tests, and discusses why the parameter is being deprecated for stricter time‑data management.
This article explains how the MySQL SELECT … FOR UPDATE statement adds a pessimistic lock, why the lock type (row or table) depends on the presence of an index or primary key, and demonstrates the behavior with practical examples and SQL scripts.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article explores PolarDB MySQL’s In‑Memory Column Index (IMCI) technology, detailing its hybrid row‑column storage architecture, optimizer enhancements, parallel execution engine, and performance gains that enable real‑time analytical queries alongside OLTP workloads, and compares its benchmarks against MySQL and ClickHouse.
This article explains the four necessary conditions that cause deadlocks, outlines four major strategies to prevent or resolve them, and provides a step‑by‑step MySQL example with code snippets and diagrams illustrating how deadlocks occur and can be avoided.
This guide walks you through setting up a Spring Boot‑based logistics warehouse system, detailing required software versions, the project’s modular features, technology stack, database and backend configuration, frontend setup with Vue, and provides screenshots of key interfaces.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds pessimistic locks, detailing when it results in row‑level locks versus table‑level locks based on the presence of indexes or primary keys, and provides verification examples with code and transaction scenarios.
This article details how a rapidly growing e‑commerce platform migrated from a single MySQL instance to a 16‑shard architecture using Sharding‑Jdbc, introduced consistent‑hashing to mitigate data skew, leveraged ES+HBase for multi‑dimensional queries, and implemented zero‑downtime migration strategies such as dual‑write and Canal replication.
This article examines a real‑world MySQL master‑slave replication delay case, analyzes the root causes using slave status, InnoDB metrics, OS‑level I/O and error logs, and provides concrete configuration and operational recommendations to reduce lag.
This article walks through MySQL's core concepts—including ACID guarantees, the four isolation levels, undo and redo logs, buffer pool flushing, lock types, gap locks, deadlock scenarios, and the B+‑tree based index architecture—providing interview‑ready insights and practical examples.
Effective MySQL sharding requires sustainable, low‑skew designs, favoring hash‑based methods with proper coprime counts, two‑stage partitioning, routing tables, or consistent hashing, while supporting expansion via doubling or flexible consistent‑hash growth to avoid hot spots and uneven data distribution.
This article explains the background, principles, architecture, and detailed implementation of Percona's thread pool, and describes TXSQL's dynamic thread‑pool activation, load‑balancing strategies, disconnection optimizations, related configuration parameters, status variables, and a concise summary.
This article describes a real‑time slow‑query monitoring solution for MySQL, detailing the overall architecture, the agent that tails slow‑log files and pushes entries to Redis, and the consumer that processes logs, stores them, and alerts DBAs, enabling near‑instant detection of performance issues.
This article presents a comprehensive guide to MySQL performance, covering maximum data volume and concurrency limits, recommended query response times, practical table‑design rules, index classification and optimization techniques, as well as detailed SQL tuning patterns such as batch processing, operator, OR, IN, LIKE, JOIN and LIMIT improvements.
This article provides a detailed, online, in‑place upgrade procedure for MySQL InnoDB Cluster (MGR) from version 5.7.25 single‑primary mode to version 8.0.26, covering preparation, backup, node‑by‑node upgrade, configuration changes, and communication‑protocol updates.
This article describes the background, functional requirements, architecture, packet‑capture implementation using libpcap, MySQL protocol parsing, result processing, and storage strategy (Kafka + ClickHouse) of a custom MySQL sniffer developed at Qunar to provide complete database access logs for auditing, performance analysis, and operations.
This tutorial introduces a music website project built with a Vue.js front‑end and a Spring Boot + MyBatis back‑end using MySQL, and provides step‑by‑step deployment instructions, configuration details, and commands to run the full‑stack application.
This tutorial walks you through creating a complete music‑streaming site using Vue.js for the front‑end and Spring Boot/MyBatis for the back‑end, covering core features, required resources, configuration, and deployment commands in a clear, practical manner.
This article explains what database indexes are, classifies them by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates how InnoDB and MyISAM handle clustered and secondary indexes with practical SQL examples.
This article explains which components—user processes, I/O wait, and system interrupts—consume most CPU in MySQL, describes the performance impact of high CPU, and provides concrete strategies such as reducing I/O, simplifying calculations, optimizing indexes, and judiciously scaling hardware to lower CPU usage.
A detailed forensic analysis reveals that a MySQL 5.7 instance stalls during startup due to a mismatch between InnoDB flush‑sync settings and truncate‑related redo logs, and the article explains how to diagnose the issue with TOP, stack traces, and source‑code inspection, then offers three practical work‑arounds.
This article introduces a lightweight Spring Boot‑based mini Tmall e‑commerce project, detailing its core features—from user registration and product browsing to cart, order processing, and admin management—along with the technology stack, UI screenshots, and a four‑step deployment guide for developers.
