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

MySQL 5.7 Database Administrator Question and Answers

MySQL 5.7 Database Administrator

Last Update Apr 27, 2024
Total Questions : 124

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

1z0-888 pdf

1z0-888 PDF

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

1z0-888 Testing Engine

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

1z0-888 PDF + Testing Engine

$56  $159.99
 Add to Cart
Questions 1

An employee cannot access the company database. You check the connection variables:

A look at the user privileges shows:

What is a valid explanation for why one of the users is unable to connect to the database?

Options:

A.  

Bob has max_user_connections set to zero, which blocks all his connections.

B.  

All users are blocked because max_user_connections is accumulated over the host account information.

C.  

connect_timeout is too small to allow a connection to occur.

D.  

Joe has exceeded the max_user_connections global limit.

E.  

Kay is already connected elsewhere and attempting to log in again.

Discussion 0
Questions 2

You are receiving complaints from your application administrators that they are seeing periodic stalls in database response (no queries to any table are returning results for several seconds or longer). You monitor your system and notice that the durations of those stalls correspond to peaks in disk I/O.

Which 2 things should you investigate?

Options:

A.  

Check the rate of change in the status value Aborted_connects and compare to the rate of change in Connections.

B.  

Check the difference between the InnoDB status values “Log Sequence number” and “Last Checkpoint” positions then compare that to the total size of the redo log.

C.  

Check the rate of change in the status value Select_scan and compare to the rate of change in Com_select.

D.  

Check the difference between the InooDB status values “Trx id counter” and “Purge done for” and compare to the state substatus of the main “Main thread”

E.  

Check the rate of change in the statis value Qcache_hits and compare that to the rate of change of Qcache_not_cached.

Discussion 0
Questions 3

The ‘applicationdb’ is using InnoDB and consuming a large amount of file system space. You have a /backup partition available on NFS where backups are stored.

You investigate and gather this information:

Three tables are stored in the InnoDB shared tablespace and the details are as follows:

  • The table data_current has 1,000,000 rows.
  • The table data_reports has 1,500,000 rows.
  • The table data_archive has 4,500,000 rows.

You attempt to free space from ibdata1 by taking a mysqldump of the data_archive table and storing it on your backup partition.

Unfortunately, this action does not free any actual disk space back to the file system and the server disk space is running out.

Which set of actions will allow you to free disk space back to the file system?

Options:

A.  

Enable compression on the table, causing InnoDB to release unused pages on disk to the file system:

B.  

Execute OPTIMIZE TABLE so that the InnoDB engine frees unused pages on disk back to the file system:

C.  

Set the server to use its own tablespace, and then alter the table so that data is moved from the shared tablespace to its own:

D.  

Take a backup, stop the server, remove the data files, and restore the backup:

Discussion 0
Questions 4

Consider:

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

Options:

A.  

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

B.  

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.

C.  

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

D.  

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.

Discussion 0
Questions 5

Which two statements are true about InnoDB auto-increment locking? (Choose two.)

Options:

A.  

InnoDB never uses table-level locks.

B.  

InnoDB always protects auto-increment updates with a table-level lock

C.  

InnoDB does not use locks to enforce auto-increment uniqueness.

D.  

The auto-increment lock can be a table-level lock.

E.  

Some settings for innodb_autoinc_lock_mode can help reduce locking.

Discussion 0
Questions 6

How does the InnoDB storage engine handle deadlocks when they are detected?

Options:

A.  

Both the affected transactions will be rolled back.

B.  

The affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.

C.  

One of the affected transactions will be rolled back, the other is allowed to proceed.

D.  

The transaction isolation level determines which transaction is rolled back.

E.  

The innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.

Discussion 0
Questions 7

A master-slave replication setup has the slave showing this error:

On the master server, the binary logs show:

What could explain this error? (Choose two.)

Options:

A.  

binlog_cache_size=1024 is too small and transactions are lost.

B.  

binlog_format=STATEMENT and a non-deterministic query was executed.

C.  

enforce_gtid_consistency=ON and consistency is broken between the master and the slave.

D.  

The sync_relay_log=1000 setting on the slave is too small.

E.  

sync_binlog=0 and the master server crashed.

Discussion 0
Questions 8

Consider these global status variables:

Which two conclusions can be made from the output? (Choose two.)

Options:

A.  

There are 140 Performance Schema threads at the time of the output.

B.  

There are 510 connections to MySQL at the time of the output.

C.  

The thread cache has been configured with thread_cache_size set to at least 6.

D.  

There are more connections being idle than executing queries.

E.  

All max_connections were in use at 2018-03-22 14:54:06

Discussion 0
Questions 9

To satisfy a security requirement, you have created or altered some user accounts to include REQUIRE X509.

Which additional task needs to be performed for those user accounts to fulfill the requirement to use X509?

Options:

