Configure Database Replication Systems

The Database Replication Expert is an autonomous agent expert in database replication systems, with deep knowledge of master-slave, master-master, and clustered replication architectures for MySQL, PostgreSQL, MongoDB, and other database systems, including replication lag, conflict resolution, failover methods, and performance optimization for replicated environments. It covers replication types and use cases - asynchronous (high performance, possible data loss on failure), synchronous (data consistency guarantee, higher latency), semi-synchronous (a balance of the two), master-slave (read scaling, backups, reporting workloads), master-master (geographic distribution, high availability), and multi-master clusters (complex conflict resolution, enterprise scaling) - and key considerations like network latency/bandwidth requirements, the CAP theorem's consistency-versus-availability tradeoff, conflict detection and resolution strategies, and backup/disaster-recovery integration.

Use this agent when setting up or hardening database replication for MySQL (binary logging, replication users, slave configuration with skip-errors and connect-retry settings), PostgreSQL (streaming replication via wal_level/max_wal_senders/replication slots, base backups with pg_basebackup, standby promotion), or MongoDB (replica set initiation with member priorities and arbiter nodes, dynamic member addition, secondary read preferences). It includes concrete monitoring scripts (a bash health check reading Slave_IO_Running/Slave_SQL_Running/Seconds_Behind_Master with warning and critical thresholds, a PostgreSQL query computing lag in bytes and seconds from pg_stat_replication) and automated failover setups (HAProxy TCP load balancing across MySQL nodes with health checks, a PostgreSQL failover script that promotes a standby and updates application config). It is not a guide for choosing a database engine or initial schema design - it assumes a database is already running and focuses specifically on replicating, monitoring, and failing it over.

// Initialize replica set
rs.initiate({
  _id: "myReplicaSet",
  members: [
    { _id: 0, host: "mongo1.example.com:27017", priority: 2 },
    { _id: 1, host: "mongo2.example.com:27017", priority: 1 },
    { _id: 2, host: "mongo3.example.com:27017", arbiterOnly: true }
  ]
});

// Add members dynamically
rs.add("mongo4.example.com:27017");

// Configure read preferences
db.collection.find().readPref("secondary");

Given a database engine and target topology, the agent produces master/slave or primary/standby configuration blocks (my.cnf and postgresql.conf settings, replication user creation SQL), replica-set initialization code like the one above, lag-monitoring scripts with defined healthy/warning/critical thresholds, and failover automation (HAProxy config for MySQL, a promote-and-repoint script for PostgreSQL).

Database administrators and infrastructure engineers setting up or operating replicated MySQL, PostgreSQL, or MongoDB deployments who need concrete configuration, monitoring, and failover scripts rather than replication theory. It suits teams that treat security (SSL/TLS for replication traffic, VPN/private networks, regular password rotation, access audits) and operational discipline (documented and regularly tested failover procedures, consistent backup strategies across all nodes, split-brain planning for multi-master setups) as part of the replication setup, not an afterthought.