2 packs: 15% off with code BUY2. 3 or more: 20% off with code BUY3.

Free Oracle 1Z0-908 Practice Questions

57 free Oracle 1Z0-908 practice questions from the CertStash pack of 228, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 57 free here, all 228 in the pack, and each step includes the one before it.

Get all 228 questions (US$39) · Download the first 10 as a PDF

Question 1

Which three are characteristics of a newly created role? (Choose three.)

  1. It can be dropped using the DROP ROLE statement.
  2. It is stored in the mysql.role table.
  3. It is created as a locked account.
  4. It can be renamed using the RENAME ROLE statement.
  5. It can be granted to user accounts.
  6. It can be protected with a password.
Show answer and explanation

Correct answer: A, C, E. It can be dropped using the DROP ROLE statement; It is created as a locked account; It can be granted to user accounts.

A newly created role can be dropped with DROP ROLE, is created as a locked account by default (so no one can log in as the role, although it can still be granted and activated), and can be granted to user accounts to assign permissions. Roles are stored in the mysql.user table (not a separate mysql.role table), cannot be renamed with a RENAME ROLE statement (no such statement exists in MySQL), and are created with no password, because CREATE ROLE has no clause for setting one.

Why the other options are wrong

  • B. Roles are stored in the mysql.user table, not in a mysql.role table.
  • D. MySQL does not support a RENAME ROLE statement for renaming roles.
  • F. A newly created role has no password; CREATE ROLE provides no way to set one.

Question 2

Which statement is true about InnoDB persistent index statistics?

  1. Updating index statistics is an I/O expensive operation.
  2. Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.
  3. Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.
  4. Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.
  5. Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.
  6. Tables are scanned and index statistics recalculated when an instance is restarted.
Show answer and explanation

Correct answer: A. Updating index statistics is an I/O expensive operation.

Updating index statistics is an I/O expensive operation because it requires scanning table pages to gather statistical information about index distribution. The other options contain inaccuracies: statistics are calculated from actual table data (not just buffer pool pages), innodb_stats_auto_recalc affects existing tables during modifications (not new index creation), increasing sample pages improves precision but increases I/O cost (not just memory), and statistics are recalculated on demand with ANALYZE TABLE or automatically after enough rows change, not on restart.

Why the other options are wrong

  • B. Statistics are calculated from the actual table data, not limited to pages currently in the buffer pool.
  • C. innodb_stats_auto_recalc causes automatic updates when data changes significantly, not specifically when new indexes are created.
  • D. innodb_stats_persistent_sample_pages applies only to persistent statistics; transient statistics use innodb_stats_transient_sample_pages, so raising it does not change plans based on transient statistics.
  • E. Increasing sample pages increases I/O and memory usage together, and doesn't determine scanning speed.
  • F. Statistics are recalculated based on configuration settings and data changes, not automatically on instance restart.

Question 3

Which two are features of MySQL Enterprise Firewall? (Choose two.)

  1. recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands
  2. blocking of potential threats by configuring pre-approved whitelists
  3. modifying SQL statement dynamically with substitutions
  4. automatic locking of user accounts who break your firewall
  5. provides stateless firewall access to TCP/3306
Show answer and explanation

Correct answer: A, B. recording incoming SQL statement to facilitate the creation of a whitelist of permitted commands; blocking of potential threats by configuring pre-approved whitelists

The MySQL Enterprise Firewall records incoming SQL statements to build a whitelist of permitted commands, and blocks potential threats by enforcing pre-approved whitelists of allowed SQL patterns. The firewall does not dynamically modify SQL statements, does not automatically lock user accounts (it blocks statements), and operates at the application level rather than providing stateless TCP firewall functionality.

Why the other options are wrong

  • C. The firewall blocks threats but does not modify SQL statements with dynamic substitutions.
  • D. The firewall blocks SQL statements; it does not automatically lock user accounts.
  • E. MySQL Enterprise Firewall operates at the SQL statement level, not as a stateless network firewall at the TCP layer.

Question 4

You must configure the MySQL command-line client to provide the highest level of trust and security when connecting to a remote MySQL Server. Which value of --ssl-mode will do this?

  1. PREFERRED
  2. VERIFY_CA
  3. REQUIRED
  4. VERIFY_IDENTITY
Show answer and explanation

Correct answer: D. VERIFY_IDENTITY

VERIFY_IDENTITY provides the highest level of trust and security by verifying both that an SSL connection is established and that the server certificate is valid for the hostname being connected to. This prevents man-in-the-middle attacks by ensuring the certificate matches the server's identity. PREFERRED allows unencrypted fallback, VERIFY_CA only verifies the certificate authority without checking hostname matching, and REQUIRED only ensures encryption without identity verification.

Why the other options are wrong

  • A. PREFERRED allows downgrade to unencrypted connections, providing lower security.
  • B. VERIFY_CA verifies the certificate chain but does not verify that the certificate matches the server hostname.
  • C. REQUIRED enforces SSL but does not verify the server's certificate identity matches the hostname.

Question 5

Consider this shell output and executed commands: [root@oel7 ~]# ps aux | grep mysqld mysql 2076 3.5 24.6 1386852 372572 ? Ssl 12:01 0:01 /usr/sbin/mysqld [root@oel7 ~]# kill -15 2076 Which statement is true about MySQL server shutdown?

  1. kill -15 should be avoided. Use other methods such as mysqladmin shutdown or systemctl stop mysqld.
  2. kill -15 and kill -9 are effectively the same forced shutdown that risk committed transactions not written to disk.
  3. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown.
  4. mysqld_safe prohibits commands that would harm the operation of the server. An error would be returned by the kill command.
Show answer and explanation

Correct answer: C. kill -15 carries out a normal shutdown process, such as mysqladmin shutdown.

Sending SIGTERM with kill -15 to the mysqld process makes the server perform the same normal shutdown sequence that mysqladmin shutdown or systemctl stop mysqld trigger. The server stops accepting new connections, terminates client threads, rolls back any uncommitted transactions, flushes InnoDB data and logs, closes tables and then exits cleanly, so committed transactions are safe. Only SIGKILL (kill -9) terminates the process abruptly without any cleanup, which leaves InnoDB to perform crash recovery on the next start.

Why the other options are wrong

  • A. kill -15 is a supported way to request a normal shutdown, so there is no reason to avoid it in favor of other tools.
  • B. kill -15 sends SIGTERM for a clean shutdown, while only kill -9 forces immediate termination without flushing data.
  • D. mysqld_safe only restarts the server after an abnormal exit and does not block signals sent to mysqld, so the kill command succeeds.

Question 6

You wish to protect your MySQL database against SQL injection attacks. Which method would fail to do this?

  1. installing and configuring the Connection Control plugin
  2. avoiding concatenation of SQL statements and user-supplied values in an application
  3. using stored procedures for any database access
  4. using PREPARED STATEMENTS
Show answer and explanation

Correct answer: A. installing and configuring the Connection Control plugin

Installing the Connection Control plugin only limits connection attempts and cannot prevent SQL injection attacks, which occur within legitimate SQL statements. Avoiding string concatenation with user input, using stored procedures properly, and using prepared statements with parameterized queries are all effective defenses against SQL injection because they separate code from data.

Why the other options are wrong

  • B. Avoiding concatenation of user-supplied values prevents SQL injection by keeping user input separate from SQL code.
  • C. Stored procedures prevent SQL injection when used correctly, as they accept parameters separately from code.
  • D. Prepared statements with parameter binding are a primary defense against SQL injection attacks.

Question 7

Which three are types of InnoDB tablespaces? (Choose three.)

  1. redo tablespaces
  2. encryption tablespaces
  3. schema tablespaces
  4. data tablespaces
  5. undo tablespaces
  6. temporary table tablespaces
Show answer and explanation

Correct answer: D, E, F. data tablespaces; undo tablespaces; temporary table tablespaces

Three of the options are InnoDB tablespace types: data tablespaces (D), such as the system, file-per-table and general tablespaces, which store table and index data, undo tablespaces (E), which store undo log information for transaction rollback, and temporary table tablespaces (F), which store data for temporary tables.

Why the other options are wrong

  • A. Redo tablespaces do not exist as a separate tablespace type; redo logs are managed separately from tablespaces.
  • B. Encryption tablespaces are not a type; encryption is a property applied to existing tablespaces.
  • C. Schema tablespaces do not exist; InnoDB uses the tablespace types listed in the correct answers.

Question 8

You plan to take daily full backups, which include the ndbinfo and sys (internal) databases. Which command will back up the databases in parallel?

  1. mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql
  2. mysqlpump --include-databases=% > full-backup-$(date +%Y%m$d).sql
  3. mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql
  4. mysqldump --all-databases > full_backup-$(date +%Y%m%d).sql
