Labour Day Special 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: exams65

MySQL 8.0 Database Administrator Question and Answers

MySQL 8.0 Database Administrator

Last Update Apr 27, 2024
Total Questions : 140

We are offering FREE 1z0-908 Oracle exam questions. All you do is to just go and sign up. Give your details, prepare 1z0-908 free exam questions and then go for complete pool of MySQL 8.0 Database Administrator test questions that will help you more.

1z0-908 pdf

1z0-908 PDF

$35  $99.99
 Add to Cart
1z0-908 Engine

1z0-908 Testing Engine

$42  $119.99
 Add to Cart
1z0-908 PDF + Engine

1z0-908 PDF + Testing Engine

$56  $159.99
 Add to Cart
Questions 1

Which two statements are true about the mysql_config_editor program? (Choose two.)

Options:

A.  

It manages the configuration of the MySQL Firewall feature.

B.  

It manages the configuration of client programs.

C.  

It can move datadir to a new location.

D.  

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

E.  

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

F.  

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

G.  

It provides an interface to change my.cnf files.

Discussion 0
Questions 2

Which statement is true about MySQL Enterprise Transparent Data Encryption (TDE)?

Options:

A.  

MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location.

B.  

Both MyISAM and InnoDB tables can be encrypted by setting the keyring_engine = ALL variable in the MySQL configuration file.

C.  

Lost tablespace encryption keys can be regenerated only if the master database key is known or present in the Key Vault specification.

D.  

TDE can encrypt InnoDB and MyISAM tables only when the tables are stored in the SYSTEM tablespace.

Discussion 0
Questions 3

Which two queries are examples of successful SQL injection attacks? (Choose two.)

Options:

A.  

SELECT user,passwd FROM members

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

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

B.  

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

C.  

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

D.  

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

E.  

SELECT email,passwd FROM members

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

‘secret’);--‘;

F.  

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

Discussion 0
Questions 4

There are five MySQL instances configured with a working group replication.

Examine the output of the group members:

Which two statements are true about network partitioning in the cluster? (Choose two.)

Options:

A.  

The cluster will shut down to preserve data consistency.

B.  

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

C.  

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

D.  

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.

E.  

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

Discussion 0
Questions 5

t is a non-empty InnoDB table.

Examine these statements, which are executed in one session:

BEGIN;

SELECT * FROM t FOR UPDATE;

Which is true?

Options:

A.  

If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.

B.  

If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.

C.  

mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.

D.  

If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.

Discussion 0
Questions 6

Examine this query:

What information does this query provide?

Options:

A.  

total memory used across all connections associated with the user on connection number 10

B.  

total memory used by the first 10 connections

C.  

total memory used by thread number 10

D.  

total memory used across all connections associated with the user on thread number 10

E.  

total memory used by connection number 10

F.  

total memory used by the first 10 threads

Discussion 0
Questions 7

MySQL programs look for option files in standard locations.

Which method will show the option files and the order in which they are read?

Options:

A.  

shell> mysqladmin --debug

B.  

shell> mysql --print-defaults

C.  

shell> mysqld --help --verbose

D.  

mysql> SHOW GLOBAL VARIABLES;

Discussion 0
Questions 8

Which statement is true about InnoDB persistent index statistics?

Options:

A.  

Updating index statistics is an I/O expensive operation.

B.  

Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.

C.  

Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.

D.  

Execution plans based on transient index statistics improve precision when innodb_stats_persistent_sample_pages is increased.

E.  

Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.

Discussion 0
Questions 9

Which three statements are true about MySQL replication? (Choose three.)

Options:

A.  

Replication can use only TCP/IP connections.

B.  

Any instance can have multiple slaves, but it can have only one master.

C.  

Each instance in a replication topology must have a unique server ID.

D.  

Binary logs contain only transactions originating from a single MySQL instance.

E.  

Each slave must have its own MySQL user for replication.

F.  

Binary logging must be enabled on the master in order to replicate to other instances.

G.  

A replication user must have the SELECT privilege for all tables that need to be replicated.

Discussion 0
Questions 10

You reconfigure and start a slave that was not replicating for several days.

The configuration file and CHANGE MASTER command are correct. Examine the GTID information from both master and slave:

Which statement is true?

Options:

A.  

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

transactions than the master.

B.  

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

C.  

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

D.  

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

GTIDs.

E.  

Replication will work.

Discussion 0
Questions 11

Which two are contained in the InnoDB system tablespace (ibdata1) by default? (Chose two.)

Options:

A.  

table data

B.  

primary indexes

C.  

user privileges

D.  

InnoDB Data Dictionary

E.  

change buffer

F.  

