MySQL and MariaDB

mysqladmin -u root -p status                                 # uptime, threads, questions, slow queries, QPS
mysql -e 'SHOW FULL PROCESSLIST' | grep -v Sleep              # active statements with their State and Time
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Aborted_connects','Innodb_row_lock_waits','Innodb_buffer_pool_wait_free','Created_tmp_disk_tables','Slow_queries')"
mysql -e 'SHOW ENGINE INNODB STATUS\G' | sed -n '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/p'
journalctl -u mysqld --since -1h                              # mariadb.service on MariaDB
df -h /var/lib/mysql; sudo du -sh /var/lib/mysql/*

# ~/.my.cnf
[client]
user = app
host = db.example.com
ssl-mode = REQUIRED          # MariaDB: ssl = on, ssl-verify-server-cert = on
# password in a login path or prompted with -p; never here on a shared host

[mysql]
prompt = "\\u@\\h [\\d]> "
pager = less -SFX            # horizontal scrolling for wide result sets
auto-rehash                  # tab completion of table and column names

mysql_config_editor set --login-path=prod --host=db.example.com --user=app --password   # prompts; stored encrypted in ~/.mylogin.cnf
mysql --login-path=prod my_db
mysql -h db.example.com -u app -p -e 'SELECT count(*) FROM orders' my_db                # one statement
mysql -N -B -e 'SELECT id FROM orders WHERE status = "stale"' my_db > ids.txt           # -N no header, -B tab separated
mysql --table my_db < report.sql                                                        # ASCII table output for a script
mysql my_db < schema.sql                                                                # run a file; add -f to continue past errors
mysqldump ... | mysql -h other.example.com my_db                                        # pipe a dump straight into another server

CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'use-a-generated-secret' REQUIRE SSL
  WITH MAX_USER_CONNECTIONS 200
  PASSWORD EXPIRE INTERVAL 180 DAY;                         -- MySQL; MariaDB: PASSWORD EXPIRE INTERVAL 180 DAY also works
GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO 'app'@'10.0.%';
GRANT EXECUTE ON my_db.* TO 'app'@'10.0.%';                 -- stored procedures
CREATE USER 'readonly'@'%' IDENTIFIED BY '...';
GRANT SELECT, SHOW VIEW ON my_db.* TO 'readonly'@'%';
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';           -- REPLICATION REPLICA is accepted from MySQL 8.0.26
CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, BACKUP_ADMIN ON *.* TO 'backup'@'localhost';   -- MySQL; MariaDB 10.5+: GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
GRANT SELECT ON performance_schema.* TO 'backup'@'localhost';

CREATE ROLE app_rw; GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO app_rw;
GRANT app_rw TO 'app'@'10.0.%'; SET DEFAULT ROLE app_rw TO 'app'@'10.0.%';   -- roles: MySQL 8, MariaDB 10.0.5+
SHOW GRANTS FOR 'app'@'10.0.%';
SHOW GRANTS FOR CURRENT_USER();                             -- what the account you actually matched has
SELECT user, host, plugin, account_locked, password_expired FROM mysql.user;
ALTER USER 'app'@'10.0.%' IDENTIFIED BY 'new-secret';
REVOKE DELETE ON my_db.* FROM 'app'@'10.0.%';
RENAME USER 'app'@'10.0.%' TO 'app'@'10.1.%';
DROP USER 'old'@'%';

CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- MySQL 8 default collation
CREATE DATABASE my_db CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; -- MariaDB 10.10+; utf8mb4_unicode_ci on older
SHOW CREATE DATABASE my_db;
SELECT table_name, engine, table_rows, round((data_length + index_length)/1024/1024) AS mib, table_collation
FROM information_schema.tables WHERE table_schema = 'my_db' ORDER BY mib DESC;
SHOW CREATE TABLE my_db.orders\G
SHOW INDEX FROM my_db.orders;
SHOW TABLE STATUS FROM my_db LIKE 'orders'\G                             -- Auto_increment, Data_free, Update_time
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- rewrites the table
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;           -- MySQL 8.0.12+/MariaDB 10.3+: metadata only
SELECT @@character_set_server, @@collation_server, @@character_set_connection;

SHOW ENGINE INNODB STATUS\G                    -- sections: SEMAPHORES, LATEST DETECTED DEADLOCK, TRANSACTIONS, BUFFER POOL AND MEMORY, ROW OPERATIONS
SELECT * FROM information_schema.INNODB_TRX WHERE trx_started < now() - INTERVAL 60 SECOND\G   -- long transactions
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';   -- read_requests vs reads: hit rate; reads should be a small fraction
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%'; -- free 0 and high 'dirty' means the pool is too small or checkpointing lags
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';           -- waits, time_avg, time_max
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;   -- online resize, MySQL 5.7+ and MariaDB 10.2+
OPTIMIZE TABLE my_db.orders;                    -- rebuilds the table online (ALGORITHM=INPLACE); reclaims Data_free after mass deletes

EXPLAIN SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' ORDER BY o.created_at DESC LIMIT 20;
EXPLAIN FORMAT=TREE SELECT ...;         -- MySQL 8.0.16+: the actual plan shape, nested
EXPLAIN ANALYZE SELECT ...;             -- MySQL 8.0.18+: runs the query; actual rows and time per node
ANALYZE FORMAT=JSON SELECT ...;         -- MariaDB: runs the query; r_rows and r_filtered are the measured values
EXPLAIN FOR CONNECTION 1234;            -- MySQL: plan of a statement currently running in another session

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log     # directory must exist and be writable by mysql; SELinux label mysqld_log_t
long_query_time = 1                               # seconds, fractional allowed; 0 logs everything
log_queries_not_using_indexes = ON                # noisy on small tables; pair with min_examined_row_limit
min_examined_row_limit = 1000
log_slow_admin_statements = ON                    # ALTER, ANALYZE, OPTIMIZE
log_slow_verbosity = query_plan,explain           # MariaDB: adds the plan to each entry

mysql -e 'SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;'   # live, until restart (SET PERSIST on MySQL to keep it)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log       # top 10 by total time, literals normalised to N and 'S'
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log       # top 10 by count
pt-query-digest /var/log/mysql/slow.log | head -60     # Percona Toolkit: fingerprinted ranking with percentiles
mysql -e "SET GLOBAL log_output = 'TABLE'"             # log to mysql.slow_log instead of a file; queryable, slower

ALTER TABLE orders ADD INDEX orders_status_created (status, created_at), ALGORITHM=INPLACE, LOCK=NONE;   -- fails loudly if it cannot be done online
CREATE INDEX orders_customer ON orders (customer_id);                    -- same thing, defaults to online where possible
ALTER TABLE orders ADD UNIQUE INDEX orders_ref (reference);              -- duplicates present: ERROR 1062 with the offending value
ALTER TABLE orders ADD INDEX orders_note_prefix (note(32));              -- prefix index for long text
ALTER TABLE orders ADD INDEX orders_lower_email ((lower(email)));        -- MySQL 8.0.13+ functional index; MariaDB: index a generated column
ALTER TABLE orders ALTER INDEX orders_customer INVISIBLE;                -- MySQL 8: optimiser ignores it; watch for regressions, then DROP
ALTER TABLE orders ALTER INDEX orders_customer IGNORED;                  -- MariaDB 10.6+ equivalent
ALTER TABLE orders DROP INDEX orders_customer;
ANALYZE TABLE orders;                                                    -- refresh cardinality estimates

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'my_db';   -- MySQL and MariaDB 10.6+: no reads since server start
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'my_db'; -- indexes that are prefixes of others
SELECT object_name, index_name, count_read, count_write FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'my_db' ORDER BY count_read DESC;

# --set-gtid-purged=ON and --source-data=2 (MySQL) record the GTID set and binlog position as comments for seeding a replica; MariaDB: --gtid --master-data=2
mysqldump --single-transaction --quick --routines --triggers --events --set-gtid-purged=ON --source-data=2 my_db | gzip > "my_db-$(date +%F).sql.gz"
mysqldump --single-transaction --all-databases --routines --triggers --events | gzip > all.sql.gz
mysqldump --no-data my_db > schema.sql                      # DDL only
mysqldump --single-transaction my_db orders --where='created_at >= "2026-01-01"' > orders-2026.sql
gunzip -c my_db-2026-09-24.sql.gz | mysql my_db             # restore; create the database first for a single-database dump
mysql -e 'SET GLOBAL innodb_flush_log_at_trx_commit = 2'    # speeds up a large restore; set back to 1 afterwards

sudo mariadb-backup --backup --target-dir=/backup/full --user=backup --password="$BACKUP_PASSWORD"     # live; needs RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
sudo mariadb-backup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=backup --password="$BACKUP_PASSWORD"
sudo mariadb-backup --prepare --target-dir=/backup/full                                              # apply redo; makes the copy consistent
sudo mariadb-backup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1               # roll the incremental into the full
cat /backup/full/xtrabackup_binlog_info                                                              # binlog file, position and GTID at backup time

# restore: the server must be stopped and the data directory empty
sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.old                    # keep it until the restored server is verified
sudo mkdir /var/lib/mysql
sudo mariadb-backup --copy-back --target-dir=/backup/full    # --move-back to avoid a second copy
sudo chown -R mysql:mysql /var/lib/mysql && sudo restorecon -Rv /var/lib/mysql
sudo systemctl start mariadb

# primary, [mysqld]
server_id = 1                                # unique per server in the topology
log_bin = binlog
binlog_format = ROW
binlog_expire_logs_seconds = 604800          # 7 days; MariaDB: expire_logs_days = 7
gtid_mode = ON                               # MySQL only; MariaDB GTIDs are always on with log_bin
enforce_gtid_consistency = ON                # MySQL only
binlog_row_image = FULL                      # MINIMAL saves space but replicas then need a primary key on every table
sync_binlog = 1                              # durable binlog at every commit; pairs with innodb_flush_log_at_trx_commit = 1

# replica, [mysqld]
server_id = 2
log_bin = binlog                             # so the replica can itself be promoted or chained
log_replica_updates = ON                     # MySQL; MariaDB: log_slave_updates = ON
read_only = ON
super_read_only = ON                         # MySQL: also blocks users with SUPER; MariaDB has read_only only
replica_parallel_workers = 4                 # MySQL; MariaDB: slave_parallel_threads = 4, slave_parallel_mode = optimistic
gtid_mode = ON
enforce_gtid_consistency = ON

-- MySQL 8.0.23+ (older releases: CHANGE MASTER TO MASTER_HOST=..., MASTER_AUTO_POSITION=1; START SLAVE)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db1.example.com', SOURCE_PORT = 3306,
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '...',
  SOURCE_SSL = 1,
  SOURCE_AUTO_POSITION = 1;                  -- GTID positioning; the dump's SET @@GLOBAL.gtid_purged line told the replica what it already has
START REPLICA;
SHOW REPLICA STATUS\G

-- MariaDB (10.5.1+ accepts REPLICA in place of SLAVE)
SET GLOBAL gtid_slave_pos = '0-1-123456';    -- from the dump header or mariadb-backup's xtrabackup_binlog_info
CHANGE MASTER TO
  master_host = 'db1.example.com', master_port = 3306,
  master_user = 'repl', master_password = '...',
  master_ssl = 1,
  master_use_gtid = slave_pos;
START REPLICA;
SHOW REPLICA STATUS\G

STOP REPLICA; START REPLICA;                              -- restart both threads
STOP REPLICA SQL_THREAD;                                  -- keep fetching, stop applying: freeze a replica for a report
SHOW BINARY LOGS; SHOW BINARY LOG STATUS;                 -- on the primary; SHOW MASTER STATUS before MySQL 8.4 / on MariaDB
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;          -- frees disk; every replica must already be past that point
SELECT * FROM performance_schema.replication_applier_status_by_worker\G   -- MySQL: per-worker errors and last applied transaction
RESET REPLICA ALL;                                        -- forget the primary entirely: on promotion of this replica

[mysqld]
bind_address = 10.0.0.5                     # not 0.0.0.0 unless the firewall does the work; MariaDB accepts a comma list from 10.11
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip_name_resolve = ON                      # grants must then use IPs or wildcards, not hostnames
max_connections = 500                       # each thread costs memory; pool in the application instead of raising this
max_allowed_packet = 64M                    # largest single statement or row; also set on the client
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci       # MariaDB: utf8mb4_uca1400_ai_ci
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
transaction_isolation = READ-COMMITTED      # fewer gap locks; REPEATABLE-READ is the default

innodb_buffer_pool_size = 12G               # 50-75% of RAM on a dedicated host
innodb_redo_log_capacity = 2G               # MySQL 8.0.30+; MariaDB: innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1          # 2 trades a second of durability for throughput
innodb_flush_method = O_DIRECT              # avoid double buffering through the page cache
innodb_io_capacity = 2000                   # background flushing rate; SSD-class values 1000-4000
innodb_io_capacity_max = 4000
innodb_print_all_deadlocks = ON             # every deadlock to the error log, not only the latest

tmp_table_size = 64M                        # in-memory temp table limit; both must be raised together
max_heap_table_size = 64M
table_open_cache = 4000
thread_cache_size = 50

log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

[client]
socket = /var/lib/mysql/mysql.sock
default_character_set = utf8mb4

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, full_scan
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;        -- since server start or last TRUNCATE
SELECT query, exec_count, rows_examined_avg FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'my_db' LIMIT 10;   -- I/O per table
SELECT * FROM sys.user_summary;                                                    -- statements, latency and connections per user
SELECT * FROM sys.host_summary;
SELECT * FROM sys.session ORDER BY statement_latency DESC LIMIT 20;                -- richer PROCESSLIST with current statement and lock info
SELECT * FROM sys.innodb_lock_waits\G                                              -- MySQL: waiting_query, blocking_pid, sql_kill_blocking_query
SELECT * FROM performance_schema.data_locks WHERE object_name = 'orders';         -- MySQL 8: every lock InnoDB holds right now
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;                         -- MySQL: where memory goes
TRUNCATE performance_schema.events_statements_summary_by_digest;                  -- reset the statement ranking for a fresh window

SHOW FULL PROCESSLIST;                                                             -- Id, User, Host, db, Command, Time, State, Info
SELECT id, user, host, db, command, time, state, left(info, 100) AS query
FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;
SELECT user, count(*) FROM information_schema.processlist GROUP BY user;           -- who is holding connections
KILL QUERY 1234;                                                                   -- stop the statement, keep the session
KILL 1234;                                                                         -- drop the session; the client sees a lost connection
KILL CONNECTION 1234;                                                              -- same; MariaDB also has KILL HARD / SOFT and KILL USER 'app'@'%'

# Server version, fork and uptime
mysql -e 'SELECT version(), @@version_comment; SHOW GLOBAL STATUS LIKE "Uptime"'

# Connections now versus the limit and the high-water mark
mysql -N -e "SELECT concat(v.Variable_value, ' running, max_used ', s.Variable_value, ', limit ', @@max_connections) FROM performance_schema.global_status v JOIN performance_schema.global_status s ON s.Variable_name='Max_used_connections' WHERE v.Variable_name='Threads_connected'"

# Active queries older than 10 seconds
mysql -e "SELECT id, user, time, state, left(info, 120) q FROM information_schema.processlist WHERE command <> 'Sleep' AND time > 10 ORDER BY time DESC"

# Kill every query of one user that has run longer than 5 minutes (prints the KILL statements first; pipe to mysql to run them)
mysql -N -e "SELECT concat('KILL QUERY ', id, ';') FROM information_schema.processlist WHERE user = 'report' AND time > 300 AND command <> 'Sleep'"

# Transactions open longer than a minute and their thread ids
mysql -e "SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_rows_locked, left(trx_query, 100) q FROM information_schema.INNODB_TRX WHERE trx_started < now() - INTERVAL 60 SECOND"

# The most recent deadlock
mysql -e 'SHOW ENGINE INNODB STATUS\G' | sed -n '/LATEST DETECTED DEADLOCK/,/^TRANSACTIONS/p'

# Buffer pool hit rate
mysql -N -e "SELECT round(100 - 100 * r.Variable_value / q.Variable_value, 2) FROM performance_schema.global_status r JOIN performance_schema.global_status q ON q.Variable_name = 'Innodb_buffer_pool_read_requests' WHERE r.Variable_name = 'Innodb_buffer_pool_reads'"

# Largest tables with fragmentation (Data_free) in MiB
mysql -e "SELECT table_schema, table_name, round((data_length+index_length)/1048576) mib, round(data_free/1048576) free_mib FROM information_schema.tables WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema') ORDER BY mib DESC LIMIT 15"

# Tables without a primary key (replication and locking hazard)
mysql -e "SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY' WHERE t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')"

# Columns still using utf8mb3
mysql -e "SELECT table_schema, table_name, column_name, character_set_name FROM information_schema.columns WHERE character_set_name = 'utf8mb3' AND table_schema NOT IN ('mysql','sys','performance_schema','information_schema')"

# Top statements by total time since the counters were reset (MySQL, MariaDB 10.6+ with performance_schema on)
mysql -e "SELECT left(query, 90) q, exec_count, total_latency, avg_latency FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10"

# Unused indexes
mysql -e "SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql','sys')"

# Replication lag and thread state, one line
mysql -e 'SHOW REPLICA STATUS\G' | grep -E 'Replica_(IO|SQL)_Running:|Seconds_Behind|Last_(IO|SQL)_Error'

# Binary log disk usage and retention
mysql -e 'SHOW BINARY LOGS' | awk 'NR>1 {s+=$2} END {printf "%.1f GiB in %d files\n", s/1073741824, NR-1}'; mysql -N -e 'SELECT @@binlog_expire_logs_seconds/86400'

# All effective server variables to a file for diffing between hosts
mysql -N -e 'SHOW GLOBAL VARIABLES' | sort > "vars-$(hostname).txt"

# Dump one table's schema and data, compressed, consistently
mysqldump --single-transaction --quick my_db orders | zstd -o orders.sql.zst

# Copy a database to another server without touching disk
mysqldump --single-transaction --routines --triggers my_db | mysql -h db2.example.com my_db

# Import a CSV (server-side file; needs FILE privilege and secure_file_priv to allow the path)
mysql -e "LOAD DATA INFILE '/var/lib/mysql-files/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES" my_db

# Import a CSV from the client machine instead
mysql --local-infile=1 -e "LOAD DATA LOCAL INFILE 'orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '\"' IGNORE 1 LINES" my_db

# Test a login exactly as the application would
mysql -h db.example.com -P 3306 -u app -p --ssl-mode=REQUIRED -e 'SELECT current_user(), @@hostname' my_db

# Accounts with no password or expired passwords
mysql -e "SELECT user, host, plugin, password_expired, account_locked FROM mysql.user WHERE authentication_string = '' OR password_expired = 'Y'"

# Grants of every account, for an audit file
mysql -N -e "SELECT concat('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -N | sed 's/$/;/' > grants.sql

# Reopen the slow log after logrotate renamed it
mysql -e 'FLUSH SLOW LOGS'

#!/usr/bin/env bash
# usage: mysql-dump-verify.sh BACKUP_DIR [KEEP_DAYS]   credentials from ~/.my.cnf or --login-path via MYSQL_LOGIN_PATH
set -euo pipefail
dir=${1:?backup dir}; keep=${2:-14}
opts=(${MYSQL_LOGIN_PATH:+--login-path="$MYSQL_LOGIN_PATH"})
mkdir -p "$dir"; stamp=$(date +%Y%m%dT%H%M)

mapfile -t dbs < <(mysql "${opts[@]}" -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys|_verify)$')
for db in "${dbs[@]}"; do
  out="$dir/$db-$stamp.sql.zst"
  mysqldump "${opts[@]}" --single-transaction --quick --routines --triggers --events "$db" | zstd -q -o "$out"
  mysql "${opts[@]}" -e "DROP DATABASE IF EXISTS _verify; CREATE DATABASE _verify"
  if zstd -dc "$out" | mysql "${opts[@]}" _verify; then
    tables=$(mysql "${opts[@]}" -N -e "SELECT count(*) FROM information_schema.tables WHERE table_schema = '_verify'")
    printf '%-24s %8s  %s tables ok\n' "$db" "$(du -h "$out" | cut -f1)" "$tables"
  else
    printf '%-24s FAILED to load: %s\n' "$db" "$out" >&2; exit 1
  fi
done
mysql "${opts[@]}" -e "DROP DATABASE IF EXISTS _verify"
find "$dir" -name '*.sql.zst' -mtime +"$keep" -print -delete

#!/usr/bin/env bash
# usage: replica-check.sh [MAX_LAG_SECONDS]   default 30
set -euo pipefail
max=${1:-30}
status=$(mysql -e 'SHOW REPLICA STATUS\G' 2>/dev/null) || { echo 'cannot query replica status'; exit 1; }
[[ -n $status ]] || { echo 'not a replica'; exit 0; }
field() { awk -v k="$1" -F': ' '$1 ~ "^ *"k"$" {print $2; exit}' <<<"$status"; }

io=$(field 'Replica_IO_Running'); [[ -n $io ]] || io=$(field 'Slave_IO_Running')
sql=$(field 'Replica_SQL_Running'); [[ -n $sql ]] || sql=$(field 'Slave_SQL_Running')
lag=$(field 'Seconds_Behind_Source'); [[ -n $lag ]] || lag=$(field 'Seconds_Behind_Master')
err=$(field 'Last_SQL_Error'); [[ -n $err ]] || err=$(field 'Last_IO_Error')

src=$(field 'Source_Host'); [[ -n $src ]] || src=$(field 'Master_Host')

printf 'io=%s sql=%s lag=%s source=%s\n' "$io" "$sql" "${lag:-NULL}" "$src"
if [[ $io != Yes || $sql != Yes ]]; then
  printf 'replication thread down: %s\n' "${err:-no error text}" >&2; exit 1
fi
if [[ $lag == NULL || $lag -gt $max ]]; then
  printf 'lag %s exceeds %s seconds\n' "$lag" "$max" >&2; exit 2
fi

#!/usr/bin/env bash
# usage: lock-waits.sh   (MySQL 8; on MariaDB use information_schema.INNODB_LOCK_WAITS joined to INNODB_TRX)
set -euo pipefail
mysql --table -e "
SELECT w.wait_age, w.locked_table, w.locked_type,
       w.waiting_pid, left(w.waiting_query, 60)  AS waiting_query,
       w.blocking_pid, w.blocking_trx_age, left(w.blocking_query, 60) AS blocking_query,
       w.sql_kill_blocking_connection
FROM sys.innodb_lock_waits w
ORDER BY w.wait_age DESC"
echo
echo 'transactions idle with an open transaction (blockers are often here, with no current query):'
mysql --table -e "
SELECT t.trx_mysql_thread_id AS pid, t.trx_started, timestampdiff(SECOND, t.trx_started, now()) AS age_s,
       t.trx_rows_locked, t.trx_rows_modified, p.user, p.host, p.command
FROM information_schema.INNODB_TRX t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep' AND t.trx_started < now() - INTERVAL 30 SECOND
ORDER BY t.trx_started"