Show answer and explanation

Correct answer: B. mysqlpump --include-databases=% > full-backup-$(date +%Y%m$d).sql

mysqlpump is the modern backup utility designed for parallel processing of databases and tables. The --include-databases=% syntax names every database (the % wildcard matches all database names), including sys and ndbinfo, and mysqlpump processes them in parallel by default. Option A uses mysqldump which does not support parallel backups. Option C uses --all-databases, which still skips the sys and ndbinfo databases unless they are named explicitly. Option D uses mysqldump which lacks parallel functionality.

Why the other options are wrong

  • A. mysqldump does not support parallel backup execution.
  • C. --all-databases still skips the sys and ndbinfo databases, which mysqlpump dumps only when they are named explicitly.
  • D. mysqldump lacks parallel processing capability.

Question 9

Which two tools are available to monitor the global status of InnoDB locking? (Choose two.)

  1. SHOW ENGINE INNODB STATUS;
  2. INFORMATION_SCHEMA.INNODB_METRICS
  3. SHOW TABLE STATUS;
  4. INFORMATION_SCHEMA.STATISTICS
  5. INFORMATION_SCHEMA.INNODB_TABLESTATS
  6. SHOW STATUS;
Show answer and explanation

Correct answer: A, B. SHOW ENGINE INNODB STATUS;; INFORMATION_SCHEMA.INNODB_METRICS

SHOW ENGINE INNODB STATUS returns the InnoDB monitor output, whose TRANSACTIONS section lists active transactions, the locks they hold and wait for, and how long they have waited, while the LATEST DETECTED DEADLOCK section describes the most recent deadlock. INFORMATION_SCHEMA.INNODB_METRICS exposes InnoDB counters, including the lock module with values such as lock_deadlocks, lock_timeouts, lock_row_lock_waits and lock_row_lock_time, giving a server-wide view of locking activity over time. Together they show both the current lock state and cumulative lock statistics for the whole InnoDB engine.

Why the other options are wrong

  • C. SHOW TABLE STATUS displays per-table metadata such as row counts and sizes, not locking information.
  • D. INFORMATION_SCHEMA.STATISTICS describes indexes and their columns, not InnoDB locking.
  • E. INFORMATION_SCHEMA.INNODB_TABLESTATS shows per-table statistics used by the optimizer, not global locking status.
  • F. SHOW STATUS returns general server status variables rather than a dedicated view of InnoDB locks and transactions.

Question 10

Which two statements are true about using MySQL Enterprise Monitor Query Analyzer? (Choose two.)

  1. The single query QRTi pie chart in the Query Analyzer view is based on the average execution of all statements.
  2. It is possible to retrieve a normalized statement, but never the exact statement that was executed.
  3. It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution.
  4. It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs.
  5. It is possible to import data into the Query Analyzer from heterogeneous sources, such as CSV.
Show answer and explanation

Correct answer: C, D. It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution; It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs.

The Query Analyzer allows configuration of built-in advisors to detect and notify about slow query execution, and it supports selecting arbitrary time ranges from timeseries graphs to analyze and list statements. Option A is incorrect because QRTi metrics are based on aggregated totals, not averages. Option B is too restrictive; both normalized and exact statements can be retrieved depending on configuration. Option E is incorrect as Query Analyzer pulls from MySQL sources, not heterogeneous CSV imports.

Why the other options are wrong

  • A. The QRTi pie chart is based on aggregated totals across all statements, not averages.
  • B. Both normalized statements and exact executed statements can be retrieved from the Query Analyzer.
  • E. Query Analyzer data comes from the monitored server's Performance Schema statement digests, not from importing external CSV files.

Question 11

Which step or set of steps can be used to rotate the error log?

  1. Execute SET GLOBAL log_error = '<new error log file>'.
  2. Execute SET GLOBAL max_error_count = <number of messages at point to rotate>.
  3. Execute SET GLOBAL expire_logs_days=0 to enforce a log rotation.
  4. Rename the error log file on disk, and then execute FLUSH ERROR LOGS.
Show answer and explanation

Correct answer: D. Rename the error log file on disk, and then execute FLUSH ERROR LOGS.

Error log rotation is performed by renaming the error log file on disk and then executing FLUSH ERROR LOGS, which closes the current error log and opens a new one with the original filename. This is the standard method for error log rotation without server restart. Option A cannot be executed at runtime. Option B limits the number of diagnostic messages kept for SHOW WARNINGS and SHOW ERRORS, not error log rotation. Option C manages binary log retention, not error log rotation.

Why the other options are wrong

  • A. The log_error variable cannot be changed with SET GLOBAL; it is a read-only startup parameter.
  • B. max_error_count limits how many error, warning and note messages are stored for display by SHOW ERRORS and SHOW WARNINGS; it has nothing to do with rotating the error log.
  • C. expire_logs_days applies to binary logs, not the error log.

Question 12

What is the correct syntax for using transparent data encryption with an existing InnoDB table?

  1. ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE = 'Y';
  2. ALTER TABLE t1 ENCRYPTION='Y';
  3. ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY;
  4. ALTER TABLE t1 SET TDE = 'ON';
Show answer and explanation

Correct answer: B. ALTER TABLE t1 ENCRYPTION='Y';

The correct syntax for enabling transparent data encryption on an existing InnoDB table is ALTER TABLE t1 ENCRYPTION='Y'. This sets the ENCRYPTION option to 'Y' to enable TDE on the specified table, which rebuilds the table and encrypts its tablespace with a tablespace key that is itself encrypted by the master key held in the keyring.

Why the other options are wrong

  • A. ENCRYPTED_TABLESPACE is not a valid ALTER TABLE option for TDE.
  • C. The WITH ENCRYPTION USING MASTER KEY syntax is not valid MySQL syntax.
  • D. TDE = 'ON' is not a valid option; the correct option name is ENCRYPTION with value 'Y' or 'N'.

Question 13

t is a non-empty InnoDB table. Examine these statements, which are executed in one session: BEGIN; SELECT * FROM t FOR UPDATE; Which is true?

  1. If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.
  2. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.
  3. mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.
  4. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.
Show answer and explanation

Correct answer: C. mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.

SELECT ... FOR UPDATE inside an open transaction holds row locks and a shared metadata lock on t until the transaction commits or rolls back. ANALYZE TABLE, which mysqlcheck --analyze issues for every table, only samples index pages to recalculate and store key distribution statistics and does not need an exclusive metadata lock, so it runs normally on all tables and returns its report. OPTIMIZE TABLE on InnoDB, with or without LOCAL, is mapped to ALTER TABLE ... FORCE, and that rebuild needs an exclusive metadata lock, so it waits for the open transaction to end. The LOCAL keyword only suppresses binary logging and does not change locking.

Why the other options are wrong

  • A. OPTIMIZE TABLE never forces another session's transaction to roll back; it waits for the metadata lock instead.
  • B. OPTIMIZE TABLE on InnoDB rebuilds the table and needs an exclusive metadata lock, so it waits until the transaction holding locks on t ends.
  • D. ANALYZE TABLE causes an implicit commit in the same session, so it ends the transaction and runs rather than hanging.

Question 14

Examine this command, which executes successfully: $ mysqlrouter --bootstrap user@hostname:port --directory=directory_path Which activity is performed?

  1. MySQL Router is configured based on the information in files in directory_path.
  2. MySQL Router configures itself based on the information retrieved from the InnoDB cluster metadata server.
  3. MySQL Router is restarted.
  4. MySQL Router configures all the cluster nodes based on the information retrieved from the InnoDB cluster metadata server.
Show answer and explanation

Correct answer: B. MySQL Router configures itself based on the information retrieved from the InnoDB cluster metadata server.

The mysqlrouter --bootstrap command performs automatic configuration by connecting to the specified InnoDB Cluster metadata server and retrieving cluster topology and configuration information. The Router then creates its own configuration files in the specified directory based on the metadata retrieved from the cluster.

Why the other options are wrong

  • A. The bootstrap process configures MySQL Router based on retrieved cluster metadata, not by reading from existing files in directory_path.
  • C. Bootstrap configures the Router but does not restart it; startup occurs in a separate step.
  • D. Bootstrap configures the MySQL Router instance itself, not the cluster nodes.

Question 15

Which two MySQL Server accounts are locked by default? (Choose two.)

  1. any user set as DEFINER for stored programs
  2. any internal system accounts
  3. any new ROLE accounts
  4. any user created without a password
  5. any user created with a username, but missing the host name
Show answer and explanation

Correct answer: B, C. any internal system accounts; any new ROLE accounts

MySQL Server accounts that are locked by default include internal system accounts (such as mysql.sys, mysql.session) and any new ROLE accounts. These accounts are created in a locked state to prevent unauthorized access and are designed for internal system purposes or administrative role management rather than direct user access.

