1Z0-908 Syllabus — Learning Objectives by Topic

Use this syllabus as your checklist for 1Z0‑908.

What’s covered

• Topic 1: Installation and Architecture
• Topic 2: Configuration, Startup, and Logging
• Topic 3: Security Administration
• Topic 4: InnoDB Operations and Storage
• Topic 5: Backup and Recovery
• Topic 6: Replication Fundamentals
• Topic 7: High Availability and Cluster Patterns
• Topic 8: Performance Monitoring and Tuning
• Topic 9: Maintenance, Upgrades, and Automation
• Topic 10: Troubleshooting and Incident Response

Topic 1: Installation and Architecture

Practice this topic →

1.1 MySQL server architecture and components

• Describe core MySQL server components (mysqld, storage engines, system tables) and how they interact.
• Differentiate server configuration from schema configuration and explain where each is stored.
• Identify the purpose of the data directory and the types of files commonly stored there (concept-level).
• Given a scenario, identify whether a symptom is likely client-side, server-side, or storage-engine related.

1.2 Installation, initialization, and first secure setup

• Explain the high-level steps to install MySQL 8.0 and initialize the data directory (concept-level).
• Identify default accounts and initial security steps (set root password, remove unsafe defaults).
• Given a scenario, choose safe defaults for production initialization (least privilege, limited remote access).
• Recognize common installation pitfalls: port conflicts, filesystem permissions, and missing dependencies.

1.3 Client tools, connectivity, and basic validation

• Use the MySQL client to connect and validate basic server health (version, variables, status).
• Explain how host-based accounts (`'user'@'host'`) affect authentication and access control.
• Given a scenario, troubleshoot connection failures by checking networking, port, and credentials.
• Identify common administrative tools (mysqladmin, mysqldump, mysqlbinlog) and when to use each.

Topic 2: Configuration, Startup, and Logging

Practice this topic →

2.1 Configuration files, precedence, and variables

• Explain the purpose of option files (`my.cnf`) and how option groups affect which settings apply.
• Given a scenario, determine why a variable value differs from what is in a config file (precedence).
• Differentiate dynamic vs static variables and identify when a restart is required.
• Use `SHOW VARIABLES` to inspect configuration state and verify effective values.

2.2 Runtime changes and persistence

• Change runtime configuration safely using `SET GLOBAL` (concept-level) and verify impact.
• Explain the difference between session variables and global variables and choose correctly for a scenario.
• Recognize configuration changes that can cause outages (connection limits, authentication settings, memory sizing).
• Given a scenario, roll back a risky configuration change and restore known-good settings (concept-level).

2.3 Logging and auditability basics

• Differentiate error log, general query log, and slow query log and explain typical use cases.
• Given a scenario, choose the safest logging approach to diagnose an issue without creating excessive overhead.
• Explain why binary logs are critical for replication and point-in-time recovery (concept-level).
• Identify log rotation and retention considerations to prevent disk exhaustion.

Topic 3: Security Administration

Practice this topic →

3.1 Users, roles, and privileges

• Create users and grant/revoke privileges using least privilege principles.
• Use roles to simplify privilege management and set default roles for accounts.
• Given a scenario, design a privilege model for an application and an analyst role with minimal access.
• Identify common privilege pitfalls: overly broad grants, wildcard hosts, and shared admin accounts.

3.2 Authentication, passwords, and account policies

• Inspect authentication plugins used by accounts and explain why plugin choice matters (concept-level).
• Given a scenario, choose safer password and authentication practices for production accounts.
• Recognize the difference between account lockout policy needs vs operational access needs (concept-level).
• Identify how to rotate credentials safely without breaking production applications (concept-level).

3.3 Secure connectivity, encryption, and auditing

• Explain TLS usage for client connections and identify when to require encryption (concept-level).
• Given a scenario, reduce attack surface by restricting network exposure and administrative access paths.
• Recognize where sensitive data can leak (logs, backups, client configs) and how to reduce exposure.
• Identify audit trail requirements for privileged actions and how to preserve audit integrity (concept-level).

Topic 4: InnoDB Operations and Storage

Practice this topic →

4.1 InnoDB architecture and sizing

