1z0-908 MySQL 8.0 Database Administrator Questions and Answers

help topics

performance monitoring information

plugins

information about table structures

audit log events

rollback segments

time zone information and definitions

Set validate-user-plugins=caching_sha2_password in the configuration file.

Define CREATE USER ''@'%' IDENTIFIED WITH sha256_password in the MySQL instance.

Add default_authentication_plugin=mysql_native_password in the configuration file.

Add default_authentication_plugin=sha256_password in the configuration file.

Only files for MySQL or Its built-in storage engines are backed

Only non-encrypted files are backed up.

The backup includes only data files and their metadata.

Only InnoDB data and log files are backed up.

Only tables stored in their own tablespaces are backed up.

ALTER TABLE t1 ADD ENCRYPTED_TABLESPACE = 'Y';

ALTER TABLE t1 ENCRYPTION='Y';

ALTER TABLE t1 WITH ENCRYPTION USING MASTER KEY;

ALTER TABLE t1 SET TDE = 'ON';

The slave relay logs should be deleted; then execute START SLAVE;

The relay logs from the master should be used to replace the corrupted relay logs.

The slave relay logs should be deleted; execute CHANGE MASTER to adjust the replication relay log file name, then issue start SLAVE;

The slave needs to be restored from backup.

Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades.

Physical upgrades are performed for current instances on bare metal deployments, whereas logical upgrades are used for virtual machines or containerized instances.

Logical upgrades are much faster because they do not require restarting the mysqld process.

Post-upgrade table storage requirements after physical upgrades are usually smaller than that after logical upgrades.

Physical upgrades are much faster because they do not require restarting the mysqld process.

Physical upgrades leave data in place, whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades.

Execute SET GLOBAL log_error = ‘’.

Execute SET GLOBAL max_error_count = .

Execute SET GLOBAL expire_logs_days=0 to enforce a log rotation.

Rename the error log file on disk, and then execute FLUSH ERROR LOGS.

The cluster will shut down to preserve data consistency.

The cluster has built-in high availability and updates group_replication_ip_whitelist to remove the unreachable nodes.

The group replication will buffer the transactions on the online nodes until the unreachable nodes return online.

There could be both a 2 node and 3 node group replication still running, so shutting down group replication and diagnosing the issue is recommended.

A manual intervention to force group members to be only the working two instances is required.

The Query Analyzer graph view range selector can extend to cover the same hour over multiple days.

It is possible to filter a Query Analyzer view graph by database and by table.

The Query Analyzer can plot a CPU utilization graph for remote hosts with a MySQL Enterprise Service Manager's built-in Agent installation.

It is possible to export statements included in a graph selection in CSV format.

slave updates logging

semi-sync replication plugin

primary key or primary key equivalent on every table

binary log checksum

binary log ROW format

binary log MIXED format

replication filters

The statement was blocked by MySQL Firewall.

John has not activated the role.

John needs to reconnect to the database.

The DBA needs to execute FLUSH PRIVILEGES.

Remove ib_logfile0 and ib_logfile1 files from the file system.

Execute mysqladmin flush-logs.

First run mysqld --initialize to refresh the Size of ib_logfile.

Create a new ib_logfile0 file of size 26214400.

Replication will fail because the slave has purged more aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa

transactions than the master.

Replication will fail because the master does not have the required transaction with bbbbbbbb-bbbb- bbbb-bbbb-bbbbbbbbbbbb GTIDs in its binary logs.

Replication will fail because the master has already purged transactions with cccccccc-cccc-cccc- cccc-cccccccccccc GTIDs.

Replication will fail because of inconsistent numbers in cccccccc-cccc-cccc-cccc-cccccccccccc

GTIDs.

Replication will work.

ALTER TABLE world.city ADD SPATIAL INDEX (Name);

ALTER TABLE world.city ADD SPATIAL INDEX (Population);

ALTER TABLE world.city ADD INDEX (Population);

ALTER TABLE world.city ADD INDEX (Name);

ALTER TABLE world.city ADD FULLTEXT INDEX (Name);

ALTER TABLE world.city ADD FULLTEXT INDEX (Population);

It installs most Oracle MySQL products.

It performs product upgrades.

It provides only GUI-driven, interactive installations.

Manual download of separate product packages is required before installing them through MySQL Installer.

It provides a uniform installation wizard across multiple platforms.

It manages the configuration of the MySQL Firewall feature.

It manages the configuration of client programs.

It can move datadir to a new location.

It manages the configuration of user privileges for accessing the server.

It will use [client] options by default unless you provide --login-path.

It can be used to create and edit SSL certificates and log locations.

It provides an interface to change my.cnf files.

RESET MASTER;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312,bbbbbbbb- bbbb-bbbb-bbbb-bbbbbbbbbbbb:1-9;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

RESET SLAVE;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3820;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa—aaaaaaaaaaaa:1-10300;

RESET MASTER;

SET GLOBAL gtid_purged-aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2312;

SET GLOBAL gtid_executed=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

RESET SLAVE;

SET GLOBAL gtid_purged=aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10167;

It can be recovered from a donor instance on host3 by cloning using the command cluster.rejoinInstance ('@host3:3377')

It can rejoin the cluster by using the command cluster.addInstance('@host3:3377')

It has been removed from the cluster by using the command STOP GROUP_REPLICATION;

It can rejoin the cluster by using the command dba.rebootClusterFromCompleteOutage()

It has been expelled from the cluster because of a transaction error.