Oracle 1z0-908 MySQL 8.0 Database Administrator Exam Practice Test

No committed transactions are lost.

The slave automatically identifies that the master is unreachable and performs any required actions so that applications can start using the slave as the new master.

As soon as the incident happens, application can read data from the slave and rely on it to return a full and current set of data.

Reads from the slave can return outdated data until the value of the rpi_semi_sync_master_timeout variable is reached.

Reads from the slave can return outdated data for some time, until it applies all transactions from its relay log.

A small amount of committed transactions may be lost in case they were committed just before the disk failure.

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.

performance metrics

InnoDB buffer pool LRU management data

access control lists

view definitions

server runtime configuration

server configuration rollback

stored procedure definitions

DESCRIBE manufacturing.parts;

SELECT * FROM information_schema.statistics WHERE table_schema=’manufacturing’ AND TABLE_NAME=’parts’;

SHOW INDEXES FROM manufacturing.parts;

SELECT * FROM information_schema.COLUMN_STATISTICS;

EXPLAIN SELECT INDEXES FROM manufacturing.parts;

MySQL writes SDI to the binary log for distributed backups.

MySQL keeps InnoDB metadata changes in .sdi files in datadir.

The metadata is serialized in JSON format in Serialized Dictionary Information (SDI).

MySQL Server stores a copy of the serialized data in the InnoDB user tablespace.

MySQL implicitly executes FLUSH TABLES and stores a snapshot backup of the metadata.

ALTER TABLE employees -

ADD INDEX (birth_date DESC);

ALTER TABLE employees -

ADD INDEX ((MONTH(birth_date)));

ALTER TABLE employees -

ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (MONTH(birth_date)) VIRTUAL NOT NULL,

ADD INDEX (birth_month);

ALTER TABLE employees -

ADD COLUMN birth_month tinyint unsigned GENERATED ALWAYS AS (birth_date->>'$.month') VIRTUAL NOT NULL,

ADD INDEX (birth_month);

ALTER TABLE employees -

ADD INDEX ((CAST(birth_date->>'$.month' AS unsigned)));

ALTER TABLE employees -

ADD INDEX (birth_date);

$HOME/.my.cnf file

$MYSQL_HOME/my.cnf file

DATADIR/mysqld-auto.cnf file

$HOME/.mylogin.cnf file

$HOME/.mysql/auth/login file

/etc/my.cnf file

$HOME/.mysqlrc file

TCP

SOCKET

PIPE

DIRECT

IPv6

FILE

IPv4

A single-file backup is created.

The backup operation will finish only when backup-and-apply-log is executed.

The --backup-dir option holds temporary output, status, and metadata files.

The backup operation will finish only when apply-log is executed.

A raw backup is created.

It can be dropped using the DROP ROLE statement.

It is stored in the mysql.role table.

It is created as a locked account.

It can be renamed using the RENAME ROLE statement.

It can be granted to user accounts.

It can be protected with a password.

Each slave thread is responsible for updating a specific database.

Cross-database constraints can cause database inconsistency.

Increasing slave_parallel_workers will improve high availability.

Setting slave_preserve_commit_order to on will improve data consistency.

Setting slave_parallel_type=DATABASE won't work for circular replication; it should be set to LOGICAL_CLOCK.

Setting transaction_allow_batching to on will improve data consistency.

It reconfigures InnoDB Cluster if the cluster was stopped.

It performs InnoDB Cluster instances rolling restart.

It only starts all InnoDB Cluster instances.

It is not mandatory that all instances are running and reachable before running the command.

It stops and restarts all InnoDB Cluster instances and initializes the metadata.

It only stops and restarts all InnoDB Cluster instances.

It picks the minimum number of instances necessary to rebuild the quorum and reconfigures InnoDB Cluster.

mysqlpump --include-tables=db.% --result-file=all_db_backup.sql

mysqlpump > all_db_backup.sql

mysqlpump --include-databases=db --result-file=all_db_backup.sql

mysqlpump --include-databases=db% --result-file=all_db_backup.sql

The instance deployed on host3 must be rebuilt with a backup from the primary instance.

The cluster is running and there is at least one ONLINE instance.

The instance deployed on host3 must be synchronized from a donor deployed on host1 by using the command cluster.addInstance(‘host1:3377’).

It is possible to determine the most up-to-date instance by comparing different global transaction identifier (GTID) sets with GTID_SUBSET(set1,set2).

The active session instance is invalid and must be re-created by using the command shell.connect (‘host3:3377’).

Restrict the number of OS users that have access at the OS level.

Ensure appropriate file system privileges for OS users and groups.

Minimize the number of non-MySQL Server-related processes running on the server host.

Encrypt the file system to avoid needing exact file-system permissions.

Keep the entire software stack on one OS host.

Run MySQL server as the root user to prevent incorrect sudo settings.

The copy-back process is used to overwrite a new backup over an existing backup.

It restores files from the data directory to their original MySQL server locations.

It restores files from the backup directory to their original MySQL server locations.

The copy-back process makes inconsistent backups.

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.

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.

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.

Remove group read/write privileges from the private_key.pem file.

Remove world read privileges from the server-cert.pem certificate file.

Change the group ownership of the mysql directory to the mysql user group.

Remove world read privileges from the public_key.pem file.

Change the parent directory owner and group to mysql.

Remove the world read/execute privilege from the accounting directory.

The instances use different user accounts unique to each instance.

The instances listen on different IP addresses.

The instances use different socket names.

The instances have appropriate net masks set.