1Z0-908 Cheatsheet — MySQL 8.0 DBA (Backups, Replication, Security, Performance)

Use this for last‑mile review. The exam rewards operational realism: correct defaults, safe recovery steps, and clear HA trade-offs.

Server basics

Start/stop and basic health checks

1systemctl status mysqld
2mysqladmin ping
3mysql -e "SELECT VERSION(), @@hostname, @@port, @@datadir"

Key configuration locations (varies by distro)

• Linux common: /etc/my.cnf, /etc/mysql/my.cnf, /etc/mysql/mysql.conf.d/mysqld.cnf
• Data directory: @@datadir
• Logs: error log, general log, slow query log (enable intentionally)

Security and accounts

Users and privileges

1CREATE USER 'app'@'10.%' IDENTIFIED BY 'StrongPass!';
2GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'10.%';
3FLUSH PRIVILEGES;
4SHOW GRANTS FOR 'app'@'10.%';

Roles (MySQL 8.0)

1CREATE ROLE 'read_only';
2GRANT SELECT ON appdb.* TO 'read_only';
3GRANT 'read_only' TO 'analyst'@'%';
4SET DEFAULT ROLE 'read_only' TO 'analyst'@'%';

Authentication plugins (concept-level)

• Know how to inspect plugins and user auth methods:

1SELECT user, host, plugin FROM mysql.user;

TLS (concept-level)

• Prefer encrypted connections for non-local traffic.
• Be able to reason about: server certificates, client verification, and enforcing TLS.

InnoDB essentials (operations)

Core facts

• Default engine is InnoDB for MySQL 8.0.
• ACID via redo/undo; row-level locking; MVCC for consistent reads.
• Buffer pool sizing is a key performance lever.

Inspect engine status

1SHOW ENGINE INNODB STATUS;

Find the engine and table stats

1SHOW TABLE STATUS LIKE 'orders';
2SELECT table_schema, table_name, engine
3FROM information_schema.tables
4WHERE table_schema='appdb';

Backups and recovery

Choose backup type (exam-style decision)

Logical backup with mysqldump

1mysqldump --single-transaction --routines --triggers --events \
2  --databases appdb > appdb.sql

Restore

1mysql < appdb.sql

Binary logs (PITR basics)

1SHOW VARIABLES LIKE 'log_bin';
2SHOW BINARY LOGS;

1mysqlbinlog --start-datetime="2025-12-01 00:00:00" binlog.000123 | mysql

Replication and HA

Replication basics (concept-level)

• Know the moving parts: source (primary), replica(s), binary log, relay log, replication user, GTID (often used).
• Know how to check status on source/replica.

1SHOW MASTER STATUS;
2SHOW REPLICA STATUS\\G

Common replication troubleshooting checklist

• Network and credentials (replication user privileges)
• Replica SQL thread errors (schema drift, data conflicts)
• Lag metrics and resource saturation
• GTID and auto-positioning configuration (concept-level)

MySQL Shell / InnoDB Cluster (concept-level)

• Be able to recognize when Group Replication / InnoDB Cluster is appropriate vs classic async replication.

Performance troubleshooting

Slow query log and EXPLAIN

1SHOW VARIABLES LIKE 'slow_query_log%';
2SHOW VARIABLES LIKE 'long_query_time';
3EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Index sanity checks

1SHOW INDEX FROM orders;

High-yield reminders:

• Put selective predicates first in composite indexes (workload-dependent).
• Avoid functions on indexed columns in WHERE when it prevents index use.
• Watch for implicit type conversions that break index usage (concept-level).

Performance Schema (concept-level)

• Know it exists and is used for instrumentation and wait/event analysis.
• Be able to interpret “top statements” and wait categories at a high level.

Logging and maintenance

Error log and common inspection commands

1SHOW VARIABLES LIKE 'log_error%';

Upgrades (concept-level)

• Read release notes, test in staging, validate backups, and plan rollback.
• Know that upgrade issues often involve deprecated settings, authentication changes, and SQL mode differences.

Quick glossary (DBA)