Overview of Key Administration Scenarios

This document explores critical scenarios encountered during the MySQL 8.0 Oracle Certified Professional certification process. It focuses on replication diagnostics, binary log management, instance lifecycle control, and security hardening strategies.

Scenario 1: Diagnosing Replication Latency

Problem: An administrator observes a steady increase in Seconds_Behind_Source despite both I/O and SQL threads reporting as running. Which factors contribute to this sustained lag?

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
       Replica_IO_Running: Yes
      Replica_SQL_Running: Yes
         Seconds_Behind_Source: 1612

Analysis:

• Correct Factor 1: The Source server is generating events faster than the Replica can apply them serially. Enabling parallel execution on the Replica (slave_parallel_workers) may mitigate this.
• Correct Factor 2: Network congestion or high load on the Source prevents timely transmission of binlog data. The Replica's I/O thread waits for data, causing the queue to grow even though the SQL thread is idle.
• Incorrect Assumptions: The metric reflects overall transaction latency, not just network I/O. Missing primary keys on large tables affect efficiency but typically do not cause linear delay growth. Lock contention usually results in fluctuating delays rather than steady accumulation.

Scenario 2: Binary Log Functionality

Problem: Identify accurate characteristics regarding binary logs used in asynchronous replication environments.

• True: Binary logs are pulled by the Replica from the Source. The Source does not initiate the connection.
• True: They record events representing data modifications (DML, DDL) rather than every single query executed (read-only statements like SELECT are excluded).
• False: They do not contain only administrative commands. They capture the full spectrum of changes ensuring data consistency across nodes.

Scenario 3: Graceful Instance Termination

Problem: On an Oracle Linux 7 host with a running MySQL daemon, identify three valid shutdown mechanisms.

• Valid Method 1: systemctl stop mysqld (Utilizes the systemd service manager).
• Valid Method 2: mysqladmin shutdown (Official utility sending a graceful signal).
• Valid Method 3: Executing SHUTDOWN within an active MySQL client session.
• Invalid Methods: Using kill on wrapper processes (mysqld_safe) can lead to corruption. Parameters like --shutdown in mysql or mysqld_safe are not standard invocation flags.

Scenario 4: InnoDB Cluster Disaster Recovery

Problem: Evaluate the capabilities of dba.rebootClusterFromCompleteOutage() in MySQL Shell.

• Capability 1: It performs a rolling restart of the cluster instances to restore quorum.
• Capability 2: The command does not require every node to be reachable beforehand; it attempts to recover based on the available metadata.
• Clarification: This function is designed for total failure scenarios. It does not simply start services but reconfigures the metadata state to rebuild the cluster topology safely.

Scenario 5: File Permission Security

Problem: Review file system permissions to enhance MySQL instance security. Which adjustments are recommended?

/path/to/data/
-rw-r--r--  mysql mysql server-cert.pem
-rw-rw----  mysql mysql private-key.pem
drwxrwxr-x  mysql mysql accounting_dir/

• Remediation 1: Restrict accounting_dir. World read/execute permissions (r-x for others) allow unauthorized traversal. Remove these privileges.
• Remediation 2: Secure private-key.pem. Group write access is risky for private keys. Ensure ownership is restricted so only the MySQL user can read/write.
• Note: Public certificates and public keys generally require world readability to facilitate client connections and authentication.

Scenario 6: General Tablespace Capabilities

Problem: Determine true statements concerning general tablespaces in MySQL 8.0.

• True: Existing tables can be moved into a general tablespace using ALTER TABLE ... TABLESPACE.
• True: New tables can be explicitly assigned to a specific general tablespace upon creation.
• False: Dropping a table from a general tablespace does not automatically return space to the OS; the .ibd file remains until manually dropped or resized.
• False: General tablespaces do not support temporary tables, which reside in separate temporary storage areas.

Scenario 7: Cloning for Node Provisioning

Problem: Enalyze the prerequisites and behaviors when using cluster.addInstance(..., {recoveryMethod: 'clone'}).

• Requirement: The target account must possess the BACKUP_ADMIN privilege to perform cloning operations.
• Behavior: The target instance must already exist (installled but empty). The clone process provisions it with data from a healthy cluster member.
• Scope: InnoDB tablespaces located outside the default datadir (e.g., custom created ones) are included in the clone operation, provided they are not temporary.
• Clarification: Redo log rotation does not halt the clone process as it works at the physical file level, not logical log application.

Scenario 8: MySQL System Database Contents

Problem: Which information sets are stored within the mysql system schema?

• Present: Time zone definitions (time_zone* tables).
• Present: Installed plugins information (plugin table).
• Present: Internal help documentation topics (help_topic table).
• Missing: Table structures reside in information_schema. Performance metrics belong in performance_schema. Rollback segment details are internal InnoDB engine metadata.

Scenario 9: Binary Log Rotation Triggers

Problem: Identify actions that force the MySQL binary log to rotate.

• Trigger 1: Execution of FLUSH LOGS command forces an immediate switch to a new log file.
• Trigger 2: Exceeding the max_binlog_size threshold. Note that rotation occurs at the end of the current transaction, potentially making the active file slightly larger then the limit.
• Non-Triggers: Setting sql_log_bin=0 disables logging for the session but does not rotate existing files. Changing sync_binlog affects durability, not file rotation.

Scenario 10: Replication Topology Rules

Problem: Validate statements regarding MySQL replication configurations.

• Rule 1: Every participant in the replication topology requires a unique server_id to avoid conflicts and circular updates.
• Rule 2: The Source must have binary logging enabled to send events to Replicas. Rule 3: Standard replication relies exclusively on TCP/IP connections; Unix domain sockets are not utilized for inter-server communication in this context. - Correction: Multiple Sources per replica are not natively supported in standard topologies (circular/chain logic applies differently). Replication users need REPLICATION SLAVE privileges, not necessarily table-level SELECT rights.