Why the other options are wrong

  • A. Users set as DEFINER for stored programs are not automatically locked by default.
  • D. Users created without a password are not necessarily locked; they can be locked or unlocked independently.
  • E. A user created without a host name gets the host '%' and is created unlocked.

Question 16

MySQL programs look for option files in standard locations. Which method will show the option files and the order in which they are read?

  1. shell> mysqladmin --debug
  2. shell> mysql --print-defaults
  3. shell> mysqld --help --verbose
  4. mysql> SHOW GLOBAL VARIABLES;
Show answer and explanation

Correct answer: C. shell> mysqld --help --verbose

Running mysqld --help --verbose prints a line listing the option files that are read and the order in which they are read, followed by all options and their values. It is the documented way to see the option file search order.

Why the other options are wrong

  • A. mysqladmin --debug writes debug trace information and does not list option files.
  • B. --print-defaults prints the options a program would receive, not the list of option files or their order.
  • D. SHOW GLOBAL VARIABLES shows current variable values, not which option files were read.

Question 17

Which two statements are true about MySQL server multi-source replication? (Choose two.)

  1. It is not compatible with auto-positioning.
  2. It needs to be re-instanced after a crash to maintain consistency.
  3. It uses only time-based replication conflict resolution.
  4. It relies on relay_log_recovery for resilient operations.
  5. It does not attempt to detect or resolve replication conflicts.
  6. It must use GTID replication.
Show answer and explanation

Correct answer: D, E. It relies on relay_log_recovery for resilient operations; It does not attempt to detect or resolve replication conflicts.

MySQL multi-source replication relies on relay_log_recovery for resilient operations to ensure consistency when relay logs are lost or corrupted. Multi-source replication does not attempt to detect or resolve replication conflicts automatically; it leaves conflict resolution to the application layer or manual intervention.

Why the other options are wrong

  • A. Multi-source replication is compatible with auto-positioning when using GTID-based replication.
  • B. Multi-source replication does not require re-instancing after a crash; relay_log_recovery handles recovery.
  • C. Multi-source replication has no conflict resolution of any kind, time-based or otherwise.
  • F. While GTID replication is recommended for multi-source replication, it is not mandatory; binary log position-based replication is also possible.

Question 18

You have a MySQL system with 500 GB of data that needs frequent backups. You use a mix of MyISAM and InnoDB storage engines for your data. Examine your backup requirement: The MySQL system being backed up can never be unavailable or locked to the client applications. The recovery from the backup must work on any system. Only 1 hour of data can be lost on recovery of the backup. Which option fulfills all backup requirements?

  1. Take a physical backup of the MySQL system.
  2. Use the Clone Plugin to copy the data to another MySQL system.
  3. Take a logical backup of the MySQL system.
  4. Take your backup from a slave of the MySQL system.
Show answer and explanation

Correct answer: D. Take your backup from a slave of the MySQL system.

A consistent backup of MyISAM tables requires table locks, because --single-transaction gives a consistent snapshot only for InnoDB, so any backup taken on the live server would lock client applications. Running the backup on a slave moves all locking and load onto the replica while the master stays fully available. The slave can be stopped or locked for the backup, a logical dump taken there restores on any system, and repeating the backup every hour keeps possible data loss within the one-hour limit.

Why the other options are wrong

  • A. A physical backup of live MyISAM tables needs a read lock, and physical files are tied to the platform and server version.
  • B. The Clone Plugin copies only InnoDB data, so the MyISAM tables are not included.
  • C. A logical backup on the live server must lock the MyISAM tables to stay consistent, which blocks client applications.

Question 19

Which four are types of information stored in the MySQL data dictionary? (Choose four.)

  1. performance metrics
  2. table definitions
  3. access control lists
  4. view definitions
  5. server runtime configuration
  6. server configuration rollback
  7. stored procedure definitions
  8. InnoDB buffer pool LRU management data
Show answer and explanation

Correct answer: B, C, D, G. table definitions; access control lists; view definitions; stored procedure definitions

The MySQL data dictionary stores structural metadata including table definitions, view definitions, stored procedure definitions, and access control lists that define user privileges and permissions. The data dictionary is the centralized repository for object definitions and security information.

Why the other options are wrong

  • A. Performance metrics are stored in the Performance Schema, not the data dictionary.
  • E. Server runtime configuration is stored in system variables, not persisted in the data dictionary.
  • F. Server configuration rollback is a feature not related to data dictionary storage.
  • H. InnoDB buffer pool LRU management data is runtime memory management, not stored in the data dictionary.

Question 20

Binary log events for the 'mydb1' schema must be copied to a different schema name 'mydb2'. Which command will do this?

  1. mysqlbinlog --rewrite-db='mydb1->mydb2' | mysql
  2. mysqlbinlog --datebase=mydb1 --database=mydb2 | mysql
  3. mysqlbinlog --rewrite-db='mydb1' --rewrite-db='mydb2' | mysql
  4. mysqlbinlog --read-from-remote-server --raw | sed 's/mydb1/mydb2/g' | mysql
Show answer and explanation

Correct answer: A. mysqlbinlog --rewrite-db='mydb1->mydb2' | mysql

The --rewrite-db option in mysqlbinlog is specifically designed to rewrite database names in binary log events. The syntax 'mydb1->mydb2' correctly specifies the source and target database names, allowing events from mydb1 to be applied to mydb2 when piped to mysql.

Why the other options are wrong

  • B. The --database option filters which databases to read from the binary log, not rewrite them; using it twice does not create a mapping.
  • C. The --rewrite-db option requires the arrow syntax (source->target), not two separate arguments.
  • D. Using sed to replace database names is unreliable because it cannot distinguish between database names in different contexts (comments, strings, identifiers) and will corrupt the binary log format.

Question 21

Examine this command, which executes successfully: mysqlbackup --defaults-file=/backups/server-my.cnf --backup-dir=/backups/full copy-back Which statement is true about the copy-back process?

  1. The copy-back process is used to overwrite a new backup over an existing backup.
  2. It restores files from the data directory to their original MySQL server locations.
  3. It restores files from the backup directory to their original MySQL server locations.
  4. The copy-back process makes inconsistent backups.
Show answer and explanation

Correct answer: C. It restores files from the backup directory to their original MySQL server locations.

The copy-back process in MySQL Enterprise Backup restores files from the backup directory to their original MySQL server locations on the file system. This is the standard recovery step after a backup has been taken and validated. The backup directory contains the backed-up data files, and copy-back copies them back to where the MySQL server expects them to be.

Why the other options are wrong

  • A. copy-back is a restore operation, not for overwriting existing backups.
  • B. copy-back restores files from the backup directory, not from the data directory.
  • D. copy-back works with consistent backups and maintains that consistency during restoration.

Question 22

You made some table definition changes to a schema in your MySQL Server. Which two statements reflect how MySQL Server handles the table definition changes? (Choose two.)

  1. MySQL writes SDI to the binary log for distributed backups.
  2. MySQL keeps InnoDB metadata changes in .sdi files in datadir.
  3. The metadata is serialized in JSON format in Serialized Dictionary Information (SDI).
  4. MySQL Server stores a copy of the serialized data in the InnoDB user tablespace.
  5. MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata.
Show answer and explanation

Correct answer: C, D. The metadata is serialized in JSON format in Serialized Dictionary Information (SDI); MySQL Server stores a copy of the serialized data in the InnoDB user tablespace.

When table definitions change in MySQL, the metadata is serialized in JSON format as Serialized Dictionary Information (SDI), and MySQL Server stores a copy of this serialized data in the InnoDB user tablespace. This approach ensures metadata consistency and recoverability without relying on separate .sdi files for core storage.

Why the other options are wrong

  • A. SDI is not written to the binary log; it is stored in the data dictionary and tablespace.
  • B. Metadata changes are stored in the InnoDB tablespace data dictionary, not in separate .sdi files in the datadir.
  • E. MySQL does not implicitly execute FLUSH TABLES for every metadata change, and this is not how metadata storage works.

Question 23

Your MySQL server is running on the Microsoft Windows platform. Which three local connection protocols are available to you? (Choose three.)

  1. named pipes
  2. shared memory
  3. SOCKET
  4. X Protocol
  5. UDP
  6. TCP/IP
Show answer and explanation

Correct answer: A, B, F. named pipes; shared memory; TCP/IP

MySQL on Windows supports three local connection protocols: named pipes for inter-process communication, shared memory for local processes on the same machine, and TCP/IP for network connections. SOCKET is a Unix/Linux mechanism not available on Windows. X Protocol is a newer MySQL protocol for X DevAPI but is not specific to local connections. UDP is not used by MySQL for standard connections.

