Replication (Primary-Replica)

# Primary my.cnf
[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW          # recommended: captures row-level changes
gtid_mode          = ON
enforce_gtid_consistency = ON
sync_binlog        = 1            # flush binlog to disk on each commit (durability)
innodb_flush_log_at_trx_commit = 1

# Replica my.cnf
[mysqld]
server-id          = 2
gtid_mode          = ON
enforce_gtid_consistency = ON
relay_log          = /var/log/mysql/relay-bin.log
read_only          = ON           # prevent accidental writes to replica
super_read_only    = ON           # block even SUPER users

-- Configure replica (MySQL 8.0+ syntax)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST     = '10.0.0.1',
    SOURCE_USER     = 'replication_user',
    SOURCE_PASSWORD = 'secret',
    SOURCE_AUTO_POSITION = 1;   -- use GTID auto-positioning

START REPLICA;
SHOW REPLICA STATUS\G  -- check Seconds_Behind_Source

-- Enable semi-synchronous replication (primary side)
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 1000; -- fallback to async after 1s

-- Replica side
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;

-- Monitor replication lag
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source: NULL = not running, 0 = caught up, N = lagging

-- Prometheus metrics for replication lag (mysqld_exporter)
-- mysql_slave_status_seconds_behind_master
-- Alert when lag > 30s:
# - alert: MySQLReplicationLag
#   expr: mysql_slave_status_seconds_behind_master > 30
#   for: 5m

-- Check GTID executed sets to verify sync
SHOW VARIABLES LIKE 'gtid_executed';   -- primary
SHOW VARIABLES LIKE 'gtid_executed';   -- replica (should match when caught up)

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    public DataSource routingDataSource(
            @Qualifier("primaryDs") DataSource primary,
            @Qualifier("replicaDs") DataSource replica) {

        return new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                // readOnly transactions route to replica
                return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                        ? "replica" : "primary";
            }

            {  // init block
                setTargetDataSources(Map.of("primary", primary, "replica", replica));
                setDefaultTargetDataSource(primary);
            }
        };
    }
}

// Usage: @Transactional(readOnly=true) → replica
//        @Transactional → primary
@Service
public class ReportService {
    @Transactional(readOnly = true)  // → replica DataSource
    public List<SalesReport> generateMonthlyReport() { ... }

    @Transactional                   // → primary DataSource
    public void createOrder(Order o) { ... }
}