Semisynchronous replication only requires that 1 replica acknowledges that the change has been recorded in its relay log, and it doesn‘t require certification of the change.

In MySQL 8.0, DDL operations are atomic. This means that if a multi-table DDL statement fails on any object, the entire statement is rolled back, leaving the database unchanged.

In this case:

DROP TABLE t1, t2; fails because t2 does not exist.

Since both tables are part of the same DROP statement, the operation is rolled back completely.

Therefore, t1 is not dropped, and the schema remains unchanged (no tables dropped).

In contrast, under MySQL 5.7, DDL was not atomic — t1 would have been dropped while t2 would only produce a warning/error.

Only the content of the Old sublist is replaced by pages read. This avoids that the pages in the New sublist and most frequently used are removed from memory.

As of MySQL 5.6.4 TIME, DATETIME and TIMESTAMP require additional storage for the fractional part

Correct:

A. Data dictionary tables

In MySQL 8.0, the data dictionary tables are stored within the mysql schema. These tables are fundamental, storing metadata about all objects in the MySQL instance, such as databases, tables, columns, indexes, and stored procedures. Having this core metadata within the mysql schema is essential for the operation of the server. This includes internal tables that were migrated from the older performance_schema or hidden system tables.

D. Grant system tables

The grant system tables (often referred to as the privilege or user accounts tables) are also stored within the mysql schema. These tables store all information related to user accounts, roles, and their associated privileges (the GRANT statements). Examples include tables like user, db, tables_priv, and columns_priv. Controlling access and security is a core administrative function, and the data for this is necessarily housed in the administrative mysql schema.

Incorrect:

B. Your application data tables

Application data tables (the tables created by users to store their actual business data) are typically stored in user-defined schemas (databases) like inventory, cms, or mydb. They are not stored in the special-purpose mysql schema, which is reserved exclusively for the server‘s internal administrative and metadata functions.

C. Your application help data tables

Tables containing application-specific help data, while sometimes separate, are still considered part of the user‘s application content and would be placed in the user‘s application schema (database), just like the main application data tables (Option B). They are not part of the critical, internal administrative tables that constitute the mysql schema.

Multi-thread replication settings affect to each defined source.

In MySQL 8.0 InnoDB stores metadata in the MySQL data dictionary and not in the InnoDB system tablespace

In MySQL 8.0 the Grant System tables use InnoDB, whereas in previous versions they used MyISAM

MySQL 8.0.16 deprecates mysql_upgrade tool and introduces the –upgrade option for mysqld
From this version on, mysqld will perform the upgrade operations.

Beginning with MySQL 8.0.16 the storage engine used for on-disk internal temporary tables is always InnoDB

Single/Multi references the number of servers that accept writes in Group Replication setups.

Correct:

D. /home/mysqlbackup/backups/my.mbi

When using the backup-to-image option with MySQL Enterprise Backup (MEB) 8.0, the command is instructed to create a single, compressed, and encrypted file known as a backup image.

The destination for this final backup file is specified by the –backup-image option in the command line.

In this specific command, –backup-image=/home/mysqlbackup/backups/my.mbi, the backup files will be consolidated and stored as the single file named my.mbi in the specified directory.

Incorrect:

A. mysqlbackup –user=mysqlbackup –password –host=127.0.0.1 –backup-image=/home/mysqlbackup/backups/my.mbi –backup-dir=/home/mysqlbackup/backup-tmp backup-to-image

This option is the entire command used to create the backup. It is not the location where the backup files are stored. The location is the result of running this command.

B. /var/lib/mysql

/var/lib/mysql is the standard, default directory where the live MySQL data files (data dictionary, table spaces, logs) are stored by the server. It is the source of the backup, not the destination for the backup files created by MEB.

C. /home/mysqlbackup/backup-tmp

This directory is specified by the –backup-dir option. When creating a full backup image using backup-to-image, MEB uses this location as a temporary directory for internal files during the backup process (like metadata files or temporary storage for the data dictionary). The final, single backup file (the image) is created from this temporary space and placed in the location specified by –backup-image. Thus, it is a temporary working location, not the final storage location for the backup image.

Correct:

C. 16KB

In MySQL 8.0, the default value for the innodb_page_size system variable is 16KB. The InnoDB storage engine uses this page size as its fundamental unit for all I/O operations, including reading and writing data and indexes to and from disk. This setting is typically configured when the server instance is initialized and cannot be changed afterward without rebuilding the instance.

Incorrect:

A. 8KB

8KB is a valid non-default value that can be set for the innodb_page_size. Choosing a smaller page size can be beneficial for specific workloads, such as those with highly random I/O access patterns and smaller row sizes, but it is not the standard default for MySQL 8.0.