• Explain what the InnoDB buffer pool does and why sizing affects performance.
• Given a scenario, identify memory pressure symptoms and choose a safe buffer pool sizing strategy (concept-level).
• Recognize the role of redo logs and undo logs in durability and consistent reads (concept-level).
• Inspect InnoDB status output at a high level to identify contention or recovery conditions.

4.2 Transactions, isolation, and locking (ops view)

• Explain transaction boundaries and why long transactions increase lock contention and undo growth.
• Given a scenario, relate isolation/locking behavior to application symptoms (blocking, timeouts).
• Recognize deadlock situations and explain why deadlocks can be normal under concurrency.
• Identify operational strategies to reduce locking issues (short transactions, proper indexes, batching).

4.3 Schema maintenance and table operations

• Use basic maintenance operations (ANALYZE/OPTIMIZE) at a conceptual level and explain when they help.
• Given a scenario, choose online vs offline schema changes based on downtime tolerance (concept-level).
• Recognize risks of large schema changes (table rebuilds, lock time, disk usage) and mitigation approaches.
• Identify when to rebuild or reorganize tables/indexes to address fragmentation or statistics issues (concept-level).

Topic 5: Backup and Recovery

Practice this topic →

5.1 Logical backups and restores

• Create logical backups with tools like `mysqldump` and include routines/triggers/events when needed.
• Given a scenario, choose options that reduce lock impact during logical backups (for example, `--single-transaction`).
• Restore logical backups safely and validate schema/data correctness after restore.
• Identify common logical backup pitfalls: missing privileges, missing objects, and large restore time.

5.2 Physical backups and snapshot concepts

• Differentiate logical vs physical backups and explain trade-offs (speed, portability, consistency).
• Given a scenario, choose a physical backup approach that meets recovery speed requirements (concept-level).
• Recognize consistency requirements for filesystem snapshots (quiescing, flush, or coordinated backup tooling).
• Identify why storing backups securely (encryption, access control, immutability) is part of the recovery plan.

5.3 Point-in-time recovery (PITR) and testing

• Explain the PITR workflow: full backup + binary logs to a target time or position (concept-level).
• Given a scenario, decide whether PITR is needed based on corruption vs outage recovery requirements.
• Validate that binary logging is enabled and retained sufficiently to meet recovery requirements.
• Design restore tests and runbooks to validate RPO/RTO assumptions before an incident happens.

Topic 6: Replication Fundamentals

Practice this topic →

6.1 Replication architecture and configuration basics

• Explain how binary logs and relay logs support replication (concept-level).
• Create a replication user with the minimum privileges required for replication (concept-level).
• Given a scenario, choose initial replica provisioning methods (backup/restore vs clone vs snapshot) at a conceptual level.
• Identify common replication prerequisites: server IDs, binary logging, and network connectivity.

6.2 GTID and replication positioning (concept-level)

• Explain what GTIDs are and why they simplify failover and replica positioning (concept-level).
• Given a scenario, decide whether GTID-based replication is appropriate for your topology (concept-level).
• Recognize common causes of replication divergence and how GTID helps detect it (concept-level).
• Identify safe practices for promoting a replica to source and re-pointing other replicas (concept-level).

6.3 Monitoring and troubleshooting replication

• Use replication status output to identify thread state, errors, and lag symptoms at a high level.
• Given a scenario, troubleshoot replication lag by checking workload saturation, slow queries, and network throughput.
• Recognize common replication breakages: schema drift, privilege issues, and data conflicts.
• Design alerting that detects replication failure and lag before it becomes an outage.

Topic 7: High Availability and Cluster Patterns

Practice this topic →

7.1 Failover and read scaling strategies

• Differentiate manual vs automated failover and identify operational risks of each.
• Given a scenario, choose read scaling patterns (read replicas, split reads/writes) and justify trade-offs.
• Explain how application connection handling affects failover success (timeouts, retry logic, DNS/routers) at a conceptual level.
• Recognize the difference between HA (fast failover) and DR (regional failure recovery) at a conceptual level.

7.2 Group Replication / InnoDB Cluster awareness (concept-level)

• Explain the goal of Group Replication / InnoDB Cluster at a conceptual level (fault tolerance, primary election).
• Given a scenario, decide when classic asynchronous replication is sufficient vs when a cluster is justified.
• Recognize operational requirements for clustered setups: network stability, quorum, and monitoring (concept-level).
• Identify why split-brain prevention and consistency modes matter for HA clusters (concept-level).

