This article demonstrates how to use MySQL Shell's DBA component to deploy multiple MySQL sandbox instances of different versions, customize their deployment directories, and configure instance parameters through command‑line options and MySQL configuration files.
This guide explains how to use Percona Toolkit's pt-archiver for MySQL data archiving, covering installation, basic and bulk commands, underlying two‑phase‑commit logic, performance trade‑offs of various parameters, and best‑practice tips for safe, efficient archiving and deletion.
This guide explains how to restore a mistakenly dropped MySQL table by enabling binlog, creating backups, extracting relevant statements from the binlog, editing out DROP commands, and re‑importing the data to recover the table to its state before deletion.
During routine MySQL maintenance a manual backup failed because the server could not parse the last GTID from the backup log, which was traced to an incorrect gtid_mode value (OFF_PERMISSIVE) and resolved by setting gtid_mode to ON and restarting the instance.
This tutorial walks through setting up MySQL master‑slave replication using Docker, configuring ShardingSphereJDBC for read‑write splitting, and integrating it into a Spring Boot project with MyBatis‑Plus, covering environment preparation, core theory, detailed configuration, and testing procedures.
This weekly roundup shares community and industry news, open‑source project updates, bug fixes, and upcoming plans for the DBLE distributed middleware, DTLE data‑transfer component, and SQLE SQL‑audit tool, along with links to detailed articles and recruitment notices.
The July 2023 DB-Engines ranking shows Oracle, PostgreSQL and MongoDB slipping, while MySQL and Microsoft SQL Server climb, and provides detailed scores, trend charts, and the methodology behind the monthly popularity scores for the top ten database systems.
The SQLE 1.2207.0-pre2 preview release introduces a JetBrains IDE audit plugin, supports incremental smart scanning for slow logs and TiDB audit logs, provides updated dashboards, optimizations, bug fixes, and includes detailed deployment links and a complete list of new features and improvements.
The article explains the impact of slow SQL on system resources, outlines priorities for addressing performance bottlenecks, describes MySQL's execution process, shows how to enable and analyze slow‑query logs, and provides practical SQL optimization techniques such as avoiding subqueries, using IN, and eliminating unnecessary ORDER BY clauses.
This article explains MySQL's multi-version concurrency control (MVCC), describing how version chains, undo logs, and ReadView work together to implement isolation levels, prevent dirty reads, non‑repeatable reads, and phantom reads, and how RC and RR isolation differ in practice.
This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.
This article explains why and how to apply database sharding, introduces key terminology, compares global ID generation methods such as auto‑increment, UUID, COMB and Snowflake, outlines sharding algorithms, discusses challenges like distributed transactions, and presents practical expansion and implementation solutions.
This article explains the lifecycle of MySQL InnoDB redo logs—from generation during DML/DDL operations, through writing into the log buffer, to being flushed into the redo log file and finally persisted on disk—detailing the roles of mtr, log_writer, and log_flusher threads.
This article introduces the ProxySQL Binlog Reader component, explains its background in read/write splitting and GTID‑based consistency, demonstrates how to configure MySQL servers, create a dedicated user, start the reader on master and replica, and verify its operation through logs and session information.
This article recounts a backend team's experience of addressing numerous slow API endpoints, detailing root causes such as MySQL slow queries, complex business logic, thread‑pool and lock misconfigurations, and offers practical solutions including pagination fixes, indexing, query refactoring, concurrency improvements, and caching strategies.
This guide explains how to use MySQL stored procedures, events (timers), and triggers—including their creation, variable handling, control structures, parameter passing, loops, scheduling options, and practical examples—to automate reporting, data monitoring, and business logic directly at the database layer.
This guide explains how to prepare the environment, install required dependencies and a newer GCC toolset, and then compile, build, and install Percona XtraBackup 8.0 from source on an Aarch64 Kylin v10 system, including troubleshooting tips for common issues.
Explore the inner workings of MySQL InnoDB indexes, from row formats like Compact, Redundant, and Dynamic to page structures, B+Tree organization, and practical indexing strategies, complete with diagrams and tips for efficient query optimization and index design.
After a month of testing a MySQL dual‑master high‑availability setup, the author details the numerous pitfalls encountered—including primary key collisions, sync failures, and data inconsistencies—and explains why they switched to a simpler master‑slave configuration, providing step‑by‑step instructions for the conversion.
The article examines how a large transaction in MySQL 5.7 can block the SHOW MASTER STATUS command by holding the lock_log during the commit's flush stage, analyzes the commit and SHOW MASTER STATUS code paths, and proposes mitigation strategies such as avoiding big transactions and using SELECT @@global.gtid_executed.
The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.
The article examines the MySQL class com.mysql.cj.jdbc.MysqlConnectionPoolDataSource, revealing that despite its name it creates new connections each request and only recycles idle ones, provides a practical Groovy example, highlights pitfalls such as duplicate setURL methods, and concludes it’s unsuitable as a true pool for testing.
This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.
The SQLE 1.2207.0‑pre1 pre‑release introduces OceanBase MySQL‑mode audit plugins, exclusive OceanBase rules, enhanced table‑name case checks, count(*) recommendations, adds TiDB audit‑log support, fixes several MySQL‑related bugs, and outlines future plans for more OceanBase and Oracle‑mode auditing capabilities.
This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.
This article explains the DBLE split feature that partitions large mysqldump files according to sharding configuration, provides command syntax and usage examples, and presents a performance test showing that split‑based import can be up to 18 times faster than direct DBLE import while preserving data correctness.
This article examines MySQL’s limited join capabilities compared to PostgreSQL, explains why multi‑table queries over three tables can be inefficient, and explores strategies such as service‑layer joins, query decomposition, and caching to improve performance and scalability in database‑centric applications.
This article explains why traditional mysqldump and file‑based methods struggle with massive tables, introduces Alibaba DataX as a high‑performance offline data integration tool, details its architecture, and provides comprehensive installation and configuration steps for full and incremental MySQL‑to‑MySQL synchronization using JSON job files.
The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.
This article explains the MySQL max_allowed_packet limit, how packets are structured and split, what operations count as a packet, and demonstrates through tests how the setting affects SQL imports, binlog replication, query results, and LOAD DATA operations.
This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.
After a month of operating a MySQL dual‑master high‑availability cluster, the author details the numerous pitfalls encountered and explains a step‑by‑step manual process for converting the setup to a simpler master‑slave architecture, including configuration changes and Keepalived adjustments.
This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.
This article walks through a MySQL 5.6 scenario where a subquery that originally took over eight hours is accelerated by orders of magnitude through strategic index creation, query rewriting, and join optimization, illustrating the impact of single‑column, composite, and covering indexes on execution plans.
This article investigates MySQL’s recommendation against UUIDs, comparing auto‑increment, UUID, and Snowflake‑style random keys through insertion and query benchmarks, analyzing their index structures, performance impacts, and trade‑offs, and concludes why sequential primary keys generally outperform non‑sequential identifiers.
This article presents a complete food delivery system project built with Spring Boot and Vue.js, detailing its technology stack, modules, database setup, and step‑by‑step instructions for launching both the admin backend and mobile client, along with resource download links.
The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.
This article explains how to extend the dble distributed middleware with ClickHouse to provide OLAP capabilities, covering environment setup, XML configuration of user.xml, db.xml and sharding.xml, command‑line testing, data import, and a sample architecture that combines dble, MySQL and ClickHouse.
This article explains how MySQL COUNT() works under different storage engines, why counting rows becomes slow on large InnoDB tables, and presents practical methods such as using EXPLAIN rows, auxiliary count tables, batch processing, and transaction‑based updates to obtain approximate or exact row counts efficiently.
This article describes a complete solution for per‑process monitoring of CPU, memory, disk I/O and network usage on Linux servers, covering the shortcomings of existing tools, data collection methods, aggregation logic, storage in MySQL, visualization with Grafana, deployment steps, and operational best practices.
The article investigates a MySQL 5.6 instance that consumed nearly all memory on a 32 GB test server, identifies misconfigurations, excessive MyISAM file handles, and severe memory fragmentation, and demonstrates how switching to tcmalloc reduced memory usage to a stable 5 GB.
This article introduces a music website built with Vue 3 on the client side and Spring Boot + MyBatis on the server side, detailing its features, technology stack, development environment, and step‑by‑step instructions for downloading, configuring, and running the full‑stack application.
The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.
This article examines why containerizing MySQL with Docker often leads to data safety risks, performance bottlenecks, state management challenges, and resource isolation problems, while also outlining scenarios and strategies where MySQL can successfully run in containers.
This article explains why undo logs can grow uncontrollably in MySQL, demonstrates how to verify and accelerate undo log truncation by adjusting purge settings in MySQL 5.7, and shows the new manual undo‑tablespace management commands introduced in MySQL 8.0.
This article investigates how many rows should be inserted per batch in MySQL by measuring the impact of packet size limits, buffer pool usage, insert buffers, transaction handling and index structures, and it provides practical recommendations based on tests with millions of rows.
This guide explains MySQL 8.0.17's Clone Plugin—how to install it, perform local and remote clones, monitor progress via performance_schema, build replicas from cloned data, understand its internal stages, configure parameters, and compare it with XtraBackup.
This article explains how MySQL’s various auto‑increment identifiers—table AUTO_INCREMENT, InnoDB row_id, Xid, trx_id, and thread_id—behave when they reach their maximum values, the resulting errors or data overwrites, and the underlying mechanisms that cause these edge‑case behaviors.
This weekly community newsletter summarizes the latest MySQL‑related articles, open‑source middleware updates for DBLE, DTLE and SQLE, bug fixes, feature developments, upcoming plans, and invites community feedback, providing a concise technical snapshot for database practitioners.
This article examines how a MySQL connection is created from a Java web application, breaking down the TCP handshake, authentication, and variable‑setting steps with Wireshark captures, measuring latency, and demonstrating why connection pooling is essential to avoid hundreds of milliseconds of overhead per request.
This article explains the differences between buffers and caches, explores MySQL’s buffer pool architecture, details write‑through/write‑back strategies, and reviews key InnoDB parameters such as innodb_flush_log_at_trx_commit and innodb_flush_method for optimizing data durability and performance.
This article explains why high‑traffic systems need database sharding, compares table‑level and database‑level partitioning methods, presents concrete MySQL table‑creation scripts and Java service code, and discusses the advantages, drawbacks, and common middleware solutions such as Sharding‑JDBC and Mycat.
This article analyzes MySQL replication anomalies caused by master failures or network interruptions that lead to incomplete transaction replay on slaves, demonstrates a reproducible experiment using network delay and iptables, and provides practical guidance for both recovering and permanently handling stalled slave IO threads.
This article explains MySQL's support for multi‑column partition tables, provides a stored‑procedure to create them, compares performance against single‑column partitions under various query conditions, discusses partition column order impact, and offers practical guidance for when and how to use multi‑column partitions.
This article compiles essential MySQL interview questions covering primary and candidate keys, date functions, trigger limits, index column counts, data copying techniques, core MySQL concepts, feature highlights, transaction ACID properties, heap versus clustered tables, numeric type differences, and objects creatable with CREATE statements.
This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.
This article explains how to configure Dble's read‑write splitting feature by preparing MySQL master‑slave instances, defining dbGroup and rwSplitUser in XML configuration files, reloading the settings, and verifying that write statements go to the primary node while reads are load‑balanced across replicas.
This article provides a comprehensive guide to MySQL index optimization, covering selectivity calculation, prefix length, composite index rules, EXPLAIN analysis, common slow‑query patterns, and practical sharding and partitioning techniques to improve query performance.
The preview release SQLE 1.2206.0-pre2 introduces enterprise‑level SQL drill‑down analysis, a refreshed login page, detailed release notes with new features and bug fixes, and outlines future plans to support more database types, while providing links to documentation, demos, and previous versions.
This article compares three stock‑deduction strategies—single‑field MySQL, sharded MySQL rows, and Redis incrby with Lua scripts—analyzes their concurrency issues, and provides a complete Spring Boot implementation using a distributed lock, Lua script, and callback for initializing stock.
The article explains how to build a reusable MySQL connection pool in Java using Apache Commons Pool2, detailing the creation of a poolable object, a factory class, the pool configuration, and a simple API wrapper for borrowing and returning connections while providing full source code examples.
This article explains the XA specification, demonstrates MySQL XA commands, and shows how Sharding-JDBC integrates XA transactions using Atomikos, including required dependencies, configuration, annotations, and testing steps, while also providing practical code examples and deployment tips.
This guide explains how to set up a lightweight database audit log platform using MariaDB's audit plugin, Rsyslog, and the LogAnalyzer web interface, covering environment preparation, plugin installation, syslog forwarding, MySQL schema creation, and LogAnalyzer configuration for real‑time monitoring and compliance reporting.
This article explains MySQL's layered design, detailing how the Server and InnoDB storage engine interact, the role of the Buffer Pool in caching data, and the functions of redo log, undo log, and binlog in ensuring durability, recovery, and replication.
This guide shows how to use Python and pandas to prepare datetime data, then insert it in bulk into Oracle and MySQL databases, handling type conversions with Oracle's TO_DATE and MySQL's STR_TO_DATE functions while managing transactions safely.
Optimistic and pessimistic locks are conceptual concurrency control strategies used across databases and caching systems; the article explains their principles, MySQL implementations with SELECT ... FOR UPDATE and version checks, compares their trade‑offs, and advises preferring optimistic locking in high‑concurrency scenarios.
This article explains the reasons for database sharding, compares vertical (scale‑up) and horizontal (scale‑out) partitioning methods, illustrates vertical database/table splits and horizontal partition strategies such as HASH, RANGE, LIST, KEY and composite, and discusses the challenges like distributed transactions, cross‑database joins and increased management overhead.
This article walks through the complete process of creating a highly available MySQL master‑master cluster using Docker containers, MySQL replication, and Keepalived for automatic health checks, virtual IP failover, and seamless traffic switching, while also sharing common pitfalls and their solutions.
This article compiles 66 MySQL interview questions covering fundamentals such as joins, data types, indexes, storage engines, transaction properties, locking mechanisms, replication, sharding, performance tuning, and high‑availability concepts for interview preparation.
Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.
This article introduces the SSL protocol, explains why encryption is needed for database communication, and provides step‑by‑step instructions for generating self‑signed certificates with OpenSSL, converting them for Java, and configuring both DBLE server and client to use SSL, including experimental verification of encrypted versus unencrypted connections.
This article analyzes why MySQL connections become invalid when using Druid with ReplicationConnection for read/write splitting, explains the underlying proxy mechanics, and presents a practical solution by extending MySqlValidConnectionChecker to correctly validate both master and slave connections.
This article examines MySQL's recommendation to use auto_increment primary keys instead of UUIDs or random keys by creating three comparable tables, inserting large data sets with Spring JdbcTemplate, and analyzing insertion speed, index structure impact, and the trade‑offs of each key strategy.
This article explains how a plain SELECT does not lock data, while SELECT … FOR UPDATE adds a pessimistic lock whose scope—row or table—depends on whether the query uses an indexed or primary‑key column, and demonstrates the behavior with SQL examples and transaction scenarios.
This article walks through setting up a high‑availability MySQL environment using master‑master replication and Keepalived on two Ubuntu servers, covering architecture design, Docker container preparation, MySQL configuration, Keepalived installation, failover testing, and troubleshooting common issues.
This article explains how the SELECT ... FOR UPDATE statement in MySQL adds a pessimistic lock, describes when it results in a row lock versus a table lock based on index usage, provides verification SQL, example scenarios, and practical tips for transaction handling.
This article walks through a real‑world case where a MySQL instance showed an abnormal 13 GB data_free value, explains how the ibtmp1 temporary tablespace caused the growth, demonstrates verification steps with sysbench and SQL queries, and provides configuration and operational solutions to prevent and resolve the issue.
An in‑depth investigation reveals that Spring’s @Transactional rollback can silently fail due to a bug in mysql‑connector‑java 8.0.28 when useLocalSessionState=true, causing auto‑commit to stay enabled; the article explains the root cause, debugging steps, and how upgrading to 8.0.29 resolves the issue.
This article recounts a MySQL high‑availability incident in a dual‑master environment, explains how missing binary‑log index files caused replication failures, and details step‑by‑step troubleshooting, directory recreation, binlog position correction, and configuration improvements to restore reliable database operation.
This article explains why the MySQL 8.0 client may report the caching_sha2_password authentication error requiring a secure connection, describes the plugin’s design, RSA and SSL mechanisms, cache behavior, and provides practical commands and replication settings to resolve the issue.
This article explains in detail what happens inside MySQL when an UPDATE statement is executed, covering the execution flow, the roles of undo log, redo log, and binlog, buffer pool mechanics, two‑phase commit, group commit, and practical optimization tips.
This article demonstrates how to calculate a running total for each code and date in a MySQL table by using user-defined variables, addresses the need to reset totals per code, and shows an optimized version with the WITH clause and indexing recommendations.
This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.
This article explains why MySQL upgrades can cause syntax, semantic and performance incompatibilities, introduces the Percona Toolkit's pt‑upgrade tool, details its detection logic and common usage patterns, provides a full demo with real query results, and lists essential parameters and precautions for safely migrating from MySQL 5.7 to 8.0.
This article demonstrates two approaches for efficiently importing massive CSV data into MySQL using Python: a direct pymysql method with chunked inserts and a concise pandas‑SQLAlchemy method, comparing performance, code complexity, and offering tips for further speed improvements.
This tutorial walks developers through installing Docker on Ubuntu, explains core concepts such as images, containers and repositories, and demonstrates practical tasks including pulling a hello‑world image, running nginx and MySQL containers, configuring network bridges, handling common errors, and building a custom Spring Boot image with a Dockerfile.
The preview release SQLE 1.2206.0-pre1 introduces TiDB audit plugin support, TiDB‑specific rules, enhanced SQL syntax checks, and recommendations on primary‑key usage, while providing full release details, documentation links, and references to prior versions for database administrators and developers.
The 2022 Gdevops Global Agile Operations Summit in Guangzhou featured a technical session by Zheng Zengquan of Aikesheng on MySQL high‑availability architecture, covering data consistency, production‑ready solutions, and a comparison of MGR versus traditional master‑slave setups, with the full PPT available for download.
This article explains MySQL index fundamentals, covering clustered and secondary indexes, best‑practice design rules such as using auto‑increment primary keys, avoiding large or low‑cardinality columns, respecting the left‑most prefix, and common scenarios that cause index loss, with practical SQL examples.
This tutorial walks through setting up a highly available MySQL master‑master cluster using Docker containers, configuring MySQL replication, and employing Keepalived for automatic failover and virtual IP management, complete with step‑by‑step commands, configuration files, and troubleshooting tips.
This article explains several methods—ranging from insecure shortcuts to recommended practices—for hiding the MySQL password‑exposure warning that appears when invoking MySQL tools from a Linux shell, providing code examples and security considerations for each approach.
The LNMP one‑click installation package V1.9 adds support for Rocky Linux, AlmaLinux, CentOS Stream 9 and several Chinese Linux distributions, introduces PHP 8.1, new PHP extensions, MySQL 5.7/8.0 binaries, IPv6 and ZeroSSL options, and provides detailed management commands for deploying and maintaining Nginx, MySQL, PHP and related services.
The article recounts a production‑like MySQL dual‑master HA setup using Keepalived, describes how a missing binary‑log index caused replication failure, and details step‑by‑step troubleshooting commands, configuration fixes, and preventive measures to restore reliable database synchronization.
This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.
This article explains MySQL's internal execution process—including the connector, cache, parser, optimizer, and executor—describes privilege tables and verification, discusses query caching removal, outlines logical SQL execution order, and provides practical tips for ordering WHERE conditions to improve performance.
This article explains how to properly set file and process permissions for Nginx, PHP‑FPM, and MySQL on Linux servers, recommending the use of a dedicated www user group, showing configuration snippets, and illustrating how to verify running processes to enhance security and avoid permission‑related errors.
This article provides a detailed guide on optimizing MySQL performance, covering SQL statement tuning, index usage, table design normalization, system configuration, hardware choices, and the internal architecture of MySQL to help developers write faster and more efficient queries.
This article enumerates twelve typical reasons why MySQL queries become slow—ranging from missing or ineffective indexes, type conversions, OR/LIKE patterns, composite‑index misuse, built‑in functions, large tables, deep pagination, join overload, oversized IN clauses, dirty‑page flushing, order‑by filesort, lock contention, delete‑in pitfalls, and group‑by temp tables—and provides concrete SQL examples, execution‑plan explanations, and optimization techniques for each case.
This article presents ten practical techniques—ranging from avoiding chat‑tool SQL transmission to using logical deletes, backups, and temporary tables—to help developers and DBAs safely execute and modify data in production environments while minimizing accidental data loss.
This article examines MySQL's ON DUPLICATE KEY UPDATE clause, explains its official behavior, runs experiments with primary and multiple unique indexes, compares InnoDB row‑level locking to MyISAM table‑level locking, and highlights performance and deadlock risks that developers should avoid.
