To maintain data consistency between Redis caches and MySQL databases, this article examines common pitfalls and presents three robust solutions—deleting cache before DB writes, updating the DB then removing cache, and implementing delete‑retry mechanisms—plus optional locking for strong consistency.
This article explains how InnoDB implements row locking by dividing the process into fast and slow paths, describing the hash‑based lock table, token acquisition, lock‑structure creation, bitmap handling, and the conditions that trigger slow‑lock logic.
This article explains why replacing MyBatis with MyBatis‑Plus caused a LocalDateTime conversion exception, identifies the root cause as an outdated mysql‑connector‑java version, and shows how upgrading the connector (to 5.1.37 or later) resolves the issue while providing code examples and debugging steps.
This article presents a comprehensive case study of a high‑traffic business middle‑platform built with SpringBoot microservices, Kong API Gateway, MyCat‑sharded MySQL, Docker containerization, Kubernetes orchestration, CI/CD pipelines, and monitoring tools, detailing design decisions, implementation tricks, and operational lessons learned.
This article presents a comprehensive design and implementation for achieving MySQL high availability by replacing the single‑master architecture with Orchestrator‑driven automatic failover, integrating DBProxy for transparent routing, and addressing topology changes and data compensation to ensure continuous, reliable service.
This article explains why MySQL columns defined as INT(10) appear as INT after upgrading from 5.7 to 8.0, demonstrates the change with reproducible examples, and discusses the storage limits, proper selection, and display‑width behavior of integer types including ZEROFILL.
This article examines the practical limits of MySQL single‑table row counts, explains how InnoDB stores data in 16 KB pages, analyzes B+‑tree index structures, and provides formulas and experiments that justify the commonly cited 20 million‑row guideline.
This guide introduces a collection of practical MySQL DBA utilities—including SQLTXPLAIN, SOAR, SQLAdvisor, and Percona Toolkit commands such as pt‑duplicate-key-checker, pt‑kill, pt‑mysql-summary, pt‑query-digest, pt‑table-checksum, and pt‑table-sync—showing how to install them, run key commands, and interpret their outputs to streamline database administration tasks.
Facing MySQL 5.7’s February 2024 end‑of‑life, the team devised a zero‑impact blue‑green migration on AWS RDS to MySQL 8.0.35, using util.checkForServerUpgrade() compatibility checks, pt‑upgrade SQL validation, and a rapid 2‑minute switchover that completed within the planned low‑traffic window.
This article walks through a practical experiment where the AI‑driven assistant ChatDBA diagnoses and resolves a MySQL “Too many open files” error by collecting system metrics, analyzing slow queries, identifying index misuse, and proposing concrete configuration and query adjustments.
This article presents a comprehensive guide to designing and implementing a Java‑based MySQL large‑table migration tool using microservice architecture, multiple design patterns, multithreading, custom logging, and flexible migration modes, complete with code snippets, repository links, and deployment considerations.
This guide details how to use mysqldump and related commands to back up single or multiple MySQL databases, specific tables, compress backups, include drop statements, export only schema, and automate the process with scripts and cron jobs, plus instructions for restoring data.
This article explains common scenarios that cause MySQL or Java processes to consume 900% CPU, walks through step‑by‑step diagnosis using Linux tools, and provides concrete optimization techniques such as indexing, caching, thread analysis, and code adjustments to bring CPU usage back to normal levels.
This article explains why database sharding is needed, how to identify performance bottlenecks, and provides step‑by‑step guidance on SQL tuning, table redesign, architectural changes, and practical horizontal and vertical sharding techniques with real‑world e‑commerce examples.
An in‑depth comparison of PostgreSQL and MySQL covers their histories, ACID compliance, performance, scalability, advanced features, security, backup strategies, installation on Linux, and provides practical SQL code examples to help full‑stack developers choose the right open‑source relational database for their projects.
This article introduces a Python‑Flask‑MySQL student training plan management system, detailing its features such as visualized progress, SVD‑based course recommendation, forum discussion, simulated enrollment, project structure, environment setup, step‑by‑step installation, configuration, and usage instructions.
This article analyzes how to import one billion 1 KB log records stored in HDFS or S3 into MySQL by evaluating single‑table limits, choosing storage engines, designing sharding, batch insertion, file‑reading strategies, task coordination, and distributed locking to achieve high‑throughput and ordered writes.
This tutorial demonstrates step‑by‑step how to set up a MySQL database, write PHP scripts to retrieve taxi trajectory data, and use Baidu Maps JavaScript API to render interactive maps showing each vehicle’s path, including code examples for database creation, data insertion, and front‑end integration.
This article explains nine common reasons why MySQL indexes become ineffective and offers concrete solutions—including query rewrites, data type alignment, index redesign, and maintenance tips—to help developers improve database query performance.
This article walks through generating tens of millions of rows in MySQL, then demonstrates a series of optimizations—including simple and covering indexes, data reduction, small‑table‑driven joins, and forced indexes—showing how each technique impacts query performance and EXPLAIN output.
The article examines Peter Zaitsev’s critique of Oracle’s stewardship of MySQL, debunks long‑standing myths, evaluates recent MySQL Heatwave features and performance gaps, and compares MySQL’s trajectory with PostgreSQL and other open‑source databases.
This article explains how to accelerate slow MySQL join queries in high‑traffic e‑commerce fulfillment systems by using wide tables, MySQL JSON columns, virtual columns, and indexed expressions, comparing their trade‑offs and providing practical SQL examples.
This article explains the types of InnoDB row‑level locks—record lock, gap lock, and next‑key lock—describes how MySQL determines lock ranges based on indexing, outlines the two fundamental locking principles with associated optimizations, and provides concrete examples for primary‑key and ordinary indexes.
This article explains InnoDB lock concepts, differentiates read and write locks, discusses table‑level versus row‑level locking, and examines how MySQL 5.6 and 8.0 handle adding columns without fully locking tables, highlighting online DDL, atomic DDL, and performance considerations.
Oracle’s post‑acquisition strategy has left MySQL lagging behind modern developers, while PostgreSQL, praised for features like parallel queries and vector search, now dominates the open‑source database market, prompting calls for MySQL’s transfer to the Linux Foundation to avoid obsolescence.
This article explains the relationship between MySQL and Redis, discusses why cache consistency is challenging, and details four cache update design patterns—delete‑then‑update, update‑then‑invalidate (Cache‑Aside), read/write‑through, and write‑behind—illustrating each with flow diagrams and practical considerations.
This article reviews four common MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous dual‑write via MQ, timer‑based SQL extraction, and real‑time Binlog replication—evaluates their pros and cons, and compares popular migration tools such as Canal, Alibaba DTS, Databus and others.
This article explains how InnoDB determines whether a transaction already holds a table lock, acquires a mutex token, checks for blocking locks, and creates or reuses lock structures, detailing the complete table‑lock acquisition workflow in MySQL 8.0.32.
This article explains common MySQL performance pitfalls such as inefficient LIMIT pagination, implicit type conversion, sub‑query‑based updates, mixed ordering, misuse of EXISTS, and condition push‑down, and provides concrete rewrites and examples that reduce query times from seconds to milliseconds.
This article explains how to design a MySQL table for product sales statistics, initialize massive test data, and compare several query strategies—including loop queries, OR‑concatenated dynamic SQL, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales figures for multiple business units and their products without violating development constraints.
MySQL offers a comprehensive set of string functions—including lower, upper, concat, substring/substr, length, char_length, instr, lpad, rpad, trim (with leading, trailing, both options), replace, repeat, reverse, and format—that enable case conversion, concatenation, extraction, padding, trimming, substitution, repetition, reversal, and numeric formatting of textual data.
This article compares four MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous dual‑write, SQL extraction, and Binlog‑based real‑time sync—evaluates their pros and cons, and reviews popular migration tools such as Canal, Alibaba DTS, Databus, Flink, CloudCanal, Maxwell, and DRDS.
This guide explains why accidental database deletion is a serious risk, outlines essential permission and logging measures, and provides step‑by‑step scripts and cron jobs to automatically back up MySQL databases and critical files using Docker, OpenSSL, rsync, and expect.
This article explains how to use PHP's mysqli_num_rows function to retrieve the number of rows returned by a MySQL query, including a complete example with connection handling, error checking, and output of the row count.
A startup’s production MySQL server hit 400% CPU usage due to poorly written queries with temporary tables, filesorts, and join buffers, prompting a deep dive into execution plans, concrete optimization recommendations, and a rollback strategy to restore service.
This article explains why plain LIKE searches become slow on large MySQL tables, shows how prefix patterns and string functions like LOCATE, POSITION, INSTR, and FIND_IN_SET can leverage indexes, and provides a comprehensive list of thirty practical SQL optimization techniques to dramatically improve query performance.
This guide explains how to design a MySQL categories table, retrieve records with PHP, construct a recursive tree based on parent_id, and render the hierarchical structure as nested HTML lists, providing a complete solution for managing product categories in e‑commerce applications.
This article details how to switch a SpringBoot‑MyBatisPlus‑MySQL application to PostgreSQL, covering driver addition, JDBC configuration changes, numerous SQL and type‑conversion pitfalls, helper scripts for bulk column updates, and essential migration notes.
This article explains the structure and components of InnoDB's lock module, including the global lock_sys object, its hash tables, latch mechanisms, waiting thread slots, and related attributes, with detailed code examples and diagrams to illustrate how row and table locks are managed.
This article thoroughly explains how the Percona pt-osc tool performs online schema changes in MySQL, analyzes trigger design, deadlock scenarios, data‑consistency issues, and provides detailed test cases, operational tips, and best‑practice recommendations for safe usage.
This article explains why MySQL master‑slave replication can become delayed—including single‑threaded binlog processing, high load, network latency, and configuration issues—and provides detailed troubleshooting steps and practical solutions such as semi‑synchronous replication, hardware upgrades, and configuration tuning.
The article defines large database transactions, explains their performance and replication risks, offers practical guidelines to avoid them, demonstrates a MySQL example that creates and repeatedly inserts data to illustrate growing execution time, and discusses challenges and solutions for handling massive tables in high‑traffic environments.
This article walks through how MySQL processes a SELECT query, detailing the server and storage engine layers, the roles of the connector, query cache, parser, optimizer, and executor, and provides practical command‑line examples to illustrate each step.
This article examines why a newly created MySQL user (sky1@%) cannot log in via socket after upgrading from 5.6 to 5.7, demonstrates how the ChatDBA AI assistant diagnoses the issue, proposes a fix by removing an anonymous user, and compares results with ChatGPT‑4o.
This article explores how to retrieve sales statistics for multiple business units and their associated products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison techniques, and explains why the row‑comparison method was ultimately chosen for its performance and compliance with development constraints.
An in‑depth postmortem of a costly MySQL data‑migration failure caused by using INSERT INTO SELECT without proper indexing, detailing the OOM issue, full‑table scans, lock contention, and best practices to safely employ the statement.
This article explains why inserting emoji characters into a MySQL table defined with the default utf8 charset triggers a string‑value error, explores the underlying encoding and charset concepts, compares utf8mb3 and utf8mb4, and shows how to convert tables to avoid such failures.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT operation, analyzes why full‑table scans and transaction isolation led to payment‑record loss, and offers practical safeguards such as proper indexing and careful use of the statement.
The article compares MySQL and PostgreSQL, covering their histories, architecture hierarchies, benchmark performance on SELECT/INSERT/UPDATE, suitable use cases, and summarizes PostgreSQL’s advantages and disadvantages relative to MySQL, providing guidance for choosing the appropriate database.
Learn to create a Bash script that backs up a specific MySQL database using mysqldump, automatically deletes old backups, handles Windows line‑ending issues, schedules daily execution with cron, and restores compressed backups, complete with detailed command examples and explanations of find -mtime.
This article evaluates MySQL single‑thread write performance by testing three insertion methods—continuous while‑loop inserts, JDBC batch processing, and multi‑row INSERT statements—detailing setup, code examples, and measured rows per minute to reveal their relative throughput and stability.
This article examines the shared and distinct fields of InnoDB's table‑lock and row‑lock structures in MySQL 8.0.32, explains the layout of the lock_t, lock_table_t and lock_rec_t structs, decodes the type_mode bit‑field, and discusses how InnoDB merges compatible row‑locks using a bitmap memory area.
This article walks through how to analyze MySQL slow queries using EXPLAIN, explains key EXPLAIN fields, and presents practical optimization techniques such as proper indexing, pagination tricks, join improvements, and ORDER BY/GROUP BY tuning to dramatically boost query performance.
This article details a step‑by‑step investigation of why a MySQL 8.0.25 server becomes read‑only after a patch‑induced restart, revealing that a persisted configuration in mysqld‑auto.cnf set by PERSIST_ONLY caused the discrepancy and how to verify and resolve it.
This article provides an overview of MySQL and PostgreSQL, compares their performance in SELECT, INSERT, and UPDATE operations under identical hardware, and discusses suitable scenarios for each database, highlighting the strengths and weaknesses of both systems.
In this episode, the AI assistant ChatDBA analyzes frequent MySQL error‑log messages, identifies misconfigured timeout parameters as the primary cause, and guides the DBA through adjusting rpl_semi_sync_master_timeout to resolve the replication delays, while also comparing results with Gemini.
This tutorial demonstrates how to build a taxi trajectory visualization by creating a MySQL table for track data, inserting sample records, writing a PHP script to serve the data as JSON, and using Baidu Maps JavaScript API in an HTML page to render the moving paths.
This article explains why MySQL’s built‑in utf8 charset only supports three‑byte characters, why emojis cause errors, and how switching to the proper utf8mb4 charset resolves insertion issues while preserving full Unicode support.
This article examines Pinterest's evolution through four development phases, the core technologies it adopted, and how it transitioned from complex clustering to a simpler sharding architecture to achieve scalable, high‑availability data storage.
This article walks through a real‑world approval workflow where a business user adds a company, explains why MySQL REGEXP is chosen over Elasticsearch for fuzzy matching, and details the step‑by‑step implementation—including name preprocessing, tokenization with IKAnalyzer, and a custom SQL query that ranks results by match degree.
This article explains the concept of MySQL master‑slave replication, outlines its advantages and drawbacks, and provides a step‑by‑step guide—including downloading MySQL 8.0, configuring my.cnf on master and slave servers, creating replication users, setting up replication parameters, and using Laravel for read‑write splitting—complete with all necessary shell and SQL commands.
This article presents a comprehensive guide to MySQL table design, covering naming conventions, appropriate data types, primary key strategies, field length choices, logical deletion, common fields, indexing rules, normalization trade‑offs, storage engine selection, time type recommendations, and SQL optimization tips.
A developer’s costly mistake using MySQL’s INSERT INTO SELECT for large data migration led to OOM, full-table scans, and lost payments, revealing how transaction locks and missing indexes can cause production failures and how to prevent them.
This article explains the difference between database sharding and table partitioning, illustrates scaling challenges with a fast‑growing startup scenario, and compares popular sharding middleware such as Cobar, TDDL, Atlas, ShardingSphere, and Mycat, offering practical recommendations for different company sizes.
This article analyzes a slow MySQL pagination query caused by uneven data distribution and range scans, demonstrates how to examine execution plans and data distribution, and shows that adding a composite index with Index Condition Pushdown dramatically reduces scanned rows and query time.
A real‑world MySQL post‑mortem shows how using INSERT INTO SELECT for large‑scale data migration caused full‑table scans, transaction‑level locking, data loss and a costly dismissal, and explains how proper indexing and query design can prevent such disasters.
This article explains the different types of InnoDB row locks—including shared, exclusive, gap, next‑key, and insert‑intention locks—by showing preparation steps, SQL examples, lock‑mode details, and how they interact under repeatable‑read isolation in MySQL 8.0.32.
This article explains common scenarios of implicit type and charset conversions in MySQL, demonstrates how they cause index loss and performance degradation through detailed examples and EXPLAIN output, and provides practical guidelines to prevent such conversions in production environments.
This article demonstrates how to use PHP functions to implement user registration and data storage, covering input validation, password hashing, MySQL database connection, SQL insertion, and returning operation results, while highlighting security considerations and practical code examples.
This guide walks through importing a CentOS 7 virtual machine in VMware, configuring network settings (dynamic or static IP), resolving common issues, then installing and configuring JDK 1.8, MySQL 5.7, and Tomcat 8.5, and finally creating systemd services for automatic startup of Java applications.
This guide explains how to define log management goals, handle special logging scenarios for services like MySQL and nginx, and implement automated cleanup using crontab and logrotate, including detailed configuration options and practical examples for reliable log rotation and storage control.
This article presents a MySQL "table is full" error case, explains how the AI‑powered assistant ChatDBA analyzes possible causes such as insufficient temporary tablespace, heavy temporary table creation, and disk limits, and provides step‑by‑step configuration adjustments to resolve the issue.
A MySQL‑driven data‑migration using INSERT INTO SELECT caused full‑table scans, lock contention and lost payment records, leading to a $10,000 loss and the engineer’s dismissal; the post‑mortem explains the root cause and how proper indexing can prevent it.
This guide walks through deploying Xenon, an open‑source Raft‑based MySQL high‑availability solution, covering environment setup, installation of Go and Percona XtraBackup, configuring Xenon’s JSON, starting the cluster, monitoring status, and troubleshooting backup failures caused by misconfigured host settings.
This guide presents comprehensive MySQL performance‑tuning methods, covering SQL pagination tricks, proper index creation and usage, join and UNION optimizations, slow‑query logging, schema design choices, and hardware recommendations to dramatically improve query speed and resource efficiency.
A large‑scale MySQL table migration using INSERT INTO SELECT caused hidden full‑table scans and lock contention, leading to data loss and a costly OOM incident, but adding the proper index and understanding transaction isolation prevented the failure.
This article explains how MySQL's SELECT ... FOR UPDATE statement acquires either row-level or table-level locks depending on the presence of indexes or primary keys, and demonstrates the behavior with multiple transaction examples and a sample table definition.
The article analyzes a real‑world MySQL data‑migration failure caused by an INSERT INTO SELECT operation that triggered full‑table scans and row‑level lock contention, leading to massive data loss and highlighting the need for proper indexing and cautious use of this command.
The article analyses the historical, technical, and market reasons why MySQL became the mainstream database in mainland China, contrasts its simplicity and performance with PostgreSQL's advanced transaction and consistency features, and discusses whether PostgreSQL can realistically replace MySQL in enterprise scenarios.
This guide walks through creating a three‑node MySQL environment, installing and configuring MariaDB, establishing master‑slave replication, and implementing read/write splitting on an application server using MySQL‑Proxy, complete with command‑line examples and configuration snippets.
A developer used MySQL’s INSERT INTO SELECT to migrate millions of rows, causing full‑table scans, OOM errors and lost payment records, leading to a costly incident; the post analyzes the root causes, explains transaction locking behavior, and offers practical indexing and testing safeguards.
The article introduces go‑sniffer, a Go‑based packet sniffer that captures MySQL, Redis, and MongoDB traffic, explains how to install required dependencies and the tool itself on CentOS or Ubuntu, and provides command‑line examples for auditing and analyzing database operations.
This article explains the architecture of MySQL's InnoDB storage engine locks, covering server‑level metadata locks, table‑level shared and exclusive locks, intent locks, and the three modes of AUTO‑INC locks, with practical SQL examples and performance‑schema queries.
This article analyzes how MySQL binlog records timestamps and exec_time for GTID, BEGIN, Table_map, Write, and Xid events on both master and slave, explains the calculation of original_commit_timestamp and immediate_commit_timestamp, and provides formulas and source‑code references to help diagnose replication delay.
The article details Bilibili’s live ranking system, explaining how configurable ranking lists—managed via a console, stored in MySQL and cached in Redis zsets—support diverse dimensions, scoring modes, and filters to boost streamer and viewer engagement, while addressing high traffic, reliability, and future scalability plans.
This tutorial walks through creating a Python GUI application using Tkinter to convert long URLs into short ones, installing required libraries, setting up MySQL tables to store mappings, and providing complete example code that handles validation, database interaction, and user interface events.
This article details a series of Kafka message backlog incidents in a busy restaurant ordering system, analyzes root causes such as consumer speed, database indexing, and sudden traffic spikes, and presents step‑by‑step optimizations—including batch queries, index tuning, data archiving, and thread‑pool adjustments—to restore real‑time performance.
This article explains MySQL's transaction concepts, the four isolation levels defined by the SQL standard, how each level prevents dirty reads, non‑repeatable reads, and phantom reads, and provides practical commands and example experiments to configure and observe their behavior in InnoDB.
A MySQL data‑migration task using INSERT INTO SELECT triggered a full‑table scan, leading to lock contention, payment‑record loss, and ultimately the developer's dismissal, highlighting the dangers of unindexed bulk operations and the need for proper indexing and testing.
This article explains how to write Go unit tests for HTTP handlers that depend on MySQL by using fake implementations and the gomock library to replace the store layer, demonstrating both fake object and mock approaches, code examples, and best practices for isolating external dependencies.
This comprehensive guide explains MySQL transaction fundamentals, defines dirty reads, non‑repeatable reads and phantom reads, compares the four isolation levels, shows how to query and set them, and details the underlying locking mechanisms such as MVCC and next‑key locks.
Using MySQL’s INSERT INTO SELECT on a large, unindexed table caused full‑table locks that timed out concurrent transactions, leading to lost payment rows; the post‑mortem shows that adding appropriate indexes to the WHERE clause and understanding locking behavior prevents such intermittent failures.
The article explains how to handle distributed consistency when a financial reimbursement process involves MySQL writes and remote calls to BPM and SAP systems, analyzes failure scenarios, and proposes a split‑transaction approach using a task table, scheduled jobs, and Spring's afterCommit hook.
This article analyzes an intermittent failure where a MySQL instance managed by systemd in forking mode hangs because an automated test prematurely kills the newly created mysqld process, leading to a missing MAIN PID and a zombie process, and provides investigation steps and a practical fix.
Yearning is an open-source MySQL SQL audit platform built with Go and Vue.js that automates statement review, generates rollback scripts, provides audit logging, supports multi-channel notifications, and offers fine-grained permission control, with installation instructions and configuration details for rapid deployment.
This article explains MySQL's cost‑based optimizer, demonstrates how to calculate query costs for full‑table, covering, ref and range scans using actual source‑code constants, and applies the model to resolve index‑conflict cases in a store‑goods table, offering practical optimization guidelines and future tool ideas.
A real‑world MySQL case study shows how using INSERT INTO SELECT for large‑scale data migration without proper indexing caused full‑table scans, lock contention, and data loss, highlighting the need for careful query planning and performance testing.
This article explains how MySQL table‑level locks and metadata locks (MDL) can block both reads and writes, describes the two lock implementations, their types, the interaction with LOCK TABLES and FLUSH TABLES, and provides practical queries for diagnosing lock‑related blocking issues.
This article provides a comprehensive overview of InnoDB's transaction lock subsystem in MySQL, explaining row‑level and table‑level lock types, their internal implementations, lock‑compatibility rules, practical SQL examples, and common deadlock scenarios with detailed code illustrations.
This guide walks through implementing a complete product management module in a Java e‑commerce system, covering Snowflake distributed ID generation, database schema design for SPU and SKU tables, CRUD operations, category‑brand linking, audit workflow, status toggling, batch processing, and logical deletion, all with concrete code examples.
