MySQL 8.0 Database Administrator Exam Questions Total Questions: 524 – 9 Mock Exams
Practice Set 1
Time limit: 0
0 of 60 questions completed
Questions:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Information
Click on Start Test
You have already completed the Test before. Hence you can not start it again.
Test is loading...
You must sign in or sign up to start the Test.
You have to finish following quiz, to start this Test:
Your results are here!! for" MySQL 8.0 Database Administrator Practice Test 1 "
0 of 60 questions answered correctly
Your time:
Time has elapsed
Your Final Score is : 0
You have attempted : 0
Number of Correct Questions : 0 and scored 0
Number of Incorrect Questions : 0 and Negative marks 0
Average score
Your score
MySQL 8.0 Database Administrator
You have attempted: 0
Number of Correct Questions: 0 and scored 0
Number of Incorrect Questions: 0 and Negative marks 0
You can review your answers by clicking on “View Answers” option. Important Note : Open Reference Documentation Links in New Tab (Right Click and Open in New Tab).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Answered
Review
Question 1 of 60
1. Question
Which method is best for performing a MySQL major version upgrade?
Correct
A logical backup upgrade ensures a clean migration while avoiding data corruption. In-place upgrades, replication, or rolling upgrades may fail due to incompatibilities.
Incorrect
A logical backup upgrade ensures a clean migration while avoiding data corruption. In-place upgrades, replication, or rolling upgrades may fail due to incompatibilities.
Unattempted
A logical backup upgrade ensures a clean migration while avoiding data corruption. In-place upgrades, replication, or rolling upgrades may fail due to incompatibilities.
Question 2 of 60
2. Question
Which performance schema table tracks mutex waits and deadlocks in MySQL?
Correct
The performance_schema.events_waits_summary_by_instance table monitors mutex waits, deadlocks, and thread contention, helping diagnose performance bottlenecks. The other tables do not exist in MySQL.
Incorrect
The performance_schema.events_waits_summary_by_instance table monitors mutex waits, deadlocks, and thread contention, helping diagnose performance bottlenecks. The other tables do not exist in MySQL.
Unattempted
The performance_schema.events_waits_summary_by_instance table monitors mutex waits, deadlocks, and thread contention, helping diagnose performance bottlenecks. The other tables do not exist in MySQL.
Question 3 of 60
3. Question
Which MySQL system variable is used to control the time a client waits before it is disconnected due to inactivity?
Correct
The wait_timeout system variable determines how long an idle session can remain open before MySQL closes the connection. If no activity is detected from the client within this period, the server terminates the connection. The connect_timeout variable controls the time limit for establishing a connection, while net_read_timeout affects network packet transmission time. The interactive_timeout variable applies specifically to interactive clients like MySQL Shell.
Incorrect
The wait_timeout system variable determines how long an idle session can remain open before MySQL closes the connection. If no activity is detected from the client within this period, the server terminates the connection. The connect_timeout variable controls the time limit for establishing a connection, while net_read_timeout affects network packet transmission time. The interactive_timeout variable applies specifically to interactive clients like MySQL Shell.
Unattempted
The wait_timeout system variable determines how long an idle session can remain open before MySQL closes the connection. If no activity is detected from the client within this period, the server terminates the connection. The connect_timeout variable controls the time limit for establishing a connection, while net_read_timeout affects network packet transmission time. The interactive_timeout variable applies specifically to interactive clients like MySQL Shell.
Question 4 of 60
4. Question
Why is GTID-based replication recommended for improving high availability in MySQL?
Correct
Global Transaction Identifiers (GTID) uniquely identify transactions across replicas, allowing for easier failovers and better consistency by ensuring that all transactions are executed exactly once.
Incorrect
Global Transaction Identifiers (GTID) uniquely identify transactions across replicas, allowing for easier failovers and better consistency by ensuring that all transactions are executed exactly once.
Unattempted
Global Transaction Identifiers (GTID) uniquely identify transactions across replicas, allowing for easier failovers and better consistency by ensuring that all transactions are executed exactly once.
Question 5 of 60
5. Question
Which InnoDB structure stores committed transaction logs for durability and recovery?
Correct
The InnoDB Redo Log System ensures transaction durability by storing committed changes, allowing MySQL to recover from crashes. The Doublewrite Buffer prevents corruption, Undo Logs enable rollbacks, and the Adaptive Hash Index speeds up lookups.
Incorrect
The InnoDB Redo Log System ensures transaction durability by storing committed changes, allowing MySQL to recover from crashes. The Doublewrite Buffer prevents corruption, Undo Logs enable rollbacks, and the Adaptive Hash Index speeds up lookups.
Unattempted
The InnoDB Redo Log System ensures transaction durability by storing committed changes, allowing MySQL to recover from crashes. The Doublewrite Buffer prevents corruption, Undo Logs enable rollbacks, and the Adaptive Hash Index speeds up lookups.
Question 6 of 60
6. Question
Which requirement must be met before adding an instance to an InnoDB Cluster?
Correct
In InnoDB Cluster, GTID-based transactions are mandatory for all nodes. The node does not require read-only mode, and asynchronous or semi-synchronous replication is not part of InnoDB Cluster setup.
Incorrect
In InnoDB Cluster, GTID-based transactions are mandatory for all nodes. The node does not require read-only mode, and asynchronous or semi-synchronous replication is not part of InnoDB Cluster setup.
Unattempted
In InnoDB Cluster, GTID-based transactions are mandatory for all nodes. The node does not require read-only mode, and asynchronous or semi-synchronous replication is not part of InnoDB Cluster setup.
Question 7 of 60
7. Question
Which Enterprise Monitor feature provides real-time system health assessments?
Correct
The Health Dashboard in MySQL Enterprise Monitor provides real-time insights into system status, resource utilization, and potential issues. Other features focus on reporting, error analysis, and process tracking.
Incorrect
The Health Dashboard in MySQL Enterprise Monitor provides real-time insights into system status, resource utilization, and potential issues. Other features focus on reporting, error analysis, and process tracking.
Unattempted
The Health Dashboard in MySQL Enterprise Monitor provides real-time insights into system status, resource utilization, and potential issues. Other features focus on reporting, error analysis, and process tracking.
Question 8 of 60
8. Question
Which replication type in MySQL provides the lowest risk of data loss while ensuring high availability?
Correct
Group replication ensures stronger consistency and availability by using a consensus-based mechanism where transactions must be validated by the group before being committed, reducing the risk of data loss.
Incorrect
Group replication ensures stronger consistency and availability by using a consensus-based mechanism where transactions must be validated by the group before being committed, reducing the risk of data loss.
Unattempted
Group replication ensures stronger consistency and availability by using a consensus-based mechanism where transactions must be validated by the group before being committed, reducing the risk of data loss.
Question 9 of 60
9. Question
What is the primary purpose of the keyring_file plugin in MySQL 8.0?
Correct
The keyring_file plugin is a keyring component in MySQL 8.0 that stores encryption keys securely in a file. It is used primarily for Transparent Data Encryption (TDE) to encrypt InnoDB tablespaces. Binary log encryption is a separate feature managed differently. User authentication does not depend on keyrings. Password protection mechanisms involve hashing, not keyring encryption.
Incorrect
The keyring_file plugin is a keyring component in MySQL 8.0 that stores encryption keys securely in a file. It is used primarily for Transparent Data Encryption (TDE) to encrypt InnoDB tablespaces. Binary log encryption is a separate feature managed differently. User authentication does not depend on keyrings. Password protection mechanisms involve hashing, not keyring encryption.
Unattempted
The keyring_file plugin is a keyring component in MySQL 8.0 that stores encryption keys securely in a file. It is used primarily for Transparent Data Encryption (TDE) to encrypt InnoDB tablespaces. Binary log encryption is a separate feature managed differently. User authentication does not depend on keyrings. Password protection mechanisms involve hashing, not keyring encryption.
Question 10 of 60
10. Question
What happens when a MySQL 8.0 primary server fails in an InnoDB Cluster with Group Replication?
Correct
InnoDB Cluster with Group Replication provides automatic failover by using a consensus-based election to select a new primary node if the existing primary fails. Transactions are not lost, as changes are replicated across nodes. The binary logs are not reset, and manual intervention is unnecessary.
Incorrect
InnoDB Cluster with Group Replication provides automatic failover by using a consensus-based election to select a new primary node if the existing primary fails. Transactions are not lost, as changes are replicated across nodes. The binary logs are not reset, and manual intervention is unnecessary.
Unattempted
InnoDB Cluster with Group Replication provides automatic failover by using a consensus-based election to select a new primary node if the existing primary fails. Transactions are not lost, as changes are replicated across nodes. The binary logs are not reset, and manual intervention is unnecessary.
Question 11 of 60
11. Question
Which MySQL 8.0 storage engine is transactional and supports foreign keys?
Correct
InnoDB is the only storage engine in MySQL 8.0 that supports transactions (ACID compliance) and foreign keys. MyISAM does not support transactions or foreign keys. MEMORY stores data in RAM for fast access but does not support foreign keys. CSV stores data in text files and lacks transactional support.
Incorrect
InnoDB is the only storage engine in MySQL 8.0 that supports transactions (ACID compliance) and foreign keys. MyISAM does not support transactions or foreign keys. MEMORY stores data in RAM for fast access but does not support foreign keys. CSV stores data in text files and lacks transactional support.
Unattempted
InnoDB is the only storage engine in MySQL 8.0 that supports transactions (ACID compliance) and foreign keys. MyISAM does not support transactions or foreign keys. MEMORY stores data in RAM for fast access but does not support foreign keys. CSV stores data in text files and lacks transactional support.
Question 12 of 60
12. Question
Which of the following statements about the doublewrite buffer in MySQL 8.0 is correct?
Correct
The doublewrite buffer protects against partial page writes by writing modified pages twice: first to the doublewrite buffer, then to the final tablespace location. This mechanism ensures data integrity even if a crash occurs mid-write. It does not manage buffer pools, store temporary tables, or recover from query errors automatically.
Incorrect
The doublewrite buffer protects against partial page writes by writing modified pages twice: first to the doublewrite buffer, then to the final tablespace location. This mechanism ensures data integrity even if a crash occurs mid-write. It does not manage buffer pools, store temporary tables, or recover from query errors automatically.
Unattempted
The doublewrite buffer protects against partial page writes by writing modified pages twice: first to the doublewrite buffer, then to the final tablespace location. This mechanism ensures data integrity even if a crash occurs mid-write. It does not manage buffer pools, store temporary tables, or recover from query errors automatically.
Question 13 of 60
13. Question
What is the primary purpose of relay logs in MySQL replication?
Correct
The relay log is a temporary storage mechanism on a replica server that holds binary log events received from the primary server. The SQL thread then processes these events in order. Unlike binary logs, relay logs are not meant for backups but enable delayed replication. They do not store query cache or error messages, which are handled elsewhere in MySQL.
Incorrect
The relay log is a temporary storage mechanism on a replica server that holds binary log events received from the primary server. The SQL thread then processes these events in order. Unlike binary logs, relay logs are not meant for backups but enable delayed replication. They do not store query cache or error messages, which are handled elsewhere in MySQL.
Unattempted
The relay log is a temporary storage mechanism on a replica server that holds binary log events received from the primary server. The SQL thread then processes these events in order. Unlike binary logs, relay logs are not meant for backups but enable delayed replication. They do not store query cache or error messages, which are handled elsewhere in MySQL.
Question 14 of 60
14. Question
Which of the following correctly enables MySQL‘s binary log on a running server?
Correct
The binary log requires log_bin to be set in my.cnf, and a server restart is necessary for activation. There is no SQL command to enable it dynamically.
Incorrect
The binary log requires log_bin to be set in my.cnf, and a server restart is necessary for activation. There is no SQL command to enable it dynamically.
Unattempted
The binary log requires log_bin to be set in my.cnf, and a server restart is necessary for activation. There is no SQL command to enable it dynamically.
Question 15 of 60
15. Question
Which strategy should be used to optimize queries that filter on multiple columns?
Correct
A composite index allows efficient filtering on multiple columns, significantly improving query performance. Creating individual indexes does not guarantee efficient query execution because MySQL may not combine them optimally. Full table scans are inefficient, and foreign key constraints ensure data integrity but do not optimize queries.
Incorrect
A composite index allows efficient filtering on multiple columns, significantly improving query performance. Creating individual indexes does not guarantee efficient query execution because MySQL may not combine them optimally. Full table scans are inefficient, and foreign key constraints ensure data integrity but do not optimize queries.
Unattempted
A composite index allows efficient filtering on multiple columns, significantly improving query performance. Creating individual indexes does not guarantee efficient query execution because MySQL may not combine them optimally. Full table scans are inefficient, and foreign key constraints ensure data integrity but do not optimize queries.
Question 16 of 60
16. Question
Which method ensures that a raw file backup is consistent before restoration?
Correct
Before taking a raw file backup, FLUSH TABLES WITH READ LOCK prevents writes, ensuring consistency. Transaction logging is unrelated. ANALYZE TABLE and OPTIMIZE TABLE are useful for performance but do not ensure backup consistency.
Incorrect
Before taking a raw file backup, FLUSH TABLES WITH READ LOCK prevents writes, ensuring consistency. Transaction logging is unrelated. ANALYZE TABLE and OPTIMIZE TABLE are useful for performance but do not ensure backup consistency.
Unattempted
Before taking a raw file backup, FLUSH TABLES WITH READ LOCK prevents writes, ensuring consistency. Transaction logging is unrelated. ANALYZE TABLE and OPTIMIZE TABLE are useful for performance but do not ensure backup consistency.
Question 17 of 60
17. Question
Which component is required to configure a MySQL InnoDB Cluster for high availability?
Correct
MySQL Router enables transparent connection routing by directing clients to the available primary node in an InnoDB Cluster. MySQL does not use a Group Coordinator, a Proxy Layer, or a Replica Handler, which are non-existent in MySQLÂ’s native HA setup.
Incorrect
MySQL Router enables transparent connection routing by directing clients to the available primary node in an InnoDB Cluster. MySQL does not use a Group Coordinator, a Proxy Layer, or a Replica Handler, which are non-existent in MySQLÂ’s native HA setup.
Unattempted
MySQL Router enables transparent connection routing by directing clients to the available primary node in an InnoDB Cluster. MySQL does not use a Group Coordinator, a Proxy Layer, or a Replica Handler, which are non-existent in MySQLÂ’s native HA setup.
Question 18 of 60
18. Question
Which MySQL command verifies the authentication plug-in assigned to a specific user?
Correct
To check a user‘s authentication plug-in, query SELECT User, Plugin FROM mysql.user. The SHOW GRANTS command displays privileges but not authentication details. SHOW AUTHENTICATION is not a valid MySQL command, and DESCRIBE mysql.user does not filter specific users or show authentication plug-ins.
Incorrect
To check a user‘s authentication plug-in, query SELECT User, Plugin FROM mysql.user. The SHOW GRANTS command displays privileges but not authentication details. SHOW AUTHENTICATION is not a valid MySQL command, and DESCRIBE mysql.user does not filter specific users or show authentication plug-ins.
Unattempted
To check a user‘s authentication plug-in, query SELECT User, Plugin FROM mysql.user. The SHOW GRANTS command displays privileges but not authentication details. SHOW AUTHENTICATION is not a valid MySQL command, and DESCRIBE mysql.user does not filter specific users or show authentication plug-ins.
Question 19 of 60
19. Question
Which Enterprise Monitor feature provides visual tracking of database size expansion?
Correct
The Capacity Planning Graphs in MySQL Enterprise Monitor help visualize database growth over time, allowing administrators to plan for future storage requirements. Other tools focus on query performance, replication, or system resource usage.
Incorrect
The Capacity Planning Graphs in MySQL Enterprise Monitor help visualize database growth over time, allowing administrators to plan for future storage requirements. Other tools focus on query performance, replication, or system resource usage.
Unattempted
The Capacity Planning Graphs in MySQL Enterprise Monitor help visualize database growth over time, allowing administrators to plan for future storage requirements. Other tools focus on query performance, replication, or system resource usage.
Question 20 of 60
20. Question
Which approach helps resolve replication lag in MySQL 8.0?
Correct
Using parallel replication with multiple worker threads reduces replication lag by processing transactions concurrently. Reducing sync_binlog affects crash recovery, logging slave updates does not improve performance, and disabling GTIDs risks inconsistencies.
Incorrect
Using parallel replication with multiple worker threads reduces replication lag by processing transactions concurrently. Reducing sync_binlog affects crash recovery, logging slave updates does not improve performance, and disabling GTIDs risks inconsistencies.
Unattempted
Using parallel replication with multiple worker threads reduces replication lag by processing transactions concurrently. Reducing sync_binlog affects crash recovery, logging slave updates does not improve performance, and disabling GTIDs risks inconsistencies.
Question 21 of 60
21. Question
Which action ensures a clean cluster restart after a full outage?
Correct
A controlled cluster restart requires defining a primary node first to prevent data divergence. Resetting binary logs, restoring backups, or enabling auto-recovery does not guarantee a clean restart.
Incorrect
A controlled cluster restart requires defining a primary node first to prevent data divergence. Resetting binary logs, restoring backups, or enabling auto-recovery does not guarantee a clean restart.
Unattempted
A controlled cluster restart requires defining a primary node first to prevent data divergence. Resetting binary logs, restoring backups, or enabling auto-recovery does not guarantee a clean restart.
Question 22 of 60
22. Question
What is the primary function of the Query AnalyzerÂ’s Normalized Queries feature?
Correct
The Normalized Queries feature in MySQL Enterprise Monitor groups queries with similar structure but different parameters, making it easier to detect inefficient query patterns. It does not rewrite queries or estimate future performance.
Incorrect
The Normalized Queries feature in MySQL Enterprise Monitor groups queries with similar structure but different parameters, making it easier to detect inefficient query patterns. It does not rewrite queries or estimate future performance.
Unattempted
The Normalized Queries feature in MySQL Enterprise Monitor groups queries with similar structure but different parameters, making it easier to detect inefficient query patterns. It does not rewrite queries or estimate future performance.
Question 23 of 60
23. Question
Which statement is true about binary logging in MySQL 8.0?
Correct
The binary log in MySQL 8.0 records all changes to the database, including INSERT, UPDATE, DELETE, and DDL statements. It is primarily used for replication, allowing a slave server to replicate changes from the master, and for point-in-time recovery after restoring from a backup. The binary log does not store SELECT statements because they do not modify data. The format can be STATEMENT, ROW, or MIXED, but ROW-based replication is preferred for consistency.
Incorrect
The binary log in MySQL 8.0 records all changes to the database, including INSERT, UPDATE, DELETE, and DDL statements. It is primarily used for replication, allowing a slave server to replicate changes from the master, and for point-in-time recovery after restoring from a backup. The binary log does not store SELECT statements because they do not modify data. The format can be STATEMENT, ROW, or MIXED, but ROW-based replication is preferred for consistency.
Unattempted
The binary log in MySQL 8.0 records all changes to the database, including INSERT, UPDATE, DELETE, and DDL statements. It is primarily used for replication, allowing a slave server to replicate changes from the master, and for point-in-time recovery after restoring from a backup. The binary log does not store SELECT statements because they do not modify data. The format can be STATEMENT, ROW, or MIXED, but ROW-based replication is preferred for consistency.
Question 24 of 60
24. Question
Which InnoDB table contains persistent index statistics in MySQL 8.0?
Correct
The mysql.index_stats table stores InnoDB index statistics, which persist across restarts. mysql.innodb_index is not a valid system table. performance_schema.index_io does not exist, and information_schema.table_io does not track index usage.
Incorrect
The mysql.index_stats table stores InnoDB index statistics, which persist across restarts. mysql.innodb_index is not a valid system table. performance_schema.index_io does not exist, and information_schema.table_io does not track index usage.
Unattempted
The mysql.index_stats table stores InnoDB index statistics, which persist across restarts. mysql.innodb_index is not a valid system table. performance_schema.index_io does not exist, and information_schema.table_io does not track index usage.
Question 25 of 60
25. Question
Which MySQL backup configuration introduces a security risk if not properly secured?
Correct
Storing unencrypted backups on a public share exposes sensitive data to unauthorized users. Binary logging with sync_binlog=1 enhances durability, mysqldump with –single-transaction ensures consistency, and –lock-all-tables prevents modifications during backups.
Incorrect
Storing unencrypted backups on a public share exposes sensitive data to unauthorized users. Binary logging with sync_binlog=1 enhances durability, mysqldump with –single-transaction ensures consistency, and –lock-all-tables prevents modifications during backups.
Unattempted
Storing unencrypted backups on a public share exposes sensitive data to unauthorized users. Binary logging with sync_binlog=1 enhances durability, mysqldump with –single-transaction ensures consistency, and –lock-all-tables prevents modifications during backups.
Question 26 of 60
26. Question
Which MySQL command retrieves the current server status metrics?
Correct
SHOW GLOBAL STATUS returns real-time server performance metrics, including query counts, buffer usage, and connection details. Other options focus on session-based data or storage engine-specific information.
Incorrect
SHOW GLOBAL STATUS returns real-time server performance metrics, including query counts, buffer usage, and connection details. Other options focus on session-based data or storage engine-specific information.
Unattempted
SHOW GLOBAL STATUS returns real-time server performance metrics, including query counts, buffer usage, and connection details. Other options focus on session-based data or storage engine-specific information.
Question 27 of 60
27. Question
Which of the following SQL modes prevents MySQL from inserting invalid dates?
Correct
STRICT_TRANS_TABLES is an SQL mode in MySQL that prevents invalid values, including incorrect dates, from being inserted into a table. When enabled, MySQL rejects an invalid date such as ‘2023-02-30‘ instead of silently converting it to ‘0000-00-00‘. ALLOW_INVALID_DATES permits such invalid dates to be inserted, making it the opposite of the required behavior. IGNORE_DATE_ERRORS is not an existing SQL mode in MySQL, and NO_ENGINE_SUBSTITUTION relates to storage engine selection rather than data validation.
Incorrect
STRICT_TRANS_TABLES is an SQL mode in MySQL that prevents invalid values, including incorrect dates, from being inserted into a table. When enabled, MySQL rejects an invalid date such as ‘2023-02-30‘ instead of silently converting it to ‘0000-00-00‘. ALLOW_INVALID_DATES permits such invalid dates to be inserted, making it the opposite of the required behavior. IGNORE_DATE_ERRORS is not an existing SQL mode in MySQL, and NO_ENGINE_SUBSTITUTION relates to storage engine selection rather than data validation.
Unattempted
STRICT_TRANS_TABLES is an SQL mode in MySQL that prevents invalid values, including incorrect dates, from being inserted into a table. When enabled, MySQL rejects an invalid date such as ‘2023-02-30‘ instead of silently converting it to ‘0000-00-00‘. ALLOW_INVALID_DATES permits such invalid dates to be inserted, making it the opposite of the required behavior. IGNORE_DATE_ERRORS is not an existing SQL mode in MySQL, and NO_ENGINE_SUBSTITUTION relates to storage engine selection rather than data validation.
Question 28 of 60
28. Question
What MySQL feature can be used to limit input length and thereby reduce SQL injection risks by preventing excessively long inputs?
Correct
The max_allowed_packet setting in MySQL limits the maximum size of a packet (including user input). By restricting the packet size, it helps reduce the risk of SQL injection attacks that attempt to send large, malicious input. The other options are not valid MySQL settings for this purpose.
Incorrect
The max_allowed_packet setting in MySQL limits the maximum size of a packet (including user input). By restricting the packet size, it helps reduce the risk of SQL injection attacks that attempt to send large, malicious input. The other options are not valid MySQL settings for this purpose.
Unattempted
The max_allowed_packet setting in MySQL limits the maximum size of a packet (including user input). By restricting the packet size, it helps reduce the risk of SQL injection attacks that attempt to send large, malicious input. The other options are not valid MySQL settings for this purpose.
Question 29 of 60
29. Question
Which MySQL table stores user privilege metadata inside the Data Dictionary?
Correct
The mysql.global_priv table stores user privileges inside the MySQL Data Dictionary. The other options are invalid or non-existent tables.
Incorrect
The mysql.global_priv table stores user privileges inside the MySQL Data Dictionary. The other options are invalid or non-existent tables.
Unattempted
The mysql.global_priv table stores user privileges inside the MySQL Data Dictionary. The other options are invalid or non-existent tables.
Question 30 of 60
30. Question
Which command forcefully releases a blocking transaction without restarting MySQL?
Correct
The KILL QUERY command terminates the blocking transaction, immediately releasing the lock. FLUSH TABLES affects all transactions, ROLLBACK TO SAVEPOINT applies only to the same session, and innodb_lock_wait_timeout only reduces wait time.
Incorrect
The KILL QUERY command terminates the blocking transaction, immediately releasing the lock. FLUSH TABLES affects all transactions, ROLLBACK TO SAVEPOINT applies only to the same session, and innodb_lock_wait_timeout only reduces wait time.
Unattempted
The KILL QUERY command terminates the blocking transaction, immediately releasing the lock. FLUSH TABLES affects all transactions, ROLLBACK TO SAVEPOINT applies only to the same session, and innodb_lock_wait_timeout only reduces wait time.
Question 31 of 60
31. Question
Which Enterprise Monitor feature helps detect performance bottlenecks in queries?
Correct
The Query Analyzer provides detailed query execution statistics, helping database administrators identify and resolve performance bottlenecks. The Advisory System offers recommendations, while the Replication Monitor and Alerts Engine focus on other areas.
Incorrect
The Query Analyzer provides detailed query execution statistics, helping database administrators identify and resolve performance bottlenecks. The Advisory System offers recommendations, while the Replication Monitor and Alerts Engine focus on other areas.
Unattempted
The Query Analyzer provides detailed query execution statistics, helping database administrators identify and resolve performance bottlenecks. The Advisory System offers recommendations, while the Replication Monitor and Alerts Engine focus on other areas.
Question 32 of 60
32. Question
Which command removes an existing MySQL Enterprise Audit plugin?
Correct
To remove the audit plugin, use UNINSTALL PLUGIN audit_log;. The DROP PLUGIN command does not exist in MySQL 8.0. The DELETE statement does not remove the plugin, and ALTER SYSTEM does not control plugin management.
Incorrect
To remove the audit plugin, use UNINSTALL PLUGIN audit_log;. The DROP PLUGIN command does not exist in MySQL 8.0. The DELETE statement does not remove the plugin, and ALTER SYSTEM does not control plugin management.
Unattempted
To remove the audit plugin, use UNINSTALL PLUGIN audit_log;. The DROP PLUGIN command does not exist in MySQL 8.0. The DELETE statement does not remove the plugin, and ALTER SYSTEM does not control plugin management.
Question 33 of 60
33. Question
Which command helps identify sessions waiting on locked rows in MySQL?
Correct
The performance_schema.data_lock_waits table shows which sessions are waiting on locks, helping administrators identify locking conflicts. The INNODB STATUS command provides general information, but not a detailed lock wait list. The locks_active table does not exist, and PROCESSLIST does not specifically show row-level waits.
Incorrect
The performance_schema.data_lock_waits table shows which sessions are waiting on locks, helping administrators identify locking conflicts. The INNODB STATUS command provides general information, but not a detailed lock wait list. The locks_active table does not exist, and PROCESSLIST does not specifically show row-level waits.
Unattempted
The performance_schema.data_lock_waits table shows which sessions are waiting on locks, helping administrators identify locking conflicts. The INNODB STATUS command provides general information, but not a detailed lock wait list. The locks_active table does not exist, and PROCESSLIST does not specifically show row-level waits.
Question 34 of 60
34. Question
Which method prevents transaction ID conflicts in GTID-based multisource replication?
Correct
In GTID-based multisource replication, each primary server must have a unique GTID auto-increment interval to prevent transaction ID conflicts. Row-based replication does not eliminate conflicts, MySQL does not auto-reconcile duplicate GTIDs, and log bin formats must be consistent across channels.
Incorrect
In GTID-based multisource replication, each primary server must have a unique GTID auto-increment interval to prevent transaction ID conflicts. Row-based replication does not eliminate conflicts, MySQL does not auto-reconcile duplicate GTIDs, and log bin formats must be consistent across channels.
Unattempted
In GTID-based multisource replication, each primary server must have a unique GTID auto-increment interval to prevent transaction ID conflicts. Row-based replication does not eliminate conflicts, MySQL does not auto-reconcile duplicate GTIDs, and log bin formats must be consistent across channels.
Question 35 of 60
35. Question
What happens if the I/O thread in MySQL replication stops unexpectedly?
Correct
If the I/O thread stops, the replica cannot fetch new events from the primary server, but any existing relay log entries continue to be applied by the SQL thread. MySQL does not automatically switch sources, restart replication, or reset binary logs in this case.
Incorrect
If the I/O thread stops, the replica cannot fetch new events from the primary server, but any existing relay log entries continue to be applied by the SQL thread. MySQL does not automatically switch sources, restart replication, or reset binary logs in this case.
Unattempted
If the I/O thread stops, the replica cannot fetch new events from the primary server, but any existing relay log entries continue to be applied by the SQL thread. MySQL does not automatically switch sources, restart replication, or reset binary logs in this case.
Question 36 of 60
36. Question
Which MySQL privilege allows a user to change privileges for existing roles?
Correct
The ROLE_ADMIN privilege allows users to modify and assign roles in MySQL 8.0. PRIVILEGE_ADMIN and ROLE_MANAGE are not valid MySQL privileges, and GRANT OPTION applies only to individual privileges, not roles.
Incorrect
The ROLE_ADMIN privilege allows users to modify and assign roles in MySQL 8.0. PRIVILEGE_ADMIN and ROLE_MANAGE are not valid MySQL privileges, and GRANT OPTION applies only to individual privileges, not roles.
Unattempted
The ROLE_ADMIN privilege allows users to modify and assign roles in MySQL 8.0. PRIVILEGE_ADMIN and ROLE_MANAGE are not valid MySQL privileges, and GRANT OPTION applies only to individual privileges, not roles.
Question 37 of 60
37. Question
Which statement correctly creates a role with SELECT and INSERT privileges on a database?
Correct
Roles in MySQL 8.0 are created separately using CREATE ROLE, and privileges are assigned afterward using GRANT ON database. TO role_name*. Other options contain incorrect syntax, as GRANT statements must follow the CREATE ROLE statement.
Incorrect
Roles in MySQL 8.0 are created separately using CREATE ROLE, and privileges are assigned afterward using GRANT ON database. TO role_name*. Other options contain incorrect syntax, as GRANT statements must follow the CREATE ROLE statement.
Unattempted
Roles in MySQL 8.0 are created separately using CREATE ROLE, and privileges are assigned afterward using GRANT ON database. TO role_name*. Other options contain incorrect syntax, as GRANT statements must follow the CREATE ROLE statement.
Question 38 of 60
38. Question
What MySQL feature can be used to validate user input before it is processed by a query to reduce SQL injection risks?
Correct
Input sanitization in application code ensures that user inputs are properly validated before being passed to SQL queries, reducing the risk of SQL injection. The other options donÂ’t directly handle input validation.
Incorrect
Input sanitization in application code ensures that user inputs are properly validated before being passed to SQL queries, reducing the risk of SQL injection. The other options donÂ’t directly handle input validation.
Unattempted
Input sanitization in application code ensures that user inputs are properly validated before being passed to SQL queries, reducing the risk of SQL injection. The other options donÂ’t directly handle input validation.
Question 39 of 60
39. Question
Which command stops the MySQL server gracefully?
Correct
The mysqladmin –shutdown-server command sends a shutdown signal to the MySQL server, stopping it gracefully. The other options do not correspond to valid MySQL shutdown commands.
Incorrect
The mysqladmin –shutdown-server command sends a shutdown signal to the MySQL server, stopping it gracefully. The other options do not correspond to valid MySQL shutdown commands.
Unattempted
The mysqladmin –shutdown-server command sends a shutdown signal to the MySQL server, stopping it gracefully. The other options do not correspond to valid MySQL shutdown commands.
Question 40 of 60
40. Question
Which MySQL command allows checking the socket path used by a running instance?
Correct
The SHOW VARIABLES LIKE ‘socket‘ command displays the Unix socket file path used by the running MySQL instance. The SHOW GLOBAL STATUS and SHOW SESSION STATUS commands provide runtime statistics but do not reveal configuration details. SHOW PROCESSLIST displays active connections but does not include socket information.
Incorrect
The SHOW VARIABLES LIKE ‘socket‘ command displays the Unix socket file path used by the running MySQL instance. The SHOW GLOBAL STATUS and SHOW SESSION STATUS commands provide runtime statistics but do not reveal configuration details. SHOW PROCESSLIST displays active connections but does not include socket information.
Unattempted
The SHOW VARIABLES LIKE ‘socket‘ command displays the Unix socket file path used by the running MySQL instance. The SHOW GLOBAL STATUS and SHOW SESSION STATUS commands provide runtime statistics but do not reveal configuration details. SHOW PROCESSLIST displays active connections but does not include socket information.
Question 41 of 60
41. Question
Which command removes a node from an InnoDB Cluster?
Correct
The dba.removeInstance() command safely removes a node from an InnoDB Cluster. Resetting the master, stopping replication, or changing replication sources do not properly remove a node.
Incorrect
The dba.removeInstance() command safely removes a node from an InnoDB Cluster. Resetting the master, stopping replication, or changing replication sources do not properly remove a node.
Unattempted
The dba.removeInstance() command safely removes a node from an InnoDB Cluster. Resetting the master, stopping replication, or changing replication sources do not properly remove a node.
Question 42 of 60
42. Question
Which method ensures point-in-time recovery in a MySQL backup strategy?
Correct
Point-in-Time Recovery (PITR) is achieved by restoring the last full backup and applying binary logs to replay changes. Reloading schema dumps recreates table structures but lacks transactional changes. Rebuilding index tables improves query performance, and Flushing redo buffers manages buffered writes.
Incorrect
Point-in-Time Recovery (PITR) is achieved by restoring the last full backup and applying binary logs to replay changes. Reloading schema dumps recreates table structures but lacks transactional changes. Rebuilding index tables improves query performance, and Flushing redo buffers manages buffered writes.
Unattempted
Point-in-Time Recovery (PITR) is achieved by restoring the last full backup and applying binary logs to replay changes. Reloading schema dumps recreates table structures but lacks transactional changes. Rebuilding index tables improves query performance, and Flushing redo buffers manages buffered writes.
Question 43 of 60
43. Question
Which command is used to stop MySQL immediately without waiting for active connections to close?
Correct
The mysqladmin –kill-all-users command forces an immediate shutdown without waiting for active connections. The other options contain incorrect syntax or refer to non-existent commands.
Incorrect
The mysqladmin –kill-all-users command forces an immediate shutdown without waiting for active connections. The other options contain incorrect syntax or refer to non-existent commands.
Unattempted
The mysqladmin –kill-all-users command forces an immediate shutdown without waiting for active connections. The other options contain incorrect syntax or refer to non-existent commands.
Question 44 of 60
44. Question
Which InnoDB feature dynamically adjusts the frequency of buffer pool flushing based on workload?
Correct
InnoDB Adaptive Flushing dynamically adjusts buffer pool flushing frequency based on workload to prevent I/O bottlenecks. The other options do not control buffer pool flushing behavior.
Incorrect
InnoDB Adaptive Flushing dynamically adjusts buffer pool flushing frequency based on workload to prevent I/O bottlenecks. The other options do not control buffer pool flushing behavior.
Unattempted
InnoDB Adaptive Flushing dynamically adjusts buffer pool flushing frequency based on workload to prevent I/O bottlenecks. The other options do not control buffer pool flushing behavior.
Question 45 of 60
45. Question
Which MySQL security feature protects against brute-force attacks on user accounts?
Correct
Locking user accounts after multiple failed attempts prevents brute-force attacks by limiting unauthorized login attempts. The other options improve security but do not directly prevent brute-force attacks.
Incorrect
Locking user accounts after multiple failed attempts prevents brute-force attacks by limiting unauthorized login attempts. The other options improve security but do not directly prevent brute-force attacks.
Unattempted
Locking user accounts after multiple failed attempts prevents brute-force attacks by limiting unauthorized login attempts. The other options improve security but do not directly prevent brute-force attacks.
Question 46 of 60
46. Question
What is the role of MySQLÂ’s query optimizer in handling complex queries?
Correct
The query optimizer analyzes complex queries and creates an optimized execution plan by considering factors like indexes, statistics, and joins. It does not necessarily break down queries into subqueries or prioritize parallel execution.
Incorrect
The query optimizer analyzes complex queries and creates an optimized execution plan by considering factors like indexes, statistics, and joins. It does not necessarily break down queries into subqueries or prioritize parallel execution.
Unattempted
The query optimizer analyzes complex queries and creates an optimized execution plan by considering factors like indexes, statistics, and joins. It does not necessarily break down queries into subqueries or prioritize parallel execution.
Question 47 of 60
47. Question
Which password policy setting prevents a user from reusing an old password immediately?
Correct
The password_history variable stores previously used passwords and prevents users from reusing old passwords within the defined number of changes. The other options are invalid MySQL settings.
Incorrect
The password_history variable stores previously used passwords and prevents users from reusing old passwords within the defined number of changes. The other options are invalid MySQL settings.
Unattempted
The password_history variable stores previously used passwords and prevents users from reusing old passwords within the defined number of changes. The other options are invalid MySQL settings.
Question 48 of 60
48. Question
Which system variable determines the size of the InnoDB buffer pool in MySQL 8.0?
Correct
The innodb_buffer_pool_size variable controls the amount of memory allocated for the InnoDB buffer pool, directly impacting database performance. The other options are not valid system variables related to buffer pool sizing.
Incorrect
The innodb_buffer_pool_size variable controls the amount of memory allocated for the InnoDB buffer pool, directly impacting database performance. The other options are not valid system variables related to buffer pool sizing.
Unattempted
The innodb_buffer_pool_size variable controls the amount of memory allocated for the InnoDB buffer pool, directly impacting database performance. The other options are not valid system variables related to buffer pool sizing.
Question 49 of 60
49. Question
Which process name must be stopped to terminate MySQL manually on Linux?
Correct
The MySQL process runs as mysql_database_daemon in the background. To terminate it, use kill or systemctl stop mysqld. The other options refer to incorrect process names.
Incorrect
The MySQL process runs as mysql_database_daemon in the background. To terminate it, use kill or systemctl stop mysqld. The other options refer to incorrect process names.
Unattempted
The MySQL process runs as mysql_database_daemon in the background. To terminate it, use kill or systemctl stop mysqld. The other options refer to incorrect process names.
Question 50 of 60
50. Question
Which restore method must be used when applying an incremental backup in MySQL Enterprise Backup?
Correct
The apply-log phase process is required to merge incremental backups with the full backup before restoration. Direct file restoration works for full backups but not for incremental ones. Binary log replay step applies transactions, not backups. Logical data import does not handle incremental recovery.
Incorrect
The apply-log phase process is required to merge incremental backups with the full backup before restoration. Direct file restoration works for full backups but not for incremental ones. Binary log replay step applies transactions, not backups. Logical data import does not handle incremental recovery.
Unattempted
The apply-log phase process is required to merge incremental backups with the full backup before restoration. Direct file restoration works for full backups but not for incremental ones. Binary log replay step applies transactions, not backups. Logical data import does not handle incremental recovery.
Question 51 of 60
51. Question
Which MySQL tool is specifically designed for hot backups without locking tables?
Correct
MySQL Enterprise Backup allows hot backups, meaning no downtime or table locking is needed. mysqldump Logical Dump can cause locks during the export. mysqlpump Export File is another logical backup method but does not support hot backups. Binary Log Backup Set stores transaction logs, not full backups.
Incorrect
MySQL Enterprise Backup allows hot backups, meaning no downtime or table locking is needed. mysqldump Logical Dump can cause locks during the export. mysqlpump Export File is another logical backup method but does not support hot backups. Binary Log Backup Set stores transaction logs, not full backups.
Unattempted
MySQL Enterprise Backup allows hot backups, meaning no downtime or table locking is needed. mysqldump Logical Dump can cause locks during the export. mysqlpump Export File is another logical backup method but does not support hot backups. Binary Log Backup Set stores transaction logs, not full backups.
Question 52 of 60
52. Question
Which statement best describes the purpose of the InnoDB buffer pool in MySQL 8.0?
Correct
The InnoDB buffer pool is a memory area that caches data pages and index pages to optimize performance. By keeping frequently accessed data in memory, MySQL minimizes disk I/O, significantly improving query response times. The buffer pool is crucial for read-heavy workloads and helps ensure efficient transaction processing. While InnoDB uses redo logs for crash recovery, those are not stored in the buffer pool. Similarly, temporary tables and session data are managed differently in MySQL, making them incorrect choices.
Incorrect
The InnoDB buffer pool is a memory area that caches data pages and index pages to optimize performance. By keeping frequently accessed data in memory, MySQL minimizes disk I/O, significantly improving query response times. The buffer pool is crucial for read-heavy workloads and helps ensure efficient transaction processing. While InnoDB uses redo logs for crash recovery, those are not stored in the buffer pool. Similarly, temporary tables and session data are managed differently in MySQL, making them incorrect choices.
Unattempted
The InnoDB buffer pool is a memory area that caches data pages and index pages to optimize performance. By keeping frequently accessed data in memory, MySQL minimizes disk I/O, significantly improving query response times. The buffer pool is crucial for read-heavy workloads and helps ensure efficient transaction processing. While InnoDB uses redo logs for crash recovery, those are not stored in the buffer pool. Similarly, temporary tables and session data are managed differently in MySQL, making them incorrect choices.
Question 53 of 60
53. Question
Which mysqlpump option ensures that triggers and stored procedures are included in the backup?
Correct
The –routines-and-triggers option in mysqlpump ensures that stored routines and triggers are included. –stored-functions-all is incorrect. –backup-definitions-db does not exist, and –enable-log-queries relates to query logging, not backups.
Incorrect
The –routines-and-triggers option in mysqlpump ensures that stored routines and triggers are included. –stored-functions-all is incorrect. –backup-definitions-db does not exist, and –enable-log-queries relates to query logging, not backups.
Unattempted
The –routines-and-triggers option in mysqlpump ensures that stored routines and triggers are included. –stored-functions-all is incorrect. –backup-definitions-db does not exist, and –enable-log-queries relates to query logging, not backups.
Question 54 of 60
54. Question
Which MySQL 8.0 system variable is used to enable or disable the binary log?
Correct
The log_bin variable enables or disables binary logging, which is required for replication and point-in-time recovery. binlog_enabled does not exist, general_log enables query logging, and slow_query_log records queries exceeding a specified execution time threshold.
Incorrect
The log_bin variable enables or disables binary logging, which is required for replication and point-in-time recovery. binlog_enabled does not exist, general_log enables query logging, and slow_query_log records queries exceeding a specified execution time threshold.
Unattempted
The log_bin variable enables or disables binary logging, which is required for replication and point-in-time recovery. binlog_enabled does not exist, general_log enables query logging, and slow_query_log records queries exceeding a specified execution time threshold.
Question 55 of 60
55. Question
Which MySQL variable restricts which files can be loaded by MySQL, enhancing host environment security?
Correct
The secure_file_priv variable restricts the directories where MySQL is permitted to load files, enhancing the security of the host environment by preventing unauthorized file access. The other options are invalid or non-existent in MySQL 8.0.
Incorrect
The secure_file_priv variable restricts the directories where MySQL is permitted to load files, enhancing the security of the host environment by preventing unauthorized file access. The other options are invalid or non-existent in MySQL 8.0.
Unattempted
The secure_file_priv variable restricts the directories where MySQL is permitted to load files, enhancing the security of the host environment by preventing unauthorized file access. The other options are invalid or non-existent in MySQL 8.0.
Question 56 of 60
56. Question
What happens if a worker thread fails to execute a transaction in parallel replication?
Correct
If a worker thread fails to execute a transaction, MySQL attempts a retry while allowing other worker threads to continue processing. The entire replication process does not stop, the primary does not resend transactions, and MySQL does not automatically skip transactions unless configured to do so.
Incorrect
If a worker thread fails to execute a transaction, MySQL attempts a retry while allowing other worker threads to continue processing. The entire replication process does not stop, the primary does not resend transactions, and MySQL does not automatically skip transactions unless configured to do so.
Unattempted
If a worker thread fails to execute a transaction, MySQL attempts a retry while allowing other worker threads to continue processing. The entire replication process does not stop, the primary does not resend transactions, and MySQL does not automatically skip transactions unless configured to do so.
Question 57 of 60
57. Question
Which network setting should be configured to limit remote MySQL connections?
Correct
The bind_address setting controls which network interfaces MySQL listens on. Setting it to 127.0.0.1 ensures that MySQL only allows local connections, improving security. The other options are not valid MySQL settings.
Incorrect
The bind_address setting controls which network interfaces MySQL listens on. Setting it to 127.0.0.1 ensures that MySQL only allows local connections, improving security. The other options are not valid MySQL settings.
Unattempted
The bind_address setting controls which network interfaces MySQL listens on. Setting it to 127.0.0.1 ensures that MySQL only allows local connections, improving security. The other options are not valid MySQL settings.
Question 58 of 60
58. Question
Which of the following is a security risk when using the MySQL root account?
Correct
Enabling –skip-grant-tables allows anyone to connect to MySQL without authentication, which is a critical security risk. While SUPER privileges should be restricted, the biggest risk is disabling authentication entirely. Changing the authentication plugin is not inherently insecure, and using the root account to create users does not introduce direct security risks.
Incorrect
Enabling –skip-grant-tables allows anyone to connect to MySQL without authentication, which is a critical security risk. While SUPER privileges should be restricted, the biggest risk is disabling authentication entirely. Changing the authentication plugin is not inherently insecure, and using the root account to create users does not introduce direct security risks.
Unattempted
Enabling –skip-grant-tables allows anyone to connect to MySQL without authentication, which is a critical security risk. While SUPER privileges should be restricted, the biggest risk is disabling authentication entirely. Changing the authentication plugin is not inherently insecure, and using the root account to create users does not introduce direct security risks.
Question 59 of 60
59. Question
Which file type is used for storing MySQL‘s redo logs?
Correct
MySQL stores redo logs in redo-logs.iblog files to support crash recovery and durability. The other options do not represent valid redo log file types.
Incorrect
MySQL stores redo logs in redo-logs.iblog files to support crash recovery and durability. The other options do not represent valid redo log file types.
Unattempted
MySQL stores redo logs in redo-logs.iblog files to support crash recovery and durability. The other options do not represent valid redo log file types.
Question 60 of 60
60. Question
Which command verifies the integrity of a MySQL Enterprise Backup before restoration?
Correct
The mysqlbackup –validate command checks backup integrity before restoration. mysqldump –check-data does not exist. mysqlpump –verify-backup is not a valid option, and mysqlbinlog –test-logs is used for binary logs, not backups.
Incorrect
The mysqlbackup –validate command checks backup integrity before restoration. mysqldump –check-data does not exist. mysqlpump –verify-backup is not a valid option, and mysqlbinlog –test-logs is used for binary logs, not backups.
Unattempted
The mysqlbackup –validate command checks backup integrity before restoration. mysqldump –check-data does not exist. mysqlpump –verify-backup is not a valid option, and mysqlbinlog –test-logs is used for binary logs, not backups.
X
Use Page numbers below to navigate to other practice tests