1Z0-908 OCP MySQL 8.0 Database Administrator Practice Questions

The USAGE privilege is the minimum required privilege to allow a user to connect to the database. It grants no other permissions and is the correct starting point.

This grant specifically allows the user to execute all stored procedures within the app_db database, which directly matches the requirement without granting excessive permissions.

The MySQL 8.0 mysqlcheck utility (or the dedicated mysqlsh upgrade checker) should be run against the 5.7 server before the upgrade. It checks for incompatibilities such as removed functions, reserved keywords, or outdated data types that will cause the upgrade to fail. This proactive check is the most important preparatory step.

For multisource replication, each source requires a unique channel name. The FOR CHANNEL clause is used with CHANGE REPLICATION SOURCE TO (or the older CHANGE MASTER TO) to specify the connection parameters for a specific, named channel, allowing the replica to maintain separate replication streams.

The deadlock analysis requires understanding the cyclic dependency. The log shows which transaction holds which lock (e.g., TRANSACTION 1 holds lock X) and which lock it is waiting for (e.g., waits for lock Y), while the other transaction holds Y and waits for X. This information, combined with the SQL statements, allows the DBA to identify the conflicting access pattern in the application code.

The performance_schema.table_io_waits_summary_by_index_usage table aggregates I/O wait events, broken down by table and index. An index that does not appear in this table or has all of its count columns as zero (e.g., COUNT_FETCH, COUNT_INSERT, etc.) has not been used for I/O operations, indicating it is a strong candidate for being dropped. The sys schema provides a simplified view on this table called schema_unused_indexes.

This setting ensures that any newly created schemas and tables within them inherit the encryption attribute, enforcing the policy for new objects.

A keyring plugin is a prerequisite for MySQL data-at-rest encryption. It manages the master encryption key, without which encryption cannot be enabled.

The mandate requires all data at rest to be encrypted. This includes binary logs (replication data) and redo logs (transactional recovery data), which are encrypted by these specific settings.

The Group Replication plugin, which underpins InnoDB Cluster, is responsible for managing group membership, detecting failures, and running the election process to automatically promote a new primary node when the current one fails. This is a core feature of its distributed consensus mechanism.

The FORCE INDEX hint is the most direct way to instruct the MySQL optimizer to use a specific index if it is at all possible. It is stronger than USE INDEX, as it tells the optimizer to consider a full table scan as very expensive, thus heavily favoring the specified index.

When autocommit is enabled (the default), each INSERT statement in the dump file is treated as a separate transaction, causing a log flush to disk for every single row. This creates massive I/O overhead. Disabling autocommit and wrapping the data load in a single transaction dramatically improves performance.

In MySQL 8.0, granted roles are not automatically activated upon login unless they are set as a default role for that user (SET DEFAULT ROLE ...). If it's not a default role, the user must manually activate it in their session using SET ROLE 'dev_write'; to gain its privileges.