CertsTopics Logo

Summer Special Limited Time 60% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 60certs

Oracle 1z0-888 Dumps

Page: 1 / 5
Total 124 questions
Get 1z0-888 Full Access Download 1z0-888 PDF

MySQL 5.7 Database Administrator Questions and Answers

Question 1

Consider the join_buffer_size parameter in MySQL Server.

Which two statements are true about the join buffer? (Choose two.)

Options:

A.

The value should be increased if the client performs several SELECT operations.

B.

The join buffer is set per connection.

C.

The join buffer is used to process sorts when complex joins are being performed.

D.

The value should be increased from the default if the query joins large rows without using an index.

E.

The join buffer is global and can be changed only by restarting the server.

Question 2

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

Question 3

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.

Question 4

Is it true that binary backups always take less space than text backups?

Options:

A.

Yes, because binary backups only contain data, and not statements required to insert data into the tables.

B.

No, because text backups can have optimizations, which make them smaller, such as updating many rows at once.

C.

No, because if InnoDB tables contain many empty pages, they could take more space than the INSERT statements.

D.

Yes, because even if InnoDB tables contain many empty pages, text backups have empty INSERT statements for them.

Question 5

A MySQL instance has this configuration values set:

A transaction involving a single InnoDB INSERT statement commits.

Which list of locations is in the sequence of disk writes for this transaction?

Options:

A.

binary log, redo log, doublewrite buffer, and tablespace

B.

redo log, binary log, doublewrite buffer, and tablespace

C.

doublewrite buffer, redo log, tablespace, and binary log

D.

redo log, doublewrite buffer, tablespace, and binary log

Question 6

You have created a new user with this statement:

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

What is the outcome?

Options:

A.

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.

B.

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.

C.

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.

D.

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

Question 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.

Question 8

Which two statements are true regarding the creating of new MySQL physical and logical backups? (Choose two.)

Options:

A.

Physical backups can be used to recover from data corruption.

B.

Logical backups are human-readable whereas physical backups are not.

C.

Logical backups are always larger than physical backups.

D.

Physical backups are usually slower than text backups.

E.

Physical backups are usually faster than text backups.

Question 9

You are no longer able to log in to an existing MySQL Server because the root password credentials not working. You need to reset the root password to complete various administrative tasks. What are the two major methods that will achieve this? (Choose two.)

Options:

A.

Start the MySQL Server in --safe-mode, which only loads the privilege system for changes as data is inaccessible.

B.

Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.

C.

Start the MySQL Server with --init-file pointing to SQL that executes an ALTER USER statement to change the root user password.

D.

Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password.

E.

Start the MySQL Server with –initialize-insecure to force a password reset procedure on the command line.

Question 10

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

Question 11

Which statement is true about using Microsoft Windows Cluster as a platform for MySQL?

Options:

A.

It relies on the shared disk architecture being visible to both servers.

B.

It is provided by means of IP-level disk replication.

C.

It implements High Availability by using the .NET Connector’s load balancing capabilities.

D.

It is a shared-nothing architecture.

Question 12

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.

Question 13

Which statement is correct about how InnoDB storage engine uses disk space?

Options:

A.

It stores data in .MYD files and its index and undo information in the common tablespace.

B.

It stores data in .MYD files, index information in .MYI files, and undo information the common tablespace.

C.

It stores its data in tablespace file(s). Index and data dictionary details are stored in .FRM files.

D.

It stores its data, index and undo information in .MYD and .MYI files.

E.

It stores data, index and undo information in tablespace file(s).

Question 14

You are investigating the performance of the server and see this information:

  • events_waits_summary_global_by_event_name in the Performance Schema shows that the wait/synch/mutex/sql/LOCK_table_cache event is dominating other wait events.
  • The Table_open_cache_overflows status variable is 0.

Which action should be taken to remove the performance bottleneck described here?

Options:

A.

Increase the value of table_open_cache_instances.

B.

Decrease the value of table_definition_cache.

C.

Decrease the value of table_open_cache.

D.

Increase the value of table_definition_cache.

E.

Increase the value of table_open_cache.

F.

Decrease the value of table_open_cache instances.

Question 15

Host slave1 has ip address 192.0.2.10.

Host slave2 has ip address 203.0.113.50

Examine these commands:

Why did this error occur?

Options:

A.

The host on the command line is not defined in the login path.

B.

The mysqld instance has not been restarted after creating the login path.

C.

There is no password defined in the login path.

D.

The DNS is not configured correctly for slave1 host.

E.

The .mylogin.cnf file is not readable.

Question 16

You are using GTIDS in replication. You need to skip a transaction with the GTID of aaa-bbb-ccc-ddd-eee:3 on a slave.

Which procedure would you execute from a MySQL prompt?

Options:

A.

B.

C.

D.

Question 17

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.

Question 18

The MySQL installation includes the mysql_config_editor utility for managing login paths stored in a .mylogin.cnf file.

Which two are true about the login path feature? (Choose two.)

Options:

A.

mysql_config_editor is the only MySQL-provided utility that can print the values stored in .mylogin.cnf.

B.

A .mylogin.cnf file can store at most one login path.

C.

It provides a FIPS-compliant keyring for storing MySQL login details.

D.

A .mylogin.cnf file can be edited using a text editor, such as vim or Notepad++.

E.

It is an alternative to storing the MySQL login details in a my.cnf file.

F.

It provides means to help avoid accidentally exposing the MySQL login details.

Page: 1 / 5
Total 124 questions
Get 1z0-888 Full Access Download 1z0-888 PDF