Oracle 1z0-888 today updated questions - Verified by Oracle Experts

MySQL 5.7 Database Administrator Questions and Answers

Question 1

You have created a new user with this statement:

CREATE USER ‘erika’@’localhost’ IDENTIFIED BY ‘first#1Pass’ PASSWORD EXPIRE;

What is the outcome?

Options:

When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will have to change the password before seeing the mysql> prompt.

When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will not be permitted to log in because the password is expired.

When ‘erika’@’localhost’ tries to log in with the MySQL command-line client, the user will be permitted to log in but will not be able to issue ant statements until the user changes the password.

You receive a syntax error that indicates that you cannot set a password and expire it at the same time.

Question 2

Which statement is true about tablespaces?

Options:

All tablespace files must be in the directory specified by the –-datadir option.

General tablespaces can be configured to span multiple files.

All tables must be in either the system tablespace or a general tablespace.

The system tablespace can be configured to span multiple files.

Question 3

Why should you be selective when granting the PROCESS privilege to an account?

Options:

It allows a client to process scripts.

It allows the use of stored routines.

It allows a client to see another user’s queries with the SHOW PROCESSLIST command.

It allows a client to control running processes on a server.

Question 4

Which two methods will provide the total number of partitions on a table? (Choose two.)

Options:

Query the INFORMATION_SCHEMA.PARTITIONS table

Use the command: SHOW TABLE STATUS

Query the performance_schema.objects_summary_global_by_type table

Query the INFORMATION_SCHEMA.TABLES table for the partition_count

Use the command: SHOW CREATE TABLE

Question 5

Consider:

Which statement best describes the meaning of the values in the ref columns?

Options:

world.City.CountryCode is used as the primary key for the Country table.

No indexed columns are used to select rows from the City table. The world.City.CountryCode column is used to select rows in the Country table.

world.City.CountryCode is used to sort the rows in the City table.

No indexed columns are used to select rows from the Country table. The world.City.CountryCode column is used to select rows in the City table.

Question 6

The /myfolder/my.cnf file has option set:

[mysqld]

skip-log-bin

/myfolder2/my.cnf has this option set:

[mysqld]

log-bin = /valid/path/to/mysqlbinlog

All mentioned paths are accessible to the account that you are currently using. Assume that any other options mentioned in either file are valid and legal option definitions.

You start an instance by using this command line:

mysqld --defaults-file=/myfolder/my.cnf --defaults-extra-file=/myfolder2/my.cnf

What is the outcome?

Options:

MySQL starts and Binary Logging is enabled.

MySQL fails to start due to the conflicting options in the configuration files.

MySQL fails to start due to conflicting options on the command line.

MySQL starts but Binary Logging is disabled.

Question 7

You have the following in your my.cnf configuration file:

[mysqld]

default_authentication_plugin=sha256_password

You want to create a new user who will be connecting from the IP address 192.0.2.10, and you want to use the authentication plug-in that implements SHA-256 hashing for user account passwords.

Which two statements would create a user named webdesign for this IP address with the password of imbatman using a SHA_256 password hash? (Choose two.)

Options:

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED AS sha256_user WITH sha256_password ‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY ‘iambatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED WITH sha256_password BY ‘imbatman’;

CREATE USER WITH sha256_password ‘sha256_user’@’192.0.2.10’ IDENTIFIED AS ‘webdesign’ USING ‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ WITH mysql_native_password USING SHA265 BY ‘imbatman’;

CREATE USER ‘webdesign’@’192.0.2.10’ IDENTIFIED BY SHA265 AS ‘imbatman’;

Question 8

You are contacted by a user who does not have permission to access a database table. You determine after investigation that this user should be permitted to have access and so you execute a GRANT statement to enable the user to access the table.

Which statement describes the activation of that access for the user?

Options:

The access does not take effect until the user logs out and back in.

The access does not take effect until the next time the server is started.

The access is available immediately.

The access does not take effect until you issue the FLUSH PRIVILEGES statement.

Question 9

Consider the two partial outputs of the SHOW GLOBAL VARIABLES command from a master and slave server:

Master:

Slave:

There is a problem with the slave replicating from the master. Which statement describes the cause of the problem?

Options:

The log_bin variable is set to OFF on the slave.

server_id is not unique.

The max_connections variable on the slave needs to be increased.