doublewrite buffer

Discussion 0
Questions 12

Examine this statement and output:

Which two SQL statements can jsmith execute? (Choose two.)

Options:

A.  

UPDATE world.country SET Name='all';

B.  

UPDATE world.country SET Name='one' LIMIT 1;

C.  

UPDATE world.country SET Name='new' WHERE Name='old';

D.  

UPDATE world.country SET Name=CONCAT('New ',Name);

E.  

UPDATE world.country SET Name='first' ORDER BY Name LIMIT

Discussion 0
Questions 13

Which three settings control global buffers shared by all threads on a MySQL server? (Choose three.)

Options:

A.  

sort_buffer_size

B.  

key_buffer_size

C.  

table_open_cache

D.  

read_buffer_size

E.  

innodb_buffer_pool_size

F.  

tmp_table_size

Discussion 0
Questions 14

Examine Joe's account:

CREATE USER 'joe'@'%' IDENTIFIED BY '*secret*'

GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%'

All existing connections for joe are killed.

Which two commands will stop joe establishing access to the MySQL instance? (Choose two.)

Options:

A.  

ALTER USER 'joe'@'%' ACCOUNT LOCK

B.  

ALTER USER 'joe'@'%' SET password='*invalid*'

C.  

REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%'

D.  

ALTER USER 'joe'@'%' PASSWORD HISTORY 0

E.  

ALTER USER 'joe'@'%' IDENTIFIED BY '*invalid*' PASSWORD EXPIRE

F.  

REVOKE USAGE ON *.* FROM 'joe'@'%'

Discussion 0
Questions 15

Which three are characteristics of a newly created role? (Choose three.)

Options:

A.  

It can be dropped using the DROP ROLE statement.

B.  

It is stored in the mysql.role table.

C.  

It is created as a locked account.

D.  

It can be renamed using the RENAME ROLE statement.

E.  

It can be granted to user accounts.

F.  

It can be protected with a password.

Discussion 0
Questions 16

Examine these statements and output:

Which statement is true?

Options:

A.  

The user is logged in with --user=accounting as an option.

B.  

The user is authenticated as the anonymous proxy user ‘’@’%’.

C.  

The user is authorized as the accounting@localhost user.

D.  

The user is authorized as the rsmith@localhost user.

E.  

The user failed to define a username and the connecting username defaulted to ‘’@’%’.

Discussion 0
Questions 17

Examine this command and output:

Which two statements are true? (Choose two.)

Options:

A.  

The lock is at the metadata object level.

B.  

The lock is a shared lock.

C.  

The lock is an intentional lock.

D.  

The lock is at the table object level.

E.  

The lock is a row-level lock.

F.  

The lock is an exclusive lock.

Discussion 0
Questions 18

Your MySQL environment has asynchronous position based-replication with one master and one slave.

The slave instance had a disk I/O problem, so it was stopped.

You determined that the slave relay log files were corrupted and unusable, but no other files are damaged.

You restart MySQL Server.

How can replication be restored?

Options:

A.  

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

B.  

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

C.  

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

D.  

The slave needs to be restored from backup.

Discussion 0
Questions 19

Which statement is true about InnoDB persistent index statistics?

Options:

A.  

Updating index statistics is an I/O expensive operation.

B.  

Index statistics are calculated from pages buffered in the buffer pool for tables with InnoDB storage engine.

C.  

Setting innodb_stats_auto_recalc=ON causes statistics to be updated automatically when a new index is created.

D.  

Execution plans based on transient index statistics improve precision when

innodb_stats_persistent_sample_pages is increased.

E.  

Increasing innodb_stats_persistent_sample_pages determines higher pages scanning speed, at the cost of increased memory usage.

F.  

Tables are scanned and index statistics recalculated when an instance is restarted.

Discussion 0
Questions 20

The mysqld instance has the connection control plugin enabled with these settings: connection_control_min_connection_delay=1000 connection_control_max_connection_delay=2000

The minimum and maximum delays need to be increased to 3000 and 5000, respectively.

A command is executed:

mysql> SET GLOBAL connection_control_min_connection_delay=3000;

What is the result?

Options:

A.  

The minimum value increases to 3000 and the maximum value increases to 4000.

B.  

Only the minimum connection value is increased to 3000.

C.  

The minimum connection value is changed to 2000.

D.  

An error is returned.

Discussion 0
Questions 21

Which four are types of information stored in the MySQL data dictionary? (Choose four.)

Options:

A.  

performance metrics

B.  

table definitions

C.  

access control lists

D.  

view definitions

E.  

server runtime configuration

F.  

server configuration rollback

G.  

stored procedure definitions

Discussion 0