The article explains why MySQL master‑slave replication lag occurs, lists common causes, provides a five‑level troubleshooting framework, and offers three concrete recovery methods—from emergency error skipping to multi‑threaded replication and long‑term architecture improvements—plus commands, configurations, and monitoring tips.
This comprehensive guide explains why MySQL master‑slave replication can fall behind, walks through systematic diagnosis of common lag scenarios, and provides concrete configuration tweaks, parallel replication settings, GTID usage, monitoring queries, and upgrade paths to eliminate delay and improve reliability.
This comprehensive guide explores MySQL 9.0's replication architecture, covering core principles, GTID and semi‑synchronous modes, various replication topologies, detailed troubleshooting steps, monitoring scripts, and best‑practice configurations, providing hands‑on examples and scripts to help engineers build and maintain robust, high‑availability database systems.
This guide presents practical, DBA‑tested MySQL replication best practices—including GTID, row‑based replication, mandatory primary keys, schema consistency, binlog tuning, read‑only protection, dedicated replication users, latency monitoring, parallel workers, and SSL encryption—to ensure reliable, predictable replica behavior.
This guide explains how to achieve uninterrupted MySQL service by using semi‑sync replication, configuring MHA for automatic failover, deploying Orchestrator for topology management, and applying VIP‑based scripts and GTID settings to guarantee zero data loss during master switches.
This article explains the design and implementation of Ctrip's Data Replication Center (DRC), a MySQL‑based high‑availability system that solves cross‑region data loop, progress tracking, concurrency, DDL handling, and conflict resolution to achieve low‑latency, reliable data replication for global travel services.
This comprehensive guide explains how to diagnose MySQL master‑slave replication lag, enable GTID mode, configure semi‑synchronous replication, optimize multi‑threaded replication, set up monitoring and alerting with Prometheus, and perform zero‑data‑loss failover using tools like Orchestrator and custom scripts.
An experienced DBA recounts a midnight MySQL replication disaster where slave lag exceeded 60 seconds, then dissects the root causes—binlog formats, semi‑synchronous replication, and GTID—offering detailed configurations, performance tweaks, monitoring scripts, and best‑practice recommendations to prevent and resolve such failures.
This tutorial walks through preparing the Linux environment, installing MySQL 5.7, configuring master and slave servers, creating replication users, verifying synchronization, troubleshooting common issues, and applying advanced options such as GTID and semi‑synchronous replication, all with complete command examples.
This guide walks you through preparing multiple virtual machines, configuring MySQL server IDs, setting up both classic and GTID‑based master‑slave replication, and managing the replication with essential commands, complete with code examples and architecture diagrams.
This article explains how to extend an existing MySQL master‑slave replication filter to include additional databases by using a low‑cost backup‑restore workflow, detailing two solution options, the chosen approach, and step‑by‑step commands for stopping replication, backing up, setting GTID points, restoring, and updating filter rules.
This guide walks you through preparing multiple virtual machines, configuring MySQL 8.0 server IDs, setting up both classic and GTID‑based master‑slave replication, and managing replication commands, providing clear code snippets and commands for a reliable MySQL clustering environment.
The article analyzes a MySQL 5.7 replication bug where, after consecutive crashes, the slave ends up with more GTIDs than the master because the last binlog’s GTIDs are not persisted, and demonstrates the problem using GDB debugging and step‑by‑step reproduction.
This article examines whether a MySQL replica can safely omit setting sync_binlog=1, explains the underlying InnoDB and GTID mechanisms, presents an experiment reproducing power‑loss scenarios, and analyzes how different master_auto_position settings affect data consistency and GTID continuity during recovery.
The article analyzes a MySQL replication error where a MEMORY table is cleared after a master restart, explains why the binlog records only a statement despite row format, and provides step‑by‑step troubleshooting and remediation measures to ensure data consistency.
This article explains how to identify and extract large MySQL transactions from binary log files by monitoring binlog size, parsing GTID information, and using shell scripts to retrieve transaction details, timestamps, positions, and DML statistics, with optional use of the my2sql tool.
This article explains MySQL 8.3’s new ability to tag GTIDs, describes the original and extended GTID formats, demonstrates how to enable and use tagged GTIDs with mysql-shell, and shows how tagged and non-tagged transactions appear in replication logs.
MySQL 8.3.0, released on January 16, 2024, adds enterprise masking refresh, OpenTelemetry telemetry, GTID extended format, automated Windows upgrades, and a new EXPLAIN JSON version while removing several legacy server options and replication variables.
Version 8.3 of MySQL introduces tagged GTID replication, a selectable JSON EXPLAIN format version, enhanced thread‑pool plugin information, changes to binlog transaction dependency tracking and new CMake options, while removing several C API functions, the FLUSH HOSTS statement, and outdated replication flags.
This article explains the meaning, generation process, and operational impact of the new immediate_commit_timestamp and original_commit_timestamp fields in MySQL 8.0 binlog events, including how they affect replication delay calculations, warning handling, and precise monitoring of transaction commit times.
This article explains why a MySQL 5.7.30 upgrade with GTID enabled causes a GTID‑consistency error for CREATE TABLE ... SELECT statements, describes the underlying mechanism, provides a safe two‑step workaround, and notes that MySQL 8.0.21+ now supports atomic DDL for this operation.
This article explains why the dbops tool does not support MySQL master‑master architecture, describes the two common dual‑master patterns, outlines their drawbacks such as role‑confusion, dirty‑data risk, split‑brain, and update loss, and provides practical workarounds and step‑by‑step deployment instructions to safely implement or avoid dual‑master setups.
The article explains why master‑master MySQL architectures are discouraged, outlines the pitfalls of dual‑write setups, and provides step‑by‑step instructions using dbops to deploy a master‑slave topology and manually create a reversible master‑master configuration for testing.
This article examines a MySQL 5.7 replication anomaly where a slave reports no errors despite GTID mismatches, demonstrates how to reproduce the issue, explains the underlying cause related to missing Auto_Position=1, and provides remediation steps and source‑code insights.
This article provides a detailed source‑code walkthrough of MySQL 8.0.33’s two‑phase commit process, explaining the Prepare and Commit stages, internal structures such as binlog queues, GTID generation, redo‑log flushing, sync handling, and the interactions between InnoDB and the binary log.
This guide walks through a real‑world MySQL master‑slave sync failure caused by a corrupted relay log, explains the replication mechanics, shows how to analyze slave status, use GTID to skip the bad transaction, and fully restore synchronization with step‑by‑step commands and screenshots.
This article explains a MySQL master‑slave synchronization failure caused by a corrupted relay log, walks through the replication principles, analyzes slave status and GTID differences, and provides step‑by‑step commands to skip the problematic GTID and restore replication.
MySQL replication copies data changes from a primary server to one or more replicas using binlog events—supporting statement, row, or mixed formats and GTID positioning—to provide real‑time backup, read‑write separation, high‑availability failover, and integration pipelines via asynchronous, semi‑synchronous, or centralized binlog services.
This article examines MySQL’s transition from dual‑master to master‑slave replication, highlights the complexities of position‑based syncing, introduces GTID as a more reliable alternative, and provides practical steps to enable GTID and accurately monitor replication lag using seconds_behind_master, binlog positions, and GTID sets.
This article explains the transition from dual‑primary MySQL to a master‑slave read/write split, outlines the pain points of position‑based replication, and demonstrates how GTID simplifies setup, failover, and monitoring of replication lag while ensuring data consistency.
This article explains MySQL 5.7’s enhanced multi‑threaded slave (MTS) parallel replication, contrasting it with the schema‑based approach of MySQL 5.6, detailing its group‑commit mechanism, configuration parameters, GTID handling, performance testing, and practical tuning tips for optimal replication throughput.
This article explains how Orchestrator selects a candidate replica during MySQL master failover, detailing the GetCandidateReplica and RegroupReplicasGTID functions, their sorting logic, promotion rules, GTID-based regrouping, and differences from MHA, while highlighting potential data loss issues and related bugs.
The article explains why a recent write may be invisible when reading from a MySQL replica, analyzes the replication threads and latency causes, and presents practical solutions such as forcing master reads, lag detection, semi‑sync mode, and GTID‑based waiting, with examples from Sharding‑JDBC, MyCat and MaxScale.
After a write to the MySQL master, reads from a slave can return old or missing data due to replication lag, and this article explains the underlying asynchronous replication mechanisms, the involved threads, and presents practical solutions such as forcing master reads, delay detection, semi‑sync mode, GTID waiting, and middleware implementations like Sharding‑JDBC, MyCat, and MaxScale.
This article explains the principles of MySQL semi‑synchronous replication, analyzes how data consistency is maintained during high‑availability failover, presents detailed step‑by‑step transaction flow, discusses scenarios causing GTID divergence, and offers testing methods and remediation techniques for DBA practitioners.
This article explains the risks of misusing the sql_slave_skip_counter parameter in MySQL replication, demonstrates a step‑by‑step test environment with both transactional and non‑transactional tables, analyzes the resulting binlog events, and provides practical methods to reset the counter safely.
This article explains how MySQL 8.0’s clone plugin changes GTID handling by storing GTID information in InnoDB undo logs, detailing the allocation of undo log segments, recording GTIDs in undo log headers, persisting them to the gtid_executed table, and coordinating purge operations.
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 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.
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 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 analyzes a MySQL replication failure where the slave reports error 1236 "Could not open log file", explains the root cause involving duplicate entries in the binlog index after manual deletion of active binlog files, and provides step‑by‑step reproduction procedures and preventive recommendations.
This article provides a step‑by‑step tutorial on deploying a DBLE cluster, configuring sharding.xml, db.xml and user.xml, creating test tables, installing DTLE, performing full MySQL‑to‑DBLE data migration, then switching to GTID‑based incremental synchronization with detailed troubleshooting tips and best‑practice recommendations.
The MHA Re-Edition tool revives the discontinued MHA manager for MySQL, adding GTID‑based failover, password‑only SSH authentication, lightweight binaries, VIP migration, WeChat alerts, remote‑card reboot, and detailed configuration options, with step‑by‑step deployment instructions and sample app1.cnf parameters for high‑availability clusters.
This article examines how MySQL slaves recover their replication state in GTID‑based and binary‑log‑file‑position scenarios, identifies unsafe factors that can cause crash‑unsafe behavior, and explains how enabling relay_log_recovery=on mitigates these risks while outlining remaining limitations.
This article analyzes a MySQL replication failure caused by stale GTID execution data and unsynchronized binlog persistence, explains the underlying mechanisms, demonstrates reproducible testing, and provides two practical remediation methods to skip duplicate-key errors and restore replication.
This article explains MySQL GTID (Global Transaction Identifier), covering its composition, lifecycle, how GTIDs are generated during commit, the mechanisms that maintain GTID state, and practical examples that illustrate GTID behavior in replication scenarios.
This article explains the concepts behind MySQL GTID replication, compares GTID‑based and binlog‑position‑based replication, and provides step‑by‑step commands for safely enabling and disabling GTID mode online with minimal impact on production workloads.
This article provides a practical guide to using mysqlbinlog for parsing and replaying MySQL binlog files, covering options for time, position, and GTID ranges, as well as important parameters such as --no-defaults, -v, --base64-output, and --skip-gtids.
The article investigates a bug introduced in MySQL 5.7.36 where mysqldump writes the SET @@GLOBAL.GTID_PURGED statement at the end of the dump file, causing GTID and data inconsistencies that break replication, and recommends using an older mysqldump version until the issue is fixed.
This article examines MySQL 5.7 replication issues caused by GTID settings during database migration, detailing environment setup, problem description, reproduction steps, analysis, conclusions on --set-gtid-purged usage, and practical solutions to ensure consistent in master‑slave synchronization.
This guide walks through exporting business databases, re‑initializing the MySQL instance, importing data, extracting GTID information, creating replication and monitoring accounts with helper functions, resetting logs, and finally re‑joining the node to a MySQL Group Replication cluster.
This article explains the definitions of QPS (queries per second) and TPS (transactions per second) in MySQL, compares the various calculation methods—including Queries‑per‑second, Questions‑per‑second, and GTID‑based approaches—and recommends the most reliable ways to measure database load and transaction throughput.
This article explains MySQL master‑slave replication fundamentals, including basic synchronization, circular replication issues, lag handling, failover strategies, parallel replication techniques, GTID usage, and read‑write splitting, providing practical guidance for reliable high‑availability database deployments.
This article explains the basic principles of MySQL master‑slave architecture, the role of read‑only replicas, loop‑replication problems, master‑slave delay measurement, various failover strategies, parallel replication mechanisms across MySQL versions and MariaDB, GTID usage, and read‑write splitting techniques, providing practical commands and diagrams for each concept.
This guide explains how to safely recover MySQL data using full backups, incremental binlog recovery, point‑in‑time restores, single‑table extraction, and methods to skip erroneous SQL statements via GTID or delayed replication, complete with practical command examples.
This article provides a comprehensive, hands‑on guide to building a MySQL master‑slave cluster with GTID replication and configuring MHA for automatic failover, covering environment preparation, password‑less SSH, MySQL settings, MHA installation, custom scripts, testing, and the key advantages of the solution.
This article compares the legacy MHA solution with the modern open‑source Xenon tool, explaining Xenon's Raft‑based architecture, automatic leader election, GTID integration, and enterprise‑grade features that make it a superior high‑availability option for MySQL deployments.
The article explains why MySQL raises a GTID consistency error when a single transaction updates both InnoDB and MyISAM tables, demonstrates how to reproduce the issue, and evaluates three mitigation strategies: separating the updates, converting MyISAM to InnoDB, or disabling ENFORCE_GTID_CONSISTENCY.
Discover three effective strategies for mastering a technology, illustrated with MySQL replication, and learn how the new book 'Deep Understanding of MySQL Master‑Slave Principles' combines code analysis, comparative study, and expert insight to guide readers through GTID, binary‑log events, and high‑availability architectures.
The article experimentally evaluates three ways of creating replication users—only on the master, separately on master and slave with CREATE statements, and separately with GRANT statements—to show how each method affects MySQL 5.7 master‑slave replication, error handling, and compatibility with GTID and MySQL 8.0.
This guide explains MySQL GTID concepts, how to enable GTID-based replication, configure the server, create replication users, initialize a slave, and verify replication using command-line examples and configuration steps, including transaction commit and rollback testing.
This guide explains how to enable and configure MySQL binary logging, examine its parameters, understand event formats, view and filter binlog contents, and extract logs using positions, timestamps, or GTIDs for backup, replication, and troubleshooting purposes.
This article examines how differences between Galera GTID and MySQL GTID caused replication interruptions during a Percona XtraDB Cluster (PXC) split at Qunar, details the migration procedure, reproduces the problem with step‑by‑step commands, explains GTID concepts, and proposes operational improvements to avoid data inconsistency.
This article explains why GTID‑based replication is preferred for synchronizing data from four regional factories to an IDC, and provides a step‑by‑step guide—including data export, GTID configuration, master‑slave channel setup, adding a new slave, and important precautions—for successful multi‑source MySQL replication.
This article explains how to restore a single table on a MySQL replica after GTID‑based replication errors by using backup snapshots, replication filters, and controlled start‑up points to keep data consistent across master and slave.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This article walks through preparing a four‑node MySQL environment, configuring password‑less SSH, setting up master‑slave replication with GTID, installing and configuring MHA, testing connectivity, running the manager, verifying automatic failover, and highlighting the key advantages of using MHA for high availability.
DRC (Data Replicate Center) is Ctrip’s database middleware that provides real‑time bidirectional MySQL replication, achieving low‑latency, multi‑active data access across data centers while ensuring consistency through GTID, writeset, conflict resolution, DDL handling, and comprehensive monitoring.
This article compares MySQL semi‑synchronous and asynchronous replication, presenting performance test results that show only a small difference in TPS and QPS, explains why the gap is minimal, and provides step‑by‑step instructions and sysbench scripts for setting up and benchmarking both replication modes.
The article describes Ctrip's Data Replicate Center (DRC), a MySQL middleware that enables real‑time bidirectional replication across data‑center clusters, detailing its architecture, low‑latency optimizations, consistency mechanisms, DDL handling, monitoring, and future high‑availability improvements.
This guide explains the fundamentals of MySQL master‑slave replication, the advantages of using GTID‑based replication, and provides detailed configuration steps, command examples, and troubleshooting tips for setting up and managing a reliable, read‑scalable database environment.
This article explains a MySQL replication experiment where a master is restored from an outdated backup, leading to missing GTID transactions due to server_id filtering, and provides analysis and solutions such as changing server_id or enabling replicate-same-server-id to prevent data loss.
This article analyses why the GTID information recorded by Xtrabackup’s xtrabackup_binlog_info file can differ from the GTID shown by SHOW MASTER STATUS after a restore, comparing the behavior of Xtrabackup 2.4 on MySQL 5.7 and Xtrabackup 8.0 on MySQL 8.0, and explains the underlying backup processes and MySQL internals that cause the discrepancy.
The article explains the shortcomings of traditional MySQL replication lag measurement, introduces the community heartbeat‑table approach and its limitations, and then details MySQL 8 enhancements (WL#7319, WL#7374) that embed transaction timestamps in binlog events and expose new performance_schema tables, providing precise SQL queries to monitor lag at various stages of the replication pipeline.
This tutorial explains how to create and operate a MySQL InnoDB ReplicaSet—deploying sandbox instances, configuring a replica set with primary and secondary roles, adding nodes, performing manual primary switches and failure recovery, and using MySQL Router to provide an automatic read/write endpoint, while highlighting current limitations.
This article explains how MySQL's Multi‑Threaded Slave (MTS) processes events, manages checkpoints, and persists state using GAQ queues, bitmaps, and system tables, providing detailed code references and configuration parameters for reliable parallel replication.
This article explains the internal workflow of MySQL's multi‑threaded slave (MTS) parallel replication, covering the coordination thread, logical‑clock sub‑mode, worker allocation, waiting states, key configuration parameters, and illustrative code snippets and log analysis.
This guide explains step‑by‑step how to configure Zabbix database high availability using MariaDB master‑master replication, GTID‑based binlog synchronization, Keepalived virtual IP failover, and associated backup and monitoring scripts for reliable production deployment.
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 provides a comprehensive, step‑by‑step analysis of MySQL’s binlog commit workflow, covering key parameters such as binlog_group_commit_sync_delay, sync_binlog, and binlog_transaction_dependency_tracking, and explains how last_commit and seq_number are generated and used in parallel replication.
This article analyzes why a large MySQL transaction can occupy an entire binlog file without triggering a binlog switch, why the final transaction appears small, and why the commit timestamps of the large and small transactions can differ by up to fifteen minutes, providing detailed explanations, test procedures, and code examples.
This article explains the concepts of unitization and multi‑active data centers, analyzes the drawbacks of single‑IDC deployments, and provides a detailed guide on implementing MySQL binlog synchronization while addressing common challenges such as data loops, duplicate inserts, unique‑index conflicts, and DDL handling, including practical solutions like GTID and auxiliary tables.
The article examines MySQL 5.6’s crash‑safe slave replication, explaining how earlier versions’ unsafe relay‑log handling could corrupt position data, describing the atomic update of mysql.slave_relay_log_info via table‑based relay‑log info and transaction coordination, and covering configuration options, recovery behavior, GTID implications, performance trade‑offs, and implementation guidance.
This article examines a real‑world MySQL Group Replication failure where network jitter caused a primary INSERT not to replicate, leading to GTID divergence, a secondary node leaving the cluster, and explains the underlying Paxos‑based proposal conflict with detailed code examples.
This guide explains why a single MySQL server is a bottleneck, how to form a clustered master‑slave architecture, the binlog and GTID replication mechanisms, step‑by‑step configuration commands, and the trade‑offs of each approach.
Aries, an internally built MySQL incremental log distribution platform, leverages GTID‑based binlog dumping to achieve stable, consistent, and real‑time data synchronization across heterogeneous systems, supporting use cases such as Elasticsearch sync, cache updates, archiving, and live statistics.
This article explains step‑by‑step how to collect replication status, examine MySQL error logs and binary logs, understand common replication failures such as duplicate databases or missing binlog files, and apply GTID and configuration best practices to troubleshoot and fix MySQL replication problems in high‑availability setups.
This guide explains how to collect replication status, analyze MySQL error logs and binary logs, compare master and slave configurations, and troubleshoot common replication failures such as GTID mismatches, missing binlogs, and conflicting statements in UDB environments.
This guide walks through preparing three CentOS 6.7 machines, installing MySQL 5.7.17, configuring my.cnf, disabling firewalls and SELinux, creating replication users, exporting databases, setting up multi‑source replication with GTID, and verifying data consistency across two masters and one slave.
This article explains why a large‑transaction update caused a binlog overflow and slave I/O failure in a remote MySQL master‑slave setup, and provides step‑by‑step commands and a stored‑procedure solution to restore replication safely.
This article explains how MySQL binlog timestamps are derived from the THD start_time, why all events in a transaction share the same timestamp, how exec_time is calculated, and demonstrates the effect of autocommit and manual commit on binlog timing through concrete examples and source code analysis.
This comprehensive guide explains MySQL GTID fundamentals, its syntax, how to view and generate GTIDs, configure GTID‑based replication, troubleshoot replication errors, integrate GTID with backup/restore, MHA failover, and ensure crash‑safe slaves while discussing performance trade‑offs.
This guide explains MySQL's Multi‑Source Replication feature, its use cases, and provides a detailed GTID‑based configuration process—including master and slave setup, unique server IDs, replication user creation, and verification of successful replication across multiple masters.
This guide explains what MySQL GTID is, compares its advantages and disadvantages to traditional binlog‑position replication, and provides a detailed, step‑by‑step procedure for configuring GTID‑based master‑slave replication, including server‑UUID handling, configuration files, data export, user creation, and verification.
After switching a MySQL master’s IP, the author observed unusually high traffic between master and slave, investigated the role of GTID, semi‑sync and the CHANGE MASTER command, and demonstrated that executing CHANGE MASTER on a lagging replica can clear the relay‑log and cause a temporary traffic surge as the missing binlog entries are re‑fetched.
This article examines common MySQL high‑availability designs—including single master‑slave, dual‑master with multiple slaves, Heartbeat + DRBD, MySQL Cluster, semi‑synchronous replication, and GTID—explaining their benefits, drawbacks, and practical considerations for reliable failover and scalability.
In a MySQL 5.7 GTID setup with dual masters, a network glitch caused the slave to switch masters, leading to the ER_MASTER_HAS_PURGED_REQUIRED_GTIDS error because the new master had purged GTIDs the slave still needed, and the article explains the root cause and step‑by‑step fix.
After switching a MySQL master’s IP, the author observed extreme master‑to‑slave traffic and investigated whether using CHANGE MASTER with auto_position=1 forces the replica to re‑fetch already executed binlogs, especially when the replica is lagging.
This article analyzes why redirecting MySQLdump output can crash the client, explores a GTID_PURGED bug that breaks AUTO_POSITION replication, examines GTID gaps caused by replicate‑do‑db filtering, and explains why TokuDB's OPTIMIZE TABLE appears to increase table size.