Why the other options are wrong

  • C. SOCKET connections are specific to Unix/Linux systems, not Windows.
  • D. X Protocol is available on Windows but is a newer protocol and not one of the three standard local protocols.
  • E. UDP is not used by MySQL for client-server communication.

Question 24

What does the binlog dump thread do?

  1. It monitors and schedules the rotation/deletion of the binary logs.
  2. It reads the relay log and executes the events contained in them.
  3. It acquires a lock on the binary log for reading each event to be sent to the slave.
  4. It connects to the master and asks it to send updates recorded in its binary logs.
Show answer and explanation

Correct answer: C. It acquires a lock on the binary log for reading each event to be sent to the slave.

The binlog dump thread on the master acquires a lock on the binary log, reads events sequentially from it, and sends them to connected slaves. This is a fundamental replication mechanism where the thread ensures consistency by locking while reading each event to be transmitted. Option A describes log rotation management. Option B describes slave SQL thread behavior. Option D describes slave connection initiation rather than the specific function of the dump thread itself.

Why the other options are wrong

  • A. Binary log rotation/deletion is managed by different processes, not the binlog dump thread.
  • B. Reading the relay log and executing events is the function of the slave SQL thread, not the binlog dump thread.
  • D. Slaves connect and request updates, but the dump thread's specific function is acquiring locks and reading events from the master's binary log.

Question 25

The 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?

  1. The minimum value increases to 3000 and the maximum value increases to 4000.
  2. Only the minimum connection value is increased to 3000.
  3. The minimum connection value is changed to 2000.
  4. An error is returned.
Show answer and explanation

Correct answer: D. An error is returned.

When attempting to set connection_control_min_connection_delay to 3000, an error is returned because this value would exceed the current maximum value of 2000. The connection control plugin enforces the constraint that the minimum delay must not exceed the maximum delay, so the SET statement fails. The maximum must be increased first before the minimum can be raised above the current maximum.

Why the other options are wrong

  • A. The values do not auto-adjust; the statement fails with an error instead.
  • B. The statement does not succeed; it returns an error due to the constraint violation.
  • C. The value does not revert to 2000; the error prevents any change from occurring.

25 questions in. Want the rest? The first half of the pack, free by email · The full pack

Question 26

Which three statements are true about MySQL replication? (Choose three.)

  1. Replication can use only TCP/IP connections.
  2. Any instance can have multiple slaves, but it can have only one master.
  3. Each instance in a replication topology must have a unique server ID.
  4. Binary logs contain only transactions originating from a single MySQL instance.
  5. Each slave must have its own MySQL user for replication.
  6. Binary logging must be enabled on the master in order to replicate to other instances.
  7. A replication user must have the SELECT privilege for all tables that need to be replicated.
Show answer and explanation

Correct answer: A, C, F. Replication can use only TCP/IP connections; Each instance in a replication topology must have a unique server ID; Binary logging must be enabled on the master in order to replicate to other instances.

A slave connects to its master as a network client, and replication connections cannot use Unix socket files, so TCP/IP must be available and skip_networking must not be set on the master. Every server in a replication topology needs a unique server_id so that events can be traced to the server where they originated and are not reapplied in loops. The master must have binary logging enabled because the slave's I/O thread reads events from the master's binary log. The replication account needs only the REPLICATION SLAVE privilege, and a single account can serve many slaves.

Why the other options are wrong

  • B. A slave can receive from several masters at once through separate replication channels, so it is not limited to one master.
  • D. With log_slave_updates enabled, which is the default in MySQL 8.0, a server also writes transactions received from its master to its own binary log.
  • E. Several slaves can connect with the same replication account.
  • G. The replication account needs only the REPLICATION SLAVE privilege; SELECT on the replicated tables is not required.

Question 27

Which three settings control global buffers shared by all threads on a MySQL server? (Choose three.)

  1. sort_buffer_size
  2. key_buffer_size
  3. table_open_cache
  4. read_buffer_size
  5. innodb_buffer_pool_size
  6. tmp_table_size
Show answer and explanation

Correct answer: B, C, E. key_buffer_size; table_open_cache; innodb_buffer_pool_size

The InnoDB buffer pool, sized by innodb_buffer_pool_size, caches table and index data for InnoDB and is allocated once for the whole server. The MyISAM key cache, sized by key_buffer_size, holds index blocks and is likewise shared by all threads. The table cache sized by table_open_cache holds open table instances that any session can reuse, so it is also a global structure. By contrast, sort_buffer_size and read_buffer_size are allocated per session when a sort or sequential scan needs them, and tmp_table_size limits each internal in-memory temporary table created for an individual query.

Why the other options are wrong

  • A. sort_buffer_size is allocated by each session that performs a sort, so it is not shared.
  • D. read_buffer_size is allocated by each thread that performs a sequential scan, so it is not shared.
  • F. tmp_table_size caps the size of each internal in-memory temporary table, which belongs to a single query and session.

Question 28

Examine this command, which executes successfully: $ mysqlbackup --user=dba --password --port=3306 --with-timestamp --only-known-file-types --backup-dir=/export/backups backup Which statement is true?

  1. Only files for MySQL or its built-in storage engines are backed up.
  2. Only non-encrypted files are backed up.
  3. The backup includes only data files and their metadata.
  4. Only InnoDB data and log files are backed up.
  5. Only tables stored in their own tablespaces are backed up.
Show answer and explanation

Correct answer: A. Only files for MySQL or its built-in storage engines are backed up.

The --only-known-file-types flag in the mysqlbackup command restricts the backup to include only files that mysqlbackup recognizes as belonging to MySQL or its built-in storage engines (InnoDB, MyISAM, etc.). This flag excludes any unrecognized file types from the backup, meaning only MySQL-related files are included. Option A correctly describes this behavior, while option D is too narrow because mysqlbackup with --only-known-file-types includes files from all recognized MySQL storage engines, not just InnoDB.

Why the other options are wrong

  • B. The --only-known-file-types flag filters by file type recognition, not encryption status; encrypted files can still be backed up if they are recognized MySQL files.
  • C. The backup includes complete database files and structures, not just data files and metadata.
  • D. While InnoDB files are included, --only-known-file-types also backs up files from other recognized MySQL storage engines like MyISAM, not exclusively InnoDB.
  • E. The --only-known-file-types flag is based on file type recognition by mysqlbackup, not whether tables are stored in individual tablespaces.

Question 29

You must run multiple instances of MySQL Server on a single host. Which three methods are supported? (Choose three.)

  1. Run MySQL Server docker containers.
  2. Use systemd with different settings for each instance.
  3. Use system tools to lock each instance to its own CPU.
  4. Start mysqld or mysqld_safe using different option files for each instance.
  5. Run mysqld with --datadir defined for each instance.
  6. Use resource groups to lock different instances on separate CPUs.
Show answer and explanation

Correct answer: B, D, E. Use systemd with different settings for each instance; Start mysqld or mysqld_safe using different option files for each instance; Run mysqld with --datadir defined for each instance.

Three supported methods for running multiple MySQL Server instances on a single host are: using systemd with different settings for each instance (B), starting mysqld or mysqld_safe with different option files for each instance (D), and running mysqld with -- datadir defined for each instance (E). These are all officially documented and supported configuration approaches.

Why the other options are wrong

  • A. While Docker containers can run MySQL instances, Docker itself is not a native MySQL Server method for running multiple instances on a single host.
  • C. Using system tools to lock instances to specific CPUs is a performance optimization technique, not a method for running multiple instances.
  • F. Resource groups manage CPU allocation but do not constitute a method for running separate MySQL instances.

Question 30

You plan to install MySQL Server by using the RPM download. Which two statements are true? (Choose two.)

  1. You can provide the root password interactively.
  2. You must manually initialize the data directory.
  3. The MySQL RPM package installation supports deploying multiple MySQL versions on the same host.
  4. MySQL uses the RPM relocatable installation target feature.
  5. The functionality is split among several RPM package files.
  6. You can find the root password in the error log after the first start.
Show answer and explanation

Correct answer: E, F. The functionality is split among several RPM package files; You can find the root password in the error log after the first start.

MySQL RPM packages are distributed across multiple separate RPM files, each containing different components such as server, client, libraries, and development files, so you install only the packages you need. After the initial installation and first startup, MySQL generates a temporary root password and writes it to the error log for security purposes, which the administrator must retrieve and use for initial login. Interactive password prompts are not provided during RPM installation. The data directory is initialized automatically the first time the server is started, without manual intervention. RPM packages are not relocatable; they install to fixed system paths. Only one MySQL version can be deployed per RPM installation on a host.

