MySQL Ops

Operating MySQL (or MariaDB, mostly the same muscle memory): install and harden, tune my.cnf, manage users, diagnose live problems, run the slow query log, and set up GTID replication.

Install & secure

# MySQL 8 on RHEL/Alma/Rocky 9
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el9-5.noarch.rpm
sudo dnf install -y mysql-community-server
sudo systemctl enable --now mysqld

# Grab the random root password the installer dropped in the log:
sudo grep 'temporary password' /var/log/mysqld.log

sudo mysql_secure_installation
#  - set a new root password (16+ chars, put it in the vault)
#  - remove anonymous users:   yes
#  - disallow root login from 0.0.0.0: yes
#  - remove the test database: yes
#  - reload privilege tables:  yes

my.cnf anatomy

On EL systems the server reads /etc/my.cnf and everything in /etc/my.cnf.d/. Put your overrides in a dedicated file so package updates don't stomp them:

# /etc/my.cnf.d/local.cnf
[mysqld]
# --- Identity ---
server_id                = 101
bind-address             = 0.0.0.0
port                     = 3306

# --- Character set / locale ---
character_set_server     = utf8mb4
collation_server         = utf8mb4_0900_ai_ci
skip-character-set-client-handshake = 1

# --- SQL correctness ---
sql_mode                 = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default_storage_engine   = InnoDB
default_authentication_plugin = caching_sha2_password

# --- InnoDB: the important block ---
innodb_buffer_pool_size        = 24G        # 60-80% of RAM on a dedicated box
innodb_buffer_pool_instances   = 8          # for buffer pools >= 1G
innodb_log_file_size           = 2G
innodb_log_buffer_size         = 64M
innodb_flush_log_at_trx_commit = 1          # 1=ACID, 2=fast+unsafe at OS crash, 0=dangerous
innodb_flush_method            = O_DIRECT
innodb_file_per_table          = 1
innodb_io_capacity             = 2000       # SSD; 200 for spinning disks
innodb_io_capacity_max         = 4000

# --- Connections ---
max_connections          = 500
thread_cache_size        = 64
table_open_cache         = 4000

# --- Binary log / replication ---
log_bin                  = /var/lib/mysql/binlog
binlog_format            = ROW
binlog_row_image         = MINIMAL
binlog_expire_logs_seconds = 604800         # 7 days
sync_binlog              = 1
gtid_mode                = ON
enforce_gtid_consistency = ON

# --- Slow log ---
slow_query_log           = 1
slow_query_log_file      = /var/log/mysql/slow.log
long_query_time          = 0.5
log_queries_not_using_indexes = 1

# --- Limits ---
max_allowed_packet       = 64M

Users, GRANTs, SSL

-- Create a user, host-qualified. 'app'@'10.0.%' and 'app'@'%' are different users.
CREATE USER 'app_rw'@'10.0.%'
  IDENTIFIED BY 'redacted'
  REQUIRE SSL
  PASSWORD EXPIRE INTERVAL 180 DAY;

CREATE USER 'app_ro'@'10.0.%'
  IDENTIFIED BY 'redacted'
  REQUIRE SSL;

GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_rw'@'10.0.%';
GRANT SELECT                           ON appdb.* TO 'app_ro'@'10.0.%';

-- Replication user for the replicas:
CREATE USER 'repl'@'10.0.1.%'
  IDENTIFIED WITH 'caching_sha2_password' BY 'redacted'
  REQUIRE SSL;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.1.%';

-- Audit:
SELECT user, host, plugin, ssl_type FROM mysql.user;
SHOW GRANTS FOR 'app_rw'@'10.0.%';

SHOW PROCESSLIST & InnoDB status

SHOW PROCESSLIST is "what is the server doing right now":

SHOW FULL PROCESSLIST;
-- Or, filtered, from performance_schema (more useful):
SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS query
FROM   information_schema.processlist
WHERE  command != 'Sleep'
ORDER  BY time DESC;

KILL QUERY 12345;     -- cancel the running query, keep the connection
KILL 12345;           -- terminate the connection outright

SHOW ENGINE INNODB STATUS is dense but packed with diagnostic gold — especially the LATEST DETECTED DEADLOCK and TRANSACTIONS sections:

SHOW ENGINE INNODB STATUS\G

Look for these sections:

• SEMAPHORES — if "OS WAIT ARRAY" numbers are climbing, contention is up.
• LATEST DETECTED DEADLOCK — full rollback of one transaction, with SQL. If you see frequent deadlocks, you have a locking order problem in the app.
• BUFFER POOL AND MEMORY — hit ratio should sit above 99% in steady state.
• TRANSACTIONS — long ACTIVE transactions block purge and bloat undo. Chase the owners.

Slow query log

-- Toggle at runtime:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

# Parse it with mysqldumpslow (ships with the client):
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
#   -s t   sort by total time
#   -t 20  top 20

# Or Percona's pt-query-digest, which is considerably better:
sudo dnf install -y percona-toolkit
pt-query-digest /var/log/mysql/slow.log | less

The output groups queries by fingerprint (literal values stripped), so one-hit surprises don't hide the heavy hitters.

Replication with GTID

GTID (Global Transaction ID) replaces file+pos coordinates with a globally-unique transaction identifier. Replicas know which transactions they've applied, not which byte of which binlog they've read — failover stops being a terror.

# /etc/my.cnf.d/local.cnf — on BOTH primary and replica
[mysqld]
server_id                = 101       # unique per node!
log_bin                  = /var/lib/mysql/binlog
binlog_format            = ROW
gtid_mode                = ON
enforce_gtid_consistency = ON
log_replica_updates      = ON        # replica also writes its own binlog (for chained topologies)

-- On the primary, after the replication user exists:
-- (nothing to do; GTID means no CHANGE MASTER TO MASTER_LOG_FILE dance)

-- On the replica:
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = 'primary.db',
  SOURCE_PORT     = 3306,
  SOURCE_USER     = 'repl',
  SOURCE_PASSWORD = 'redacted',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL      = 1;

START REPLICA;
SHOW REPLICA STATUS\G

Key fields in SHOW REPLICA STATUS:

• Replica_IO_Running, Replica_SQL_Running — both should be Yes.
• Seconds_Behind_Source — rough lag; take it with salt on idle replicas.
• Retrieved_Gtid_Set vs Executed_Gtid_Set — what's been fetched vs applied.
• Last_Error — non-empty means you have an incident to write up.

Semi-sync & group replication

Async replication is the default: the primary returns to the client the moment it writes the binlog locally. A primary crash before the binlog reaches a replica means data loss.

Semi-synchronous replication: the primary waits until at least one replica has received (not applied) the event before acking the client.

-- On the primary:
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;   -- ms before falling back to async

-- On each replica:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = 1;
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

Group Replication is the MySQL 8 built-in for synchronous multi-primary / single-primary clustering. It uses a Paxos-like protocol and needs an odd number of members for quorum. Setup is non-trivial — most teams wrap it with InnoDB Cluster (Group Replication + MySQL Router + mysqlsh admin API) rather than wiring it by hand.

Common gotchas

See also: Postgres Ops, Backup & Restore.