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

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);

ALTER USER baduser@hostname PASSWORD DISABLED;

ALTER USER baduser@hostname MAX_USER_CONNECTIONS 0;

ALTER USER baduser@hostname ACCOUNT LOCK;

ALTER USER baduser@hostname IDENTIFIED WITH mysql_no_login;

ALTER USER baduser@hostname DEFAULT ROLE NONE;

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.

It supports backing up only table structures.

It can perform hot or warm backups.

It creates logical backups.

It supports the creation of incremental backups.

It supports backup of a remote MySQL system.

It supports restoring to a remote MySQL system.

starting and stopping the MySQL instance

tracing import and export with mysqidump

deploying the MySQL agent on supported target operating system

creating e-mail alerts and SNMP traps for MySQL warnings

monitoring the availability of the MySQL instance

analyzing executed MySQL queries

monitoring of NDB Cluster API nodes

QN = 3

QN = 5

QN = 1

QN = 4

QN = 2

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.

It encrypts any connecting slaves connection thread.

It can be set at run time.

It requires a keyring plugin.

When enabled it encrypts existing binary logs.

It can be activated per session.

It is the time between the I/O thread receiving details of the master's last transaction and the time it was applied by the SQL thread.

It is the time between the most recent transaction written to the relay logs and the time it was committed on the master.

It is the time between the I/O thread receiving details of the master’s last transaction and the time it was written to the relay log on the slave.

It is the time between the most recent transaction applied by a SQL thread and the time it was committed on the master.

mysqldump --single-transaction > full-backup-$(date +%Y%m%d).sql

mysqlpump --include-databases=% > full-backup-$(date +%Y%m$d).sql

mysqlpump --all-databases > full-backup-$(date +%Y%m%d).sql

mysqldump --all-databases > full_backup-$(date +%Y%m%d).sql

innodb_flush_log_at_trx_commit=1

buffer_pool_size=24G

innodb_log_file_size=1G

sync_binlog=0

innodb_doublewrite=0

max_connections=10000

innodb_undo_directory=/dev/shm

This file is for storing MySQL Server configuration options in ISON format.

This file is for logging purposes only and is never processed.

It is read and processed at the beginning of startup configuration.

It is read and processed at the end of startup configuration.

It is always updated with changes to system variables.

This file is for storing MySQL server_uuid values only.

The master is most probably too busy to transmit data and the slave needs to wait for more data.

One or more large tables do not have primary keys.

This value shows only I/O latency and is not indicative of the size of the transaction queue.

The master is producing a large volume of events in parallel but the slave is processing them serially.

The parallel slave threads are experiencing lock contention.

SELECT user,passwd FROM members

WHERE user = ‘?’;INSERT INTO members(‘user’,’passwd’) VALUES

(‘bob@example.com’,‘secret’);--‘;

SELECT id, name FROM user WHERE user.id=(SELECT members.id FROM members);

SELECT id, name FROM user WHERE id=23 OR id=32 OR 1=1;

SELECT id, name FROM user WHERE id=23 OR id=32 AND 1=1;

SELECT email,passwd FROM members

WHERE email = ‘INSERT INTO members(‘email’,’passwd’) VALUES (‘bob@example.com’,

‘secret’);--‘;

SELECT user, phone FROM customers WHERE name = ‘\; DROP TABLE users; --‘;

mysqlbinlog --stop-position=500324 binlog.000036 | mysql

mysqlbinlog --stop-datetime='2019-ll-20 14:55:18' binlog.000036 | mysql

mysqlbinlog --stop-position=5004S3 binlog.000036 | mysql

mysqlbinlog --stop-datetime='2019-ll-20 14:55:16' binlog.000036 | mysql

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.

It fails if any of the roles is specified in the mandatory_roles variable.

You must revoke r_role1 and r_role2 from all users and other roles before dropping the roles.

Existing connections can continue to use the roles’ privileges until they reconnect.

You must revoke all privileges from r_role1 and r_role2 before dropping the roles.

It fails if you do not have the ADMIN OPTION of the roles r_role1 and r_role2.

It fails if at least one of the roles does not exist.

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.

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.

20

24

21

25

22

6

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.