MySQL Support

Because group commit batches many sessions into one binlog fsync, sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 usually cost far less than teams fear, and relaxing them trades a small latency gain for data loss on a crash and broken replicas. We relax durability only when the business has accepted the RPO in writing.

The other decisive settings are memory and I/O: a buffer pool sized to the working set, a redo log large enough that checkpoints never stall writes, and I/O capacity matched to what the storage sustains rather than its burst rating. MySQL 8.4 changed several InnoDB defaults, including innodb_io_capacity, the change buffer and the adaptive hash index, so an upgrade is also a configuration review.

InnoDB baseline for MySQL 8.4 LTS, with default, reason and reload behaviour per line

# /etc/my.cnf.d/minervadb-innodb.cnf  -- MySQL 8.4 LTS, dedicated 64 GB host (illustrative)
# Format per line: parameter = proposed   # 8.4 default -> why ; dynamic or restart
[mysqld]
innodb_buffer_pool_size        = 44G      # 128M -> ~70% of RAM for the working set ; dynamic (resizes in chunks)
innodb_redo_log_capacity       = 8G       # 100M -> absorb write bursts without checkpoint stalls ; dynamic
innodb_flush_log_at_trx_commit = 1        # 1 -> keep: full durability, group commit amortises fsync ; dynamic
sync_binlog                    = 1        # 1 -> keep: binlog survives crash, required for safe PITR/replicas ; dynamic
innodb_io_capacity             = 4000     # 10000 -> match the volume's sustained IOPS, not its burst ; dynamic
innodb_io_capacity_max         = 8000     # 2x io_capacity -> ceiling for flushing under pressure ; dynamic
innodb_flush_method            = O_DIRECT # avoid double caching in the OS page cache ; restart
binlog_format                  = ROW      # deterministic replication and CDC ; dynamic (new sessions)
gtid_mode                      = ON       # positionless failover and PITR ; staged change via OFF_PERMISSIVE/ON_PERMISSIVE
enforce_gtid_consistency       = ON       # prerequisite for gtid_mode=ON ; dynamic
binlog_expire_logs_seconds     = 604800   # 30 days -> 7 days, sized to the backup cadence ; dynamic
max_connections                = 800      # 151 -> sized to pooler output, not to app instances ; dynamic
performance_schema             = ON       # evidence source for every diagnosis ; restart

Replication health and lag per applier worker, plus Group Replication membership

-- MySQL support for replication: applier health and real lag per worker (8.0+).
SELECT c.channel_name,
       c.service_state                                       AS io_state,
       a.worker_id,
       a.service_state                                       AS sql_state,
       TIMESTAMPDIFF(MICROSECOND,
           a.last_applied_transaction_original_commit_timestamp,
           a.last_applied_transaction_end_apply_timestamp) / 1e6 AS last_txn_lag_s,
       a.last_error_number,
       LEFT(a.last_error_message, 80)                        AS last_error
FROM performance_schema.replication_connection_status       AS c
JOIN performance_schema.replication_applier_status_by_worker AS a
  ON a.channel_name = c.channel_name
ORDER BY last_txn_lag_s DESC;

-- Group Replication / InnoDB Cluster: membership, role and state of every node.
SELECT member_host, member_port, member_role, member_state, member_version
FROM performance_schema.replication_group_members;

ProxySQL read/write split that follows read_only after failover

-- MySQL support for routing: ProxySQL 2.x read/write split (admin interface, port 6032).
-- Hostgroup 10 = writer, 20 = readers. Monitor credentials come from your secret store.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag) VALUES
  (10, 'db-primary.internal', 3306, 0),
  (20, 'db-replica-1.internal', 3306, 5),     -- shunned if lag > 5 s
  (20, 'db-replica-2.internal', 3306, 5);

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type)
VALUES (10, 20, 'read_only');                  -- follows read_only after a failover

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1),    -- locking reads stay on the writer
  (200, 1, '^SELECT ',              20, 1);    -- other reads go to replicas

LOAD MYSQL SERVERS TO RUNTIME;     SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Roles, TLS-only service accounts and a privilege audit

-- MySQL support, security baseline (MySQL 8.0+ roles; 8.4 defaults to caching_sha2_password).
CREATE ROLE IF NOT EXISTS 'app_read', 'app_write', 'ops_dba';
GRANT SELECT                         ON shop.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_write';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'ops_dba';   -- no SUPER for daily work

CREATE USER 'svc_orders'@'10.20.%'
  IDENTIFIED WITH caching_sha2_password BY '${SVC_ORDERS_PASSWORD}'
  REQUIRE SSL                                   -- refuse unencrypted connections
  PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
GRANT 'app_write' TO 'svc_orders'@'10.20.%';
SET DEFAULT ROLE 'app_write' TO 'svc_orders'@'10.20.%';

-- Audit: accounts that can do everything, and accounts allowed from anywhere.
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y' OR host = '%';