Why the other options are wrong

  • A. RPM installation does not provide interactive password entry; passwords are generated and logged.
  • B. The data directory is initialized automatically the first time the server is started.
  • C. RPM installation supports only one MySQL version per installation on the same host.
  • D. MySQL RPM packages use fixed installation paths and are not relocatable.

Question 31

Which command enables rule-based MySQL Auditing capabilities?

  1. shell> mysql < audit_log_filter_linux_install.sql
  2. shell> mysqld --initialize --log-raw=audit.log
  3. mysql> INSTALL PLUGIN audit_log;
  4. mysql> INSTALL COMPONENT audit_log;
Show answer and explanation

Correct answer: A. shell> mysql < audit_log_filter_linux_install.sql

Rule-based MySQL Auditing is enabled by executing the audit_log_filter_linux_install.sql script, which installs the audit_log plugin and creates the necessary tables and functions for filter-based auditing. The script is run through the mysql client, for example mysql -u root -p < audit_log_filter_linux_install.sql, to initialize the audit system.

Why the other options are wrong

  • B. The --log-raw option is not the correct method for enabling audit logging; mysqld -- initialize is for database initialization.
  • C. Using INSTALL PLUGIN directly for audit_log does not set up the filter tables and functions needed for rule-based auditing.
  • D. MySQL 8.0 has no audit_log component; MySQL Enterprise Audit is a plugin, and the audit_log_filter_linux_install.sql script is what sets up rule-based filtering.

Question 32

You are using mysqlcheck for server maintenance. Which two statements are true? (Choose two.)

  1. The mysqlcheck --check --all-databases command takes table write locks while performing a series of checks.
  2. The mysqlcheck --optimize --all-databases command reclaims free space from table files.
  3. The mysqlcheck --repair --all-databases command can repair an InnoDB corrupted table.
  4. The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default.
  5. The mysqlcheck --analyze --all-databases command performs a series of checks to spot eventual table corruptions.
Show answer and explanation

Correct answer: B, D. The mysqlcheck --optimize --all-databases command reclaims free space from table files; The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default.

The mysqlcheck --optimize command reclaims free space from table files by reorganizing the physical storage of data and indexes. The mysqlcheck utility is designed with multiple operational modes and can be invoked under different names (mysqlcheck, mysqlrepair, mysqlanalyze) where the invocation name determines the default operation mode, so renaming it to mysqlrepair would make repairs the default action. The --check operation takes only READ locks, not write locks, while the --repair command cannot repair InnoDB tables since InnoDB handles corruption through its own crash recovery mechanisms, and the --analyze command performs statistical analysis rather than corruption detection.

Why the other options are wrong

  • A. A check operation locks each table with a READ lock only, not a write lock.
  • C. InnoDB tables cannot be repaired using mysqlcheck; InnoDB relies on its built-in crash recovery mechanisms.
  • E. --analyze performs statistical analysis on tables for query optimization, not corruption detection.

Question 33

Which four connection methods can MySQL clients specify with the --protocol option when connecting to a MySQL server? (Choose four.)

  1. TCP
  2. SOCKET
  3. PIPE
  4. DIRECT
  5. IPv6
  6. FILE
  7. IPv4
  8. MEMORY
Show answer and explanation

Correct answer: A, B, C, H. TCP; SOCKET; PIPE; MEMORY

MySQL clients can specify four connection methods via the --protocol option: TCP (network connection using TCP/IP), SOCKET (Unix socket file on the same host), PIPE (named pipe on Windows), and MEMORY (shared memory on Windows). IPv6 and IPv4 are address families, not connection protocols themselves. DIRECT and FILE are not valid --protocol values in MySQL.

Why the other options are wrong

  • D. DIRECT is not a valid MySQL connection protocol option.
  • E. IPv6 is an address family, not a connection protocol that can be specified with -- protocol.
  • F. FILE is not a valid MySQL connection protocol option.
  • G. IPv4 is an address family, not a connection protocol that can be specified with -- protocol.

Question 34

Examine this MySQL client command to connect to a remote database: mysql -h remote.example.org -u root -p --protocol=TCP --ssl-mode= Which two --ssl-mode values will ensure that an X.509-compliant certificate will be used to establish the SSL/TLS connection to MySQL?

  1. REQUIRED
  2. VERIFY_CA
  3. VERIFY_IDENTITY
  4. PREFERRED
  5. DISABLED
Show answer and explanation

Correct answer: B, C. VERIFY_CA; VERIFY_IDENTITY

The --ssl-mode option controls how strictly the client handles encryption. VERIFY_CA requires an encrypted connection and also verifies the server's X.509 certificate against the CA certificates named with --ssl-ca, refusing the connection if the check fails. VERIFY_IDENTITY performs the same CA verification and additionally checks that the host name the client used, remote.example.org here, matches the identity in the server certificate. Both modes therefore guarantee that a valid X.509 certificate is used to establish the SSL/TLS connection, whereas the remaining modes never validate the certificate.

Why the other options are wrong

  • A. REQUIRED demands an encrypted connection but does not verify the server certificate against a CA.
  • D. PREFERRED encrypts when possible, falls back to an unencrypted connection, and never verifies the certificate.
  • E. DISABLED establishes an unencrypted connection, so no certificate is used.

Question 35

Which three actions are effective in capacity planning? (Choose three.)

  1. buying more RAM
  2. monitoring OS resources for patterns
  3. adding circular replication nodes for increased DML capability
  4. buying more CPU
  5. buying more disk
  6. basing expected growth on an average of the last 3 years
  7. consulting the application team about any future projects and use
  8. upgrading to the latest application version
Show answer and explanation

Correct answer: B, F, G. monitoring OS resources for patterns; basing expected growth on an average of the last 3 years; consulting the application team about any future projects and use

Effective capacity planning requires monitoring OS resources over time to identify patterns and trends, consulting with application teams about future projects and usage expectations, and basing projections on historical data such as the last 3 years of growth patterns. These are all proactive, data-driven approaches. Simply buying hardware without planning is reactive. Circular replication does not increase DML capability. Upgrading software versions alone does not address capacity needs.

Why the other options are wrong

  • A. Buying RAM without analysis is reactive, not part of capacity planning.
  • C. Circular replication does not increase DML capacity and is not a capacity planning action.
  • D. Buying CPU without analysis is reactive, not part of planning.
  • E. Buying disk without analysis is reactive, not part of planning.
  • H. Upgrading application version alone is not a capacity planning action.

Question 36

Which two statements are true about MySQL Enterprise Backup? (Choose two.)

  1. It supports backing up only table structures.
  2. It can perform hot or warm backups.
  3. It creates logical backups.
  4. It supports the creation of incremental backups.
  5. It supports backup of a remote MySQL system.
  6. It supports restoring to a remote MySQL system.
Show answer and explanation

Correct answer: B, D. It can perform hot or warm backups; It supports the creation of incremental backups.

MySQL Enterprise Backup can perform both hot and warm backups (B), allowing backups of running databases with minimal downtime. It also supports the creation of incremental backups (D), which back up only the data that has changed since the last backup, reducing backup time and storage requirements. These are key features of MySQL Enterprise Backup.

Why the other options are wrong

  • A. MySQL Enterprise Backup does not support backing up only table structures; it performs full or incremental data backups.
  • C. MySQL Enterprise Backup creates physical backups, not logical backups.
  • E. MySQL Enterprise Backup does not support backing up remote MySQL systems directly.
  • F. MySQL Enterprise Backup does not support restoring directly to a remote MySQL system.

Question 37

You want to check the values of the sort_buffer_size session variables of all existing connections. Which performance_schema table can you query?

  1. user_variables_by_thread
  2. global_variables
  3. variables_by_thread
  4. session_variables
Show answer and explanation

Correct answer: C. variables_by_thread

The variables_by_thread table in performance_schema contains session-level variable values for each thread/connection. This allows you to query the sort_buffer_size and other session variables for all existing connections. The table shows which variable values are set for each specific thread, making it the correct source for per-connection variable inspection.

Why the other options are wrong

  • A. user_variables_by_thread tracks user-defined variables, not system variables like sort_buffer_size.
  • B. global_variables shows only global system variables, not session variables per connection.
  • D. session_variables shows session variable values only for the current connection, not for all existing connections.

Question 38

Where is the default data directory located after installing MySQL using RPM on Oracle Linux 7?

  1. /usr/mysql
  2. /usr/bin
  3. /etc/my.cnf
  4. /var/lib/mysql
  5. /usr
Show answer and explanation

Correct answer: D. /var/lib/mysql

When MySQL is installed using RPM packages on Oracle Linux 7, the default data directory is /var/lib/mysql. This is the standard location where the MySQL server stores database files, log files, and other data by default in RPM-based distributions.

