Skip to main content

1Z0-888 Real Exam Questions

MySQL 5.7 Database Administrator

155 questions available · Page 1 of 16

Updated Exam DumpsVerified AnswersPass Guarantee

Get Complete Exam Dumps
Question 1 Single choice

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?

  1. A

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

  2. B

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

  3. C

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

    mid-way.

  4. D

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

  5. E

    Secondary indexes no longer fit into the buffer pool.

Show answer and explanation

Correct answer: A

Question 2 Multiple choice

You are remotely logged in to MySQL as the user "backup".

The account has temporarily been granted full privileges WITH GRANT to perform the actions required.

You want to change the password for the locally existing "backup" user.

Select the three commands which will perform the required action. (Choose three.)

  1. A

    SET PASSWORD FOR `backup'@'localhost' = `password';

  2. B

    SET PASSWORD = `password';

  3. C

    SET PASSWORD FOR `backup'@'localhost' = PASSWORD (`password')

  4. D

    ALTER USER USER() IDENTIFIED BY `password';

  5. E

    CREATE USER `backup'@'localhost' IDENTIFIED BY `password';

  6. F

    ALTER USER `backup' IDENTIFIED BY `password';

  7. G

    ALTER USER `backup'@'localhost' IDENTIFIED BY `password';

Show answer and explanation

Correct answers: D, E, G

Question 3 Single choice

Which storage option for MySQL data directory typically offers the worst performance in a highly concurrent, OLTP-heavy, IO-bound workload?

  1. A

    battery-backed locally-attached RAID 5 array

  2. B

    iSCSI Lun

  3. C

    SAN (Fibre Channel) Lun

  4. D

    NFS (Networked File System) mount

Show answer and explanation

Correct answer: A

Question 4 Single choice

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?

  1. A

    Increase the value of table_open_cache_instances.

  2. B

    Decrease the value of table_definition_cache.

  3. C

    Decrease the value of table_open_cache.

  4. D

    Increase the value of table_definition_cache.

  5. E

    Increase the value of table_open_cache.

  6. F

    Decrease the value of table_open_cache instances.

Show answer and explanation

Correct answer: A

Question 5 Single choice

While attempting to set up a new replication slave on host `192.168.0.25' with the user `replication', you
encounter this error:

What should you do to resolve this error?

  1. A

    Add the user [email protected] with the correct password to the master.

  2. B

    Edit the DNS table on the master to include the domain name for the IP address of 192.168.0.25.

  3. C

    Edit the my.ini file on the slave so that the master-host variable is equal to the IP address of the master, and restart the slave.

  4. D

    Add the user [email protected] with the correct password to the slave.

Show answer and explanation

Correct answer: A

Question 6 Single choice

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?

  1. A

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

  2. B

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

  3. C

    There is no password defined in the login path.

  4. D

    The DNS is not configured correctly for slave1 host.

  5. E

    The .mylogin.cnf file is not readable.

Show answer and explanation

Correct answer: E

Question 7 Single choice

One of your colleagues is trying to make a change using the mysql command-line client for his or her application session.

The colleague instant messages you this command:

mysql> SET SESSION max_connections = 200;

Why does the command fail?

  1. A

    max_connections requires the GLOBAL scope.

  2. B

    Its current user does not have the SUPER privilege.

  3. C

    max_connections is not a dynamic variable. You need to change the config file and restart the database.

  4. D

    Users can control only the max_user_connections variable.

Show answer and explanation

Correct answer: A

Question 8 Single choice

Which statement best describes the purpose of the InnoDB buffer pool?

  1. A

    It is amount of buffers available during a transaction.

  2. B

    It caches only the indexes for InnoDB tables.

  3. C

    It caches data and indexes for InnoDB tables.

  4. D

    It holds changes made during a transaction before they are written to the log.

  5. E

    It is a pool of memory for SQL query sort operations from within the InnoDB engine.

Show answer and explanation

Correct answer: C

Explanation

References:
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-optimization.html

Question 9 Single choice

A particular government's security policy is to have very strict data encryption and safety settings. This is done by restricting access based on their own CA authority and limiting access to particular users within a department.

Which method could be used to restrict access as required?

  1. A

    using GRANT ... REQUIRE X509 AND REQUIRE ISSUER `/C=.....' AND REQUIRE SUBJECT `/C=.....'

  2. B

    using GRANT USAGE, X509, .......ON *.* TO user@remotehost IDENTIFIED BY `secret_password'

  3. C

    using GRANT ... REQUIRE SSL for a secure connection

  4. D

    using GRANT USAGE, SSL, .....ON *.* TO user@remotehost IDENTIFIED BY `secret_password'

Show answer and explanation

Correct answer: A

Question 10 Single choice

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

  1. A

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

  2. B

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

  3. C

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

  4. D

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

  5. E

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

Show answer and explanation

Correct answer: E