B. 64KB

64KB is a valid non-default value for the innodb_page_size. A larger page size is typically chosen for workloads with large rows (e.g., those containing many text/blob columns) or sequential access patterns, as it reduces the I/O overhead per record, but it is not the default.

D. 4KB

4KB is a valid non-default value for the innodb_page_size. This is the smallest page size allowed by InnoDB. While it can be useful in specific scenarios, particularly where storage blocks are smaller, it is generally considered an atypical setting and is not the default.

The @@gtid_purged value on the primary shows that it has already deleted binary logs containing GTIDs that the replica has not executed yet. Specifically:

Replica has executed GTID up to: …:256

Primary has purged GTIDs up to: …:1-325

Since the replica still needs GTIDs 257–325, but the primary has already purged those transactions, the missing events are no longer available for automatic retrieval via replication.

Because of this, replication cannot resume automatically. You must manually intervene, either by:

Restoring the purged binary logs to the primary, or

Rebuilding / resyncing the replica from a fresh backup

Correct:

A. Metadata files

Prior to MySQL 8.0, some essential parts of the data dictionary were stored outside of the database itself, in metadata files scattered across the file system. For instance, table definitions were often stored in .frm files within the database directory.

B. Storage engine specific data dictionaries

In older versions, the data dictionary information was fragmented. While the server maintained a general data dictionary (like the .frm files), each storage engine (most notably InnoDB) also maintained its own, separate data dictionary. This duplication and lack of a single, authoritative source was a major architectural issue, as it led to inconsistencies between the server‘s view and the storage engine‘s view of the schema.

C. Non-Transactional tables

The main tables that held information on user privileges, server configuration, and other essential system data were stored in the mysql schema using the MyISAM storage engine. MyISAM tables are non-transactional, meaning that operations on these system tables could not be guaranteed to be atomic or rolled back, which posed significant consistency and recovery challenges.

Incorrect:

D. Transactional tables

The system tables that held the data dictionary information (specifically those in the mysql schema) were primarily MyISAM, which is non-transactional. The move to MySQL 8.0 was a major architectural shift specifically to consolidate the data dictionary into a single, reliable set of transactional (InnoDB) tables, which greatly improved crash recovery and consistency. Therefore, prior to 8.0, the core data dictionary was not stored in transactional tables.

Roles can be not enabled by default, you will need to usie the SHOW GRANTS FOR… USING syntax to show privileges for non-default roles.

MySQL Enterprise Audit is a server plugin; it runs as part of the MySQL process.

InnoDB buffer pool implements a variation of Least Recently Used (LRU) to evict pages

PROCESS allow a user to monitor other sessions in execution

Prior to MySQL 8.0 partial in-place updates for JSON objects was not possible, now this is the most efficient method to modify a single attribute.

3/8 of the buffer pool is devoted to the old sublist

A JSON object can use approximately the same space as a LONGBLOB or LONGTEXT column, but it will include some metadata and internal structures overhead.
MySQL 8.0 imposes an additional condition to the size as it cannot exceed max_allowed_packet

By default admin_address is not defined, this keep disabled the new administrative interface for MySQL 8.0

AUTO_INCREMENT values are stored in the mysql.innodb_dynamic_metadata table using InnoDB. They are not stored anymore in the buffers of the InnoDB system tablespace

All those metadata files are not created in MySQL 8.0

One operation can be recorded using multiple GTIDs.
In this example, the creation of the table is recorded with one GTID and the copy of the data is recorded with other.

MySQL Enterprise Firewall can run in 3 different modes:
Recording: the whitelist is populated with the new queries and those are executed
Detecting: queries not present in the whitelist are executed but logged in the Firewall log
Protecting: queries not present in the whitelist are blocked and logged in the Firewall log

In MySQL 8.0 auto creation of users granting privileges was removed.
Creating a user with “IDENTIFIED BY PASSWORD ‘password‘“ is also removed

MySQL 8.0 InnoDB uses by default 2 UNDO tablespaces in their own datafiles

The source doesn’t perform the commit until at least 1 replica has acknowledged that the event is stored in its relay log.

The EXPLAIN statement provides a detailed query execution plan, helping identify potential bottlenecks like table scans or inefficient joins. It does not just show result sets or syntax issues.

The validate_password_policy variable enforces password complexity rules in MySQL 8.0, ensuring users create stronger passwords. The other options do not exist as valid MySQL settings.

In MySQL replication, the I/O thread runs on the replica server and requests binary log events from the primary server, storing them in the relay log. The SQL thread executes them, while the Coordinator thread is used for parallel replication. The Receiver thread does not exist in MySQL replication.