Why the other options are wrong

  • A. /usr/mysql is not a standard MySQL directory on Linux systems.
  • B. /usr/bin contains MySQL executable binaries, not data files.
  • C. /etc/my.cnf is the configuration file location, not the data directory.
  • E. /usr is a parent directory but not the specific data directory location.

Question 39

Which two queries are examples of successful SQL injection attacks? (Choose two.)

  1. SELECT user,passwd FROM members WHERE user = '?';INSERT INTO members('user','passwd') VALUES ('bob@example.com','secret');--';
  2. SELECT id, name FROM user WHERE user.id=(SELECT members.id FROM members);
  3. SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1;
  4. SELECT id, name FROM user WHERE id=23 OR id=32 AND 1=1;
  5. SELECT email,passwd FROM members WHERE email = 'INSERT INTO members('email','passwd') VALUES ('bob@example.com', 'secret');--';
  6. SELECT user, phone FROM customers WHERE name = '\; DROP TABLE users; --';
Show answer and explanation

Correct answer: A, C. SELECT user,passwd FROM members WHERE user = '?';INSERT INTO members('user','passwd') VALUES ('bob@example.com','secret');--';; SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1;

Option A demonstrates a classic SQL injection where the injected quote closes the WHERE clause string, the semicolon appends a new INSERT statement, and the -- comment disables the leftover quote. Option C shows a logical injection where '1=1' is added to an OR clause, making the condition always true and bypassing the intended WHERE filter. Both are successful injection attacks that alter the query's intended logic or add malicious statements.

Why the other options are wrong

  • B. This is a legitimate subquery, not an injection attack; it follows proper SQL syntax without breaking out of intended logic.
  • D. AND binds more tightly than OR, so AND 1=1 applies only to id=32 and changes nothing; the query still returns only ids 23 and 32.
  • E. The INSERT statement is treated as a literal string value, not executed as SQL code.
  • F. The backslash and DROP TABLE are treated as literal string content, not executed as separate SQL commands.

Question 40

An existing asynchronous replication setup is running MySQL 8. Which two steps are a part of implementing GTID replication? (Choose two.)

  1. Enable GTID by executing this on the master and the slave: SET GLOBAL GTID_ENABLED=on;
  2. Execute this on the slave to enable GTID: START SLAVE IO_THREAD WITH GTID;
  3. Restart MySQL (master and slave) with these options enabled: --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency
  4. Execute this on the slave to enable GTID: RESET SLAVE; START SLAVE GTID_NEXT=AUTOMATIC;
  5. On the slave, alter the MySQL master connection setting with: ALTER channel CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
  6. On the slave, alter the MySQL master connection setting with: CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
Show answer and explanation

Correct answer: C, F. Restart MySQL (master and slave) with these options enabled: --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency; On the slave, alter the MySQL master connection setting with: CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

Two key steps for implementing GTID replication are: restarting MySQL on both master and slave with the options --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency (C) to enable GTID mode, and on the slave, altering the MySQL master connection setting with CHANGE MASTER TO MASTER_AUTO_POSITION = 1 (F) to enable automatic position tracking based on GTIDs.

Why the other options are wrong

  • A. GTID_ENABLED is not a valid global variable; the correct approach is to restart with --gtid_mode=ON.
  • B. START SLAVE IO_THREAD WITH GTID is not valid syntax for enabling GTID.
  • D. RESET SLAVE and START SLAVE GTID_NEXT=AUTOMATIC are not the correct commands for enabling GTID on a slave.
  • E. ALTER channel CHANGE MASTER TO is not valid syntax; the correct command is CHANGE MASTER TO without ALTER.

Question 41

You 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.)

  1. No committed transactions are lost.
  2. 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.
  3. 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.
  4. Reads from the slave can return outdated data until the value of the rpl_semi_sync_master_timeout variable is reached.
  5. Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log.
  6. A small amount of committed transactions may be lost in case they were committed just before the disk failure.
Show answer and explanation

Correct answer: A, E. No committed transactions are lost; Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log.

With semi-synchronous replication enabled and the timeout never reached, no committed transactions are lost (A) because the master waits for slave acknowledgment before returning from the commit. However, reads from the slave can return outdated data until it applies all transactions from its relay log (E), since the slave may lag behind in processing the relay log even though transactions are committed on the master. The slave does not automatically detect master failure or perform automatic failover.

Why the other options are wrong

  • B. The slave does not automatically identify master unreachability or perform failover actions; manual intervention is required.
  • C. Applications can read from the slave, but it may not return a full and current set of data while relay log entries are still unapplied.
  • D. The timeout value does not determine when the slave catches up; it only determines when the master stops waiting for acknowledgment.
  • F. Committed transactions are not lost in semi-synchronous replication when the timeout is not reached, as the master waits for slave confirmation.

Question 42

A user wants to connect without entering his or her username and password on the Linux command prompt. Which three locations can be used to store the user's mysql credentials to satisfy this requirement? (Choose three.)

  1. $HOME/.my.cnf file
  2. $MYSQL_HOME/my.cnf file
  3. DATADIR/mysqld-auto.cnf file
  4. $HOME/.mylogin.cnf file
  5. $HOME/.mysql/auth/login file
  6. /etc/my.cnf file
  7. $HOME/.mysqlrc file
Show answer and explanation

Correct answer: A, D, F. $HOME/.my.cnf file; $HOME/.mylogin.cnf file; /etc/my.cnf file

