MySQL DBA cheat sheet: client, processlist, InnoDB, sys schema, replication and backups

#Client and admin

mysql -h db01 -u dba -p --ssl-mode=REQUIRED
mysql -e "SELECT @@hostname, @@version, @@read_only"
mysqladmin -u dba -p status                          # uptime, threads, queries
mysqladmin -u dba -p processlist
mysqladmin -u dba -p extended-status | grep -E 'Threads_connected|Slow_queries'
mysqlsh dba@db01 --sql                               # MySQL Shell (also --js / --py)

In the client: \G vertical output, \s status, pager less -S, source file.sql.

#Server status and variables

SELECT @@version, @@hostname, @@port, @@datadir, @@server_id, @@read_only, @@super_read_only;
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Uptime';
SET PERSIST max_connections = 800;                   -- persists across restarts (mysqld-auto.cnf)
SELECT * FROM performance_schema.persisted_variables;

#Sessions and locks

SHOW FULL PROCESSLIST;
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS query
FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;

KILL QUERY 1234;     -- stop the statement
KILL 1234;           -- end the connection

-- who blocks whom (InnoDB row locks)
SELECT * FROM sys.innodb_lock_waits\G

-- metadata locks (e.g. ALTER TABLE waiting)
SELECT * FROM sys.schema_table_lock_waits\G

-- long-running transactions
SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_mysql_thread_id, trx_rows_modified
FROM information_schema.innodb_trx ORDER BY trx_started;

#Slow queries and statements

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SHOW VARIABLES LIKE 'slow_query_log_file';

SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10\G
SELECT * FROM sys.statements_with_full_table_scans ORDER BY exec_count DESC LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 10;

EXPLAIN ANALYZE SELECT ...;                     -- 8.0.18+
EXPLAIN FORMAT=TREE SELECT ...;

pt-query-digest /var/lib/mysql/db01-slow.log > digest.txt      # Percona Toolkit

#InnoDB health

SHOW ENGINE INNODB STATUS\G                     -- latest deadlock, history list length, buffer pool
SELECT name, count FROM information_schema.innodb_metrics
WHERE name IN ('trx_rseg_history_len','buffer_pool_reads','buffer_pool_read_requests','lock_deadlocks');

-- buffer pool hit ratio (should be close to 100%)
SELECT ROUND(100 * (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads')
  / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')), 2) AS hit_pct;

SET GLOBAL innodb_print_all_deadlocks = ON;     -- log every deadlock to the error log

#Sizes

SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS gb
FROM information_schema.tables GROUP BY table_schema ORDER BY gb DESC;

SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024) AS mb,
       ROUND(data_free / 1024 / 1024) AS free_mb, table_rows
FROM information_schema.tables WHERE table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY data_length + index_length DESC LIMIT 20;

#Indexes

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
SHOW INDEX FROM sales.orders;
ALTER TABLE sales.orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
ANALYZE TABLE sales.orders;
OPTIMIZE TABLE sales.orders;                    -- rebuilds the table; reclaims space

pt-online-schema-change --alter "ADD COLUMN note varchar(200)" D=sales,t=orders --execute
gh-ost --database=sales --table=orders --alter="ADD COLUMN note varchar(200)" --execute

#Users and privileges

CREATE USER 'app'@'10.20.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON sales.* TO 'app'@'10.20.%';
SHOW GRANTS FOR 'app'@'10.20.%';
ALTER USER 'app'@'10.20.%' IDENTIFIED BY 'new-password';
ALTER USER 'app'@'10.20.%' ACCOUNT LOCK;
SELECT user, host, account_locked, password_last_changed, plugin FROM mysql.user ORDER BY user;
DROP USER 'old'@'%';

#Binary logs

SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;                          -- 8.4 (SHOW MASTER STATUS in 8.0)
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY; -- only if replicas and backups don't need them
SELECT @@binlog_expire_logs_seconds, @@gtid_mode, @@gtid_executed;

mysqlbinlog --start-datetime="2026-10-23 09:00:00" --stop-datetime="2026-10-23 09:15:00" \
  --base64-output=DECODE-ROWS -vv binlog.000123 | less

#Replication (GTID)

-- on a replica
SHOW REPLICA STATUS\G           -- Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, Last_Error
STOP REPLICA; START REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='db01', SOURCE_USER='repl', SOURCE_PASSWORD='...',
  SOURCE_AUTO_POSITION=1, SOURCE_SSL=1;
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

-- on the source
SHOW REPLICAS;                   -- 8.0.22+ (SHOW SLAVE HOSTS before)

Never "skip" errors on a GTID replica without understanding them; inject an empty transaction only when you're sure the change is already applied.

#Backup and restore

# logical (small/medium databases)
mysqldump --single-transaction --routines --triggers --events --source-data=2 \
  --set-gtid-purged=ON -h db01 -u dba -p sales > sales.sql
mysql -h db02 -u dba -p sales < sales.sql

# MySQL Shell: parallel dump and load (much faster than mysqldump)
mysqlsh dba@db01 -- util dump-schemas sales --outputUrl=/backup/sales_dump --threads=8
mysqlsh dba@db02 -- util load-dump /backup/sales_dump --threads=8

# physical hot backup (Percona XtraBackup)
xtrabackup --backup --user=backup --password=... --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full     # into an empty datadir, then chown mysql

#Error log

SELECT logged, prio, error_code, data FROM performance_schema.error_log
ORDER BY logged DESC LIMIT 30;
SHOW VARIABLES LIKE 'log_error';

Tip

The sys schema answers most "why is it slow?" questions in one query: sys.statement_analysis, sys.innodb_lock_waits, sys.schema_table_lock_waits and sys.host_summary. Learn those four before anything else.