1Z1-888 Exam Training Practice Tests (Oracle MySQL 5.7 Database Administrator)]

Question 1

You are using replication and the binary log files on your master server consume a lot of disk space. Which two steps should you perform to safely remove some of the older binary log files? (Choose two.)

A. Edit the .index file to remove the files you want to delete. B. Execute the PURGE BINARY LOGS NOT USED command. C. Use the command PURGE BINARY LOGS and specify a binary log file name or a date and time to remove unused files. D. Ensure that none of the attached slaves are using any of the binary logs you want to delete. E. Remove all of the binary log files that have a modification date earlier than today.

Correct Answer: C,D

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 2

You have just executed a manual backup by using this command:
mysqlbackup -u root -p --socket=/tmp/my.sock --backup-dir=/my/backup/ backup The operation completed without error.
What is the state of this backup and operation required before it is ready to be restored?

A. Backup State = Prepared Backup
Operation = apply-log B. Backup State = Raw Backup
Operation = backupdir-to-image C. Backup State = Prepared Backup
Operation = validate D. Backup State = Raw Backup
Operation = apply-log E. Backup State = Compressed Backup
Operation = copy-back

Correct Answer: B

Question 3

At which stage during query processing does MySQL create a query's execution plan?

A. Parsing B. Optimizing C. Executing D. Authorizing

Correct Answer: B

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 4

There are multiple instances of MySQL Server running on a single OS that is backed up using the mysqlbackup command.
The /etc/my/cnf contains default values, for example, datadir=/var/lib/mysql/, with extra instances having their own separate my.cnf file (for example /etc/mysql/instanceN.cnf) overriding the defaults. A restore of the second instance is attempted from the mysqlbackup archive using this command:
mysqlbackup --backup-dir=/opt/backup/mysql/instance2 copy-back
Upon starting the second MySQL instance, you notice that the data does not match the expected backup.
Which command-line option is required to successfully update the second instance?

A. --backup-instance=/var/lib/mysql/instance2 B. --defaults-file=/etc/mysql/instance2.cnf C. --copy-back-from-log D. --restore=2 E. --instance=/var/lib/mysql/instance2

Correct Answer: B

Question 5

old_alter_table is disabled as shown.
mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table:
mysql> ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing this statement?

A. All data in p1 and p3 partitions is removed, but the table definition remains unchanged. B. All data in p1 and p3 partitions is removed and the table definition is changed. C. Only the first partition (p1) will be dropped because only one partition can be dropped at any time. D. It results in a syntax error because you cannot specify more than one partition in the same statement.

Correct Answer: A

Question 6

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?

A. to allow for point-in-time recovery on the slave B. to allow a backup to be created under reduced load C. to allow the remaining events to be processed on the slave while not receiving new events from the master D. to prevent schema changes from propagating to the slave before they are validated E. to prevent any transaction experiencing a deadlock

Correct Answer: A,B

Question 7

After analysis on the slow query log on a high-end OLTP service, the table identified in the slow queries is:

What are the two most likely reasons for the slowness given this output?

A. The engine type is not appropriate to the application use. B. No indexes are defined. C. Using default values for DATETIME causes table scans. D. Date should be a TIMESTAMP field for better performance. E. The User field is too long for most names.

Correct Answer: A,B

Question 8

You attempt to connect to a MySQL Server by using the mysql client program. However, you receive this notice:

What would you run to fix the issue?

A. the mysql client with the --ignore-password-hashing option B. the install plugin command for the mysql_cleartext_password plugin C. the mysql_upgrade script D. the mysql_secure_installation script to update server security settings E. the mysql client with the --enable-cleartext-plugin option

Correct Answer: D

Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).

Question 9

Exhibit:

What does the possible_keys column in this output denote?

A. if there are any indexes that may be used to solve this query B. whether there are any indexes in your query C. if it is possible for you to include any indexes in your query D. whether there are any indexes on the tables that you are querying

Correct Answer: C

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