MySQL clients can read credentials from three primary locations: $HOME/.my.cnf (the user's home directory configuration file), $HOME/.mylogin.cnf (the encrypted login path file), and /etc/my.cnf (the system-wide configuration file). These files allow users to store credentials in a format that MySQL utilities recognize, enabling passwordless connections.

Why the other options are wrong

  • B. $MYSQL_HOME/my.cnf is not a standard MySQL credential location.
  • C. DATADIR/mysqld-auto.cnf is used for persisted system variables, not user credentials.
  • E. $HOME/.mysql/auth/login is not a recognized MySQL credential storage location.
  • G. $HOME/.mysqlrc is not a standard MySQL configuration or credential file.

Question 43

Which two storage engines provide a view of the data consistent with the storage system at any moment? (Choose two.)

  1. MyISAM
  2. NDB
  3. MEMORY
  4. ARCHIVE
  5. InnoDB
Show answer and explanation

Correct answer: B, E. NDB; InnoDB

NDB and InnoDB provide consistent views of data as stored on disk at any moment. NDB (MySQL Cluster) provides strong consistency guarantees across distributed nodes. InnoDB provides ACID consistency with crash recovery, ensuring data consistency from the last committed transaction. MyISAM does not provide consistency guarantees and can leave data in an inconsistent state after crashes. MEMORY is volatile and provides no durability. ARCHIVE stores compressed, insert-only historical data without consistency mechanisms.

Why the other options are wrong

  • A. MyISAM does not provide crash recovery or consistency guarantees.
  • C. MEMORY is a volatile in-memory engine without durability or consistency mechanisms.
  • D. ARCHIVE is optimized for compressed storage of historical data, not for consistent current-state views.

Question 44

Examine this command, which executes successfully: cluster.addInstance('<user>@<host>:<port>', {recoveryMethod: 'clone'}) Which three statements are true? (Choose three.)

  1. The account used to perform this recovery needs the BACKUP_ADMIN privilege.
  2. A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster.
  3. InnoDB tablespaces outside the datadir are able to be cloned.
  4. It is always slower than {recoveryMethod: 'incremental'}.
  5. A new instance is installed, initialized, and provisioned with data from an instance already in the cluster and joined to the cluster.
  6. InnoDB redo logs must not rotate for the duration of the execution; otherwise, the recovery will fail.
Show answer and explanation

Correct answer: A, B, C. The account used to perform this recovery needs the BACKUP_ADMIN privilege; A target instance must exist, then it will be provisioned with data from an instance already in the cluster and joined to the cluster; InnoDB tablespaces outside the datadir are able to be cloned.

When using recoveryMethod: 'clone', the account needs BACKUP_ADMIN privilege to access backup capabilities. A target instance must already exist and be initialized before cloning data into it. InnoDB cloning also copies tablespaces created outside the datadir. Option D is incorrect because clone can be faster than incremental in many scenarios. Option E is incorrect because you need an existing target instance. Option F is incorrect because redo logs can rotate during clone operations.

Why the other options are wrong

  • D. Clone can be faster than incremental recovery depending on network bandwidth and data volume.
  • E. A target instance must already exist; clone does not install or initialize a new instance.
  • F. InnoDB redo logs can rotate during clone operations without causing failure.

Question 45

Examine this command, which executes successfully: shell> mysqldump --master-data=2 --single-transaction --result-file=dump.sql mydb Which two statements are true? (Choose two.)

  1. It executes flush tables with read lock.
  2. It enforces consistent backups for all storage engines.
  3. The backup created is a consistent data dump.
  4. This option uses the READ COMMITTED transaction isolation mode.
  5. It is a cold backup.
Show answer and explanation

Correct answer: A, C. It executes flush tables with read lock; The backup created is a consistent data dump.

The --single-transaction option starts a REPEATABLE READ transaction with a consistent snapshot, giving a consistent view of InnoDB tables at a single point in time, so the dump is consistent. The --master-data=2 option records the binary log position as a comment, enabling point-in-time recovery. Because --master-data needs a binary log position that matches the snapshot, mysqldump briefly executes FLUSH TABLES WITH READ LOCK, starts the transaction, reads the position and then releases the lock. This is a hot backup since the database remains accessible during execution.

Why the other options are wrong

  • B. While --single-transaction provides consistency for InnoDB tables, it does not enforce consistency for all storage engines like MyISAM.
  • D. The --single-transaction option uses the REPEATABLE READ isolation level, not READ COMMITTED.
  • E. This is a hot backup (online backup) because the database remains operational during execution; a cold backup requires the server to be offline.

Question 46

Examine these statements, which execute successfully: TRUNCATE test; BEGIN; INSERT INTO test(id, name) VALUES(1, "Hello"); ROLLBACK; SELECT id FROM test; Which three storage engines would return a nonempty recordset for the test table when executing the statements? (Choose three.)

  1. NDB
  2. ARCHIVE
  3. InnoDB
  4. BLACKHOLE
  5. MEMORY
  6. MyISAM
Show answer and explanation

Correct answer: B, E, F. ARCHIVE; MEMORY; MyISAM

ARCHIVE, MEMORY and MyISAM are nontransactional storage engines, so BEGIN and ROLLBACK have no effect on them. The INSERT is written to the table as soon as it executes, the ROLLBACK has nothing it can undo (MySQL only raises a warning that some nontransactional changes could not be rolled back), and the final SELECT returns the row with id 1. The TRUNCATE at the start only guarantees the table was empty before the transaction, so the single inserted row is exactly what these three engines return.

Why the other options are wrong

  • A. NDB is transactional, so the ROLLBACK undoes the INSERT and the table stays empty.
  • C. InnoDB is transactional, so the ROLLBACK undoes the INSERT and the table stays empty.
  • D. BLACKHOLE discards all written data, so a SELECT always returns an empty result.

Question 47

Which two statements are true about using backups of the binary log? (Choose two.)

  1. Multiple binary logs can be used to restore data.
  2. Multiple binary logs can be applied in parallel for faster data restoration.
  3. Binary logs are relatively small, and therefore, excellent for long-term storage and disaster recovery.
  4. Binary logs can always be used to unapply unwanted schema changes.
  5. They allow for point-in-time recovery of the data.
Show answer and explanation

Correct answer: A, E. Multiple binary logs can be used to restore data; They allow for point-in-time recovery of the data.

Multiple binary logs can be replayed sequentially to restore data up to a specific point in time, enabling point-in-time recovery of the database. Multiple binary logs can be combined and applied in sequence to recover from a backup to any desired state. Binary logs are not suitable for long-term storage due to their size and the requirement to maintain an unbroken chain; they are temporary transaction records best used with full backups. Binary logs cannot reliably unapply schema changes like DROP TABLE without triggering errors or data loss.

Why the other options are wrong

  • B. Binary logs cannot be applied in parallel; they must be applied sequentially to maintain transaction order and consistency.
  • C. Binary logs grow continuously and are large; they are not suitable for long-term storage and disaster recovery without a full backup.
  • D. Binary logs cannot always unapply unwanted schema changes, especially destructive operations like DROP TABLE, which may result in data loss.

Question 48

Which two are valid uses for binary logs on a MySQL instance? (Choose two.)

  1. recording the order in which queries are issued
  2. audit of all queries
  3. point-in-time recovery
  4. replication
  5. logging the duration and locks for all queries
Show answer and explanation

Correct answer: C, D. point-in-time recovery; replication

Binary logs enable point-in-time recovery by recording all data-modifying statements in sequence, allowing restoration to any specific moment in time. They are also essential for replication, as slaves read the master's binary log to replicate changes to their own datasets.

Why the other options are wrong

  • A. Binary logs record data changes in commit order and do not log SELECT statements, so they do not record the order in which all queries were issued.
  • B. Binary logs are not designed for comprehensive query auditing; they only record data-modifying statements, not all queries.
  • E. Binary logs do not record query duration or locking information; that information is provided by other logging mechanisms like the slow query log.

Question 49

Your MySQL installation is running low on space due to binary logs. You need to reduce your log space usage urgently. Which two sets of actions when completed will accomplish this? (Choose two.)

  1. Use SET GLOBAL binlog_expire_logs_seconds=<value> and restart the server.
  2. Set binlog_expire_logs_seconds in my.cnf.
  3. Set binlog_expire_logs_seconds = 0 in my.cnf and restart the server.
  4. Use SET PERSIST binlog_expire_logs_seconds=<value>.
  5. Use PURGE BINARY LOGS to <binlog_name>.
  6. Use SET GLOBAL binlog_expire_logs_seconds=<value> and run the FLUSH BINARY LOGS command.
Show answer and explanation

Correct answer: E, F. Use PURGE BINARY LOGS to <binlog_name>; Use SET GLOBAL binlog_expire_logs_seconds=<value> and run the FLUSH BINARY LOGS command.

PURGE BINARY LOGS TO removes all binary logs before the named file immediately. Setting binlog_expire_logs_seconds with SET GLOBAL and then running FLUSH BINARY LOGS causes a log rotation, which triggers the removal of logs older than the new expiry period straight away. Both free space now without a restart.

Why the other options are wrong

  • A. A value set with SET GLOBAL is lost on restart, so the new expiry setting would not take effect.
  • B. Changing my.cnf has no effect until the server is restarted, so it does not free space urgently.
  • C. A value of 0 disables automatic expiry, so no logs would be removed.
  • D. SET PERSIST alone waits for the next log rotation before any purge happens, so it does not free space immediately.

Question 50

Examine Joe's account: CREATE USER 'joe'@'%' IDENTIFIED BY '*secret*' GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%' All existing connections for joe are killed. Which two commands will stop joe establishing access to the MySQL instance? (Choose two.)

  1. ALTER USER 'joe'@'%' ACCOUNT LOCK
  2. ALTER USER 'joe'@'%' SET password='*invalid*'
  3. REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%'
  4. ALTER USER 'joe'@'%' PASSWORD HISTORY 0
  5. ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE
  6. REVOKE USAGE ON *.* FROM 'joe'@'%'
Show answer and explanation

Correct answer: A, E. ALTER USER 'joe'@'%' ACCOUNT LOCK; ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE

ALTER USER 'joe'@'%' ACCOUNT LOCK directly locks the account, preventing any login regardless of password validity. ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE sets an invalid password and marks it as expired, requiring a password reset before login is possible. These two commands are the most direct mechanisms to prevent account access. Revoking privileges still allows the account to connect, just without permissions. Changing password history settings does not affect login ability. Option B is not valid ALTER USER syntax, so it fails and changes nothing.

Why the other options are wrong

  • B. ALTER USER has no SET password clause, so this statement fails with a syntax error and joe can still log in.
  • C. REVOKE ALL PRIVILEGES removes permissions but does not prevent the user from establishing a connection.
  • D. PASSWORD HISTORY settings control password reuse policy, not login access.
  • F. USAGE means no privileges, so revoking it removes nothing and joe can still log in.

50 questions in. Want the rest? The first half of the pack, free by email · The full pack

Question 51

A valid raw backup of the shop.customers MyISAM table was taken. You must restore the table. You begin with these steps: 1. Confirm that secure_file_priv='/var/tmp' 2. mysql> DROP TABLE shop.customers; 3. shell> cp /backup/customers.MY* /var/lib/mysql/shop/ Which two actions are required to complete the restore? (Choose two.)

  1. shell> cp /backup/customers.sdi /var/tmp
  2. shell> cp /backup/customers.sdi /var/lib/mysql/shop/
  3. mysql> SOURCE '/var/tmp/customers.sdi'
  4. mysql> IMPORT TABLE FROM /var/tmp/customers.sdi
  5. shell> cp /backup/customers.frm /var/lib/mysql/shop/
  6. mysql> IMPORT TABLE FROM /var/lib/mysql/shop/customers.sdi
  7. mysql> ALTER TABLE shop.customers IMPORT TABLESPACE
  8. mysql> ALTER TABLE shop.customers DISCARD TABLESPACE
Show answer and explanation

Correct answer: A, D. shell> cp /backup/customers.sdi /var/tmp; mysql> IMPORT TABLE FROM /var/tmp/customers.sdi

In MySQL 8.0 a MyISAM table has no .frm file; its definition is stored in a serialized dictionary information (.sdi) file. To restore from a raw copy, copy the .sdi file into the directory allowed by secure_file_priv and then run IMPORT TABLE FROM on that file. The server reads the definition and registers the table with the copied .MYD and .MYI files.

Why the other options are wrong

  • B. IMPORT TABLE reads the .sdi file from the secure_file_priv directory, not from the data directory.
  • C. SOURCE executes SQL statements from a script, and an .sdi file is JSON metadata, not SQL.
  • E. MySQL 8.0 no longer uses .frm files, so there is none to copy.
  • F. The .sdi file must be in the secure_file_priv location, which is /var/tmp, not the schema directory.
  • G. IMPORT TABLESPACE is an InnoDB transportable tablespace operation and does not apply to MyISAM.
  • H. DISCARD TABLESPACE is an InnoDB operation and the table has already been dropped.

Question 52

Examine this command, which executes successfully: mysqlpump --user=root --password > full_backup.sql Which two databases will be excluded from this dump? (Choose two.)

  1. information_schema
  2. world
  3. employee
  4. sys
  5. mysql
Show answer and explanation

Correct answer: A, D. information_schema; sys

By default, mysqlpump excludes the information_schema and sys databases from dumps because these are system metadata databases that should not be backed up as part of regular backup operations. The world, employee, and mysql databases are user-accessible or system tables that would be included unless explicitly excluded with the -- exclude-databases option.

Why the other options are wrong

  • B. The world database is not excluded by default and would be included in the dump.
  • C. The employee database is not excluded by default and would be included in the dump.
  • E. The mysql database is not excluded by default; it contains system tables like user accounts and would be included in the dump.

Question 53

You are backing up raw InnoDB files by using mysqlbackup. Which two groups of files will be backed up during a full backup? (Choose two.)

  1. ibbackup files
  2. *.CSM files
  3. *.sdi files
  4. *.ibd files
  5. ib_logfile* files
Show answer and explanation

Correct answer: D, E. *.ibd files; ib_logfile* files

During a full InnoDB backup with mysqlbackup, two critical file groups are backed up: ib_logfile* files (the InnoDB transaction log files essential for crash recovery) and *.ibd files (the individual tablespace files containing table data for file-per-table configurations). Both are necessary for a complete and recoverable backup.

Why the other options are wrong

  • A. ibbackup files are temporary files created during the backup process itself, not primary backup targets.
  • B. *.CSM files are associated with CSV storage engine, not InnoDB.
  • C. *.sdi files are Serialized Dictionary Information files used for metadata but are not a primary focus of mysqlbackup full backups.

Question 54

You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in-place upgrade. Examine the message sequence generated during the first start of MySQL 8.0.18: ... [System] ... /usr/sbin/mysqld (mysqld 8.0.18-commercial) starting as process 2754 ... [System] ... Starting upgrade of data directory. ... [ERROR] ... Table upgrade required. Please do "REPAIR TABLE `columns_priv`" or dump/reload to fix it! ... [ERROR] ... Table upgrade required. Please do "REPAIR TABLE `event`" or dump/reload to fix it! ... [ERROR] ... Table upgrade required. Please do "REPAIR TABLE `proc`" or dump/reload to fix it! ... [ERROR] ... Table upgrade required. Please do "REPAIR TABLE `proxies_priv`" or dump/reload to fix it! ... [ERROR] ... Table upgrade required. Please do "REPAIR TABLE `tables_priv`" or dump/reload to fix it! ... [ERROR] ... Failed to open mysql.event Table. ... [ERROR] ... Failed to open mysql.proc Table. ... [ERROR] ... Failed to Populate DD tables. ... [ERROR] ... Aborting ... [System] ... /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18-commercial) MySQL Enterprise Server - Commercial. Which step or set of steps will resolve the errors?

  1. Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again.
  2. Execute: mysqlcheck --check-upgrade mysql columns_priv event proc proxies_priv tables_priv.
  3. Start mysqld again using the --upgrade=FORCE option.
  4. Execute: mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv.
  5. Go to the <datadir>/mysql directory and execute: myisamchk --update-state columns_priv event proc proxies_priv tables_priv.
