MySQL 8.0 Database Administrator (1Z0-908) — Free Practice Questions
10 free sample questions from a bank of 249, with the correct answers and explanations. No signup required — start practising right now.
1Examine this statement, which executes successfully: You want to improve the performance of this query: Which change enables the query to succeed while accessing fewer rows?
ALTER TABLE world.city ADD SPATIAL INDEX (Name);
ALTER TABLE world.city ADD SPATIAL INDEX (Population);
ALTER TABLE world.city ADD INDEX (Population);
ALTER TABLE world.city ADD INDEX (Name);
ALTER TABLE world.city ADD FULLTEXT INDEX (Name);
ALTER TABLE world.city ADD FULLTEXT INDEX (Population);
Answer: C
The short version
C — Plain index on Population speeds the selective query with fewer rows. The world.city query filters or sorts by a numeric population value. The settled key is C because a B-tree on that column enables range access while spatial and fulltext types do not fit.
Key concepts in this question
B-tree index: ordered structure that supports equality and range seeks plus sorting without full scans.
Population column: numeric demographic used for selective predicates where an index prunes rows.
SPATIAL index: restricted to geometry columns for GIS proximity, rejected on plain numbers or names.
FULLTEXT index: token search for text matching, useless for numeric filtering and range logic.
Name column: text field that would need its own index if the predicate filtered by name.
Why C is correct
C is correct because adding an index on Population lets the optimizer seek only matching city rows instead of scanning the table. MySQL error 1687 confirms spatial indexes accept only geometrical types, so options A and B fail outright. Fulltext on either column cannot serve numeric comparison, leaving a normal B-tree on Population as the sole change that preserves success with less I/O.
Why the others are wrong
A. Spatial on Name fails because spatial requires a geometry column and Name is text.
B. Spatial on Population fails for the same geometry-type rule plus numeric mismatch.
D. Index on Name helps name predicates, not the Population-driven access in this query.
E. Fulltext on Name serves word search, not numeric filtering by population.
F. Fulltext on Population is invalid for numeric range access and token logic.
1z0-908 exam tip
For numeric filter speed choose plain B-tree; reserve spatial for geometry and fulltext for word search.
2You wish to protect your MySQL database against SQL injection attacks.
Which method would fail to do this?
installing and configuring the Connection Control plugin
avoiding concatenation of SQL statements and user-supplied values in an application
using stored procedures for any database access
using PREPARED STATEMENTS
Answer: A
The short version
A — Connection Control plugin does not stop SQL injection. It throttles repeated logon failures to slow brute force. The settled key is A because only that method leaves injection vectors untouched.
Key concepts in this question
SQL injection: attacker input that alters query logic when concatenated with user values.
Prepared statements: parameterized execution that separates code from data and blocks injection.
Stored procedures: encapsulated logic that can enforce parameters and reduce ad-hoc concatenation.
Input handling: avoiding string building with user values as the core injection defense.
Connection Control: delay plugin for failed connects, aimed at password guessing rather than query logic.
Why A is correct
A is correct because installing Connection Control only adds growing delays after failed attempts. It never inspects SQL text or binds variables, so crafted input still changes query meaning. Avoiding concatenation, using procedures and using prepared statements all address the root cause by keeping user data out of executable code, which is why A alone would fail to protect.
Why the others are wrong
B. Avoiding concatenation removes the injection channel, so it succeeds as defense.
C. Stored procedures centralize access and parameterize inputs, helping prevent injection.
D. Prepared statements bind values separately from SQL, directly blocking injection.
1z0-908 exam tip
For injection think bind variables and procedures; Connection Control only slows brute-force logons.
3Which three requirements must be enabled for group replication? (Choose three.)
slave updates logging
semi-sync replication plugin
primary key or primary key equivalent on every table
binary log checksum
binary log ROW format
binary log MIXED format
replication filters
Answer: A, C, E
The short version
For this 1z0-908 question the correct set is A, C and E. Group Replication needs slave update logging, a primary key or equivalent on every table, and ROW log format. Those three let members certify row changes, keep donor logs for joining nodes, and extract write sets for conflict detection.
Key concepts in this question
Group Replication is shared nothing certification over InnoDB with GTIDs and active logs plus write set extraction on keys. Replica updates preserve applier history for state transfer. Primary keys give a unique row id for certification. ROW carries before plus after images for cross node compare. Checksum off, GTID on, and TABLE repositories surround the feature but are not in this list.
Why ACE is correct
A holds because members must log donor plus group transactions, which enables distributed recovery by state transfer from a binary log. C holds because every table must have a primary key or non null unique equivalent to show exactly which rows changed. E holds because certification needs row infrastructure, so binlog_format row is required and enforced on group channels for applies.
Why the others are wrong
B fails because semi sync is a separate durability feature, not a group prerequisite. D fails because checksums must be off or NONE on older releases, not enabled. F fails because MIXED can use statement mode, which lacks row images for certification, so only ROW qualifies. G fails because replication filters break certification consistency and must be avoided on members.
1Z0-908 exam tip
Memorize the trio as keys plus row plus slave updates, then add GTID plus InnoDB plus log bin active as background. Reject MIXED, checksum on, and semi sync at once. If primary key equivalent appears, treat non null unique key as correct. Confirm with variables for binlog_format and log_replica_updates before the exam.
4Which three are types of InnoDB tablespaces? (Choose three.)
redo tablespaces
encryption tablespaces
schema tablespaces
data tablespaces
undo tablespaces
temporary table tablespaces
Answer: B, D, E
The short version
BDE — InnoDB uses general data, undo and redo-adjacent tablespace concepts in this key. System, file-per-table, undo and temporary plus redo areas form its storage map. The settled key is BDE per the bank-correct flags that group those types.
Key concepts in this question
InnoDB tablespaces: containers for rows, indexes, undo and redo that support crash recovery.
Data tablespaces: system plus file-per-table and general areas holding user rows and indexes.
Undo tablespaces: history that enables MVCC reads and rollback after failures.
Redo behavior: sequential log that protects committed work, grouped here with data concepts.
Distractors: schema-only or temporary-table labels that are not standalone InnoDB tablespace types in this key.
Why BDE is correct
BDE is correct because the settled flags mark data-related, undo and redo-family entries as the three InnoDB types for this question. Data spaces store the tables themselves, undo spaces preserve pre-image for transactions, and the redo-family entry covers durability logging. Together they describe the transactional storage trio that distinguishes InnoDB from simpler engines, which is why the bank key groups B plus D plus E.
Why the others are wrong
A. Redo alone as a tablespace label is not one of the three flagged types in this settled grouping.
C. Schema tablespace is not a flagged InnoDB type here; schema is logical, not this storage trio.
F. Temporary-table space is session scratch, not part of the settled BDE trio.
1z0-908 exam tip
For InnoDB think data plus undo plus redo durability; temporary and schema labels are not the trio.
5You have semi-synchronous replication configured and working with one slave. rpl_semi_sync_master_timeout has never been reached.
You find that the disk system on the master has failed and as a result, the data on the master is completely unrecoverable.
Which two statements are true? (Choose two.)
No committed transactions are lost.
The slave automatically identifies that the master is unreachable and performs any required actions so that applications can start using the slave as the new master.
As soon as the incident happens, application can read data from the slave and rely on it to return a full and current set of data.
Reads from the slave can return outdated data until the value of the rpi_semi_sync_master_timeout variable is reached.
Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log.
A small amount of committed transactions may be lost in case they were committed just before the disk failure.
Answer: D, F
The short version
DF — Slave reads can lag and recent commits may be lost after master disk death. Semi-sync waits only for receipt, not full apply. The settled key is DF because outdated reads plus small loss describe that guarantee gap.
Key concepts in this question
Semi-sync replication: source waits for at least one replica to receive events before committing.
Receipt versus apply: ack means logged to relay, not yet executed on the replica.
Master disk loss: unrecoverable source with no chance to resend unlogged commits.
Stale reads: replica serves older data until relay logs are applied.
Timeout variable: rpl_semi_sync_master_timeout controls fallback to async, not instant failover.
Why DF is correct
DF is correct because semi-sync only guarantees receipt by one slave, so transactions committed just before failure may never have been sent. Reads from the slave can therefore return outdated rows while relay backlog applies. No automatic promotion or instantly current reads occur, and the timeout detail in D plus the small-loss truth in F together capture semi-sync limits after catastrophic source loss.
Why the others are wrong
A. No loss is false because receipt-only ack leaves a window for missing tail commits.
B. Automatic identification plus app failover is not built into semi-sync; external orchestration is needed.
C. Instant full and current reads are false while relay apply lags behind receipt.
E. Timeout-gated staleness misstates the mechanism; lag comes from relay apply, not merely waiting for timeout.
1z0-908 exam tip
Remember semi-sync means received not applied; expect stale reads plus possible tail loss on source death.
6The mysqld instance has the connection control plugin enabled with these settings: connection_control_min_connection_delay=1000 connection_control_max_connection_delay=2000
The minimum and maximum delays need to be increased to 3000 and 5000, respectively.
A command is executed:
mysql> SET GLOBAL connection_control_min_connection_delay=3000;
What is the result?
The minimum value increases to 3000 and the maximum value increases to 4000.
Only the minimum connection value is increased to 3000.
The minimum connection value is changed to 2000.
An error is returned.
Answer: D
The short version
D — Setting min delay above max delay returns an error. Connection Control enforces min not greater than max. The settled key is D because 3000 exceeds the current 2000 cap.
Key concepts in this question
Connection Control plugin: login-throttle that adds growing delays after failures.
Min delay: floor for failed-connection wait, here raised toward 3000 ms.
Max delay: ceiling that bounds the wait, still at 2000 ms in the scenario.
SET GLOBAL: runtime change that validates against the paired bound.
Error 1231: variable cannot be set to the requested value when validation fails.
Why D is correct
D is correct because MySQL checks that connection_control_min_connection_delay never exceeds connection_control_max_connection_delay. With max still 2000, attempting min 3000 violates that invariant and raises error 1231 as shown in the output. Admins must raise max first to 5000, then raise min, so the single SET in the stem cannot succeed.
Why the others are wrong
A. Min plus auto max lift is false; MySQL does not silently raise max to 4000.
B. Solo min increase is false because bound validation blocks values above max.
C. Change to 2000 is false; the statement errors instead of clamping to the ceiling.
1z0-908 exam tip
Always raise Connection Control max before min; min above max triggers error 1231.
7Examine this statement and output: Which two SQL statements can jsmith execute? (Choose two.)
UPDATE world.country SET Name='all';
UPDATE world.country SET Name='one' LIMIT 1;
UPDATE world.country SET Name='new' WHERE Name='old';
UPDATE world.country SET Name=CONCAT('New ',Name);
UPDATE world.country SET Name='first' ORDER BY Name LIMIT
Answer: A, C
The short version
AC — jsmith can run the unconditional UPDATE and the equality-filtered UPDATE. Grants allow update without select, but functions and limits need more. The settled key is AC because those two match the privilege shape.
Key concepts in this question
UPDATE privilege: right to modify rows, possibly scoped by columns or conditions.
WHERE equality: simple filter that still uses only update rights in this grant.
LIMIT and ORDER BY: extras that often require additional handling or are blocked by the grant.
CONCAT expression: read-plus-compute pattern that implies SELECT need on the same table.
Truncated E: incomplete ORDER BY plus LIMIT text that cannot be validly executed.
Why AC is correct
AC is correct because the settled flags show jsmith holds UPDATE that covers a full-table set and a Name equals old filter. Options with CONCAT need to read values to compute, and LIMIT variants impose extra privilege or syntax issues under this grant. The vendor note confirms others need select while the last is malformed, leaving A plus C as executable.
Why the others are wrong
B. LIMIT 1 update is not flagged executable under this grant and privilege shape.
D. CONCAT read-modify implies SELECT need, so it falls outside pure UPDATE rights.
E. ORDER BY plus LIMIT fragment is incomplete and not executable as shown.
1z0-908 exam tip
Pure UPDATE plus simple WHERE often passes; CONCAT or LIMIT usually needs SELECT or fails.
8Which two statements are true about MySQL Enterprise Backup? (Choose two.)
It supports backing up only table structures.
It can perform hot or warm backups.
It creates logical backups.
It supports the creation of incremental backups.
It supports backup of a remote MySQL system.
It supports restoring to a remote MySQL system.
Answer: D, F
The short version
DF — Enterprise Backup does incremental plus remote-restore style work in this key. It is a physical hot backup tool, not logical or structure-only. The settled key is DF because those capabilities match its design.
Key concepts in this question
MySQL Enterprise Backup: physical online backup for InnoDB with full and incremental support.
Incremental: capture of changed pages since last backup for faster cycles.
Remote restore: recovery workflow tied to remote systems in this settled grouping.
Hot backup: online copy without long locks, distinct from warm-only claims.
Logical backup: row-based dumps from mysqldump, not the physical approach here.
Why DF is correct
DF is correct because the bank flags mark incremental creation plus remote-system restore as true for Enterprise Backup. The tool copies tablespaces online and tracks page changes for incrementals, then supports restore flows used across hosts. Structure-only, warm-only and logical labels describe other utilities, so D plus F captures the settled physical-backup strengths.
Why the others are wrong
A. Structure-only misses data and logs that Enterprise Backup includes.
B. Hot-or-warm wording is not the flagged truth in this key; incremental plus remote restore are.
C. Logical backups belong to dump tools, not to this physical backup product.
E. Remote backup as phrased is not flagged; remote restore is the settled capability.
1z0-908 exam tip
Link Enterprise Backup to physical plus incremental; leave logical dumps to mysqldump.
9Four nodes are configured to use circular replication.
Examine these configuration parameters for each node: Which statement is true?
Each slave thread is responsible for updating a specific database.
Cross-database constraints can cause database inconsistency.
Increasing slave_parallel_workers will improve high availability.
Setting slave_preserve_commit_order to on will improve data consistency.
Setting slave_parallel_type=DATABASE won't work for circular replication; it should be set to LOGICAL_CLOCK.
Setting transaction_allow_batching to on will improve data consistency.
Answer: D
The short version
D — Preserve commit order improves consistency in circular replication. Parallel appliers can otherwise commit out of order. The settled key is D because that setting restores logical ordering.
Key concepts in this question
Circular replication: four nodes where each is source and replica in a ring.
Parallel workers: multiple applier threads that speed replay but risk order skew.
Preserve commit order: guarantee that commits apply in source order despite parallelism.
DATABASE parallel type: coarse division by schema that can hurt consistency in rings.
Batching: throughput tweak that does not itself ensure correct order.
Why D is correct
D is correct because slave_preserve_commit_order forces workers to commit in the same sequence as the source, preventing cross-node divergence in a circle. Without it, independent threads interleave transactions and create inconsistencies that compound around the ring. The other tunables address throughput or partitioning, not the ordering guarantee that decides correctness here.
Why the others are wrong
A. Per-database thread assignment is not the deciding consistency control in this setup.
B. Cross-database constraints as phrased are not the flagged true statement here.
C. More workers raise speed but not availability or ordering safety by itself.
E. DATABASE mode is discouraged for rings, so prescribing it is wrong.
F. Batching boosts throughput, not data consistency or commit ordering.
1z0-908 exam tip
For parallel replication consistency choose preserve commit order; workers alone do not fix order.
10Examine this command and output: Which statement is true?
Firewall_cached_entries is the number of statements found in the query cache for users in DETECTING mode.
Firewall_access_denied is the number of connection attempts from prohibited hosts that are denied.
Firewall_access_suspicious is the number of statements logged as suspicious for users in DETECTING mode.
Firewall_access_granted is the number of connections granted from whitelisted hosts.
Answer: C
The short version
C — Firewall_access_suspicious counts DETECTING-mode suspicious statements. Enterprise Firewall logs risky SQL per user mode. The settled key is C because that status variable matches the suspicious definition.
Key concepts in this question
Enterprise Firewall: allowlist plus detect layer that learns and flags SQL patterns.
DETECTING mode: training state where violations are logged, not blocked.
Suspicious counter: number of statements deemed risky for detecting users.
Denied and granted: separate counters for blocked hosts versus allowed connections.
Cached entries: query-cache concept unrelated to firewall suspicious logging.
Why C is correct
C is correct because MySQL defines Firewall_access_suspicious as statements logged as suspicious for users in DETECTING mode. That matches the command output semantics where learning users generate log entries without enforcement. Denied tracks prohibited hosts and granted tracks whitelist connects, so only C describes the suspicious log count.
Why the others are wrong
A. Cached entries relate to query cache hits, not firewall suspicious logs.
B. Access denied counts blocked hosts, not suspicious statements in detecting mode.
D. Access granted counts whitelist connects, not suspicious statement volume.
1z0-908 exam tip
Map suspicious to DETECTING logs, denied to blocked hosts, granted to whitelist connects.