This guide explains how to create automated backup plans for MySQL and MariaDB databases on Jelastic PaaS, covering script configuration, cron scheduling, remote storage options, and restoring backups through phpMyAdmin, ensuring data safety and easy recovery.
This article explains the different forms of MySQL's COUNT function, compares COUNT(*), COUNT(1) and COUNT(column), reveals engine‑specific optimizations for MyISAM and InnoDB, and provides guidance on which form to use in practice.
This article explains the fundamentals of MySQL indexing, tracing the evolution from binary search trees and balanced AVL trees to B‑trees and B+‑trees, and details how InnoDB implements clustered and non‑clustered indexes, including search processes and performance considerations.
This article explains why MySQL uses B+‑tree indexes by tracing the evolution from binary search trees through balanced trees and B‑trees, detailing the structure of clustered and non‑clustered indexes in InnoDB and illustrating how data is searched using these indexes.
The article explains how MySQL multi‑source replication can be employed to back up, horizontally or vertically partition, and merge data from several servers into a single target database, illustrating three typical scenarios and providing example SQL for data consolidation.
This article walks through a MySQL 5.6 scenario where a nested subquery to find students scoring 100 in Chinese runs for over 30,000 seconds, then demonstrates how adding single‑column and multi‑column indexes, analyzing execution plans, and applying join optimizations can reduce the runtime to milliseconds, while also covering best practices for index design and query tuning.
This article explains the purpose and configuration of MySQL's join_buffer_size for INNER JOIN operations without indexed join keys, compares Nested Loop, Block Nested Loop, and the newly introduced Hash Join algorithms, and shows how each affects execution plans and query performance.
This guide explores advanced MySQL techniques—including ON DUPLICATE KEY UPDATE for upserts, INSERT IGNORE to bypass errors, replacing subqueries with JOINs, handling LEFT JOIN pitfalls, efficient pagination strategies, multi‑table UPDATE/DELETE via JOIN, and the nuances of COUNT(*) versus COUNT(1).
This article explains the internal representation and meaning of MySQL Handler_read_* status variables, demonstrates how InnoDB implements each handler, and shows practical query tests that illustrate how these counters reflect full table scans, index scans, range scans, and join operations.
This article explains how to accelerate MySQL data insertion by using multithreaded inserts for single and multiple tables, prepared statements, batch execution, multi-value SQL, and transaction commits, providing detailed code examples and performance analysis.
This article explains the concept of response time (RT) for MySQL queries, illustrates how network delays and server load affect RT, and provides two practical monitoring methods—using Percona's tcprstat tool and the MySQL Query Response Time plugin—along with installation commands, sample outputs, and visualization tips.
The article explains how MySQL Group Replication handles unintended member exits, introduces the OFFLINE_MODE option added in 8.0.18, and shows how DBAs can configure and query the system to maintain high availability during failures.
This guide explains how to create a production‑grade, near‑real‑time MySQL data‑synchronisation and query platform using Alibaba's open‑source Otter middleware, covering architecture, key features, installation steps, configuration of manager and node components, and best practices for safe query operations.
This article explains how multithreaded inserts, prepared statements, multi‑value SQL, and batch transaction commits can dramatically improve MySQL insert performance by reducing connection overhead, parsing time, and network I/O, achieving insertion of 100 000 rows in roughly ten seconds.
A detailed case study shows how a DELETE statement executed inside a MySQL stored procedure can switch binlog format to ROW, causing billions of bitmap operations on a partitioned table and resulting in severe master‑slave replication delay, and presents three practical solutions.
This article details the challenges and solutions encountered when migrating Fengchao's high‑throughput payment platform—handling over a hundred‑billion rows and millions of QPS—from a sharded MySQL architecture to TiDB, covering performance bottlenecks, TiDB advantages, migration steps, tooling, pitfalls, and optimization recommendations.
This weekly community newsletter highlights the open‑source launch of the TXLE distributed transaction framework, DBLE user meetups, deep dives into MySQL slow‑query analysis and EXPLAIN ANALYZE, recent DBLE feature developments, bug fixes, community Q&A, and a preview of next week's technical plans.
The article explains how MySQL 8.0 introduced the instant algorithm for adding columns, compares it with older copy and inplace‑rebuild methods, shows usage examples, performance test results, and outlines the restrictions and version differences of this fast DDL operation.
MySQL 8.0.18 adds the EXPLAIN ANALYZE feature, which runs a query and reports actual execution times, row counts, and loop iterations, enabling developers to compare estimated costs with real performance and fine‑tune their queries for better efficiency.
This comprehensive guide explains MySQL’s logical architecture, common performance bottlenecks, and practical techniques for writing efficient SQL, designing tables, configuring parameters, and optimizing indexes to maintain system stability during updates and scaling.
This article describes a practical method for monitoring the daily update timestamps of dictionary files on hundreds of servers, using a MySQL‑backed central registry, a public query API, per‑server scripts, and scheduled monitoring jobs that generate alerts and dashboard displays.
This article examines MySQL's event scheduler architecture, detailing its core classes, execution flow, heap insertion and deletion algorithms, and investigates two critical bugs—slow‑log misrecording and delayed event execution—while proposing concrete fixes to improve reliability.
The article explains why MySQL's legacy "utf8" charset only supports three‑byte characters, causing storage errors for true four‑byte UTF‑8 symbols, and advises developers to migrate all MySQL/MariaDB databases to the proper "utf8mb4" charset.
This article investigates Alibaba's recommendation against joining more than three tables by designing a MySQL experiment with large synthetic datasets, analyzing performance results, comparing with Oracle, and providing full SQL and data‑generation scripts to illustrate the underlying reasons.
This article systematically explains MySQL's logical server architecture, the various lock types (shared, exclusive, intention, record, gap, next‑key, insert‑intention, AUTO‑INC), transaction fundamentals, isolation levels, MVCC mechanics, and practical deadlock detection and avoidance techniques, complete with real‑world SQL examples.
This article analyzes the MySQL slow query log in Percona 5.7, explaining how timestamps, execution time, lock time, and various log fields are derived from the source code, and clarifies the meaning of each output component for accurate performance debugging.
This article explains how to use a Groovy script to crawl over 7 million historical weather entries for 3,200 cities spanning 2011‑2019, process the JSON responses, and store the cleaned data into a MySQL table, while sharing practical tips and code snippets.
Understanding MySQL indexes—from hash tables, ordered arrays, and B+ trees—to their practical use cases, including clustered and secondary indexes, reveals why they boost query speed, when they may hinder performance, and how factors like selectivity, dirty page flushing, type mismatches, and function usage affect query efficiency.
This article walks through the complete evolution of a legacy monolithic database system to a distributed MySQL architecture, covering background analysis, four migration phases, eight concrete strategies—including functional transfer, system splitting, horizontal scaling, read/write separation, and performance tuning—while providing code examples, benchmark results, and deployment considerations.
The article explains a 14‑hour discrepancy between MySQL's created_time and recovery_time timestamps, analyzes JVM and driver timezone settings, traces MyBatis and MySQL driver conversions, and provides practical fixes by adjusting MySQL server and JDBC URL timezones.
This weekly DBLE community roundup covers Amazon's migration away from Oracle databases, Percona's 2019 open‑source data management survey, the MySQL 8.0.18 GA release, and detailed DBLE and DTLE middleware updates including new features, bug fixes, and upcoming plans.
An open‑source Java crawler that fetches historical‑today events from a public API is presented, detailing three practical challenges—GET request length limits, ambiguous JSON value types, and month string construction—along with a full code example and a GitHub repository link for reference.
This article explains the MySQL Clone plugin introduced in version 8.0.17, describes its underlying mechanism, installation and execution steps, outlines usage constraints and scenarios, and presents a detailed performance comparison with LVM snapshots and Xtrabackup under various workloads.
This article explains how MySQL 8.0.18 introduced privilege checking for replication applier threads, outlines the three-step process to create a restricted‑privilege user, assign necessary permissions or roles, associate the user with the CHANGE MASTER TO statement, and discusses observability and important considerations.
The article analyzes a MySQL replication issue where a low slave_net_timeout combined with a larger MASTER_HEARTBEAT_PERIOD causes frequent zombie dump threads, leading to massive small relay‑log files, and provides detailed explanations, code references, and step‑by‑step remediation procedures.
The article presents a Java/Groovy script that crawls a website offering curated e‑books, extracts book details such as title, author, and download links using HTTP requests and regular expressions, and then inserts the collected information into a MySQL database.
This article explores MySQL's hidden rowid concept, demonstrates how to observe its existence, explains its relationship with primary keys, highlights the risks of primary‑key misuse, and provides a step‑by‑step test that reveals rowid's potential overflow bottleneck.
This article explains the structure and purpose of the MySQL slave_relay_log_info table, how relay log positions are persisted and synchronized, the role of master_auto_position and GTID, and the mechanisms of relay_log_recovery to ensure reliable replication after crashes.
This article explains why database indexes are crucial for performance, describes the fundamental principles of B‑Tree indexes, and compares how Oracle, MySQL, SQL Server, and PostgreSQL implement and use these indexes, helping developers master SQL optimization across platforms.
This article reviews MySQL replication concepts, compares asynchronous, semi‑synchronous, group, and Percona XtraDB Cluster solutions, explains row‑based versus statement‑based replication, and clears up common misconceptions about replication, clustering, backup, and load balancing.
This comprehensive guide explains Galera‑based Percona XtraDB Cluster architecture, high‑availability mechanisms, state‑transfer methods, flow‑control, deployment patterns, routine inspection, monitoring variables, backup management, common failure scenarios, and real‑world case studies for MySQL clusters.
This article examines a MySQL performance problem where frequent requests become unresponsive, analyzes processlist, system I/O/CPU metrics, and pstack traces to identify InnoDB thread concurrency and concurrency tickets as the root cause, then explains the relevant parameters, configuration guidelines, and a simulated test to demonstrate the solution.
This guide explains how to download, install, and configure Alibaba Canal—including extracting the package, setting up canal.properties, instance.properties, and instance.xml files, and tuning key parameters—to enable reliable MySQL binlog capture for big‑data pipelines.
This article introduces Alibaba's Canal project, explaining its background, architecture, and how it parses MySQL binlog for real‑time data subscription, and provides a complete Java client example with code snippets to demonstrate connecting, fetching, and processing binlog events.
This weekly community digest presents the latest DB‑Engines October ranking with MSSQL’s rebound, the Archery v1.7.0 release enhancing MySQL account management, DBLE’s new features, bug fixes and community Q&A, plus DTLE’s performance improvements and upcoming roadmap, all accompanied by relevant links and images.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, then demonstrates how to analyze their execution plans with EXPLAIN, covering key fields, common values, and practical examples for effective performance optimization.
This article explains the new NOWAIT and SKIP LOCKED keywords introduced in MySQL 8.0.1, describes their background, functionality, and internal implementation, and demonstrates their practical impact on lock‑wait scenarios through a fictional flash‑sale case study with code examples.
This article explains how MySQL 8.0 introduces the SET PERSIST and SET PERSIST_ONLY statements to modify configuration variables dynamically and persist them across restarts, demonstrates usage with both dynamic and static parameters, shows the generated mysqld-auto.cnf file, discusses required privileges, and illustrates how to query persisted settings via performance_schema tables.
This article introduces MySQL fundamentals, covering logical architecture, locking, transactions, storage engines, schema and data type design, as well as index types and their constraints, plus practical creation‑time and query‑time optimization techniques with code examples and best‑practice recommendations.
This article explains the concept of database transactions, the ACID properties, details MySQL’s four isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—and provides step‑by‑step command‑line experiments demonstrating phenomena such as dirty reads, non‑repeatable reads, phantom reads, and serialization effects.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors when storing true four‑byte UTF‑8 symbols like emojis, and shows why switching to the proper “utf8mb4” charset is essential for correct Unicode handling.
This article provides a comprehensive guide to MySQL InnoDB tablespace encryption, covering its architecture, supported plugins, configuration steps, key rotation, limitations, encrypted table operations, import/export procedures, and backup/restore methods using mysqlbackup and innobackupex.
This article demonstrates a Java‑based web crawler written in Groovy that uses regular‑expression parsing to retrieve paginated company data from a government portal, constructs SQL insert statements, and stores the results in MySQL, with full source code and structural screenshots.
This article describes a complete solution that uses Alibaba's Canal for real‑time MySQL binlog collection, Kafka for transport, and a customized Camus pipeline to load and merge binlog data into Hive, addressing performance, consistency, and delete‑event challenges in large‑scale data warehousing.
This article explains how the apply‑log‑only option in Percona XtraBackup affects MySQL restoration, illustrates the risk of data inconsistency when the option is omitted, and provides guidance on correctly using the parameter during full and incremental backup recovery to ensure complete transaction replay.
A bug was discovered where a user's VIP expiration date could not extend beyond a specific day in 2038 because the timestamp was stored in a signed INT column, whose maximum value (2147483647) corresponds to 2038‑01‑19 11:14:07, causing the system to fail to save later dates.
This article introduces mycli, a Python‑based MySQL command‑line client that offers easy installation via pip, advanced auto‑completion, high‑risk operation confirmations, customizable syntax highlighting, output paging, command bookmarking, logging, multiline editing, flexible credential handling, and additional features such as SSL support and query looping.
The article describes how to migrate a multi‑gigabyte InnoDB table from a pre‑production MySQL 5.7 instance to production using the transportable tablespace feature, detailing the step‑by‑step procedure, performance benefits, and the specific limitations of this method.
This article explains how to calculate cumulative daily user totals in MySQL using user-defined variables, demonstrates the necessary SQL queries, compares them with a Java‑style algorithm, and highlights important considerations such as variable initialization and practical pitfalls.
Effective MySQL backup and disaster recovery are essential for protecting critical business data; this guide explains backup types, tools like Percona XtraBackup, scheduling, local and remote strategies, incremental processes, preparation and restoration steps, and introduces a platform for automated backup management.
This guide walks through configuring JMeter to connect to MySQL and Oracle databases via JDBC, covering driver installation, JMeter component setup, request creation, result verification, and troubleshooting common connection errors for performance testing.
This article provides a comprehensive MySQL replication FAQ covering cross‑OS and hardware compatibility, master‑slave connection behavior, monitoring lag, forcing master pause, bidirectional replication considerations, performance improvements, high‑availability setups, and how to exclude GRANT/REVOKE statements from replication.
This article demonstrates how to use MySQL Shell's util.import_table and util.import_json functions to import CSV and JSON data efficiently, compares their performance with the traditional mysqlimport tool, explains required options such as file path, schema, table, dialect, threading, and shows example sessions in both JavaScript and Python modes.
This article examines how MySQL's FLUSH TABLE WITH READ LOCK command can cause blocking, detailing two cases where global read locks or table cache flushes stall other operations, explaining the underlying MDL lock mechanisms, table cache handling, and providing code examples and debugging insights.
This article explains how multithreaded inserts into a MySQL table can be faster than single‑threaded inserts by reducing connection and parsing overhead, and it details techniques such as prepared statements, multi‑value inserts, and batch transaction commits to boost performance.
MySQL 8.0 introduces Hash Join for dramatically faster large‑dataset queries, supports in‑memory and spill‑to‑disk processing, can replace Block Nested‑Loop joins, and adds EXPLAIN ANALYZE with detailed runtime statistics and tree‑style output, highlighting the platform’s rapid evolution.
This article explains MySQL thread fundamentals, distinguishes PID, LWP ID and Thread TID, shows a small C program to observe these IDs, demonstrates how to map MySQL threads to LWP IDs using system tools and SQL, and provides a step‑by‑step guide to set up a GDB‑based debugging environment on Linux.
This article reviews various distributed ID generation strategies—including MySQL auto‑increment tables, multi‑master configurations, segment allocation, Snowflake algorithm, and open‑source solutions like Baidu uid‑generator and Meituan Leaf—highlighting their designs, trade‑offs, and implementation details for high‑performance backend systems.
This article explains the internal workflow of the gh‑ost online DDL tool, covering its execution steps, binlog handling, data consistency analysis, cut‑over process, lock management, and failure scenarios, with detailed code examples and practical insights for safe schema changes in MySQL.
This article introduces the gh-ost tool for performing online schema changes on large MySQL tables, explains its architecture and operation modes, provides detailed command‑line parameters and practical examples, and compares its features and performance with the traditional pt‑osc solution.
The author records API request durations in a MySQL table, then uses Python, pandas, and Plotly to fetch the data, filter out outliers, and generate a violin chart that clearly visualizes the distribution of response times for multiple endpoints.
This article explores common MySQL DATETIME timestamp precision issues caused by mismatched mysql‑connector‑java and server versions, demonstrates three practical solutions, and provides a step‑by‑step case study with code examples to help developers choose the right time field type and avoid timezone pitfalls.
This article explains how to update and use a Java HttpClient‑based spider that removes duplicate links, handles legacy page formats, extracts movie metadata and download URLs (magnet, ed2k, Baidu Pan), and stores the results in a MySQL database, with complete source code examples.
MLOG_CHECKPOINT is a mechanism in MySQL InnoDB crash recovery that ensures complete mapping between space IDs and file paths by supplementing missing MLOG_FILE_NAME records, addressing gaps that could occur between checkpoints and subsequent file modifications.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting slow subqueries to efficient joins, and applying covering indexes and ordering optimizations, with detailed examples and timing results.
This article compiles essential Java interview questions covering language fundamentals, core APIs, collections, concurrency mechanisms, JVM internals, design patterns, database concepts, and Netty networking, providing a thorough reference for candidates preparing for backend development positions.
This article explains how MySQL's slave_rows_search_algorithms setting and the presence of primary or unique indexes affect the way a replica locates rows for DELETE operations, compares index‑scan, table‑scan, and hash‑scan methods, and provides practical guidance to reduce replication lag.
This article explains several practical techniques—including using the performance_schema.metadata_locks view, events_statements_history, a gdb script, and filtered SHOW PROCESSLIST queries—to locate and terminate the MySQL session that holds a global read lock, thereby restoring normal write operations.
This comprehensive guide walks you through MySQL optimization philosophy, risk management, stakeholder involvement, practical tools, hardware and system tuning, kernel parameter adjustments, and detailed MySQL configuration tweaks to achieve both performance gains and reliable stability in production environments.
This article explains the functional and performance differences among MySQL's count(*), count(1), and count(column) expressions, illustrates how NULL values affect each, and provides a practical example with query results to guide optimal usage.
This weekly MySQL community newsletter highlights September 2019 DB‑Engines rankings, offers free Mycat diagnosis, summarizes DBLE and DTLE development updates, and showcases original technical articles on WRITESET parallel replication, InnoDB primary‑key optimization, MySQL 8.0 error‑log enhancements, and query optimization, inviting community feedback.
Canal, an open‑source Alibaba project, mimics MySQL slave behavior to subscribe to binlog events, parses them, and supports both standalone and ZooKeeper‑coordinated cluster deployments, offering flexible state storage, message processing pipelines, and integration options such as TCP, Kafka, and RocketMQ for real‑time data synchronization.
This guide explains how to configure replication from a MySQL 8.0 master to a MySQL 5.7 slave, covering supported version combinations, character‑set adjustments, authentication plugin changes, user creation, CHANGE MASTER commands, status verification, quick testing, and common pitfalls when using newer MySQL features.
MySQL 8.0 adds JSON‑formatted error logs and flexible filtering components, allowing administrators to install JSON sink and filter modules, extract specific fields with tools like jq, and apply rules that drop or throttle messages to improve log readability and automation.
This article provides a comprehensive guide to MySQL performance optimization, covering database design principles, field type selection, indexing strategies, storage engine choices, query caching, partitioning methods, master‑slave replication setup, read/write splitting, load balancing, and profiling tools for diagnosing slow queries.
This article explains how to use Apache ShardingSphere's data‑masking (encryption) features to protect both new and existing MySQL/Oracle/PostgreSQL/SQLServer workloads, providing detailed YAML configurations, migration steps, advantages, applicable scenarios, and limitations for secure database operations.
This article explains how InnoDB stores data using B‑Tree primary keys, why explicit primary keys are essential, and compares ordered versus random insert patterns, illustrating their impact on page fill factor, index size, and query performance with practical sysbench examples.
To meet growing e‑commerce search demands, the team replaced a MySQL‑based intermediate table with a real‑time binlog‑driven pipeline that streams changes through Kafka into Elasticsearch, detailing design choices, ordering and completeness guarantees, custom modules, and monitoring for sub‑second sync latency.
This article summarizes a MySQL webinar covering cost‑based query optimization, monitoring and analysis tools, data‑access methods and index selection, join optimization techniques, and ways to influence the optimizer through hints and configuration, providing a concise technical reference for database practitioners.
This article presents a step‑by‑step analysis of several real‑world MySQL slow‑query problems, demonstrates how to reproduce the issues, examines execution plans, applies concrete optimizations such as removing function calls, adjusting date comparisons, adding bigint indexes, and explains the impact of different EXPLAIN types on performance.
This comprehensive guide explains MySQL optimization philosophy, required participants, key dimensions, essential tools, step‑by‑step tuning procedures, hardware and system tuning, and detailed MySQL and InnoDB parameter settings to improve performance and reliability.
This article explains MySQL's write‑set based parallel replication, detailing how writeset hashes are generated, stored in a historical map, and used to adjust the last‑commit value for improved parallel replay, including configuration parameters, code examples, and the impact of different dependency‑tracking modes.
This article explains why retrieving a MySQL table's on‑disk size via INFORMATION_SCHEMA.TABLES is unreliable, describes the impact of storage engines and row formats, and shows how to obtain accurate sizes by querying INNODB_SYS_TABLESPACES, including considerations for compression and configuration settings.
This article demonstrates how to use MySQL's stacked diagnostics and a custom stored procedure to automatically capture and log errors from bulk INSERT statements during Oracle‑to‑MySQL migration, enabling quick identification and correction of problematic rows without manual inspection.
This article explains how MySQL's InnoDB engine marks rows as deleted without freeing disk space, demonstrates the issue with 100,000 rows, and shows how the OPTIMIZE TABLE or ALTER TABLE commands can rebuild the table to reclaim space and improve performance.
This article presents nineteen practical MySQL optimization methods—including using EXPLAIN, limiting IN clause size, specifying columns in SELECT, proper use of LIMIT, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, leveraging full‑text indexes, and applying join and index best practices—to improve query performance and reduce resource consumption.
A production server suffered catastrophic data loss after an rm -rf command erased the entire disk, prompting a frantic recovery effort that leveraged ext3grep, extundelete, and MySQL binlog to restore critical MySQL tables and bring the service back online.
This guide explains how to restore MySQL InnoDB tables and their dictionary metadata after accidental drops, corruption, or disk failures by using the undrop‑for‑innodb toolkit, parsing system tables, and executing a series of command‑line steps to rebuild schema and data.
This guide compiles nineteen practical MySQL optimization tips—from using EXPLAIN and avoiding costly IN clauses to leveraging full‑text indexes, proper pagination, and join strategies—providing concrete SQL examples and actionable advice to improve query performance and reduce resource consumption.
This article investigates a MySQL slow query caused by a subquery that triggers a full‑table scan, explains MySQL's query rewrite behavior, and shows how Django's lazy ORM evaluation can worsen performance, offering practical fixes using explicit query execution or join rewrites.