Show answer and explanation

Correct answer: A. Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables for upgrade. Upgrade to 8.0.18 again.

The log shows mysqld 8.0.18 starting the data directory upgrade, reporting Table upgrade required for columns_priv, event, proc, proxies_priv and tables_priv, failing to open mysql.event and mysql.proc, failing to populate the DD tables, then Aborting and shutting down. Because the 8.0 server cannot start, the tables must be fixed under 5.7. The recovery is to remove the redo log files, put the 5.7.28 binaries back, start 5.7.28 on the data directory, repair and prepare the tables for upgrade, shut down cleanly and repeat the upgrade to 8.0.18.

Why the other options are wrong

  • B. mysqlcheck needs a running server, and the 8.0.18 server aborts during startup; -- check-upgrade also only checks tables.
  • C. --upgrade=FORCE repeats the same data dictionary upgrade and fails again on the same tables.
  • D. mysqlcheck cannot connect because the 8.0.18 server shuts down during the failed upgrade.
  • E. myisamchk --update-state only marks tables as checked and does not upgrade their format.

Question 55

Which three are requirements for a secure MySQL Server environment? (Choose three.)

  1. Restrict the number of OS users that have access at the OS level.
  2. Ensure appropriate file system privileges for OS users and groups.
  3. Minimize the number of non-MySQL Server-related processes running on the server host.
  4. Encrypt the file system to avoid needing exact file-system permissions.
  5. Keep the entire software stack on one OS host.
  6. Run MySQL server as the root user to prevent incorrect sudo settings.
Show answer and explanation

Correct answer: A, B, C. Restrict the number of OS users that have access at the OS level; Ensure appropriate file system privileges for OS users and groups; Minimize the number of non-MySQL Server-related processes running on the server host.

A secure MySQL environment starts with the host itself. Limiting the number of operating system users who can log in to the server reduces the people able to reach data files, logs and configuration directly. Setting correct file system ownership and permissions ensures that only the mysql account and authorized administrators can read or change the data directory, option files and keys. Keeping unrelated processes off the server host reduces the attack surface, because every extra service is another possible way in and a potential source of privilege escalation.

Why the other options are wrong

  • D. File system encryption protects data at rest but does not remove the need for correct file system permissions on a running server.
  • E. Placing the entire software stack on one host enlarges the attack surface rather than securing the database server.
  • F. Running mysqld as root is a serious risk, and MySQL should run under a dedicated unprivileged account such as mysql.

Question 56

Examine this parameter setting: audit_log=FORCE_LOG_PERMANENT What effect does this have on auditing?

  1. It will force the load of the audit plugin even in case of errors at server start.
  2. It causes the audit log to be created if it does not exist.
  3. It prevents the audit plugin from being removed from the running server.
  4. It prevents the audit log from being removed or rotated.
Show answer and explanation

Correct answer: C. It prevents the audit plugin from being removed from the running server.

The audit_log option accepts the plugin load values ON, OFF, FORCE and FORCE_PLUS_PERMANENT. The FORCE_PLUS_PERMANENT setting tells the server that the audit plugin must initialize successfully at startup and, once loaded, cannot be unloaded while the server runs, so UNINSTALL PLUGIN audit_log fails with an error. This protects auditing from being switched off by anyone with administrative access to the running instance; removing the plugin requires changing the option and restarting the server.

Why the other options are wrong

  • A. The forced setting does the opposite: if the plugin fails to initialize, the server refuses to start rather than continuing.
  • B. The audit log file is created whenever the plugin is active and needs it, independent of this load option.
  • D. The option controls whether the plugin can be unloaded and has no effect on removing or rotating the audit log file.

Question 57

You want to log only the changes made to the database objects and data on the MySQL system. Which log will do this by default?

  1. general query log
  2. audit log
  3. slow query log
  4. binary log
  5. error log
Show answer and explanation

Correct answer: D. binary log

The binary log records all changes made to database objects and data in a binary format, enabling point-in-time recovery and replication. It captures INSERT, UPDATE, DELETE, and DDL statements. The general query log records all queries executed, the audit log tracks security-related events, the slow query log records long-running queries, and the error log records server errors.

Why the other options are wrong

  • A. The general query log records all queries including read operations, not just changes to data.
  • B. The audit log focuses on security events and user actions, not database changes by default.
  • C. The slow query log records queries exceeding a time threshold, not all changes.
  • E. The error log records server errors and warnings, not data changes.

That was 57 of 228.

The full Oracle 1Z0-908 pack has all 228 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack

Not ready yet? Get the first 109 free as one PDF by email. Get the Half Pack →