Exam Code
1Z0-908 · multiple choice
Credential
OCP · MySQL 8.0 Database Administrator
Coverage
7 domains · Architecture → High Availability
Duration / Questions / Pass
Varies by source → confirm at education.oracle.com
Retirement Date
Nov 16, 2026 · final exam window
01
Architecture Mimari
| Topic | What You Need to Know |
|---|
| Client Connections | TCP/IP 3306, X Protocol 33060, Unix socket (Linux), named pipe / shared memory (Windows). On Linux localhost → socket; 127.0.0.1 → TCP. Force TCP with --protocol=TCP. |
| Data Storage | Transactional Data Dictionary stored in InnoDB (mysql.ibd). No .frm / .TRG / .par files. Metadata serialized as SDI inside .ibd → inspect via ibd2sdi. |
| InnoDB Files | ibdata1 (system TS, change buffer) · file_per_table .ibd (default ON) · general TS · undo_001/002 · ibtmp1 + session temp · doublewrite #ib_*.dblwr (8.0.20+) · redo: ib_logfile* → 8.0.30+ #innodb_redo/ |
| Buffers & Caches | Global: innodb_buffer_pool_size, innodb_log_buffer_size, table_open_cache, key_buffer_size (MyISAM).
Per-session: sort_buffer_size, join_buffer_size, read_buffer_size, tmp_table_size. |
| Data Dictionary | INFORMATION_SCHEMA consists of views over internal DD tables. DD tables cannot be accessed directly. Cached table stats: information_schema_stats_expiry (86400s). |
Quick Discovery
SELECT SPACE, NAME, ROW_FORMAT, SPACE_TYPE
FROM information_schema.INNODB_TABLESPACES;
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SELECT @@innodb_redo_log_capacity; -- 8.0.30+
Exam Pitfall: Query Cache was completely removed in MySQL 8.0 — all query_cache_* variables produce errors. Eliminate any answer choice suggesting the query cache.
Memory Sizing Formula: Total Memory ≈ global buffers + (max_connections × per-session buffers). Increasing session buffers multiplies across active client connections.
02
Installation & Configuration Install & Config
| Topic | What You Need to Know |
|---|
| Installation | RPM/DEB repo, generic tarball, MSI, Docker. mysqld --initialize → random temporary root password written to error log; --initialize-insecure → empty root password. |
| Generated Files | datadir (/var/lib/mysql), auto.cnf (server_uuid), auto-generated ca.pem / server-cert.pem, mysqld-auto.cnf (SET PERSIST), error log. |
| Start / Stop | systemctl start mysqld, mysqld_safe, SQL SHUTDOWN / RESTART, mysqladmin shutdown. |
| Upgrades | 8.0.16+ upgrades are executed automatically by the server; mysql_upgrade is deprecated. --upgrade=AUTO|NONE|MINIMAL|FORCE. Pre-check: MySQL Shell util.checkForServerUpgrade(). In-place vs logical. |
| Option Files | Order: /etc/my.cnf → /etc/mysql/my.cnf → SYSCONFDIR/my.cnf → $MYSQL_HOME/my.cnf → ~/.my.cnf → ~/.mylogin.cnf. Last read wins. --defaults-file, --defaults-extra-file, --no-defaults, my_print_defaults. |
| Variables | SET GLOBAL → lost on restart · SET PERSIST → immediate + persistent · SET PERSIST_ONLY → read-only vars, applied on next restart · RESET PERSIST. Audit via performance_schema.variables_info. |
| Multi-Instance | Requires dedicated datadir, port, socket, pid-file, server_id per instance. Managed via mysqld_multi + [mysqldN] groups or systemd mysqld@N. |
Persisted Config & Obfuscated Login
SET PERSIST max_connections = 500;
SET PERSIST_ONLY innodb_log_file_size = 1073741824;
SELECT VARIABLE_NAME, VARIABLE_SOURCE
FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE = 'PERSISTED';
# ~/.mylogin.cnf (obfuscated)
$ mysql_config_editor set --login-path=dba \
--user=root --host=localhost --password
Exam Pitfall: If the same option is repeated across files, the last read option takes precedence. mysqld-auto.cnf (SET PERSIST) is processed after all standard option files.
03
Security Hardening & RBAC
High Weight
| Topic | What You Need to Know |
|---|
| Accounts | 'user'@'host' — most specific host matches first. CREATE USER, ALTER USER, RENAME USER, DROP USER. |
| Auth Plugins | Default: caching_sha2_password. Legacy: mysql_native_password. auth_socket, sha256_password; Enterprise: LDAP, PAM, Kerberos. |
| Roles | CREATE ROLE → GRANT role TO user. Roles are inactive by default: activate via SET DEFAULT ROLE or activate_all_roles_on_login=ON. mandatory_roles. |
| Privileges | Global / DB / table / column / routine levels. Dynamic privileges: BACKUP_ADMIN, SYSTEM_VARIABLES_ADMIN, CLONE_ADMIN… partial_revokes, WITH GRANT OPTION. |
| Passwords | component_validate_password, PASSWORD EXPIRE, password_history, FAILED_LOGIN_ATTEMPTS + PASSWORD_LOCK_TIME, dual passwords: RETAIN CURRENT PASSWORD. |
| Transport SSL | require_secure_transport=ON, REQUIRE SSL|X509, tls_version. SSL certificates auto-generated during data directory initialization. |
| Encryption (TDE) | InnoDB TDE requires a keyring component/plugin. ENCRYPTION='Y', default_table_encryption, binlog_encryption, redo/undo encryption. ALTER INSTANCE ROTATE INNODB MASTER KEY. |
| OS & SQLi | Run under OS user mysql, configure secure_file_priv, set local_infile=OFF. Mitigate SQLi with prepared statements. Enterprise Firewall: RECORDING → PROTECTING / DETECTING. |
Role-Based Account Provisioning
CREATE ROLE 'app_ro';
GRANT SELECT ON shop.* TO 'app_ro';
CREATE USER 'ali'@'10.0.%' IDENTIFIED BY 'S3cr3t!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
GRANT 'app_ro' TO 'ali'@'10.0.%';
SET DEFAULT ROLE ALL TO 'ali'@'10.0.%';
Exam Pitfall: In MySQL 8.0, GRANT ... IDENTIFIED BY cannot create users — you must run CREATE USER first. Older client libraries without caching_sha2 support fail to connect.
MySQL OCP 8.0 Exam Topic Map · Monitoring, Query Optimization & Backup
04
Monitoring & Maintenance Monitoring
| Topic | What You Need to Know |
|---|
| Error Log | log_error, log_error_verbosity (1–3, default 2), log_error_services (components for JSON sink). |
| General / Slow Log | general_log · slow_query_log, long_query_time (default 10s), log_queries_not_using_indexes, min_examined_row_limit. log_output=FILE|TABLE. Analysis: mysqldumpslow. |
| Binary Log | Enabled by default in 8.0. binlog_format=ROW, binlog_expire_logs_seconds (30 days), PURGE BINARY LOGS, FLUSH BINARY LOGS. |
| Audit | MySQL Enterprise Audit: audit_log plugin, JSON/XML formats, rule-based filtering with audit_log_filter_set_filter(). |
| Threads & Status | SHOW PROCESSLIST, performance_schema.threads, sys.processlist, KILL [QUERY]. Status: Threads_connected, Threads_running, Aborted_connects, Max_used_connections. |
| Enterprise Monitor | Service Manager + Agent architecture, Advisors, events, Query Analyzer, real-time dashboards. |
| Capacity Planning | DATA_LENGTH + INDEX_LENGTH, DATA_FREE (I_S.TABLES), INNODB_TABLESPACES.FILE_SIZE, sys.schema_table_statistics; track historical growth trends. |
| Locks | performance_schema.data_locks / data_lock_waits (new in 8.0), metadata_locks, sys.innodb_lock_waits, innodb_lock_wait_timeout (50s), innodb_print_all_deadlocks. |
Who is Blocking Whom?
SELECT waiting_pid, waiting_query,
blocking_pid, blocking_query,
wait_age, sql_kill_blocking_query
FROM sys.innodb_lock_waits;
-- Metadata lock (waiting for DDL):
SELECT * FROM sys.schema_table_lock_waits;
-- Recent deadlock information:
SHOW ENGINE INNODB STATUS\G
Exam Pitfall: information_schema.INNODB_LOCKS and INNODB_LOCK_WAITS were removed in 8.0 → replaced by performance_schema.data_locks and data_lock_waits.
05
Query Optimization Optimizer & Indexes
| Topic | What You Need to Know |
|---|
| EXPLAIN Variants | EXPLAIN (tabular), FORMAT=JSON (costs), FORMAT=TREE (8.0.16), EXPLAIN ANALYZE (8.0.18 — actually executes the query). |
| Optimizer | Cost-based; mysql.server_cost / engine_cost. optimizer_switch, optimizer_trace, hints like /*+ INDEX(t idx) */, hash joins (8.0.18+). |
| 8.0 Index Features | Invisible indexes (test before drop), descending indexes, functional indexes (8.0.13), histograms: ANALYZE TABLE t UPDATE HISTOGRAM ON c. |
| Index Design | Clustered PK → secondary indexes append PK columns. Leftmost prefix rule, covering indexes, prefix indexing, FULLTEXT, SPATIAL. |
| Index Statistics | innodb_stats_persistent=ON, innodb_stats_persistent_sample_pages (20), ANALYZE TABLE, mysql.innodb_index_stats, SHOW INDEX → Cardinality. |
| Query Analysis | MEM Query Analyzer, events_statements_summary_by_digest, sys.statements_with_full_table_scans, sys.schema_unused_indexes, sys.schema_redundant_indexes. |
EXPLAIN type — from best to worst
systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL
| Extra Value | Meaning & Impact |
|---|
Using index | Covering index — table row access avoided |
Using index condition | ICP (Index Condition Pushdown) — filter pushed to engine |
Using filesort | Extra sorting pass — ORDER BY not satisfied by index |
Using temporary | Temporary table — GROUP BY / DISTINCT / UNION |
Test Index Before Dropping
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;
EXPLAIN ANALYZE SELECT * FROM orders WHERE cust_id = 42;
CREATE INDEX idx_fx ON users ((LOWER(email)));
Exam Pitfall: Primary keys cannot be made invisible. Histograms are not indexes — they provide selectivity estimates for non-indexed columns and do not auto-update.
06
Backup & Recovery Logical & Physical
High Weight
| Tool | Type | Key Capabilities |
|---|
mysqldump | Logical | --single-transaction (consistent hot InnoDB backup), --source-data=2, --routines --events, --set-gtid-purged |
mysqlpump | Logical | Multi-threaded (--default-parallelism); deprecated in 8.0.34 |
Shell util.* | Logical | dumpInstance(), dumpSchemas(), loadDump() — multi-threaded, compressed, chunked |
| MEB | Physical | mysqlbackup: backup-and-apply-log, --incremental, backup-to-image, validate, copy-back · Hot for InnoDB |
| Raw / Snapshot | Physical | Cold copy (server stopped), LVM/ZFS snapshots, transportable tablespaces: FLUSH TABLES t FOR EXPORT |
| CLONE plugin | Physical | 8.0.17+: CLONE INSTANCE FROM — provision new replica/cluster member in seconds |
mysqlbinlog | Binlog | --read-from-remote-server --raw --stop-never for real-time remote binlog streaming |
Core Concepts
| Hot / Warm / Cold | Read+write available / Read-only available / Server stopped |
| Full / Incr. / Diff. | Entire database / Changes since last backup / Changes since last full backup |
| Logical vs Physical | SQL statements, portable, slower restore / Raw file copy, fast restore, requires same OS/version |
Point-in-Time Recovery (PITR)
$ mysqldump --single-transaction --source-data=2 \
--routines --events --all-databases > full.sql
# 1) Restore full baseline backup
$ mysql < full.sql
# 2) Replay binary logs up to the exact incident timestamp
$ mysqlbinlog --start-position=157 \
--stop-datetime="2026-10-13 09:59:59" \
binlog.000042 binlog.000043 | mysql
Exam Pitfall: --single-transaction does not guarantee consistency for MyISAM tables. In 8.0.26+, --master-data was renamed to --source-data.
MySQL OCP 8.0 Exam Topic Map · High Availability & Exam Preparation Plan
07
High Availability Replication · Group Replication · InnoDB Cluster
Highest Weight
Replication Architecture
| Use Cases | Read scale-out, failover targets, offloading backup overhead, geo-redundancy, analytics isolation. |
| Modes | Async (default), semi-sync (rpl_semi_sync_source plugin — requires ≥1 replica ACK), GTID or binlog coordinates. |
| Topologies | Source → replica, chained (relay; log_replica_updates), multi-source (FOR CHANNEL), circular. |
| Threads | Source: binlog dump · Replica: receiver (I/O) → relay log → applier (SQL) → workers (replica_parallel_workers, LOGICAL_CLOCK). |
| Prerequisites | Unique server_id, binary log enabled, dedicated user with REPLICATION SLAVE, for GTID: gtid_mode=ON + enforce_gtid_consistency=ON. |
| Monitoring | SHOW REPLICA STATUS\G: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, Last_SQL_Error. P_S: replication_connection_status, replication_applier_status_by_worker. |
| Troubleshooting | Skip an errant GTID: inject an empty transaction matching the failed GTID. sql_replica_skip_counter only works when GTID is OFF. Broken replica → reseed with CLONE. |
GTID Replication Setup (8.0.23+ syntax)
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='db1', SOURCE_USER='repl', SOURCE_PASSWORD='***',
SOURCE_AUTO_POSITION=1, SOURCE_SSL=1;
START REPLICA;
-- Skip errant GTID transaction:
STOP REPLICA; SET GTID_NEXT='uuid:1234';
BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; START REPLICA;
Group Replication & InnoDB Cluster
| Group Replication | Paxos-based (XCom) group communication; single-primary (default) or multi-primary mode. Certification-based conflict detection. |
| GR Requirements | InnoDB only, Primary Key on every table, GTID enabled, binlog_format=ROW, group communication port 33061. |
| Quorum | n = 2f + 1 → 3 members tolerate 1 failure, 5 members tolerate 2 failures. Maximum 9 members. |
| InnoDB Cluster | Group Replication + MySQL Shell (AdminAPI) + MySQL Router. Router default ports: 6446 RW / 6447 RO (classic), 6448/6449 (X). |
| Recovery | Distributed recovery: incremental via binary logs or automatic CLONE. Full outage recovery: dba.rebootClusterFromCompleteOutage(). Loss of quorum: forceQuorumUsingPartitionOf(). |
| Companion Solutions | InnoDB ReplicaSet (8.0.19+, async, manual failover), InnoDB ClusterSet (8.0.27+, multi-cluster disaster recovery). |
MySQL Shell — AdminAPI (JS mode)
dba.checkInstanceConfiguration('admin@db1:3306')
dba.configureInstance('admin@db1:3306')
var c = dba.createCluster('prodCluster')
c.addInstance('admin@db2:3306', {recoveryMethod: 'clone'})
c.status() // ONLINE / RECOVERING / UNREACHABLE
c.setPrimaryInstance('db2:3306')
c.rejoinInstance('db3:3306')
$ mysqlrouter --bootstrap admin@db1:3306 --user=mysqlrouter
Exam Pitfall: Terminology transitioned between 8.0.22 and 8.0.26: MASTER→SOURCE, SLAVE→REPLICA. Both legacy and modern syntax may appear in questions — recognize both.
08
Exam Preparation Checklist
| Set up lab: 3-node VM/Docker with InnoDB Clusterdba.createCluster → addInstance → Router | Critical |
| PITR rehearsal: restore dump + replay binlogmysqldump → mysqlbinlog --stop-datetime | Critical |
| Break & fix replicationduplicate key → empty GTID tx → START REPLICA | Critical |
| User & role scenariosroles inactive by default → SET DEFAULT ROLE | Critical |
| Option file precedence & SET PERSISTmy_print_defaults mysqld | Important |
| Read EXPLAIN output: type + ExtraEXPLAIN FORMAT=TREE / ANALYZE | Important |
| Lock triage (row + metadata locks)sys.innodb_lock_waits | Important |
| TDE + keyring configurationALTER TABLE t ENCRYPTION='Y' | Important |
| Enterprise tools conceptual understandingMEB · MEM · Audit · Firewall | Recommended |
| Memorize crucial default valuesports 3306/33060/33061 · 6446/6447 | Recommended |
Critical Must practice hands-on Important Practice at least once Recommended Understand conceptually
Strategy: Pay close attention to "Choose two" or "Choose three" prompts. Eliminate obvious false choices first; focus on version-specific features (8.0.x) and default values.
Time Constraint: 1Z0-908 retires on November 16, 2026. For subsequent cycles, follow Oracle's updated MySQL DBA learning path.
09
Essential Default Values to Memorize MySQL 8.0 defaults
| Network & Connections | Default |
|---|
| port | 3306 |
| mysqlx_port | 33060 |
| group_replication (XCom) | 33061 |
| Router RW / RO (classic) | 6446 / 6447 |
| max_connections | 151 |
| wait_timeout | 28800s |
| default_authentication_plugin | caching_sha2_password |
| InnoDB Engine | Default |
|---|
| innodb_buffer_pool_size | 128M |
| innodb_page_size | 16K |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_lock_wait_timeout | 50s |
| innodb_autoinc_lock_mode | 2 (interleaved) |
| transaction_isolation | REPEATABLE-READ |
| Logs, Binlog & Character Set | Default |
|---|
| log_bin | ON |
| binlog_format | ROW |
| binlog_expire_logs_seconds | 2592000 (30 days) |
| sync_binlog | 1 |
| long_query_time | 10s |
| gtid_mode | OFF |
| character_set_server | utf8mb4 · utf8mb4_0900_ai_ci |