This article provides a comprehensive practical guide to MySQL master-slave replication, covering synchronization principles, three replication modes, delay causes and troubleshooting, read-write splitting implementation with common pitfalls, failover strategies, and five production pitfalls to avoid.
Database High AvailabilityDatabase ReplicationFailover
The article walks through diagnosing database bottlenecks, explains MySQL replication flow, read‑write separation benefits and limits, shows when to apply vertical versus horizontal partitioning, details sharding‑key selection, cross‑shard challenges, high‑availability steps, and presents a complete e‑commerce case study.
The article investigates why master‑slave replication delay spikes each morning between 05:00 and 07:00, tracing it to the inventory‑snapshot worker that floods binlog traffic, evaluates five mitigation strategies, implements a big‑data extraction pipeline to Elasticsearch, and reports that the nightly delay disappeared and disk utilization improved.
The article analyzes why the master‑slave database replication experiences 30‑minute delays each morning between 5 AM and 7 AM, traces the cause to massive inventory‑snapshot jobs, evaluates several mitigation options, and details a big‑data extraction workflow that eliminates the lag while reducing disk usage.
The article analyzes how to design a high‑availability architecture for large‑scale e‑commerce systems, detailing layered risk isolation, stateful storage strategies for flow and state data, unified document‑ID routing, multi‑replica databases, multi‑datacenter synchronization, and real‑world JD case studies that demonstrate elastic scaling and disaster recovery.
This article analyzes why a warehouse management system’s master‑slave database replication lagged up to 30 minutes during nightly inventory snapshot generation, evaluates several mitigation strategies, and details the chosen big‑data‑driven solution that moved snapshots to Elasticsearch, reducing lag and disk usage.
This article examines three MySQL high‑availability solutions—MHA, Percona XtraDB Cluster, and Galera Cluster—detailing their architectures, advantages, limitations, deployment scenarios, and best‑practice recommendations to help engineers choose the most suitable approach for continuous, reliable database operations.
This comprehensive guide explains PostgreSQL streaming replication concepts, required environment, primary and standby configuration commands, verification queries, failover procedures, and production best‑practice recommendations, enabling you to build a reliable high‑availability database cluster.
An in‑depth comparison of three popular MySQL high‑availability architectures—MHA, Percona XtraDB Cluster (PXC), and Galera Cluster—covers their principles, architectures, strengths, limitations, deployment scenarios, and best‑practice recommendations to help you select the optimal solution for your production environment.
The article explains how to design a scalable, highly available backend system capable of handling millions of requests per second by defining key performance metrics, estimating traffic with the 2/8 rule, and applying architectural patterns such as load‑balanced clusters, vertical service splitting, distributed caching, and database master‑slave replication, illustrated with a Taobao case study.
The article examines the write‑after‑read consistency problem in multi‑active cross‑region systems, compares single‑write‑single‑read routing, quorum‑based multi‑write‑multi‑read, and read‑while‑copy methods, explains why primary‑secondary replication is preferred, and proposes a four‑step framework—scenario flagging, data marking, latency evaluation, and near‑by asynchronous replication—to meet WAR requirements efficiently.
This article explains the MySQL 8.0 Clone plugin introduced in version 8.0.17, compares it with Xtrabackup, details its five‑step cloning process, describes the code structure across SQL, plugin, and InnoDB layers, and outlines the page‑archiving subsystem used to track dirty pages during cloning.
This article explains the four major differences between MySQL’s redo log and binlog—including their purposes, recorded content, data types, and lifecycle—highlighting how redo logs ensure data durability while binlogs support replication, recovery, auditing, and high‑availability scenarios.
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 the essential elements of high‑concurrency system design—performance, availability, scalability—introduces quantitative metrics, classifies read‑heavy and write‑heavy scenarios, and details practical solutions such as database read/write separation, local and distributed caching, cache‑penetration and avalanche mitigation, and CQRS implementation for billion‑user applications.
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 comprehensive tutorial walks you through configuring Oracle Data Guard for master‑slave replication, covering prerequisites, detailed command‑by‑command steps, common pitfalls, testing procedures, switchover operations, and post‑switch recovery, all illustrated with diagrams and code snippets.
This article walks you through building a scalable system—from a single‑server prototype to a multi‑data‑center design that uses load balancers, database replication, caching, CDNs, stateless layers, message queues and automated monitoring—to handle traffic from hundreds of thousands to millions of users.
This article explains how to evolve a single‑server application into a highly available, horizontally scalable system that can serve over a million users by introducing load balancers, database replication, caching layers, CDNs, stateless network design, multi‑data‑center deployment, and message queues.
This guide walks you through expanding a single‑server application into a highly available, horizontally‑scaled architecture that can serve over a million users by adding load balancers, database replication, caching layers, CDNs, stateless network design, multi‑data‑center support, message queues, monitoring and automation.
This article explains the concept of read‑write separation, why it improves performance, when it should be applied, the underlying principles of master‑slave replication, and provides a detailed MySQL example with diagrams to illustrate the architecture and data synchronization process.
This article explains MySQL's binary log (binlog), a binary format file that records SQL statements for database updates, its role in master-slave replication, crash recovery, and detailed implementation mechanisms including cache management and event writing processes.
This article explains MongoDB's three cluster architectures—deprecated Master‑Slave replication, the highly available Replica Set with automatic failover, and horizontal scaling Sharding—detailing their roles, data flow, advantages, limitations, and operational considerations for building reliable, scalable databases.
This article investigates how differing MySQL lower_case_table_names settings between source and target affect DTLE's ability to synchronize data, presenting test scenarios, observed behaviors, and recommendations for configuration and job management.
This article explains why master‑slave replication is needed in high‑concurrency scenarios, describes the consistency challenges it introduces, and presents three practical solutions—half‑synchronous replication, database middleware, and cache‑key recording—detailing their principles, advantages, and drawbacks.
DBSyncer is an open‑source middleware that synchronizes data across multiple databases such as MySQL, Oracle, SqlServer, PostgreSQL, Elasticsearch, Kafka and files, offering custom plug‑in development, real‑time monitoring, performance alerts, and detailed configuration guides for various connectors and use cases.
This article explains high‑availability concepts for PostgreSQL, reviews standby database types, describes clustering models, and evaluates several HA solutions such as DRBD, ClusterControl, Rubyrep, Pgpool II, Bucardo, Postgres‑XC, Citus, and PostgresXL, while noting practical considerations and trade‑offs.
This article compares two methods for synchronizing MySQL data with Redis cache—using MySQL triggers combined with a UDF function, and parsing MySQL binlog via tools like Canal—detailing their workflows, advantages, limitations, and implementation considerations.
This article explains two methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers with a UDF function and parsing MySQL binlog streams—detailing their processes, advantages, limitations, and related open‑source tools like Canal.
This article explains two technical solutions for keeping MySQL data in sync with Redis cache—using a MySQL trigger with a UDF function and parsing MySQL binlog events—while also reviewing the Canal open‑source tool and discussing their trade‑offs and implementation details.
This article documents a MySQL read‑write splitting architecture, investigates an unexpected slave‑node crash, and provides a step‑by‑step guide to using Keepalived for monitoring and automatically restarting the MySQL container to achieve high availability of the replica.
After a month of testing a MySQL dual‑master high‑availability setup, the team encountered numerous replication pitfalls and decided to migrate to a simpler master‑slave architecture, detailing the reasons, differences, and a concrete manual conversion procedure.
This article explains how to design and evolve a system from a single‑server deployment to a highly scalable architecture using vertical and horizontal scaling, load balancing, database replication, sharding, caching, CDN, and stateless design to support billions of users.
This article provides a comprehensive, code‑rich guide on using TiDB DM v2.0.7 to perform full and incremental migration of terabyte‑scale MySQL data to DBLE, covering environment setup, TiDB DM installation, DBLE configuration, data generation, task execution, verification, and important operational considerations.
This article details Ctrip's Data Replicate Center (DRC) architecture, challenges, and solutions for migrating MySQL databases to the cloud, covering read‑only and read‑write scenarios, cost analysis, proxy modules, transaction‑table replication, data isolation, filtering, deployment workflow, and achieved performance and cost benefits.
This tutorial explains how to configure MySQL master‑slave replication using Docker, set up read‑write splitting with ShardingSphereJDBC, integrate MyBatis‑Plus, and build a Spring Boot application that demonstrates write operations on the master and read operations on the slave.
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.
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.
ShopeePay replaces its traditional active‑standby design with a unit‑based multi‑active data‑center architecture—splitting user, public, and order data across independent units, employing routing tables, caches, and a dual‑active model that enables seamless failover, reduced latency, and continuous payment processing across two or more IDC sites.
This article introduces the concept and practice of Change Data Capture (CDC), explaining how it captures database changes to provide real‑time incremental data for analytics and reporting without impacting source performance, and outlines modern CDC methods, challenges, and production‑ready system requirements.
The article explains the fundamentals of Change Data Capture (CDC), describing how capturing DML changes from relational databases like MySQL or PostgreSQL can provide incremental, near‑real‑time data for analytics and reporting while preserving source performance, and outlines modern CDC architectures, transaction‑log based extraction, and production‑ready design considerations.
This article explains how to evolve a simple single‑server web application into a highly available, horizontally and vertically scalable system for billions of users by covering DNS, vertical and horizontal scaling, load balancing, database replication, sharding, denormalization, SQL/NoSQL choices, stateless design, caching, CDN and global deployment.
This article explains how to design a highly scalable system for tens of millions of users by starting with a simple single‑server setup, applying vertical and horizontal scaling, using load balancers, expanding relational databases, choosing between SQL and NoSQL, and employing advanced techniques such as caching, CDN, and GeoDNS.
This article explains the concepts of high availability, continuous recovery, and standby databases, then reviews various PostgreSQL clustering options such as DRBD, ClusterControl, Rubyrep, Pgpool‑II, Bucardo, Postgres‑XC, Citus, and PostgresXL, highlighting their features, advantages, and drawbacks.
This article details Xianyu's transition from a single‑site to a multi‑region deployment, covering scalability limits, disaster‑recovery strategies, traffic routing, data consistency decisions, service and database architecture, and the operational principles that enable low‑cost, high‑availability scaling across regions.
This article explains how to use Keepalived together with PostgreSQL to build a two‑node high‑availability cluster, covering Keepalived's VRRP mechanism, host planning, installation steps, asynchronous master‑slave replication configuration, monitoring scripts, and detailed failover drills.
The Xianyu recommendation service was re‑architected into an active‑active, multi‑region high‑availability system—using a unified access‑layer router, centralizing long‑tail dependencies, keeping data unsharded, refactoring caches and MySQL replication, and adhering to traffic‑closed‑loop and availability‑first principles—to overcome latency, improve scalability, and ensure low‑cost disaster recovery across two regions and three data centers.
The article examines the 2012 GitHub data‑leak caused by MySQL master‑slave failover and auto_increment misuse, explains why developers' expectations of uniqueness, monotonicity and continuity are unrealistic, and proposes half‑sync replication and distributed ID algorithms like Snowflake as more reliable solutions.
This guide explains how to build a highly available, scalable architecture for supporting hundreds of millions of users, covering decoupling, redundancy, vertical and horizontal scaling, load balancing, database replication, sharding, caching, CDN, GeoDNS, and best practices for progressive system expansion.
This article explains SQL Server's evolution from legacy high‑availability solutions to the modern AlwaysOn architecture, detailing its data‑synchronization process, synchronous and asynchronous commit modes, failover scenarios, and practical deployment recommendations for enterprises handling both moderate and terabyte‑scale workloads.
This article explains how to enable and configure MySQL's Federated storage engine to access remote tables without data synchronization, covering engine architecture, supported features, environment setup, server configuration, table creation, data manipulation, and binlog verification, along with practical code examples.
This article explains how to break the barrier between heterogeneous databases by synchronizing MySQL to ClickHouse using QingCloud MySQL Plus and the MaterializeMySQL engine, covering MySQL replication history, architecture details, HTAP scenarios, and providing practical code examples for setup and operation.
The article explains two technical methods for keeping MySQL and Redis in sync—using MySQL triggers with a custom UDF to write directly to Redis, and parsing MySQL binlog streams (or using Alibaba's Canal) to propagate changes, while discussing their suitable scenarios, challenges, and implementation details.
This article explains two practical methods for keeping MySQL and Redis cache in sync—using MySQL triggers with UDF functions or parsing binlog events—and introduces the open‑source Canal project for incremental log parsing, while also discussing the pitfalls of reverse synchronization.
This article explains two technical methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers combined with a UDF function and parsing MySQL binary logs (with Canal)—detailing their workflows, advantages, limitations, and implementation considerations.
The article explains how massive DROP TABLE operations in PostgreSQL master‑slave replication trigger costly buffer‑invalidation loops that cause severe lag, and describes extracting this step into a separate subprocess with a shared hash table, cutting replication lag from over 400 GB to about 10 MB—a 30,000‑fold speedup.
This article details Dada's dual‑cloud active‑active disaster‑recovery implementation, explaining high availability versus disaster recovery, describing the first‑phase architecture and challenges, and outlining the second‑phase enhancements such as multi‑data‑center Consul, bidirectional database replication, precise load‑balancing, tool adaptations, capacity elasticity, and future plans.
This article explains how Redis Sentinel provides automatic failover by combining multi‑replica deployment, health monitoring, leader election, and distributed consensus to detect master failures, select a new master, promote it, and notify clients, ensuring high availability for Redis databases.
This article explains the three core MySQL logs—binlog, redo log, and undo log—their structures, formats, configuration parameters, and how they work together to provide replication, crash recovery, and transactional atomicity while balancing performance and consistency.
This article explains MySQL Group Replication (MGR), covering its evolution from traditional replication, the full transaction lifecycle within an MGR cluster, and the detailed conflict‑detection mechanism that ensures data consistency and automatic failover.
This article presents a hands‑on experiment that measures the performance impact of using pt‑table‑checksum on a MySQL master‑slave setup, explains the tool’s design choices such as reduced lock‑wait time and RR isolation, and concludes that its effect on production traffic is minimal when configured properly.
This article outlines the key characteristics of large‑scale web systems and walks through their architectural evolution—from monolithic beginnings to service separation, caching, clustering, read/write splitting, CDN/reverse‑proxy acceleration, distributed storage, NoSQL, business splitting, and distributed services—while highlighting core values and common design pitfalls.
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.
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.
The DTLE 2.19.11.0 release notes detail the project's purpose as a MySQL‑focused data transfer component, outline new features such as binlog relay and MySQL 8.0 support, list numerous bug fixes, and provide links to documentation and download resources for developers.
This article examines the unique regulatory and operational characteristics of the securities industry and explains how careful technology selection—ranging from native database log replay to third‑party replication and big‑data platforms—enables robust high‑availability architectures, illustrated with real‑world practices from Dongfang Securities.
Database ReplicationHigh AvailabilitySecurities Industry
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 the MySQL 8.0 CLONE plugin simplifies creating reliable replica instances by installing the plugin, granting necessary privileges, executing the CLONE SQL statement, monitoring progress via performance_schema, and verifying successful completion of all clone stages.
This article explains the financial‑industry driven requirements for real‑time data sync, describes the TDSQL‑MULTISRCSYNC architecture—including producer, store, and consumer components—and details core designs such as row‑hash concurrency, idempotent binlog handling, and a lock‑based ordering mechanism that ensure high throughput and consistency.
This article provides a comprehensive step‑by‑step guide for migrating data from Oracle to MySQL using Oracle GoldenGate, covering architecture overview, environment setup, table and data migration, configuration of source and target processes, handling of common issues, data validation, and reference resources.
This article details Alibaba Cloud's evolution of SQL Server RDS, covering product growth, the challenges of read/write separation, technical evaluations of AlwaysOn versus Transactional Replication, cloud architecture iterations, and the final productized solution for high‑availability database services.
The article outlines the defining traits of large‑scale websites—high concurrency, massive traffic, high availability, and huge data volumes—and explains how their architecture evolves from a single‑server setup to layered, distributed systems using caching, load balancing, database read/write splitting, CDN, NoSQL, and service isolation.
This article explains the evolution of MySQL replication, from the classic master‑slave model to schema‑level and group‑commit parallelism in 5.7, and finally the WriteSet‑based parallel replication introduced in MySQL 8.0, including configuration parameters, conflict‑detection logic, and performance test results.
This article explains practical techniques for designing high‑availability and scalable architectures across entry, business, cache, and database layers, helping developers handle rapid user growth without service interruptions.
This article presents a step‑by‑step operations‑focused roadmap for evolving a website from a single‑server prototype to a highly available, horizontally scalable architecture using load balancing, caching, database replication, service‑oriented design, DNS round‑robin, CDN, and disaster‑recovery techniques.
This article reviews the evolution of Meituan‑Dianping's MySQL high‑availability architecture over recent years, detailing the transition from the MMM replication manager to MHA, the integration of Zebra and Proxy middleware, and future design considerations such as distributed agents, semi‑synchronous replication, and MySQL Group Replication.
This article explains the design and implementation of PhxSQL, a MySQL‑compatible high‑availability solution that uses a reliable log storage based on Paxos, Proxy request forwarding, automatic master election, and other mechanisms to overcome native MySQL replication flaws and provide strong data consistency and fault‑tolerant performance.
A practical benchmark on three identical servers shows MySQL Group Replication achieving higher TPS than Semi‑Sync despite larger network traffic, and explains how Paxos‑based parallel log transmission gives it an edge over the single‑threaded Semi‑Sync approach.
This article explains why Paxos is needed for consistency in distributed systems, details its roles and three-phase protocol, illustrates the algorithm with a real‑world analogy, and shows how Paxos underpins high‑availability database replication such as MySQL binlog synchronization.
This article explains why Paxos is needed for distributed consistency, describes its three roles and phases, illustrates the protocol with a real‑world analogy, and shows how Paxos‑based log replication can provide strong, fault‑tolerant high‑availability for databases.
This article explains what Galera Cluster is, how its multi‑master MySQL architecture provides strong data consistency and high availability, outlines its core mechanisms such as write‑set replication and flow control, and discusses operational pitfalls and suitable use‑cases.
This article explains why scalability is a crucial non‑functional requirement, describes vertical (scale‑up) and horizontal (scale‑out) approaches, introduces the AKF model’s X/Y/Z axes, and provides practical guidance for scaling services, databases, caching, and deployment to achieve robust, high‑throughput applications.
This article walks through the evolution of a website from a single‑server setup to a distributed, high‑performance architecture, covering service separation, caching strategies, server clustering, load balancing, database replication, CDN acceleration, distributed storage, NoSQL adoption, and modular business decomposition.
This article explains the design and implementation of XDB, a MySQL high‑availability framework used by Baidu Waimai, detailing its component architecture, Horus state‑machine based failover, monitoring mechanisms, log management, and real‑world deployment experiences.
This article explains the concept of read/write splitting in distributed databases, outlines two implementation methods—static dual data sources and dynamic AOP-based switching—provides a complete Spring‑MyBatis demo with configuration files, code snippets, and guidance on setting up master and slave connections.
This article introduces MHA (Master High Availability) for MySQL, explains its architecture, outlines required hardware and software configurations, provides detailed commands to set up master and slave nodes, create configuration files, and demonstrates how to start and verify the high‑availability cluster.
This article demonstrates how to enable Oracle Data Guard’s Maximum Protection mode, examines the behavior of primary and standby databases when the standby connection is broken, and walks through the resulting errors, forced shutdown, and step‑by‑step recovery back to normal operation.
This article details Shandong Mobile's cloud‑based dual‑active architecture, covering virtualization, VMware HA/DRS, Oracle GoldenGate and ADG optimizations, large Layer‑2 network designs, heartbeat mechanisms to avoid split‑brain, comprehensive testing scenarios, and the resulting reductions in downtime and hardware costs.
This tutorial walks through setting up a MySQL master‑slave replication cluster, configuring password‑less SSH, installing MHA‑node and MHA‑manager, creating the MHA configuration, validating the setup, and testing automatic failover when the master goes down.
This guide walks through preparing the primary and standby Oracle 11g databases on RH Linux, configuring listeners, tnsnames, force logging, adding standby redo logs, editing parameter files, transferring files with RMAN, verifying roles, activating Active Data Guard, using snapshot standby, switching logs, and setting an archive‑log deletion policy.
This article explains how MHA provides automated master monitoring, rapid failover within 10‑30 seconds, online master switching, various failover modes, extensible scripts, key commands, and best‑practice workflows to achieve high‑availability MySQL replication without performance impact.
This article explains how startup engineers can design and implement a scalable web architecture—covering server clustering, load balancing, distributed caching, database replication, and team organization—to handle rapid user growth without compromising performance or reliability.
This article explains how startup engineers can design a scalable web system by separating services onto multiple servers, using load balancers, distributed caches, master‑slave replication, and team‑splitting strategies, ensuring performance and reliability as user traffic and data volumes surge.
This article explains how startup engineers can design and evolve a scalable web architecture—covering server partitioning, load balancing, distributed caching, database replication, and team organization—to handle rapid user growth without compromising performance or reliability.
A performance test using sysbench on a dual‑node DL380 G7 MySQL 5.6 cluster shows that semi‑synchronous replication delivers roughly one‑third the TPS of asynchronous replication and highlights recovery and replication‑lag limitations that can be mitigated with sharding and parallel workers.
The article reviews common MySQL high‑availability solutions—including shared‑storage SAN, DRBD disk replication, keepalived/heartbeat, MHA, ZooKeeper‑based HA, Galera/PXC clusters, and proxy middleware—detailing their architectures, advantages, limitations, and suitability for different business and operational requirements.
This article explains the design, configuration parameters, core data structures, initialization, coordinator distribution, worker execution, checkpointing, and shutdown procedures of MySQL 5.6's Multi‑Threaded Slave (MTS) parallel replication, providing a code‑level walkthrough for developers and DBAs.
The article explains how internet startups can design and evolve a scalable web architecture—through service separation, clustering, load balancing, distributed caching, database replication, and effective team organization—to handle rapid user growth and avoid performance bottlenecks.
This article examines the difficulties of achieving strong consistency and continuous availability in database replication, reviews the Paxos consensus protocol and its variants, and explains how Multi‑Paxos and clock‑driven leader election can be applied to build robust high‑availability architectures.