7.3 HA/DR design with backups and replication

• Design an HA/DR approach that combines replication and backups to meet RPO/RTO goals.
• Given a scenario, choose backup retention and binlog retention that supports failover and PITR needs.
• Recognize that replicas are not backups and explain failure modes that replication cannot protect against.
• Identify validation steps for failover readiness (drills, runbooks, monitoring, and access controls).

Topic 8: Performance Monitoring and Tuning

Practice this topic →

8.1 Performance fundamentals (ops perspective)

• Differentiate server resource constraints (CPU, memory, disk I/O) and how each manifests in symptoms.
• Given a scenario, choose whether to investigate query patterns vs capacity limits first.
• Recognize the role of indexes and schema design in performance, even for DBA responsibilities (concept-level).
• Explain why baselines and trend monitoring are required to detect regressions.

8.2 Slow query workflow and EXPLAIN awareness

• Enable and use the slow query log safely to identify high-impact statements.
• Given a slow query, choose the next diagnostic step (EXPLAIN, indexes, statistics, data volume).
• Recognize common query anti-patterns that DBAs should flag (N+1 queries, missing predicates, unbounded sorts).
• Identify when caching, schema changes, or query rewrites are the most appropriate remediation (concept-level).

8.3 Performance Schema / sys schema awareness

• Explain what `performance_schema` is used for at a high level (instrumentation, waits, statement metrics).
• Given a scenario, use instrumentation data to identify top waits or top statements (concept-level).
• Recognize the difference between transient spikes and sustained saturation and what to alert on.
• Identify operational risks of over-instrumentation and how to balance visibility with overhead (concept-level).

Topic 9: Maintenance, Upgrades, and Automation

Practice this topic →

9.1 Upgrades and compatibility planning

• Explain why upgrade planning requires backups, staging tests, and rollback decisions (concept-level).
• Given a scenario, identify compatibility risks (deprecated options, authentication changes, SQL mode differences).
• Recognize why client driver versions and application behavior must be validated during upgrades.
• Design a safe upgrade runbook that includes verification steps and post-upgrade monitoring.

9.2 Disk management, retention, and housekeeping

• Given a scenario, identify disk consumption drivers (binlogs, undo growth, temp files, logs) and mitigations.
• Explain why log rotation and retention policies prevent outages from full disks.
• Recognize maintenance tasks that reduce risk: statistics refresh, index review, and backup verification.
• Identify safe practices for purging binary logs without breaking replication (concept-level).

9.3 Automation and operational runbooks

• Design automation that is idempotent and safe to retry (for backups, provisioning, and checks).
• Given a scenario, choose alerting and runbook actions that support faster incident response.
• Recognize secrets management needs for automation accounts and rotation policies (concept-level).
• Identify why periodic disaster recovery drills improve operational readiness and reduce mean time to recover.

Topic 10: Troubleshooting and Incident Response

Practice this topic →

10.1 Startup failures and crash recovery

• Given a startup failure, identify whether the likely cause is configuration, permissions, or corrupted files.
• Explain crash recovery at a conceptual level and why redo logs and recovery time matter.
• Recognize common causes of startup loops: invalid options, missing directories, and port conflicts.
• Identify safe triage steps: preserve logs, avoid destructive changes, and validate backups before risky actions.

10.2 Connectivity, authentication, and privilege issues

• Troubleshoot login failures by checking account host patterns, passwords, and plugin requirements.
• Given a scenario, diagnose TLS failures (certificate mismatch, client requirements) at a conceptual level.
• Identify privileges required for common admin operations and how to grant them safely.
• Recognize when networking vs MySQL configuration is the root cause (firewalls, bind address, ports).

10.3 Data correctness incidents (corruption, divergence, human error)

• Differentiate outage recovery from logical corruption recovery and choose the correct response path.
• Given a scenario, decide whether to restore from backup, use PITR, or fail over to a replica.
• Recognize replication divergence symptoms and identify safe remediation steps (concept-level).
• Design post-incident actions: root-cause analysis, runbook updates, and preventive controls.