1Z0-908: MySQL 8.0 Database AdministratorDemo

MySQL Enterprise Backup performs physical backups. It can take hot backups for InnoDB tables and warm backups for tables using other storage engines, and it supports incremental backups that capture changes since a prior backup.

Dropping the metadata schema removes MySQL Shell’s InnoDB Cluster metadata without dissolving the underlying Group Replication deployment, so the deployment must be adopted again for MySQL Shell management. The metadata schema removal is applied across reachable cluster members. MySQL Router depends on this metadata; its existing connections are dropped and new connections are refused after the metadata is removed.

InnoDB data-at-rest encryption transparently supports all indexes. Data is encrypted in the tablespace files and is decrypted when read into memory; network traffic requires separate MySQL network encryption such as SSL/TLS.

MEMORY and ARCHIVE are nontransactional storage engines, so the row inserted after BEGIN is not undone by ROLLBACK. InnoDB and NDB support transactions, while BLACKHOLE discards data and always returns an empty result set.

On Windows, MySQL supports TCP/IP connections as well as the Windows-specific named-pipe and shared-memory transports. Unix socket-file transport is not available on Windows, and UDP is not a MySQL client connection transport. MySQL Reference Manual: Connection Transport Protocols

By default, mysqlpump dumps all databases except schemas subject to its built-in restrictions. INFORMATION_SCHEMA is not dumped, and the sys schema is also excluded by default; each must be explicitly selected where supported. The mysql database is included as a database, although mysqlpump has separate restrictions on certain grant tables.

A failed MySQL 5.7-to-8.0 upgrade reverts changes to the data directory. The 8.0 redo logs must be removed before restarting the original 5.7 server, where the system tables can be prepared or repaired. After a slow shutdown of 5.7, retrying the 8.0 upgrade allows the server to upgrade and open the required MySQL system tables.

MySQL replication requires TCP/IP connectivity between a replica and its source. Every server participating in a replication topology must use a unique server_id. Binary logging must be enabled on a source because its binary log supplies the changes that replicas replicate.

innodb_directories defines directories that InnoDB scans at startup to discover tablespace files. Adding /innodb_extras makes that additional location available for discovery of InnoDB tablespaces; it does not move tablespaces or change the data-home or temporary-directory settings.

The default_authentication_plugin server option controls the authentication plugin assigned by default to newly created user accounts. Setting it to sha256_password configures basic SHA-256 password authentication as that default; specifying a plugin in CREATE USER affects only the individual account.

RESET MASTER clears the GTID execution and purged sets. Setting gtid_purged to aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167 records exactly the valid transactions received from the master and adds them to gtid_executed. The unwanted local GTIDs bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9 are removed, so GTID auto-positioning can continue with the master’s subsequent transactions without treating those erroneous transactions as part of the slave’s history.

On Unix, signal 15 (SIGTERM) instructs mysqld to perform its normal shutdown process. MySQL documents this as equivalent to executing the SQL SHUTDOWN statement, whose shutdown functionality is also provided by mysqladmin shutdown.

A larger InnoDB redo log reduces checkpoint flush activity, avoiding unnecessary disk writes when the workload is write-intensive. Disabling the InnoDB doublewrite buffer removes durability and torn-page recovery protection but can improve performance when data integrity after a failure is not required. The buffer pool already has substantial free space, so enlarging it is not the most beneficial disk-I/O change.

World-writable permissions on the MySQL data directory allow unauthorized operating-system users to delete or alter database files. They also expose files stored in that directory, including persisted server configuration, to modification. MySQL documents that the data directory requires restricted permissions and that mysqld-auto.cnf is located in the data directory.

VERIFY_CA requires TLS and validates the server certificate against configured CA certificates. VERIFY_IDENTITY performs that CA validation and also verifies that the certificate identity matches the target host name. Both modes require a matching CA certificate source, such as --ssl-ca or --ssl-capath.

ALTER USER changes properties of an existing MySQL account, including its authentication credentials. SET PASSWORD is also a dedicated statement for assigning a password to a MySQL user account.

MySQL Enterprise Backup performs hot, online, non-blocking backups for InnoDB data, reducing application disruption and lock-contention waits. It can also interface with media-management software such as Oracle Secure Backup for tape backups; Oracle Secure Backup supports virtual tape libraries.

MySQL supports TCP, SOCKET, PIPE, and MEMORY as values for --protocol. TCP, SOCKET, and PIPE appear in the choices; the fourth valid value, MEMORY, is absent. IPv6 can be used as an address family with a host value, but it is not a --protocol value.

GTID-based replication is configured by enabling gtid_mode and enforce-gtid-consistency on the participating MySQL servers, with binary logging enabled for the source; the standard startup configuration can also enable replica update logging. The replica is switched to GTID-based positioning by setting MASTER_AUTO_POSITION = 1 in CHANGE MASTER TO, allowing it to synchronize using GTID sets rather than binary-log file positions.

For long-term growth, the InnoDB system tablespace requires its last data file to use autoextend. A single auto-extending file satisfies this requirement, as does a configuration with fixed preceding files and an auto-extending final file. The autoextend attribute is permitted only on the last specified data file; a /tmp location is not appropriate for durable long-term data storage.

By default, mysqldump includes a SET @@GLOBAL.gtid_purged statement when GTIDs are enabled and gtid_executed is nonempty. That statement cannot run while the Group Replication plugin is active. Deleting it from the existing dump, or generating the dump with --set-gtid-purged=OFF so it is not emitted, prevents the prohibited GTID_PURGED update.

mysql_config_editor manages login-path options used by MySQL client programs, storing connection credentials in the obfuscated .mylogin.cnf file. Unless --login-path specifies another login path, it operates on the client login path by default.

MySQL Installer simplifies installation and updating for a wide range of MySQL products. Its console supports an upgrade operation for one or more installed products; it also supports command-line and silent operation, rather than only GUI-driven installation.

Group Replication requires a majority to reach consensus. With only two reachable members in a five-member group, that partition has no quorum and cannot determine whether the three unreachable members failed or form a separate three-member majority partition. The minority partition should be stopped while the issue is diagnosed. If the other three instances are confirmed failed and the two surviving instances must continue, an administrator must manually force a new group membership containing those two instances.