The MySQL Instances Dashboard provides real-time insights into server workload, CPU usage, connection activity, and query execution rates. Other dashboards focus on specific areas such as query performance, system health, or replication.

The auth_pam plug-in allows MySQL to integrate with LDAP and other external authentication methods via Pluggable Authentication Modules (PAM). auth_ldap is not a valid MySQL authentication plug-in. auth_socket is used for Unix socket authentication, and auth_sasl is not available in MySQL.

In MySQL, if multiple option files are loaded, the last file processed takes priority, overriding settings from earlier files. MySQL does not merge settings from different files.

To run multiple MySQL instances on a single host, each instance must listen on a unique port. The port parameter specifies the TCP/IP port number for MySQL. While datadir is also important to separate data files, it does not handle network communication. basedir defines the MySQL installation directory, and socket is used for local Unix connections but does not affect network-based access.

expire_logs_days controls automatic binary log purging. log_bin_index tracks binary log files but does not remove them. relay_log_recovery is used for replication. innodb_log_group_home_dir is for InnoDB redo logs.

The MySQL Key Buffer Cache improves performance for MyISAM tables by storing index blocks in memory. The other options are not related to MyISAM.

The group_replication_group_seeds parameter ensures cluster nodes can discover each other. Other settings (flush logs, binlog sync, or log_slave_updates) affect performance or durability but are not mandatory for InnoDB Cluster configuration.

The mysql command-line client connects to the MySQL server, allowing interactive SQL execution. The other options do not exist as MySQL client utilities.

Group Replication in multi-primary mode enables multiple servers to act as primary nodes while using a Paxos-based consensus algorithm to resolve conflicts automatically. Traditional statement-based replication and circular replication do not provide built-in conflict resolution. Delayed replication is for disaster recovery, not multi-primary setups.

Majority quorum rules ensure transaction commits require most nodes to agree, preventing split-brain scenarios. Disabling failover, asynchronous replication, or binlog retention does not prevent split-brain issues.

The wait_timeout variable determines how long an idle session remains connected before MySQL automatically disconnects it. Other options do not exist or serve different purposes.

The max_connections variable determines the maximum number of simultaneous client connections allowed to the MySQL server. If all available connections are used, additional connection requests are denied unless a privileged user like SUPER is connecting. The variable does not control session timeouts, query limits, or memory directly, although high connections may increase memory usage.

Binary logs record all changes to the database that modify data, such as INSERT, UPDATE, and DELETE statements. They are primarily used for replication, where changes can be applied to a replica server, and for point-in-time recovery, allowing administrators to restore a database to a specific state. Unlike redo logs, which are used for crash recovery in InnoDB, binary logs help in data replication and auditing. They do not store user privilege information or backups directly.

To enable GTID-based multisource replication, MySQL requires GTID consistency enforcement via enforce_gtid_consistency. This ensures transactions are logged deterministically. The other options relate to functions, startup behavior, and commit order but do not directly enable GTID-based multisource replication.

In asynchronous replication, the primary server does not wait for replicas to acknowledge transactions before committing. This improves performance and availability but does not guarantee immediate consistency or prevent transaction loss.

MySQL allows max_connections connections and 1 additional connection for users with SUPER or CONNECTION_ADMIN privilege.

Hot backup is performed while the database is running. InnoDB tables are backed like this.
Warm backup is performed while the database is running but in read-only mode. MyISAM tables are backed like this.
Cold backup is performed while the database is stopped. MySQL Enterprise Backup 8.0 doesn‘t support this mode.

Binary log dump thread is created in the primary server and will send the binary log information to the replica servers

Query Analyzer retrieves the data from performance_schema This feature is enabled by default and its impact is minimum.

ROLE_ADMIN enables a user to grant/revoke roles to other users

That filter disables unconditionally the logging for ‘user1@localhost‘

Running validate and restoring the backup are methods to confirm that the backup is valid.
Starting a MySQL 8.0 server process directly in a backup directory will not work and will corrupt the backup.

MySQL 8.0 introduces a new default engine for in-memory temporary tables; TempTable. This engine is more efficient for VARCHAR and VARBINARY columns

Index records are stored in leaf pages, in the intermediate or root pages only point information is kept

The data dictionary contains metadata that cannot be read using SELECT and its tables are hidden in release builds.
System tables contain auxiliar data such as time zone or help information and are visible and SELECTABLE.
Metadata files have been removed in 8.0 and the data dictionary is transactional

Always follow the minimum privilege required to solve the scenario.
In this case GRANT ALL on the schema will be enough.

ANALYZE TABLE will calculate key distribution on the indexes but it will not generate histograms by default. We can use the ANALYZE TABLE… UPDATE HISTOGRAM ON… syntax to create them.