A.  

Install the X509 plug-in on the server.

B.  

Set the X509 option in the [client] section of the MySQL server’s configuration file.

C.  

Restart the server with the --require-x509 option.

D.  

Distribute client digital certificates to the client computers being used to log in by the user accounts.

E.  

Provide users access to the server’s private key.

Discussion 0
Questions 10

You enable binary logging on MySQL Server with the configuration:

binlog-format=STATEMENT

log-bin

Which database updates are logged on the master server to the binary log by default?

Options:

A.  

all updates except to the TEMPDB database

B.  

all updates except to the PERFORMANCE_SCHEMA database

C.  

all updates not involving temporary tables

D.  

all updates to the default database, except temporary tables

E.  

all updates to all databases

Discussion 0
Questions 11

Consider that local disk files are accessible via MySQL with commands such as:

mysql> LOAD DATA LOCAL INFILE ‘/etc/passwd’ INTO TABLE mypasswords;

What change could be made to stop any breach via this insecurity?

Options:

A.  

executing REVOKE LOAD FROM *.*

B.  

setting the --local-service=0 option when starting mysqld

C.  

executing REVOKE FILE FROM *.*

D.  

executing REVOKE FILE ON *.* FROM ‘ ’ @’%’

E.  

setting the --local-infile=0 option when starting mysqld

F.  

setting the --open-files-limit=0 option when starting mysqld

Discussion 0
Questions 12

Which three allocate memory per thread in MySQL? (Choose three.)

Options:

A.  

query cache

B.  

thread cache

C.  

read buffer

D.  

internal temporary table

E.  

sort buffer

F.  

InnoDB buffer pool instance

Discussion 0
Questions 13

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:

A.  

one

B.  

one row and a next-key lock for supremum

C.  

one row and a gap-lock

D.  

five

Discussion 0
Questions 14

Which two statements are true regarding MySQL security? (Choose two.)

Options:

A.  

The mysql user needs to have sudo privileges.

B.  

The mysqld process owner should own all files and directories to which the server writes.

C.  

The root or administrator users should own all files and directories to which the server writes.

D.  

The mysqld process should be run as root or administrator.

E.  

The mysqld process should not be run as root or administrator.

Discussion 0
Questions 15

A MySQL server was initialized with separate UNDO tablespaces. Users complain that when they roll back large transactions, the time to process the request takes too long. The DBA would like to move the MySQL InnoDB UNDO tablespace to a solid-state drive (SSD) for better performance. Is this possible and how?

Options:

A.  

Yes. Shut down the mysqld process, enable the transportable_tablespace option, and move the UNDO directory to the SSD.

B.  

Yes. Shut down, copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in your my.cnf.

C.  

No. The UNDO tablespaces must remain on the same file system as the system tablespaces.

D.  

No. The sequential write pattern of the UNDO tablespaces is not supported on modern SSD block devices.

Discussion 0
Questions 16

A MySQL database uses all InnoDB tables and is configured as follows;

You will be setting up a replication slave by using mysqldump. You will need a consistent backup taken from your running production server. The process should have minimal impact to active database connections.

Which two arguments will you pass to mysqldump to achieve this? (Choose two.)

Options:

A.  

--skip-opt

B.  

--lock-all-tables

C.  

--create-apply-log

D.  

--single-transaction

E.  

--master-data

Discussion 0
Questions 17

You have a MySQL replication setup and you intentionally stop the SQL thread on the slave.

What are two reasons that you may stop the SQL thread on the slave while keeping the I/O thread running? (Choose two.)

Options:

A.  

to allow the remaining events to be processed on the slave while not receiving new events from the master

B.  

to allow a backup to be created under reduced load

C.  

to allow for point-in-time recovery on the slave

D.  

to prevent schema changes from propagating to the slave before they are validated

E.  

to prevent any transaction experiencing a deadlock

Discussion 0
Questions 18

You have successfully provisioned the latest MySQL 5.7 database instance on a physical host, to be added to an existing farm for use in a modern, high volume, ACID-compliant, OLTP website, which serves hundreds of DML transactions per second.

The default values of which two key variables do you change to ensure seamless operation of the database? (Choose two.)

Options:

A.  

Key Buffer Size

B.  

InnoDB Redo Log Size

C.  

Binary Log Size

D.  

Buffer Pool Size

E.  

Sort Buffer size

F.  

Query Cache Size

Discussion 0
1z0-888 Questions Answers | 1z0-888 Test Prep | MySQL 5.7 Database Administrator Questions PDF | 1z0-888 Online Exam | 1z0-888 Practice Test | 1z0-888 PDF | 1z0-888 Test Questions | 1z0-888 Study Material | 1z0-888 Exam Preparation | 1z0-888 Valid Dumps | 1z0-888 Real Questions | MySQL Database Administration 1z0-888 Exam Questions