The shared_memory_base_name variable must match the master.

The version of the slave is newer that the version of the master.

Question 10

Which three statements correctly describe MySQL InnoDB Cluster? (Choose three.)

Options:

The cluster can be operated in multimaster mode with conflict detection for DML statements.

All MySQL client programs and connectors can be used for executing queries.

It provides fully synchronous replication between the nodes.

There is support for automatic failover when one node fails.

The data is automatically shared between the nodes.

Each query will be executed in parallel across the nodes.

Question 11

You inherited a busy InnoDB OLTP Instance with 100 schemas and 100 active users per schema.

Total dataset size is 200G with an average schema size of 2G.
The data is transient and is not backed up and can be repopulated easily.
Performance and responsiveness of the DB is paramount.
The query pattern for the DB instance is split 90/10 read/write.
DB host is dedicated server with 256G RAM and 64 cores.

One of your colleagues made some recent changes to the system and users are now complaining of performance impacts.

Which four configuration file edits might your colleague have performed to cause the negative DB performance? (Choose four.)

Options:

table_open_cache = 64

innodb_buffer_pool_instances=64

innodb_buffer_pool_size=200G

log_bin=mysql –bin

Innodb_flush_log_at_trx_commit=1

sync_binlog=10

innodb_flush_method=O_DIRECT

max_heap_table_size = 2G

tmp_table_size=2G

query_cache_size = 2G

query_cache_enabled=1

Question 12

Consider:

Which statement best describes the meaning of the value for the key_len column?

Options:

It shows how many bytes will be used from each index row.

It shows the number of characters indexed in the key.

It shows the total size of the index row.

It shows how many columns in the index are examined.

Question 13

You have a consistent InnoDB backup created with mysqldump, the largest table is 50 GB in size.

You start to restore your backup with this command;

shell> mysql –u root –p < backup.sql

After 30 minutes, you notice that the rate of restore seems to have slowed down. No other processes or external factors are affecting server performance.

Which is the most likely explanation for this slowdown?

Options:

The MySQL server has stopped inserting data to check index consistency.

InnoDB is doing CRC32 checks over the tablespace data as it grows.

The MySQL server is taking a periodical snapshot of data so it can resume the restore if it is interrupted mid-way.

InnoDB has filled the redo log and now must flush the pages.

Secondary indexes no longer fit into the buffer pool.

Question 14

Examine the mydata table and SELECT statements:

You issue:

mysql> begin;

mysql> update mydata set a=0 where b=3;

How many rows are now protected by locks with the default InnoDB configuration?

Options:

one

one row and a next-key lock for supremum

one row and a gap-lock

five

Question 15

This output is from a SHOW SLAVE STATUS:

What would cause the SQL_Delay variable to have a value of 360?

Options:

The master has performed a large transaction, which will take 360 seconds to complete on the slave.

The network latency between the master and the slave is 360 milliseconds.

The slave will need an estimated 360 seconds to update the remaining contents from the relay log.

The slave was configured for delayed replication with a delay of six minutes.

Question 16

An admin attempts to enforce stronger security by using these commands:

The admin then leaves the system running with the specified changes. What are two remaining security concerns? (Choose two.)

Options:

validate_password_policy cannot be set without restarting the MySQL instance.

The name of the dictionary file is too obvious.

The dictionary file word list is too short.

validate_password_dictionary_file cannot be set without restarting the MySQL instance.

The validate_password plug-in has not been loaded.

The dictionary file is in an insecure location.

Question 17

You back up by using mysqldump.

Which configuration is required on the MySQL Server to allow point-in-time recovery?

Options:

binlog_format=STATEMENT

log-bin

apply-log

bonlog_format=ROW

gtid_enable

Question 18

Consider:

What does the range value in the type column mean?

Options:

You can use an index and return rows that fall within a range of values.

The table will be scanned over a certain range of values.

This type of index uses the range hash.

There is a range of indexes that can be used.

Load More 1z0-888 Questions

Weekend Sale Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70percent

Oracle 1z0-888 MySQL 5.7 Database Administrator Exam Practice Test

MySQL 5.7 Database Administrator Questions and Answers

Options:

Answer:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Explanation:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Options:

Answer:

Explanation:

Options:

Answer:

Options:

Answer:

Explanation:

Options:

Answer: